A packet capture found database credentials in cleartext on a cluster where TLS had been enabled and verified eight months earlier

Reported symptoms

A scheduled network capture during a compliance audit finds PostgreSQL protocol traffic in cleartext on port 5432 — a SCRAM exchange and query text, readable.

All of it is to db-replica-02. None to the primary.

TLS was enabled across the estate eight months ago and signed off with evidence at the time. No application has reported an error. No application configuration has changed. The connection strings do not mention sslmode at all.

A test connection from the database host with psql shows ssl = t, which is what the original sign-off recorded.

db-replica-02 was rebuilt from a base image in March after a hardware failure.

Evidence provided

SHOW ssl on the primary returns on. On db-replica-02 it returns off. The March rebuild ran the base image build and then pg_basebackup; postgresql.auto.conf on the replica has no ssl entry.

Both servers use host, not hostssl, for the application rule.

Every connection string omits sslmode, so every client gets the libpq default, which is prefer. Here is what that actually does:

$ for M in disable allow prefer require verify-ca verify-full; do psql "host=... sslmode=$M" -c 'SELECT ssl FROM pg_stat_ssl WHERE pid=pg_backend_pid()'; done

  sslmode=disable:      ssl = f
sslmode=allow:        ssl = f
sslmode=prefer:       ssl = f
sslmode=require:
  psql: error: connection to server at "172.17.0.11", port 5432 failed: server does not support SSL, but SSL was required
sslmode=verify-ca:
  psql: error: connection to server at "172.17.0.11", port 5432 failed: server does not support SSL, but SSL was required
sslmode=verify-full:
  psql: error: connection to server at "172.17.0.11", port 5432 failed: server does not support SSL, but SSL was required

And on the replica, joining the two views shows it directly:

$ psql -c "SELECT a.pid, a.usename, a.client_addr, a.application_name, s.ssl, coalesce(s.version,'(plaintext)') AS tls_version FROM pg_stat_activity a LEFT JOIN pg_stat_ssl s USING (pid) WHERE a.client_addr IS NOT NULL ORDER BY s.ssl NULLS FIRST, a.pid;"

 pid | usename  | client_addr | application_name | ssl | tls_version 
-----+----------+-------------+------------------+-----+-------------
140 | postgres | 172.17.0.11 | psql             | f   | (plaintext)
138 | postgres | 172.17.0.11 | psql             | t   | TLSv1.3
147 | postgres | 172.17.0.11 | psql             | t   | TLSv1.3
(3 rows)

The original sign-off evidence is a psql run from the database host against 127.0.0.1, on the primary.

Work the evidence before reading on

1. No application reported an error for eight months. Why not?
2. sslmode is absent from every connection string. What value applies?
3. Two independent controls could have stopped this. Name both, and say what each was set to.
4. What did the original sign-off actually prove?

Root cause

prefer is an optimisation, not a control

Two permissive defaults met each other

Client side: sslmode unset, so prefer. Server side: host rather than hostssl, so plaintext is accepted at the door.

Either control alone would have caught the March rebuild. Both were at their permissive value, so the rebuild had nothing to trip over and produced a replica that worked perfectly and encrypted nothing.

PostgreSQL itself never downgrades

It is worth knowing where the silence does not come from, because it narrows the search:

$ psql -c 'SELECT pg_reload_conf();' && tail -2 postgresql.log

2026-08-28 07:20:23.305 UTC [1] LOG:  could not load server certificate file "/var/lib/postgresql/18/docker/tls/server.crt": no start line
2026-08-28 07:20:23.305 UTC [1] LOG:  SSL configuration was not reloaded

New connections after that reload still negotiated TLSv1.3. And across a restart with the same broken file:

$ pg_ctl restart && tail -2 postgresql.log

2026-08-28 07:20:37.833 UTC [1] FATAL:  could not load server certificate file "/var/lib/postgresql/18/docker/tls/server.crt": no start line
2026-08-28 07:20:37.833 UTC [1] LOG:  database system is shut down

The sign-off could not have caught it

It was one psql run, from the database host, over 127.0.0.1, on the primary. The estate has two servers and several application paths. It tested none of them, and it was filed as though it had.

Resolution

Establish the real state of every server:

SHOW ssl;
SELECT name, setting FROM pg_settings
WHERE name IN ('ssl','ssl_cert_file','ssl_key_file','ssl_ca_file','ssl_min_protocol_version');

Then measure sessions, not configuration:

SELECT a.pid, a.usename, a.client_addr, a.application_name,
       s.ssl, coalesce(s.version, '(plaintext)') AS tls_version
FROM pg_stat_activity a
LEFT JOIN pg_stat_ssl s USING (pid)
WHERE a.client_addr IS NOT NULL
ORDER BY s.ssl NULLS FIRST, a.pid;

Enable TLS on the replica with the same certificate material as the primary. ssl is reloadable:

ALTER SYSTEM SET ssl = on;
ALTER SYSTEM SET ssl_cert_file = '/etc/postgresql/18/main/tls/server.crt';
ALTER SYSTEM SET ssl_key_file  = '/etc/postgresql/18/main/tls/server.key';
SELECT pg_reload_conf();

Confirm SHOW ssl is on and that a prefer client now negotiates TLSv1.3 — before going further.

Then close the hole, in this order, so you do not lock out a client that is still misconfigured:

1. Set sslmode=verify-full explicitly in every client.
2. Distribute the CA certificate and set sslrootcert. verify-full cannot work without it.
3. Change host to hostssl for every application rule, and reload.

Verification

SHOW ssl returns on on every server, checked individually.

The live-session query returns no row with ssl = f, from any application, after a deployment cycle has recycled the pools. Existing connections keep whatever they negotiated, so this check means nothing until connections have turned over.

A plaintext client is now refused — this is the check that proves enforcement rather than configuration:

$ psql "host=... sslmode=disable" -c "select 1"

psql: error: connection to server at "172.17.0.11", port 5432 failed: FATAL:  no pg_hba.conf entry for host "172.17.0.11", user "postgres", database "postgres", no encryption

Note no encryption at the end: PostgreSQL is telling you the client arrived without TLS and no rule matched it.

A verify-full client succeeds by the certificate’s name and fails by any other name. That failure is the proof identity checking is on; require and verify-ca both connect happily in that case.

A repeat packet capture on the same segment shows no cleartext protocol traffic.

Prevention

Enforce on the server with hostssl; do not request on the client with sslmode. A client-side setting is a preference that a rebuild, a library default, or a new service can quietly ignore. hostssl fails closed.

Never leave sslmode unset. Set verify-full explicitly, everywhere — including cron jobs and migration tools.

Alert on ssl = off and on pg_stat_ssl.ssl = false. The first catches a server; the second catches a client that got through anyway. Either would have caught this in March.

Put TLS in the host build and in a post-build check. The March rebuild produced a replica that worked, and “worked” did not include this. A rebuild checklist ending in SHOW ssl costs nothing.

Do not accept loopback evidence for a network control. Verification must traverse the same path the application traverses, from the same network position.