Skip to content

Database audit fix · Transport Encryption

How to Configure PostgreSQL SSL Certificates on VPS

PostgreSQL reads its server certificate from ssl_cert_file and its key from ssl_key_file, and a relative path is resolved against the data directory, not the directory postgresql.conf sits in. Renew before 30 days remain, then reload.

Hiren KalariyaHiren KalariyaLast reviewed postgresql-certificate
CtrlOpsPostgreSQL CertificateMEDIUM

What did your audit say?

WARN · when the audit printed

Expires within 30 days / expired

Renew and reload now, because verifying clients refuse the connection once the notAfter date passes.

Severity
Medium
Needs root or sudo
Yes
Results it can return
WARN · SKIP · PASS
Check in this audit
4 of 5

Every threshold on this page is transcribed from the database-security-transport-encryption audit script that CtrlOps runs, and a build check fails if the two ever disagree. See all 5 Transport Encryption checks, or how the audit runs.

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.

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
ResultWhenWhat it means
PASSValid beyond 30 daysThe certificate has more than a month left, and the check looked at nothing else about it.
WARNExpires within 30 days / expiredRenew and reload now, because verifying clients refuse the connection once the notAfter date passes.
SKIPTLS off, file missing, or not accessibleNo 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.

Same as the MySQL Certificate check: an expired server certificate breaks verify-ca/verify-full clients or trains operators to disable verification.

  • 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 reload or 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.

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.

sh
CERT=$(sudo -u postgres psql -X -tAc 'SHOW ssl_cert_file')sudo openssl x509 -in "$CERT" -noout -subject -issuer -datessudo 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

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.

Download for MacDownload for WindowsAll 5 Transport Encryption fixes

14-day free trial, no credit card required.

Explore article with AI tools

Written by

Hiren Kalariya

Co-Founder & CEO, TST Technology

Hiren Kalariya is the Co-Founder and CEO of TST Technology and the product lead behind CtrlOps, a local-first desktop app that makes Linux server management fast and easy for developers and DevOps teams. Over the past five years he has shipped 60+ production systems, and he still tests every tool he writes about on real servers.

Stop juggling tools. Start managing fleets.

PuTTY
Termius
MobaXterm
WinSCP
SecureCRT
Royal TS
Warp
CtrlOps14 tools left
Webmin
aaPanel
RunCloud
Dokploy
IP spreadsheet
bash (2)
sticky notes

One app. All your servers. No context switching. Free to start, free to leave.

Start instantly · No credit card · Cancel anytime

Your privacy preferences. We use product analytics to improve CtrlOps. Google measurement runs without analytics cookies. Live chat stays off until you allow it. Cookie policy