Postgres Connection Strings and SSL Modes in Go Explained

A PostgreSQL connection string packs a lot into a line, and one parameter of it decides whether your database traffic is encrypted, whether the server is who it claims to be, or neither. sslmode has six values, and the names do not say what they protect against. This article reads the connection string format used by pgx, then tests every mode against a real server with TLS, using a certificate authority generated for the lab, so you can see which ones accept a server they should not.

It belongs to the database cornerstone. The first connection setup is in connecting to PostgreSQL, and the wider security picture in SQL injection in Go.

The connection string

pgx accepts two formats, and both end up as the same configuration.

The URL form: postgres://user:password@host:5432/dbname?sslmode=verify-full&sslrootcert=/etc/ssl/db-ca.pem&connect_timeout=5. The password must be URL encoded if it contains @, /, : or %, a common cause of "invalid port" errors. Several hosts separated by commas are allowed, with target_session_attrs=read-write to pick the primary, which is how an application survives a failover without a code change.

The key value form: host=db.internal port=5432 user=app dbname=app sslmode=verify-full sslrootcert=/etc/ssl/db-ca.pem. Values with spaces are single quoted. It avoids the encoding problem, and is easier to build in code.

Parameters are also read from the environment (PGHOST, PGUSER, PGPASSWORD, PGSSLMODE, PGSSLROOTCERT) and from ~/.pgpass, which keeps secrets out of the string. Keep the whole string in configuration, never in source control, and never log it: it holds the password. pgx.ParseConfig turns a string into a *pgx.ConnConfig you can inspect and adjust, which the lab uses to override name resolution.

The six modes

Only the last two authenticate the server. Everything before them protects against someone listening on the wire and does nothing against someone who answers in place of the real server.

Against a real server

The lab starts a PostgreSQL container with TLS on, using a certificate for the name db.example.test signed by a CA it generated, and tries each mode from Go.

package main

import (
	"context"
	"fmt"
	"os"
	"strings"
	"time"

	"github.com/jackc/pgx/v5"
)

func try(label, dsn string, resolve ...string) {
	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()
	cfg, err := pgx.ParseConfig(dsn)
	if err != nil {
		fmt.Printf("%-52s bad connection string: %v\n", label, err)
		return
	}
	if len(resolve) > 0 {
		// Stand in for DNS: the name in the URL resolves to the lab container, and the certificate is checked against the name.
		cfg.LookupFunc = func(context.Context, string) ([]string, error) { return resolve, nil }
	}
	conn, err := pgx.ConnectConfig(ctx, cfg)
	if err != nil {
		msg := err.Error()
		if i := strings.Index(msg, "failed to connect to"); i >= 0 {
			// drop the host and user prefix; the reason is the part after the last colon-space group
			if j := strings.LastIndex(msg, "): "); j >= 0 {
				msg = msg[j+3:]
			}
		}
		fmt.Printf("%-52s refused: %s\n", label, msg)
		return
	}
	defer conn.Close(ctx)
	var ssl bool
	var version, cipher *string
	_ = conn.QueryRow(ctx, "select s.ssl, s.version, s.cipher from pg_stat_ssl s where s.pid = pg_backend_pid()").Scan(&ssl, &version, &cipher)
	v := "none"
	if version != nil {
		v = *version
	}
	fmt.Printf("%-52s connected, encrypted: %-5v protocol: %s\n", label, ssl, v)
}

func main() {
	ca := os.Getenv("CA_FILE")
	tls := "postgres://postgres:[email protected]:55656/postgres"
	plain := "postgres://postgres:[email protected]:55432/lab"

	fmt.Println("a server that offers TLS, with a certificate for db.example.test signed by our own CA")
	try("sslmode=disable", tls+"?sslmode=disable")
	try("sslmode=prefer (the pgx default when unset)", tls+"?sslmode=prefer")
	try("sslmode=require", tls+"?sslmode=require")
	try("sslmode=verify-ca, no root certificate given", tls+"?sslmode=verify-ca")
	try("sslmode=verify-ca with the CA file", tls+"?sslmode=verify-ca&sslrootcert="+ca)
	try("sslmode=verify-full with the CA file", tls+"?sslmode=verify-full&sslrootcert="+ca)
	try("verify-full, URL names db.example.test", "postgres://postgres:[email protected]:55656/postgres?sslmode=verify-full&sslrootcert="+ca, "127.0.0.1")

	fmt.Println("a server that does not offer TLS")
	try("sslmode=prefer", plain+"?sslmode=prefer")
	try("sslmode=require", plain+"?sslmode=require")
	try("sslmode=verify-full", plain+"?sslmode=verify-full&sslrootcert="+ca)
}

set -e
d=$(mktemp -d)
trap 'docker rm -f gh-lab-tls >/dev/null 2>&1; rm -rf "$d"' EXIT
openssl req -new -x509 -days 2 -nodes -subj "/CN=Lab CA" -keyout "$d/ca.key" -out "$d/ca.crt" 2>/dev/null
openssl req -new -nodes -subj "/CN=db.example.test" -keyout "$d/server.key" -out "$d/server.csr" 2>/dev/null
printf "subjectAltName=DNS:db.example.test" > "$d/ext.cnf"
openssl x509 -req -in "$d/server.csr" -CA "$d/ca.crt" -CAkey "$d/ca.key" -CAcreateserial -days 2 -extfile "$d/ext.cnf" -out "$d/server.crt" 2>/dev/null
docker rm -f gh-lab-tls >/dev/null 2>&1 || true
docker run -d --name gh-lab-tls -p 127.0.0.1:55656:5432 -e POSTGRES_PASSWORD=secret -v "$d:/in:ro" --entrypoint sh postgres:18-alpine -c \
  'mkdir /certs && cp /in/server.crt /in/server.key /certs/ && chown postgres:postgres /certs/* && chmod 600 /certs/server.key && exec docker-entrypoint.sh postgres -c ssl=on -c ssl_cert_file=/certs/server.crt -c ssl_key_file=/certs/server.key' >/dev/null
