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-256
With a private CA:
postgres://user:password@host:5432/dbname?sslmode=verify-full&sslrootcert=/etc/db-ca.crt&channel_binding=require&require_auth=scram-sha-256
I use these libpq-style parameters with libpq and pgx.
Client versions matter as well as the URL.
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. I reserve it for local development.allow: tries plaintext first, then TLS.prefertries TLS first. Both permit plaintext and skip certificate checks.preferis the default.require: requires TLS but does not check the hostname. With a CA file, it checks the certificate chain likeverify-ca.verify-ca: checks the certificate chain but not the hostname.
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.
Some providers use a private 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.
A URI from a CLI
A provider CLI or console can print a connection URI with no TLS
parameters. libpq then uses the default sslmode=prefer, and the
connection can be plaintext. My code parses that URI and sets
sslmode=verify-full, sslrootcert, and channel_binding=require
before it gives the URI to a client. Those values replace any TLS value
in the printed URI.
A parameter in the URI wins over the PGSSLMODE and
PGCHANNELBINDING environment variables, so a shell setting cannot
weaken the connection. libpq refuses to connect when the sslrootcert
file is missing.
A CA certificate is public. When the provider uses a private CA, I
commit its root certificate to the repository, next to the code that
names it.
postgres / dev-test-clusters
shows this for pg_dump.
A CA set in code
pgx reads sslrootcert as a file path or the word system. The
uni guest has no files and no system trust store. So I set
RootCAs on the parsed config in Go, and I do not use sslrootcert.
I require verified TLS on every connection target before I set its roots.
cfg, err := pgxpool.ParseConfig(databaseURL)
if err != nil {
log.Fatal("invalid database URL")
}
roots := x509.NewCertPool()
if !roots.AppendCertsFromPEM(rootCertPEM) {
log.Fatal("database CA contains no certificate")
}
hosts := append([]*pgconn.FallbackConfig{{
Host: cfg.ConnConfig.Host,
TLSConfig: cfg.ConnConfig.TLSConfig,
}}, cfg.ConnConfig.Fallbacks...)
for _, host := range hosts {
tc := host.TLSConfig
if tc == nil || tc.InsecureSkipVerify || tc.ServerName == "" {
log.Fatal("database URL must use sslmode=verify-full")
}
tc.RootCAs = roots
}
pgx can try several targets, including plaintext fallbacks for allow
and prefer. Setting RootCAs on the first target leaves those fallbacks
unchanged. I check every target and set its roots.
InsecureSkipVerify alone does not mean pgx skips all checks.
For verify-ca, pgx checks the chain in a callback but omits the hostname.
My guest requires both checks, so it refuses that mode too.
Startup checks
My apps check the parsed connection settings before opening a pool. Every target must verify TLS. The connection must also require channel binding and SCRAM authentication. I return a fixed error for a malformed URL because a parser error can contain the password.
For local development, I allow an explicit sslmode=disable only when
every target is a literal loopback IP address or Unix socket:
postgres://user:[email protected]:5432/dbname?sslmode=disable
A missing sslmode does not qualify. I use no separate environment flag.
A local address can be a tunnel, so I do not use this exception for
production. The guest has no plaintext exception.
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 server can ask for password,
which sends the password itself rather than a SCRAM proof. TLS can
encrypt that exchange, but the server still receives the password.
require_auth names the methods the client accepts. I set
require_auth=scram-sha-256, so the client refuses other methods
or skipped authentication. SCRAM proves knowledge of the password
without sending it. The parameter takes a comma-separated list. A
leading ! on every entry inverts it.
verify-full rejects a server without a trusted certificate for the
hostname. require_auth also limits what a server with that certificate
can request.
libpq supports require_auth from version 16. I use pgx 5.12 or later:
5.12 fixes authentication-completion checks
for require_auth and required channel binding. That bug did not bypass
TLS certificate verification.