December 13, 2023
Summary: in this tutorial, you will learn how to enable certificate authentication using SSL in PostgreSQL.
As additional security, you may wish to enable certificate authentication using SSL certificates. When it is enabled, every client connection will be authenticated through an additional certificate exchange. For this purpose, a client certificate must be used for identifying the client. The client certificates must be copied to the remote server (or an application server) to use this method. In this tutorial, we shall discuss the steps involved in enabling certificate authentication using SSL.
Getting ready
In order to enable SSL, we must have the server and client certification files that are signed by a CA. This is usually performed by the security teams in most organizations. Admins could use OpenSSL to generate the certificates.
How to do it…
We will enable certificate authentication using the following steps:
1. Copy the client certificate signed by the CA to the remote server:
$ scp postgresql.crt postgresql.key rootCA.crt postgres@192.168.130.1:/var/lib/postgresql
On the remote server, modify the permissions of the certificates:
$ chmod 0400 postgresql.crt postgresql.key rootCA.crt
2. Modify the authentication method in the pg_hba.conf file of the database server to enable certification authentication:
$ vi $PGDATA/pg_hba.conf
hostssl postgres postgres 192.168.130.1 cert clientcert=1
3. Perform a reload to get the changes into effect:
$ psql -c "select pg_reload_conf()"
4. Validate the connection from the remote host using the client certificates:
$ psql "host=192.168.130.70 user=postgres port=5432 sslcert=postgresql.crt sslkey=postgresql.key sslrootcert=rootCA.crt"
This results in the following output:
$ psql "host=192.168.130.70 user=postgres port=5432 sslcert=postgresql.crt sslkey=postgresql.key sslrootcert=rootCA.crt"
Password for user postgres:
psql (12.4 (Ubuntu 12.4-1.pgdg18.04+1))
Type "help" for help.
postgres=#
How it works…
To enable certificate authentication using SSL, the additional requirement is that the client certificates need to be copied to the remote server or the application server from where the connections are considered to be using this authentication mode. Once copied, the only change to the pg_hba.conf file, as seen in step 2, is that the authentication mode must be replaced with cert clientcert=1. Once this is done, we could issue a reload to get the changes into effect and validate the connection from the remote server. As you see in step 4, we must now add sslcert, sslkey, and sslrootcert to the psql connection URI. These certificates are used to perform the authentication between the databases. It does not mean that the password is no longer needed. The server should supply the password set to that user for a successful connection.