postgres / connection

updated October 21, 2026

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 self-signed CA:

postgres://user:password@host:5432/dbname?sslmode=verify-full&sslrootcert=/etc/db-ca.crt&channel_binding=require&require_auth=scram-sha-256

libpq-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.
allow and prefer: try TLS, fall back to no TLS, and verify nothing. prefer is the default when a URL names no sslmode.
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.

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. That works for verify-full only.

cfg, err := pgxpool.ParseConfig(databaseURL)
fatal(err, "parse db url")

pool, err := x509.SystemCertPool()
fatal(err, "load system cert pool")
pool.AppendCertsFromPEM(rootCertPEM)

cfg.ConnConfig.TLSConfig.RootCAs = pool

In pgx (pgconn/config.go), configTLS sets InsecureSkipVerify unless the URL specifies verify-full. For allow and prefer, ParseConfig also adds a non-TLS fallback. Setting RootCAs in Go later does not remove those fallbacks.

A URL without verify-full and a CA configured in Go checks no certificate and can fall back to plaintext. The sports guest refuses any database URL that lacks sslmode=verify-full.

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.

← All articles