for i in $(seq 1 40); do
  docker exec gh-lab-tls pg_isready -U postgres >/dev/null 2>&1 && break
  sleep 0.5
done
sleep 2
CA_FILE="$d/ca.crt" go run .

a server that offers TLS, with a certificate for db.example.test signed by our own CA
sslmode=disable                                      connected, encrypted: false protocol: none
sslmode=prefer (the pgx default when unset)          connected, encrypted: true  protocol: TLSv1.3
sslmode=require                                      connected, encrypted: true  protocol: TLSv1.3
sslmode=verify-ca, no root certificate given         refused: failed to write startup message: write failed: x509: “db.example.test” certificate is not trusted
sslmode=verify-ca with the CA file                   connected, encrypted: true  protocol: TLSv1.3
sslmode=verify-full with the CA file                 refused: failed to write startup message: write failed: tls: failed to verify certificate: x509: cannot validate certificate for 127.0.0.1 because it doesn't contain any IP SANs
verify-full, URL names db.example.test               connected, encrypted: true  protocol: TLSv1.3
a server that does not offer TLS
sslmode=prefer                                       connected, encrypted: false protocol: none
sslmode=require                                      refused: tls error: server refused TLS connection
sslmode=verify-full                                  refused: tls error: server refused TLS connection

After each connection it asks the server whether that session is encrypted, through pg_stat_ssl, so the output states what happened and not what the string requested.

disable connected with encrypted: false. No surprise.

prefer, the mode pgx uses when you do not set one, connected with encrypted: true and protocol TLSv1.3. The server offered TLS and the client took it, without looking at the certificate. This is a trap in the other direction too: against a server with no TLS, prefer connected unencrypted and said nothing. A service configured without sslmode has no way to tell you it is sending its password and its data in the clear.

require connected, encrypted. It accepted a certificate the client had no reason to trust, since it did not check it. It is better than prefer because it refuses a plain connection, and it still lets an attacker in the middle present their own certificate. Against the server with no TLS it was refused: tls error: server refused TLS connection.

verify-ca without a root certificate was refused: x509: "db.example.test" certificate is not trusted. The client has no list of authorities to check against unless you give one with sslrootcert. With the CA file it connected. It proves the certificate was signed by the authority you trust. It does not prove the certificate belongs to this host: any certificate issued by that CA is accepted, including one issued for a different server.

verify-full with the CA file but connecting to 127.0.0.1 was refused: cannot validate certificate for 127.0.0.1 because it doesn't contain any IP SANs. The certificate names db.example.test and nothing else, and the client asked for a different name. When the URL used db.example.test, with the lab resolving it to the container, it connected, encrypted. This is the mode that protects against both eavesdropping and impersonation, and it is the right setting for production. Its failures are certificate and naming problems, and they are the failures you want.

Choosing and deploying

1. Use verify-full in production and give the client the CA certificate with sslrootcert. Managed providers publish theirs: download it into the image, or mount it as a secret.
2. Use the host name the certificate names, the DNS name, not an IP address. If you must connect by IP, issue a certificate with an IP subject alternative name.
3. Use require only as a stepping stone, when you cannot yet distribute the CA, and plan to move. Never leave prefer or disable outside local development.
4. Make the setting explicit and checked. Reject a connection string with no sslmode at start-up, so a missing parameter is a deploy failure and not an unencrypted production.
5. Verify after connecting. Run select ssl from pg_stat_ssl where pid = pg_backend_pid() once at start-up in production and fail if it is false, as the lab's helper does.
6. Rotate certificates before they expire, and monitor the expiry date. Pools reconnect on their own, so a new certificate on the server is picked up as connections cycle; SetConnMaxLifetime bounds how long old ones live.
7. Mind the server side too. pg_hba.conf lines of type hostssl accept only TLS clients, and hostnossl rejects them; a server that accepts plain host connections from the internet is open regardless of what the client asks.

Client certificates (sslcert, sslkey) add mutual authentication: the server checks the client's certificate and can map it to a role with the cert method in pg_hba.conf. They remove passwords from the connection string, at the cost of certificate management.

Troubleshooting messages

• server refused TLS connection: the server has ssl = off or the pg_hba.conf line does not allow it. Fix the server or lower the client's requirement knowingly.
• certificate is not trusted or certificate signed by unknown authority: the client does not have the CA. Check sslrootcert, and that the file is the CA that signed the server certificate, not the server's own.
• cannot validate certificate for X because it doesn't contain any IP SANs or x509: certificate is valid for A, not B: the name in the connection does not match the certificate. Connect by the name the certificate carries.
• certificate has expired or is not yet valid: check the clock on both sides before you check anything else.
• password authentication failed after a successful TLS handshake means TLS is fine and the credentials are not; see handling database errors.

The parameters are documented in the PostgreSQL manual's page on connection strings and SSL support, whose table of modes the one above follows, and pgx's own options in the pgconn documentation.