Runbook: Enable or Renew TLS on a PostgreSQL Cluster

1 · Prerequisites

Confirm every item is in place before any state change.

• The server certificate, its private key, and the CA chain, with the certificate valid for every name clients use to reach this cluster
• Shell access to the database host as a user who can place files in the data directory or the configured certificate path and set their ownership
• A test client on the application network path with the CA certificate available to it
• Knowledge of which clients currently connect without TLS, because enforcement will refuse them
• A change window if enforcement is part of this work, since switching host to hostssl is an immediate cut for any client not configured for TLS
• The expiry date of the certificate being installed, and the name of whoever renews it next

2 · Pre-checks

Read-only diagnostic commands. If any of these don't match expected output, stop and investigate further.

• · Read the current TLS state. SELECT name, setting FROM pg_settings WHERE name IN ('ssl','ssl_cert_file','ssl_key_file','ssl_ca_file','ssl_min_protocol_version') ORDER BY name; and SHOW ssl;. Check every server in the estate individually rather than inferring from one.
• · Measure how many live sessions are actually encrypted. SELECT a.usename, a.client_addr, s.ssl, coalesce(s.version,'(plaintext)') AS tls 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; This is the list of clients that enforcement will break.
• · Read the certificate before installing it. openssl x509 -in server.crt -noout -subject -issuer -dates -ext subjectAltName. Confirm every name clients use appears in the SAN, and confirm notAfter is far enough away to be worth the change.
• · Confirm the key matches the certificate. Compare openssl x509 -in server.crt -noout -pubkey with openssl pkey -in server.key -pubout. Identical output means they are a pair; anything else means the server will refuse to start.
• · Confirm the key is not passphrase-protected, or that you have arranged for ssl_passphrase_command. A passphrase-protected key on a host that reboots unattended is an outage waiting for a power event.
• · **Check the current pg_hba.conf for host versus hostssl.** SELECT rule_number, type, address, auth_method FROM pg_hba_file_rules ORDER BY rule_number; host accepts plaintext; only hostssl enforces.
• · Save the existing certificate material with a timestamp, if any. The rollback in this procedure is a file restore.

3 · Procedure

Execute each step in order. Verify the expected output of a step before moving to the next.

1. 1Place the certificate, key and CA chain in their configured location with ownership and permissions PostgreSQL accepts: owned by the postgres OS user, and the key mode 0600. A key readable by others causes the server to refuse to start, deliberately.
2. 2Point the settings at the files. 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'; ALTER SYSTEM SET ssl_ca_file = '/etc/postgresql/18/main/tls/ca.crt'; ALTER SYSTEM SET ssl = on;
3. 3Set a minimum protocol version deliberately. ALTER SYSTEM SET ssl_min_protocol_version = 'TLSv1.2'; or TLSv1.3 where every client supports it. Leaving this at a default nobody chose is how an old protocol survives an audit.
4. 4Reload, then read the log. SELECT pg_reload_conf(); then grep -E "SSL|certificate" /var/log/postgresql/postgresql-18-main.log | tail -10. A certificate PostgreSQL cannot load produces could not load server certificate file ... and SSL configuration was not reloaded — and the previously working context is kept, so nothing breaks and nothing changed.
5. 5**Confirm SHOW ssl is on and that a new connection negotiates TLS.** From a client: SELECT ssl, version, cipher FROM pg_stat_ssl WHERE pid = pg_backend_pid();
6. 6Distribute the CA certificate to clients and configure sslrootcert. Nothing beyond this point works without it, because verify-full cannot validate a chain it cannot see.
7. 7**Set sslmode=verify-full in every client connection string.** This is the change that actually buys the security property; the server-side work only makes it possible. require encrypts and accepts any certificate, which does not resist an active attacker.
8. 8Verify from a client that validates. Connect with sslmode=verify-full using the name in the certificate. Then connect by a name that is not in the certificate and confirm it fails with server certificate for "..." does not match host name "...". That failure is the proof identity checking is on.
9. 9Only now, enforce on the server. Change host to hostssl for every application rule in pg_hba.conf and reload. Doing this before the clients are configured cuts them off; doing it after is a formality with a real safety benefit.
10. 10Confirm a plaintext client is refused. psql "host=... sslmode=disable" must produce FATAL: no pg_hba.conf entry for host "...", user "...", database "...", no encryption. The trailing no encryption names the cause.
11. 11Re-check the live-session query. Every row should now report ssl = t, after the pools have recycled. Existing connections keep whatever they negotiated, so this check means nothing until connections have turned over.
12. 12**Record the certificate serial, its notAfter, the renewal owner, and the date the expiry alert was configured for.**

