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