How to activate SSL (self-signed certs) for PostgreSQL

postgres_ssl_configuration.md

SSL for Postgres connections

In the following we will create a self-signed certificate (without self-signed Root CA certificate, to keep things simple)

postgresql.conf

In /var/lib/postgresql/data:

nano postgresql.conf

# activate ssl 
ssl=on

postgresql.conf

Then update pg_hba.conf with hostssl entries parallel to your host entries.

nano pg_hba.conf

# example entry without ssl
host all all all md5

# example entry with ss
hostssl all all all md5

Create self-signed certificates

In /var/lib/postgresql/data

openssl req -new -x509 -days 3650 -nodes -text -out server.crt -keyout server.key -subj "/CN=postgres-userdata"

chown postgres:postgres server.crt 
chown postgres:postgres server.key
chmod og-rwx server.key

Restart server

Connect with SSL and check

Connect with sslmode=require, e.g.

psql "host=localhost port=5433 user=pguser dbname=testdb sslmode=require"

To check whether SSL works, execute

SELECT * FROM pg_stat_ssl;

# should output something like the following

pid | ssl | version |         cipher         | bits | compression | client_dn | client_serial | issuer_dn 
-----+-----+---------+------------------------+------+-------------+-----------+---------------+-----------
37 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256 | f           |           |               | 

If you get ssl = t, success!

Note: Connecting with sslmode=verify-ca will not work, unless you also generate a Root CA certificate and make it also available to the clients.

Source: https://www.cybertec-postgresql.com/en/setting-up-ssl-authentication-for-postgresql/