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=require&require_auth=scram-sha-256libpq-style parameters after ? control TLS and authentication. 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.
require_auth
The parameters above decide whether the client trusts the server. The
server still chooses the authentication method, and the client answers
what it is asked. A rogue server can ask for password, which sends
the password in the clear, and get it.
require_auth names the methods the client accepts. I set
require_auth=scram-sha-256, so the client refuses anything weaker
and the exchange proves the password without sending it. The parameter
takes a comma-separated list, and a leading ! on every entry inverts
it.
verify-full already rejects a server without the right certificate,
so this covers the server that holds one: a provider's host I do not
run, or a proxy in front of it. It matters most under sslmode=prefer,
where a downgrade to no TLS otherwise leads to a downgrade to a weaker
method.
libpq reads require_auth from version 16, and
pgx from version 5.10.