Using SSL certificates to authenticate

This recipe shows you how to set up your PostgreSQL system so that it requires clients to present a valid X.509 certificate before allowing them to connect.

This can be used as an additional security layer to use double authentication, where the client must both have a valid certificate to set up the SSL connection and also know the database user's password. It can also be used as the sole authentication method, where the PostgreSQL server will first verify the client connection using the certificate presented by the client, and then retrieve the username from the same certificate.

Getting ready

Get, or generate, a root certificate and a client certificate to be used by the connecting client.

How to do it…

For testing purposes, or for just setting up a single trusted user, you can use a self-signed certificate:

openssl  genrsa  2048  >  client.key
openssl  req  -new  -x509  -key  server.key  -out  client.crt

On the server, set up a line in pg_hba.conf file...