postgres / connection
Connecting an app to Postgres comes down to a connection URL and its TLS settings. Here is what I set and why.
The URL
postgres://user:password@host:5432/dbname?sslmode=verify-full
Libpq-style parameters after ? control TLS. Most drivers accept it.
sslmode
sslmode decides how much the client trusts the server. Over a
network I use verify-full: encrypt, verify the server certificate
against a CA, and check the hostname. Nothing weaker holds up:
disable: no TLS. Fine on a local socket, wrong over a network.require: encrypts, but does not verify the server's identity. It stops passive eavesdropping while leaving an active man-in-the-middle free to pose as the server.
Only verify-full defeats an active attacker, so it is the setting I
reach for.
sslrootcert
verify-full needs the CA that signed the server certificate:
...?sslmode=verify-full&sslrootcert=/etc/db-ca.crt
Managed Postgres providers offer the CA bundle for download. I place
it on the box and point sslrootcert at it.
channel_binding
channel_binding=require ties authentication to the TLS channel, so a
man-in-the-middle cannot relay the auth handshake. I add it on top of
verify-full for defense in depth:
...?sslmode=verify-full&sslrootcert=/etc/db-ca.crt&channel_binding=require