How to examine PostgreSQL server's SSL certificate?

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:

• Git commit
• s_client manpage

Following the idea in Craig Ringer's comment:

One option is to patch openssl s_client to handshake with the PostgreSQL protocol. You can probably also do it with Java, by passing a custom SSLSocketFactory to PgJDBC. I'm not sure there are any simple options.

...I wrote a simple SSL socket factory. I copied the code of PgJDBC's own NonValidatingFactory class and just added code to print the certificates.

Here's what it looked like, when it was all said and done:

import java.security.GeneralSecurityException;
import java.security.cert.X509Certificate;
import java.sql.Connection;

import javax.net.ssl.SSLContext;
import javax.net.ssl.TrustManager;
import javax.net.ssl.X509TrustManager;

import org.postgresql.ds.PGSimpleDataSource;
import org.postgresql.ssl.WrappedFactory;

public class ShowPostgreSQLCert {
    public static void main(String[] args) throws Throwable {
        PGSimpleDataSource ds = new PGSimpleDataSource();
        ds.setServerName( ... );
        ds.setSsl(true);
        ds.setUser( ... );
        ds.setDatabaseName( ... );
        ds.setPassword( ... );
        ds.setSslfactory(DumperFactory.class.getName());
        try (Connection c = ds.getConnection()) { }
    }

    public static class DumperFactory extends WrappedFactory {
        public DumperFactory(String arg) throws GeneralSecurityException {
            SSLContext ctx = SSLContext.getInstance("TLS");
            ctx.init(null, new TrustManager[] { new DumperTM() }, null);
            _factory = ctx.getSocketFactory();
        }
    }

    public static class DumperTM implements X509TrustManager {
        public X509Certificate[] getAcceptedIssuers() { return new X509Certificate[0]; }
        public void checkClientTrusted(X509Certificate[] certs, String authType) { }
        public void checkServerTrusted(X509Certificate[] certs, String authType) {
            for (int i=0; i<certs.length; ++i) {
                System.out.println("Cert " + (i+1) + ":");
                System.out.println("    Subject: " + certs[i].getSubjectX500Principal().getName());
                System.out.println("    Issuer: " + certs[i].getIssuerX500Principal().getName());
            }
        }
    }
}

If you don't want to bother with installing java and compiling, and you already have python, you could try this python script: https://github.com/thusoy/postgres-mitm/blob/master/postgres_get_server_cert.py

I use it to check certificate dates:

postgres_get_server_cert.py example.com:5432 | openssl x509 -noout -dates

Or for the full cert as text:

postgres_get_server_cert.py example.com:5432 | openssl x509 -noout -text