Setting up SSL authentication for PostgreSQL

PostgreSQL is a secure database and we want to keep it that way. It makes sense, then, to consider SSL to encrypt the connection between client and server. This posting will help you to set up SSL authentication for PostgreSQL properly, and hopefully also to understand some background information to make your database more secure.

At the end of this post, you should be able to configure PostgreSQL and handle secure client server connections in the easiest way possible.

Configuring PostgreSQL for OpenSSL

The first thing we have to do to set up OpenSSL is to change postgresql.conf. There are a couple of parameters which are related to encryption:

 ssl = on
#ssl_ca_file = ''
#ssl_cert_file = 'server.crt'
#ssl_crl_file = ''
#ssl_key_file = 'server.key'
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
#ssl_prefer_server_ciphers = on
#ssl_ecdh_curve = 'prime256v1'
#ssl_min_protocol_version = 'TLSv1.2'
#ssl_max_protocol_version = ''
#ssl_dh_params_file = ''
#ssl_passphrase_command = ''
#ssl_passphrase_command_supports_reload = off 

Once ssl = on, the server will negotiate SSL connections in case they are possible. The remaining parameters define the location of key files and the strength of the ciphers. Please note that turning SSL on does not require a database restart. The variable can be set with a plain reload. However, you will still need a restart, otherwise PostgreSQL will not accept SSL connections. This is an important point which frequently causes problems for users:

postgres=# SELECT pg_reload_conf();
  pg_reload_conf
----------------
  t
(1 row)

postgres=# SHOW ssl;
  ssl
-----
  on
(1 row)

The SHOW command is an easy way to make sure that the setting has indeed been changed. Technically, pg_reload_conf() is not needed at this stage. It is necessary to restart later anyway. We just reloaded to show the effect on the variable.

In the next step, we have to adjust pg_hba.conf to ensure that PostgreSQL will handle our connections in a secure way:

# TYPE    DATABASE     USER      ADDRESS           METHOD

# "local" is for Unix domain socket connections only
local     all          all                         peer
host      all          all       127.0.0.1/32      scram-sha-256
host      all          all       ::1/128           scram-sha-256
hostssl   all          all       10.0.3.0/24       scram-sha-256

Then restart the database instance to make sure SSL is enabled.

The next thing you have to do is to create certificates.

In order to keep things simple, we will simply create self-signed certificates here. However, it is of course also possible with other certificates are. Here is how it works:

[postgres@node1 data]$ openssl req -new -x509 -days 365 \
     -nodes -text -out server.crt \
     -keyout server.key \
     -subj "/CN=cybertec-postgresql.com"
Generating a RSA private key
.......+++++
....................................................+++++
writing new private key to 'server.key'
-----

This certificate will be valid for 365 days.

Set permissions

Next we have to set permissions to ensure the certificate can be used. If those permissions are too relaxed, the server will not accept the certificate:

[postgres@node1 data]$ chmod og-rwx server.key

Self-signed certificates are nice. However, to create a server certificate whose identity and origin can be validated by clients, first create a certificate signing request and a public/private key file:

[postgres@node1 data]$ openssl req -new -nodes -text \
     -out root.csr \
     -keyout root.key \
     -subj "/CN=cybertec-postgresql.com"
Generating a RSA private key
.................................+++++
....................+++++
writing new private key to 'root.key'
-----

Again, we have to make sure that those permissions are exactly the way they should be:

[postgres@node1 data]$ chmod og-rwx root.key

Then we sign the request.

To do that with OpenSSL, we first have to find out where openssl.cnf can be found. We have seen that it is not always in the same place – so make sure you are using the right path:

[postgres@node1 data]$ find / -name openssl.cnf \
     2> /dev/null
/etc/pki/tls/openssl.cnf

We use this path when we sign the request:

[postgres@node1 data]$ openssl x509 -req -in root.csr \
     -text \
     -days 3650 \
     -extfile /etc/pki/tls/openssl.cnf \
     -extensions v3_ca \
     -signkey root.key -out root.crt
Signature ok
subject=CN = cybertec-postgresql.com
Getting Private key

Let’s create the certificate with the new root authority:

[postgres@node1 data]$ openssl req -new -nodes -text \
     -out server.csr \
     -keyout server.key \
     -subj "/CN=cybertec-postgresql.com"
Generating a RSA private key
.....................+++++
...........................+++++
writing new private key to 'server.key'
[postgres@node1 data]$ chmod og-rwx server.key
[postgres@node1 data]$ openssl x509 -req \
     -in server.csr -text -days 365 \
     -CA root.crt -CAkey root.key -CAcreateserial \
     -out server.crt
Signature ok
subject=CN = cybertec-postgresql.com
Getting CA Private Key

server.crt and server.key should be stored on the server in your data directory as configured on postgresql.conf.

But there’s more: root.crt should be stored on the client, so the client can verify that the server’s certificate was signed by the certification authority. root.key should be stored offline for use in creating future certificates.

The following files are needed:

Checking your setup

Now that all the certificates are in place it is time to restart the servers:

[root@node1 ~]# systemctl restart postgresql-13

Without a restart, the connection would fail with an error message (“psql: error: FATAL: no pg_hba.conf entry for host "10.0.3.200", user "postgres", database "test", SSL off”).

However, after the restart, the process should work as expected:

[root@node1 ~]# su - postgres
[postgres@node1 ~]$ psql test -h 10.0.3.200
Password for user postgres:
psql (13.2)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

test=#

psql indicates that the connection is encrypted. To figure out if the connection is indeed encrypted, we need to check the content of pg_stat_ssl:

postgres=# \d pg_stat_ssl

View "pg_catalog.pg_stat_ssl"
Column          | Type    | Collation | Nullable | Default
----------------+---------+-----------+----------+---------
pid             | integer |           |          |
ssl             | boolean |           |          |
version         | text    |           |          |
cipher          | text    |           |          |
bits            | integer |           |          |
compression     | boolean |           |          |
client_dn       | text    |           |          |
client_serial   | numeric |           |          |
issuer_dn       | text    |           |          |

Let us query the system view and see what it contains:

test=# \x
Expanded display is on.
test=# SELECT * FROM pg_stat_ssl;
-[ RECORD 1 ]-+-----------------------
pid           | 16378
ssl           | t
version       | TLSv1.3
cipher        | TLS_AES_256_GCM_SHA384
bits          | 256
compression   | f
client_dn     |
client_serial |
issuer_dn     |

The connection has been successfully encrypted. If “ssl = true”, then we have succeeded.

Different levels of SSL supported by PostgreSQL

Two SSL setups are not necessarily identical. There are various levels which allow you to control the desired level of security and protection. The following table outlines those SSL modes as supported by PostgreSQL:

The overhead really depends on the mode you are using. First let’s take a look at the general mechanism:

The main question now is: How does one specify the mode to be used? The answer is: It has to be hidden as part of the connect string as shown in the next example:

[postgres@node1 data]$ psql "dbname=test host=10.0.3.200 user=postgres password=1234 sslmode=verify-ca"
psql: error: root certificate file "/var/lib/pgsql/.postgresql/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.

In this case, verify-ca does not work because to do that the root.* files have to be copied to the client, and the certificates have to be ones which allow for proper validation of the target server.

Encrypting your entire server: PostgreSQL TDE

So far, you have learned how to encrypt the connection between client and server. However, sometimes it is necessary to encrypt the entire server, including storage. PostgreSQL TDE does exactly that:

To find out more, check out our website about PostgreSQL TDE. We offer a fully encrypted stack to help you achieve maximum security. PostgreSQL TDE is available for free (Open Source).

What might also interest you …

More recent information about encryption keys: Manage Encryption Keys with PostgreSQL TDE

Materialized views are an important feature in most databases, including PostgreSQL. They can help to speed up large calculations, or at least to cache them.

If you want to make sure that your materialized views are up-to-date, and if you want to read more about PostgreSQL right now, check out our blog about pg_timetable, which shows you how to schedule jobs in PostgreSQL. Why is pg_timetable so useful? Our scheduler makes sure that identical jobs cannot overlap, but simply don’t execute again in case the same job is already running. In the case of long jobs, using a scheduler is super important – especially if you want to use materialized views.