postgres / connection
How I connect to Postgres from an app.
The URL
postgres://user:password@host:5432/dbname?sslmode=verify-full&sslrootcert=/etc/db-ca.crt&channel_binding=requirelibpq-style parameters after ? control TLS. Most drivers accept them.
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. The weaker modes:
disable: no TLS. Fine on a local socket, wrong over a network.require: encrypts, but does not verify the server's identity. A man-in-the-middle can pose as the server.
sslrootcert
verify-full needs the CA that signed the server certificate.
Managed Postgres providers offer the CA bundle for download.
I put it on the machine 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 authentication handshake.
I add it on top of verify-full for defense in depth.