Add SSL/TLS certificate check?

[ng-pe]

Hello,

I'm thinking of adding a control to check the SSL/TLS certificate of the PostgreSQL instance:

• check SSL/TLS fingerprint
• check of expiration date
• check if SSL is activate or not

I've made a small example to illustrate how information retrieval works (https://gist.github.com/ng-pe/a88da92b7061bbaceff441ac8dca62e2)
This is done by connecting directly to the PostgreSQL instance via socket, starting StartTLS, and using the Net::SSLeay lib.

What do you think?
Do you think this type of check is appropriate here? (... that this check will add an additional dependency to a Perl library.)

If yes, I will propose an MR integrating this new service ;)

Regards,
Nicolas

[rjuju]

Wouldn't it be better to rely on https://www.postgresql.org/docs/current/sslinfo.html? Sure it needs an extra extension to be installed, but if anyone cares about it it's trivial to install, at that way we won't have to maintain a lot of code to get the same info.

[ng-pe]

Hi,

Indeed, it would have been easy if only this extension (sslinfo contrib) provided information about the 'server' certificate: this extension provides information about the 'client' certificate, which is used for authentication, among other things.

Eg.:

I'm connected to my Postgresql over TLS with psql :

postgres=# select ssl_is_used() as ssl_is_used, ssl_version() as ssl_version, ssl_cipher() as ssl_cipher, ssl_client_cert_present() as ssl_client_cert_present, ssl_client_serial() as ssl_client_serial, ssl_client_dn() as ssl_client_dn, ssl_issuer_dn() as ssl_issuer_dn;
-[ RECORD 1 ]-----------+------------------------------
ssl_is_used             | t
ssl_version             | TLSv1.2
ssl_cipher              | ECDHE-ECDSA-CHACHA20-POLY1305
ssl_client_cert_present | f
ssl_client_serial       | 
ssl_client_dn           | 
ssl_issuer_dn           | 

Unfortunately, this extension does not allow you to obtain server certificate information.

Nothing in the "pg_stat_ssl" view either:

postgres=# select * from pg_stat_ssl where pid = pg_backend_pid();
-[ RECORD 1 ]-+------------------------------
pid           | 531898
ssl           | t
version       | TLSv1.2
cipher        | ECDHE-ECDSA-CHACHA20-POLY1305
bits          | 256
client_dn     | 
client_serial | 
issuer_dn     | 

Nicolas