Authentication with SSL Client Certificates

You can use SSL (or as we should call it these days TLS) quite happily without using client certificates. In fact, the most widespread use of TLS almost never involves client certificates. That's your web browser, and you're using SSL right now in connecting to this web site, without using a client certificate. Client certificates can be cumbersome to use, and getting a certificate that is going to be trusted by arbitrary web servers is difficult, as you have to prove your identity to the issuing authority. That's why web sites instead demand that you log in using a password and/or some other form of identification such as a Yubikey or a TOTP like Authy.

Things are a bit different in the Postgres world, though. There, especially in corporate or government settings, administrators usually have control over both ends of a connection, making deployment of client certificates more feasible. Client certificate use can either replace or supplement other forms of authentication. If the authentication mechanism in your pg_hba.conf file is cert then only client certificate authentication will be used. But if instead you add clientcert=verify-full as an option to some other authentication mechanism such as scram-sha-256 then it will be used in addition to that authentication method, giving you two factor authentication. Note that both these options can only be used with hostssl lines in pg_hba.conf.

What are the requirements for using client certificates? Essentially, they must be signed by the Certificate Authority whose public key is stored on the server in the location specified by the ssl_ca_file configuration parameter. And they must have user's name in the CN (Common Name) field of the certificate's subject (or, if the clientname=DN option is used, the user name must match the entire subject). What is being asserted with the use of the certificate is that as the connecting user has the private key that goes along with the certificate being presented, they are in fact the user named in the certificate. That last part isn't true if you use verify-ca instead of verify-full, but I strongly recommend against using that except possibly for testing. After all, the whole point of using client certificates is to verify the identity of the connecting user. Note that verify-full is implied by the cert authentication method. Note too that this name requirement means you can't use a server SSL certificate as a client certificate. And you shouldn't want to. You should keep a server certificate (or specifically its key) very closely held. However, I have all too frequently seen people trying to use a server certificate on the client side.

For libpq clients such as psql, the client certificate and its key belong in ~/.postgresql/ on Unix or %APPDATA%\postgresql on Windows, and be called postgresql.crt and postgresql.key respectively. If you want to use alternative locations, they can be specified using the sslcert and sslkey connection string parameters. Use with JDBC is almost identical except that you will need a PKCS#8 key instead of the regular SSL key file, normally called postgresql.pk8.

Five years ago I gave a talk on using SSL with Postgres, including the use of client certificates. As part of preparing for that talk I created some scripts that demonstrate their use, along with examples of setting Certificate Authority and integration with pgbouncer. These scripts are possibly a bit dated, but still illustrative of how you can set up to use Client Certificates successfully.