What this check reads
Starts by asking the cluster SHOW ssl, and goes no further unless the answer is on. It then reads SHOW ssl_cert_file; a value that does not begin with / is joined to SHOW data_directory, which on Debian and Ubuntu is not the directory postgresql.conf lives in. The resolved file is opened as root and tested with openssl x509 -checkend 2592000, so the threshold between a pass and a warning is 2592000 seconds, 30 days, of remaining validity.
When it applies
Runs only when PostgreSQL is detected and reachable (PGOK=1), and only when SHOW ssl is already on - with TLS off it SKIPs, because there is no certificate in use to judge. Relative ssl_cert_file values are resolved against data_directory, and reading the file needs root. The single test is openssl x509 -checkend 2592000 - 30 days of remaining validity - so nothing else about the certificate is assessed: not the key, the chain, the hostname, or client trust. An already-expired certificate also produces WARN rather than FAIL.
What each result means
| Result | When | What it means |
|---|---|---|
| PASS | Valid beyond 30 days | The certificate has more than a month left, and the check looked at nothing else about it. |
| WARN | Expires within 30 days / expired | Renew and reload now, because verifying clients refuse the connection once the notAfter date passes. |
| SKIP | TLS off, file missing, or not accessible | No certificate was tested, so nothing is known about its expiry - and when the reason is TLS being off, the PostgreSQL TLS check is the finding to read instead. |
Why it matters
Same as the MySQL Certificate check: an expired server certificate breaks verify-ca/verify-full clients or trains operators to disable verification.
Why it fails, and when it is wrong
- Debian's default is
/etc/ssl/certs/ssl-cert-snakeoil.pem(10-year self-signed). It passes the date check but offers no identity assurance. - Let's Encrypt certs used for PostgreSQL expire every 90 days; a renewal hook must
pg_ctl reloador the server keeps serving the old cert from memory. - Certificates as root-only files: PostgreSQL must be able to read them at reload; the check reads as root so it will not notice a permissions problem that PostgreSQL itself would hit.
How to fix it
Renew/replace the cert, ensure ssl_key_file is 0600 and owned by postgres (or 0640 root:postgres), then SELECT pg_reload_conf();. Verify with openssl s_client -starttls postgres -connect host:5432.
Verify the fix
CERT=$(sudo -u postgres psql -X -tAc 'SHOW ssl_cert_file')
sudo openssl x509 -in "$CERT" -noout -subject -issuer -dates
sudo openssl x509 -checkend 2592000 -noout -in "$CERT" && echo 'valid > 30 days'
# Key permissions PostgreSQL insists on, and which break startup if wrong:
sudo ls -l "$(sudo -u postgres psql -X -tAc 'SHOW ssl_key_file')"
# expected: -rw------- postgres postgres
# End to end, including verification:
psql "host=<server> user=app dbname=appdb sslmode=verify-full sslrootcert=/path/ca.crt" -c 'SELECT 1'Debugging
Expected when ssl=off; fix the PostgreSQL TLS check first, then this one becomes meaningful.
The configured path does not exist or could not be read even as root. sudo ls -l "$(sudo -u postgres psql -X -tAc 'SHOW ssl_cert_file')". Remember relative paths resolve against data_directory, which on Debian/Ubuntu is not where postgresql.conf lives.
PostgreSQL rereads certificate files on SELECT pg_reload_conf(); (a full restart also works). If the warning stays, you renewed a different file than the one ssl_cert_file names.
The new key is usually root-owned and 0644, which PostgreSQL rejects. Copy it into place with chown postgres:postgres and chmod 0600, or point ssl_key_file at a copy managed by a renewal hook.
Expiry is the only thing tested. Hostname mismatches and untrusted issuers pass here; test with sslmode=verify-full as above.
Sources
- PostgreSQL: Secure TCP/IP Connections with SSL (file permissions, reload)
- PostgreSQL: ssl_cert_file
- OpenSSL: s_client with
-starttls postgres - Certbot: renewal hooks (
--deploy-hook)
How the script reads this
Next
Re-run the Transport Encryption audit after applying the fix and confirm this check moves to PASS. CtrlOps runs all 5 checks over your existing SSH connection and scores the result, so the change is visible without reading another config file.
All 5 Transport Encryption fixes