Enable TLS for PostgreSQL

The fixpostgresql.conf

ssl = on
ssl_cert_file = '/etc/postgresql/ssl/server.crt'
ssl_key_file = '/etc/postgresql/ssl/server.key'
ssl_min_protocol_version = 'TLSv1.2'

Then require it in pg_hba.conf, which is the half that matters:

# TYPE     DATABASE  USER     ADDRESS       METHOD
hostssl    appdb     appuser  10.0.1.0/24   scram-sha-256
hostnossl  all       all      0.0.0.0/0     reject

Turning ssl on for the first time needs a restart; certificate changes afterwards only need a reload.

Why it matters

ssl defaults to off. That is one of the few PostgreSQL defaults that is not the safe choice, and it means a source-built or minimally-configured cluster sends every query, every result row and every parameter in cleartext.

SCRAM does not save you here. It protects the stored hash and resists replay of the credential — but the data itself, and everything you select, crosses the wire in the clear. Anyone on the path reads your table contents.

Debian and Ubuntu packages enable ssl = on with a snakeoil certificate at install time, so a packaged cluster is usually encrypting already. A container or a compiled-from-source cluster usually is not. Check rather than assume.

ssl = on offers TLS; hostssl requires it

This is the trap, and it mirrors the same one in Redis and SSH: enabling a secure option is not the same as disabling the insecure one.

With ssl = on and a host rule, TLS is available. A client that asks for it gets it; a client that doesn’t, connects in plaintext and is accepted. Since libpq’s default sslmode is prefer, most clients will negotiate TLS — and any client that doesn’t silently won’t, with no error and no log entry that stands out.

Three rule types decide this:

Rule	Matches
host	TLS and plaintext — offers encryption, requires nothing
hostssl	TLS only
hostnossl	Plaintext only — useful as an explicit reject

Use hostssl for anything crossing a network. The hostnossl ... reject line is belt and braces: it turns a plaintext attempt into a loud refusal instead of a quiet success.

The client’s sslmode is the other half

A server can require TLS; only the client can require that it’s the right server. sslmode=require encrypts but does not verify the certificate — it stops passive eavesdropping and does nothing against an active machine-in-the-middle.

For that you need the client to verify:

psql "host=db.internal dbname=appdb sslmode=verify-full sslrootcert=/etc/ssl/ca.crt"

verify-full checks the chain and the hostname. It is the only mode that means what people usually think require means.

Verify it worked

Confirm the server has it on:

SHOW ssl;
SHOW ssl_min_protocol_version;

Then check which connections are actually encrypted — this is the query that settles it:

SELECT a.usename, a.client_addr, s.ssl, s.version, s.cipher
FROM pg_stat_ssl s
JOIN pg_stat_activity a USING (pid)
WHERE a.client_addr IS NOT NULL;

Any row with ssl = f is a live plaintext connection. That is the finding — and it is invisible from the config alone, because ssl = on looks correct while host rules quietly permit it.

Prove a plaintext attempt is refused:

psql "host=your.db dbname=appdb user=appuser sslmode=disable" -c 'SELECT 1'

Expect a pg_hba.conf rejection.

Before you apply this

hostnossl ... reject cuts off every client that isn’t doing TLS, and you may not know which those are until they fail.

Find out first — the query above lists every connected client and whether it is encrypted. Anything showing ssl = f breaks the moment you add the reject rule.

Sequence:

1. ssl = on, restart. Nothing breaks; TLS is merely offered.
2. Watch pg_stat_ssl until every client shows ssl = t. Fix the stragglers by changing their connection strings, not the server.
3. Only then switch host to hostssl and add the hostnossl reject.

Two things that bite regardless:

• Certificate expiry refuses new connections. Existing sessions survive, so the failure appears gradually and looks like an application bug. Whatever issues these certificates needs renewal automation and expiry monitoring.
• The key file’s permissions are enforced. PostgreSQL refuses to start if ssl_key_file is group- or world-readable. It must be 0600 owned by the postgres user (or 0640 owned by root with group access). A permissive key file is a startup failure, not a warning.

Related

• Scope pg_hba.conf rules — where hostssl goes.
• Use scram-sha-256, not md5 — protects the credential; this protects the data.