postgres / connection
How I connect to Postgres from an app.
The URL
With a public CA:
postgres://user:password@host:5432/dbname?sslmode=verify-full&sslrootcert=system&channel_binding=require&require_auth=scram-sha-256With a self-signed CA:
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.
Some providers such as Ubicloud sign with a public
CA. The system trust store already trusts the certificate chain. I set
sslrootcert=system instead of naming a file. I do not need to download or
rotate a bundle on the machine.
Older providers use a self-signed CA. The provider console offers the CA bundle
for download. I copy the bundle to the machine and point sslrootcert at the
file path.
libpq supports sslrootcert=system from version 16.
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 require_auth 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.