How to enable SSL for postgres connections

Using encrypted network connections is not really a choice in a modern environment. But: using an additional encryption phase, and using an encryption handshake will add latency and overhead to a network connection.

This writeup is more a scratchpad for myself, because there are other blogposts that guide you with enabling SSL. However, these did not exactly what I wanted to do.

This blogpost describes a quick and an as simple as possible SSL setup for postgres, such as for a proof of concept. This blogpost does NOT describe an SSL setup with an officially signed certificate and carefully hardened SSL cyphers.

First step: certificates

For SSL, there must be a encryption key (which is the certificate) and the decryption key (which is the key). The way SSL certificates work, is that creating a certificate must involve another encryption and decryption combination which has the special function of being the signing authority (called 'certificate authority'). That way, a certificate can be determined to be legitimate if it's signed by the signing authority you have chosen to trust, and therefore most operating system SSL installs come with a preset bundle of trusted certificate authority certificates.

For a quick and simple setup, we still have to perform the signing of a certificate by an authority. Only we will generate the authority certificates ourself. This setup of signing your own certificate is quite logically named a 'self signed certificate'. This has the downside that any client that does not know about our personal authority alias has that certificate set as authority, will raise an error.

However, if we have control over our test environment, this is not a problem, because we can point the client to our own certificate authority (via its certificate).

These are the commands (for linux) to generate the certificate authority (CA) key and certificate, and then generate the server key and csr (certificate signing request) and then sign the request, creating the certificate:

--CA
openssl genrsa 2048 > ca.key
openssl req -new -x509 -nodes -days 365000 -key ca.key -out ca.cert
--server
openssl req -newkey rsa:2048 -nodes -days 365000 -keyout server.key -out server.csr
openssl x509 -req -days 365000 -set_serial 01 -in server.csr -out server.cert -CA ca.cert -CAkey ca.key

Please mind you must enter some details. The CN alias common name should be set to the hostname.

This results in the following files:

• ca.key -> Certificate authority key. Not needed for common usage.
• ca.cert -> Certificate authority certificate. Needed.
• server.key -> Key file. Needed.
• server.csr -> Signing request. Not needed for common usage.
• server.cert -> Certificate file. Needed.

Second step: postgreSQL configuration

The SSL related files must be made available to the postmaster in order to use these for encryption:

ssl = on
ssl_ca_file = '/var/lib/pgsql/11/data/ssl/ca.cert'
ssl_cert_file = '/var/lib/pgsql/11/data/ssl/server.cert'
ssl_key_file = '/var/lib/pgsql/11/data/ssl/server.key'

This is not all that is needed. The pg_hba.conf must allow SSL. That is configured with the first argument, the connection type, which must allow SSL. The common type 'host' allows both SSL and non-SSL. The type 'hostssl' enforces SSL and the type 'hostnossl' explicitly disallows SSL.

With the type 'host' both SSL and non-SSL can be served from the same port.

Optional third step: pgbouncer

If you use pgbouncer, you can enable SSL for pgbouncer too. Pgbouncer requires the same files (certificate authority and server certificate, and the server key file) to serve to its clients:

client_tls_sslmode = allow
client_tls_ca_file = /tmp/ca.cert
client_tls_key_file = /tmp/server.key
client_tls_cert_file = /tmp/server.cert

When pgbouncer provides SSL, it can use a non-SSL connection to the database (or a socket connection), and thus have pgbouncer work as an SSL terminator.

If pgbouncer must talk SSL to the database too, it requires additional settings, which start with 'server_tls', alike above 'client_tls' parameters.

Comments

[Tom Harrison]

Thanks!

Change permissions on SSL files

Note that by default the key files will have open permissions. Need to run something like this to give the files rw on owner, only.

chown 600 server.key ca.cert server.cert

macos / Homebrew users

• in your homebrew root (/opt/homebrew for new macs, /usr/local/ for older)
• change to var/postgresql@vv where vv is version you have installed, e.g. 15
• make and change to a subdir ssl then run commands to generate keys/certs as per op
• brew services stop postgresql@vv
• brew services start postgresql@vv
• confirm all well with psql postgres
• see logs in /opt/homebrew/var/log/postgresql@vv

[Hoàng Trần Bá]

I have to log in to say a big thanks. I was totally fucked up by useless instructions and gpt response. You literally f*cking saved my life! Don't know how these shit outside can even work, sometimes a simplest idea like using cert file instead of password is horriblly guided.

[FORCHA]

great job. please provide file path and os commands