Postgres extension to get information about SSL certificates.
* Create extensions if not exists.
testdb> SELECT * FROM pg_available_extensions WHERE name = 'ssl_info';
+------+-----------------+-------------------+---------+
| name | default_version | installed_version | comment |
|------+-----------------+-------------------+---------|
+------+-----------------+-------------------+---------+
SELECT 0
Time: 0.228s
testdb> create extension sslinfo;
CREATE EXTENSION
Time: 0.246s
testdb> select ssl_is_used();
+-------------+
| ssl_is_used |
|-------------|
| True |
+-------------+
SELECT 1
Time: 0.220s
testdb> select ssl_cipher();
+-----------------------------+
| ssl_cipher |
|-----------------------------|
| ECDHE-RSA-AES256-GCM-SHA384 |
+-----------------------------+
SELECT 1
Time: 0.218s
testdb> select ssl_version();
+-------------+
| ssl_version |
|-------------|
| TLSv1.2 |
+-------------+
SELECT 1
Time: 0.218s
testdb> select * from pg_stat_ssl;
+-------+------+---------+-----------------------------+------+-------------+-----------+---------------+-----------+
| pid | ssl | version | cipher | bits | compression | client_dn | client_serial | issuer_dn |
|-------+------+---------+-----------------------------+------+-------------+-----------+---------------+-----------|
| 10441 | True | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | False | <null> | <null> | <null> |
| 17043 | True | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | False | <null> | <null> | <null> |
| 11171 | True | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | False | <null> | <null> | <null> |
+-------+------+---------+-----------------------------+------+-------------+-----------+---------------+-----------+
SELECT 3
Time: 0.228s
* Select extension if exists
defaultdb> SELECT * FROM pg_available_extensions WHERE name = 'sslinfo';
+---------+-----------------+-------------------+------------------------------------+
| name | default_version | installed_version | comment |
|---------+-----------------+-------------------+------------------------------------|
| sslinfo | 1.2 | <null> | information about SSL certificates |
+---------+-----------------+-------------------+------------------------------------+
SELECT 1
Time: 0.240s
#security #postgres #databases