TLS and the sslmode ladder

TLS for PostgreSQL is a client-side decision expressed in one connection parameter, and the parameter has six values that people routinely treat as a single spectrum of “more secure”. They are not a spectrum. There is a categorical break in the middle, between the modes that encrypt and the modes that also verify.

The ladder, demonstrated

TLS was enabled on a live 18.6 cluster with a locally issued certificate for the name rbpg-auth, and every mode was tried against the address 127.0.0.1.

$ psql 'host=127.0.0.1 user=demo_scram dbname=postgres sslmode=MODE sslrootcert=root.crt' -tAc "SELECT ssl||' '||coalesce(version,'-') FROM pg_stat_ssl WHERE pid=pg_backend_pid()"

sslmode=disable      ->  false -
sslmode=prefer       ->  true TLSv1.3
sslmode=require      ->  true TLSv1.3
sslmode=verify-ca    ->  true TLSv1.3
sslmode=verify-full  ->  psql: error: connection to server at "127.0.0.1", port 5432
                       failed: server certificate for "rbpg-auth" does not
                       match host name "127.0.0.1"

The interesting result is the last one. require and verify-ca both completed with TLS 1.3, against a certificate issued for a different name than the one the client connected to. Only verify-full noticed.

What each mode actually does

Three of these deserve individual attention.

prefer is the default, and it means “use TLS if the server offers it, otherwise carry on without”. A client that has never configured anything is silently downgraded by a server that does not offer TLS — or by anything in the path that can persuade the client the server does not offer it. It gives opportunistic encryption and no guarantee.

require is the mode most people mean when they say “we use TLS”, and it verifies nothing about who the certificate belongs to. The connection is encrypted to whoever answered. A self-signed certificate generated by anybody is accepted without complaint. It defends against passive interception and not against an active attacker who can answer in the server’s place.

verify-full is the only mode that establishes you are talking to the server you named. It checks that the chain reaches a CA in sslrootcert and that the certificate names the host you asked for.

Enabling TLS on the server

# The three parameters that matter. ssl is postmaster context.
psql -U postgres -c "ALTER SYSTEM SET ssl = on"
psql -U postgres -c "ALTER SYSTEM SET ssl_cert_file = 'server.crt'"
psql -U postgres -c "ALTER SYSTEM SET ssl_key_file  = 'server.key'"
# ssl is postmaster context: this needs a restart, not a reload.

The key file must be owned by the database user and readable only by it; PostgreSQL refuses to start otherwise, on the same reasoning as the data directory permission check from Part I.

Confirm from the server side:

$ psql -c 'SELECT a.pid, s.ssl, s.version, s.cipher, s.bits FROM pg_stat_activity a JOIN pg_stat_ssl s USING (pid) WHERE a.pid = pg_backend_pid()'

 pid | ssl | version |         cipher         | bits
-----+-----+---------+------------------------+------
49 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256
(1 row)

Making TLS non-optional

Because sslmode is a client decision, the server has to enforce it if it is to be guaranteed. That is what hostssl is for.

# Only matches connections that negotiated TLS.
hostssl  all  +app_roles  10.0.0.0/8  scram-sha-256

# Explicitly refuse the unencrypted case, so the error is clear.
hostnossl all all         10.0.0.0/8  reject

A client arriving with sslmode=disable does not match the hostssl line, matches the hostnossl line, and is refused. Without the second line it would fall through to whatever comes next, or to “no pg_hba.conf entry” — which is a correct outcome with a confusing message.

This enforces encryption. It cannot enforce verification, because the server cannot tell whether the client checked the certificate. That half remains a client-configuration problem.

Production discipline

1. Audit sslmode in connection strings, not in the database. The server cannot tell you whether a client verified anything.
2. Use verify-full for anything crossing a network. require encrypts to whoever answered.
3. Pair hostssl with an explicit hostnossl ... reject, so an unencrypted client gets a clear refusal rather than a confusing one.
4. Issue certificates for the names clients actually use, including service and virtual-IP names, and put them all in the SAN list.
5. Remember ssl is postmaster context. Enabling it needs a restart, so plan it with the other restart-only changes.
6. Treat a verify-full name mismatch as a naming problem first. The error names the identity the certificate carries.

Cross-course references

• Secrets, PKI & Certificate Management — Part V (X.509) covers SAN construction, Part VI (Chains and trust stores) covers sslrootcert, and Part IX (Certificate lifecycle) covers the expiry monitoring a database listener needs.
• Linux for Production Sysadmins — Part LXXI (TLS) covers the host trust store that a client without an explicit sslrootcert falls back to.
• Observability for Production Sysadmins — Part LXIV (TLS monitoring) covers probing a listener’s certificate expiry from outside, which is the control that prevents the outage.

Quiz