====== 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


===== Encryption-Only Setup =====

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:

<code>
ssl = on
ssl_cert_file = '/var/lib/postgresql/data/ssl-cert-snakeoil.pem'
ssl_key_file = '/var/lib/postgresql/data/ssl-cert-snakeoil.key'
</code>
Both files must be present in mounted folders.

The bogus certificate / private key can be found here:

<code>
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
</code>
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:

<code>
hostnossl  all  all  0.0.0.0/0     reject
hostnossl  all  all  ::/0          reject
</code>
This will reject all unencrypted ipv{4,6} connections.

Finally docker restart.

(2) Client support

To use encryption with psycopg2 just use:

<code>
conn = psycopg2.connect(db_uri, sslmode='require')
</code>
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//:

<code>
conn = psycopg2.connect(db_uri, sslmode='disable')
</code>
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:

<code>
asyncpg.exceptions.InvalidAuthorizationSpecificationError: pg_hba.conf rejects connection for host "a.b.c.d", user "docker", database "products", SSL off
</code>
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.


===== Python =====

Work in Progress

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

<code>
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')
</code>
HINT: 'The context is created with secure default values.' .. whatever this means {{/dokuwiki/lib/images/smileys/icon_smile.gif|:-)}}

With asyncpg

<code>
connect(, ...)
</code>
connect_kwargs: ssl=ssl_ctx

{{/dokuwiki/lib/images/smileys/fixme.gif|FIXME}}: ensure that the server certificate is validated\\
{{/dokuwiki/lib/images/smileys/fixme.gif|FIXME}}: disallow insecure fallbacks\\
{{/dokuwiki/lib/images/smileys/fixme.gif|FIXME}}: need more details


==== References ====

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