Postgresql – How to examine PostgreSQL server’s SSL certificate

postgresqlpostgresql-9.4ssl

Suppose there is a PostgreSQL server running and it has SSL enabled. Using "standard" Linux and PostgreSQL tools, how can I examine its SSL certificate?

I'm hoping for output similar to what you would get from running openssl x509 -text .... And I'm hoping for a one- or two-liner command line answer so I don't have to resort to running a packet sniffer.

I do not have access to the PostgreSQL server, so I cannot look at its configuration files directly.

I do not have a superuser login, so I can't get the value of the ssl_cert_file setting and then pg_read_file on it.

Using openssl s_client -connect ... doesn't work because PostgreSQL doesn't seem to want to do the SSL handshake right away.

From a quick look at the psql documentation, I could not find a command-line parameter that makes it show that information on startup. (Though it does show me certain cipher information.)

Best Answer

It looks like OpenSSL's s_client tool added Postgres support using the -starttls in 1.1.1, so you can now use the full power of OpenSSL's command line tools without additional helper scripts:

openssl s_client -starttls postgres -connect my.postgres.host:5432 # etc...

References:

Related Question