December 13, 2023
Summary: in this tutorial, you will learn how to setup the encryption of data over communication using SSL in PostgreSQL.
Table of Contents
Introduction
It may be one of the most important requirements to enable encryption of communication between the client and the server when dealing with critical financial databases and also to satisfy certain compliances. PostgreSQL satisfies this requirement by allowing connections to use SSL. In this tutorial, we shall discuss the steps involved in setting up the encryption of data moving over the wire.
Getting ready
In order to enable SSL, we must have the server and client certification files that are signed by a Certification Authority (CA). This is usually performed by the security teams in most organizations. Admins could use OpenSSL to generate the certificates. Once they are created, we should set the following parameters in PostgreSQL:
-
ssl_ca_file
: Specifies the name of the file containing the SSL server CA -
ssl_cert_file
: Specifies the name of the file containing the SSL server certificate -
ssl_key_file
: Specifies the name of the file containing the SSL server private key
We must also have OpenSSL installed on the server:
$ sudo yum install openssl
How to do it…
Let’s get started by using the following steps:
1. Ensure that the permissions set to the files are appropriate. Grant read access to the postgres
user only:
$ cd $PGDATA
$ chmod 0400 server.crt server.key rootCA.crt
2. Enable SSL-related parameters in PostgreSQL:
$ psql -c "ALTER SYSTEM SET ssl TO 'ON'"
$ psql -c "ALTER SYSTEM SET ssl_ca_file TO 'rootCA.crt'"
$ psql -c "ALTER SYSTEM SET ssl_cert_file TO 'server.crt'"
$ psql -c "ALTER SYSTEM SET ssl_key_file TO 'server.key'"
3. Perform a reload to get the changes into effect:
$ psql -c "select pg_reload_conf()"
4. Validate by enabling SSL for connections over a socket. Add the following entry to the pg_hba.conf
file. Make sure to add it to the top to avoid the already existing entries to get it into effect:
$ vi $PGDATA/pg_hba.conf
hostssl all all 127.0.0.1/32 trust
Perform a reload to get the changes into effect:
$ psql -c "select pg_reload_conf()"
Validate using psql
:
$ psql -h localhost
psql (13.1)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCMSHA384, bits: 256, compression: off)
Type "help" for help.
postgres=# \q
5. For enabling SSL for remote connections, add the IP of the remote host to the pg_hba.conf
file and specify hostssl
. In this example, 192.168.130.70
is the IP of the database server and 192.168.130.1
is the IP of a remote server.
Add the following entry to the pg_hba.conf
file:
$ vi $PGDATA/pg_hba.conf
hostssl postgres postgres 192.168.130.1 md5
Perform a reload to get the changes into effect:
$ psql -c "select pg_reload_conf()"
Validate from the remote host:
$ psql -h 192.168.130.70 -d postgres -U postgres
Password for user postgres:
psql (13.1 (Ubuntu 13.1-1.pgdg18.04+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCMSHA384, bits: 256, compression: off)
Type "help" for help.
postgres=# \q
How it works…
As seen in the preceding steps, the server and client certification files that are signed by a CA are a must to enable SSL. Once they are in place, they need to be copied to a location that is accessible by the postgres
user and only readable by the postgres
user. Once they are made available, we could set the parameters that are seen in step 2 to set SSL to ON
and also to set the location of the certificates. It requires a reload but not a restart to get the changes into effect. So, SSL mode can be enabled on an existing database server without the need for a restart.
To validate SSL, as seen in steps 4 and 5, we could add hostssl
as a prefix instead of host
in the pg_hba.conf
file. We need to add the appropriate IP from where the communications to the database should be encrypted over SSL.
By default, all the connections are sent over SSL when it is enabled. To avoid establishing communication over SSL for a certain instance, use hostnossl
instead of hostssl
as a prefix, as seen in the following log:
hostnossl postgres postgres 192.168.130.1 md5