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 with the hostssl method and the clientcert option set to 1:

hostssl  all    all    0.0.0.0/0         md5  clientcert=1

Put the client root certificate in the root.crt file in the server data directory ($PGDATA/root.crt). This file may contain multiple trusted root certificates.

If you are using a central certificate authority, you probably also have a certificate revocation list, which should be put in a root.crl file and regularly updated.

On the client, put the client's private key and certificate in ~/.postgresql/postgresql.key and ~/.postgresql/postgresql.crt. Make sure that the private key file is not world-readable or group-readable by running the following command:

chmod 0600 ~/.postgresql/postgresql.key

On the Windows client, the corresponding files are %APPDATA%\postgresql\postgresql.key  and %APPDATA%\postgresql\postgresql.crt. No permission check is done, as the location is considered secure.

If the client certificate is not signed by the root CA but by an intermediate CA, then all the intermediate CA certificates up to the root certificate must be placed in the postgresql.crt file as well.