PostgreSQL: SSL Connections

Overview: https://www.postgresql.org/docs/12/ssl-tcp.html

A blog that is likely based on the link above: https://blog.crunchydata.com/blog/ssl-certificate-wauthentication-postgresql-docker-containers

And yet another blog: https://medium.com/@bansal.nidhi/postgresql-authentication-without-password-52dac3d65746

Keywords: postgres sql ssl certificate

The purpose of this mode is to be as lightweight as possible, but without actually using certificates.

The setup consists of the server part (1) and the client part (2):

(1) Server setup

In the postgresql.conf, disable 'ssl = off' if present and replace it with:

ssl = on
ssl_cert_file = '/var/lib/postgresql/data/ssl-cert-snakeoil.pem'
ssl_key_file = '/var/lib/postgresql/data/ssl-cert-snakeoil.key'

Both files must be present in mounted folders.

The bogus certificate / private key can be found here:

ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'

The path should work for Debian-based systems and might be different otherwise.

HINT: the file permission for the key must be like 600

To prevent non-SSL connections altogether, modify the pg_hba.conf:

hostnossl  all  all  0.0.0.0/0     reject
hostnossl  all  all  ::/0          reject

This will reject all unencrypted ipv{4,6} connections.

Finally docker restart.

(2) Client support

To use encryption with psycopg2 just use:

conn = psycopg2.connect(db_uri, sslmode='require')

It should be noted that psycopg2 might use sslmode='require' automatically. To verify that you cannot login without SSL, check that the call will fail:

conn = psycopg2.connect(db_uri, sslmode='disable')

For asyncpg the setup is almost the same. The option is also named ssl and it is also automatically detected, if SSL is required. The negative test is the same as for psycopg2.

HINT: asyncpg 0.22 or newer is required for the auto-ssl mode.

An error looks like this:

asyncpg.exceptions.InvalidAuthorizationSpecificationError: pg_hba.conf rejects connection for host "a.b.c.d", user "docker", database "products", SSL off

AGAIN: no certificate-based authentication is performed, but everything, including passwords, is now encrypted. This means everybody with the correct password is allowed to login.

Work in Progress

Client(!) code: ssl.Purpose.SERVER_AUTH forces server authentication

import ssl    

ssl_ctx = ssl.create_default_context(ssl.Purpose.SERVER_AUTH, cafile='/path_to_client_root_ca')
ssl_ctx.load_cert_chain('/path_to_client_public_key.pem', '/path_to_client_private_key.pem')

HINT: 'The context is created with secure default values.' .. whatever this means

With asyncpg

connect(, ...)

connect_kwargs: ssl=ssl_ctx

: ensure that the server certificate is validated
: disallow insecure fallbacks
: need more details

https://docs.python.org/3/library/ssl.html#ssl.CERT_REQUIRED
https://magicstack.github.io/asyncpg/current/api/index.html
https://www.electricmonk.nl/log/2018/06/02/ssl-tls-client-certificate-verification-with-python-v3-4-sslcontext/
https://stackoverflow.com/questions/41701791/aiohttp-and-client-side-ssl-certificates
https://www.cybertec-postgresql.com/en/setting-up-ssl-authentication-for-postgresql

Keywords psycopg2 encryption ssl cert