4 · Verification

Confirm the procedure actually fixed the problem.

• ✓SHOW ssl returns on on every server in the estate, checked individually.
• ✓A verify-full client connects successfully by the name in the certificate, from the application network path.
• ✓The same client, connecting by a name not in the certificate, is refused with a hostname mismatch. Without this check you have verified encryption but not identity.
• ✓A sslmode=disable client is refused with no pg_hba.conf entry ... no encryption, which proves server-side enforcement rather than client-side preference.
• ✓SELECT count(*) FROM pg_stat_activity a LEFT JOIN pg_stat_ssl s USING (pid) WHERE a.client_addr IS NOT NULL AND s.ssl IS NOT TRUE; returns zero after a full pool recycle.
• ✓SELECT version, cipher FROM pg_stat_ssl WHERE pid = pg_backend_pid(); reports the protocol version you intended, not merely a version.
• ✓The certificate notAfter is recorded and an expiry alert exists, tested by setting its threshold temporarily so it fires once.

5 · Rollback

If verification fails, undo the procedure in reverse order.

• ↶Restore the previous certificate material from the timestamped copy and reload. A failed reload keeps the working SSL context, so this is safe: PostgreSQL 18 logs SSL configuration was not reloaded and continues serving with the certificate it already had.
• ↶To reverse enforcement, change hostssl back to host in pg_hba.conf and reload. This restores plaintext acceptance immediately and is the correct first move if enforcement has cut off a client you cannot reconfigure quickly.
• ↶To disable TLS entirely, ALTER SYSTEM SET ssl = off; and reload. Understand what this does to clients: sslmode=prefer will silently connect in cleartext, while require and above will fail loudly with server does not support SSL, but SSL was required.
• ↶Do not roll back by relaxing client sslmode from verify-full to require. That removes the identity check while leaving the appearance of TLS, and it tends to become permanent.
• ↶If a restart has been attempted with a bad certificate, the server will not start: FATAL: could not load server certificate file ... followed by database system is shut down. Restore the previous files and start again. This failure is loud and complete, which is preferable to a silent downgrade.
• ↶If enforcement was rolled back, record the clients that could not comply and a date by which they will. A hostssl reverted with no follow-up is a security control that has been quietly abandoned.

6 · Escalation

When the runbook isn't enough, contact:

• · A client cannot be configured for TLS at all: escalate to the application owner before reverting enforcement. The question is whether that client should be reaching production, not whether the rule should be widened.
• · The certificate does not cover a name clients actually use, and reissuing is not quick: escalate to whoever owns the certificate. Adding a host rule for those clients is a workaround that will outlive everybody involved.
• · The private key is passphrase-protected and the host reboots unattended: escalate to the platform owner before proceeding. A cluster that cannot start without a human typing a passphrase has an availability property nobody has agreed to.
• · Traffic was found to have been in cleartext for a period: escalate to security. The credentials that traversed that path should be treated as disclosed, and that is a rotation decision rather than a TLS one.
• · The certificate is issued by a CA that clients do not trust and cannot be made to trust: escalate to the PKI owner. Distributing a CA certificate by hand to a subset of clients produces an estate where TLS works for some paths and not others.
• · Replication connections are affected: escalate to whoever owns the standbys before enforcing. primary_conninfo carries its own sslmode, and a standby that cannot connect after enforcement stops replicating silently.

References

1. PostgreSQL 18 documentation, Secure TCP/IP Connections with SSL
2. PostgreSQL 18 documentation, SSL Support in libpq
3. PostgreSQL 18 documentation, pg_stat_ssl
4. PostgreSQL 18 documentation, The pg_hba.conf File