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:

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.

← All articles