How to examine PostgreSQL server's SSL certificate?

Question

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.)

Answer 1

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

Answer 2

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

Answer 3

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());
            }
        }
    }
}

Answer 4

csd's answer really saved me. Here is a more detailed walkthrough for those of us who don't know or have forgotten java.

1. Make sure your server can compile java. Try the command "which javac", if it outputs something like "... no javac in ..." then you need to install a JDK (JRE won't work, it has "java" but not "javac").

2. Install postgresql-jdbc if you don't have it already. For RHEL6 the command is "yum install postgresql-jdbc". Figure out where the jar files are installed. There will be several of them, one for each version. I used "/usr/share/java/postgresql-jdbc3.jar".

3. Copy csd's code and insert database information (the other answer), or use my slightly modified version at the end of this answer. Save it in a file called exactly "ShowPostgreSQLCert.java". Upper/lowercase matters, call it anything else and it won't compile.

4. In the directory with the ShowPostgreSQLCert.java file, run the following command (modify the location of postgresql-jdbc3.jar if needed): "javac -cp /usr/share/java/postgresql-jdbc3.jar ShowPostgreSQLCert.java". You should now have 3 .class files in the same directory.

5. Finally, run the following command: "java -cp .:/usr/share/java/postgresql-jdbc3.jar ShowPostgreSQLCert". The "." after "-cp" means it should look in the current dir for the .class files. You can insert the full path to the class files here, just remember to keep the ":" between the path and the location of the .jar file.

6. If you need to run the command on a different machine you need to have the same jar file installed (postgresql-jdbc3.jar), or you can probably just copy it from the server you compiled the .class files on. Then just copy the .class files over and run the command from 5. after modifying the paths.

I slightly modified the code so that you can pass database information on the command line instead of compiling it into the .class file. Just run it without any arguments and it will display a message showing which arguments it expects. csd's code + modifications is:

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();
        if( args.length != 4 ) {
            System.out.println("Not enough arguments. Usage: ShowPostgreSQLCert ServerName User DatabaseName Password");
            System.exit(1);
        }
        ds.setServerName( args[0] );
        ds.setSsl(true);
        ds.setUser( args[1] );
        ds.setDatabaseName( args[2] );
        ds.setPassword( args[3] );
        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());
            }
        }
    }
}

Answer 5

I added some code from https://stackoverflow.com/questions/3313020/write-x509-certificate-into-pem-formatted-string-in-java to output the certificates as PEM, and removed the need to specify a db, username or password (they are not needed to get the certificate).

Using this, I was able to verify that a restart of PostgreSQL does unfortunately seem necessary to switch to a new certificate.

Not being a Java developer, my steps to build and run are probably not so great, but they work, so long as you can find a postgresql jdbc

# locate postgresql | grep jar
/path/to/a/lib/postgresql-9.1-901-1.jdbc4.jar   <-- this one will do
...

To compile:

javac -cp /path/to/a/lib/postgresql-9.1-901-1.jdbc4.jar ./ShowPostgreSQLCert.java

To run:

java -cp /path/to/a/lib/postgresql-9.1-901-1.jdbc4.jar:. ShowPostgreSQLCert 127.0.0.1

Sample output:

Cert 1:
    Subject: CN=...
    Issuer: CN=...
    Not Before: Fri Oct 21 11:14:06 NZDT 2016
    Not After: Sun Oct 21 11:24:00 NZDT 2018
-----BEGIN CERTIFICATE-----
MIIHEjCCBfqgAwIBAgIUUbiRZjruNAEo2j1QPqBh6GzcNrwwDQYJKoZIhvcNAQEL
...
IcIXcVQxPzVrpIDT5G6jArVt+ERLEWs2V09iMwY7//CQb0ivpVg=
-----END CERTIFICATE-----

Cert 2:
...

Source:

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;

import javax.xml.bind.DatatypeConverter;
import java.security.cert.X509Certificate;
import java.io.StringWriter;

public class ShowPostgreSQLCert {
    public static void main(String[] args) throws Throwable {
        PGSimpleDataSource ds = new PGSimpleDataSource();
        if( args.length != 1 ) {
            System.out.println("Not enough arguments.");
            System.out.println("Usage: ShowPostgreSQLCert ServerName");
            System.exit(1);
        }
        ds.setServerName( args[0] );
        ds.setSsl(true);
        ds.setUser( "" );
        ds.setDatabaseName( "" );
        ds.setPassword( "" );
        ds.setSslfactory(DumperFactory.class.getName());
        try (Connection c = ds.getConnection()) { }
        catch (org.postgresql.util.PSQLException e) {
            // Don't actually want to login
        }
    }

    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 String certToString(X509Certificate cert) {
        StringWriter sw = new StringWriter();
        try {
            sw.write("-----BEGIN CERTIFICATE-----\n");
            sw.write(DatatypeConverter.printBase64Binary(cert.getEncoded()).replaceAll("(.{64})", "$1\n"));
            sw.write("\n-----END CERTIFICATE-----\n");
        } catch (java.security.cert.CertificateEncodingException e) {
            e.printStackTrace();
        }
        return sw.toString();
    }

    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());
                System.out.println("    Not Before: " + certs[i].getNotBefore().toString());
                System.out.println("    Not After: " + certs[i].getNotAfter().toString());

                System.out.println(certToString(certs[i]));
            }
        }
    }
}