TLS Configuration, Certificate Verification, Channel Security, and Rotation

Learning outcomes

A SCRAM password can authenticate a user, but a client must also know it is talking to the intended PostgreSQL server rather than an attacker that can observe or relay network traffic. Transport Layer Security (TLS)—called SSL in PostgreSQL parameter names for historical reasons—encrypts the channel. Certificate verification adds server identity.

1. Use a separate disposable cluster

Do not experiment with private keys in the normal ServiceHub lab. Create a separate PostgreSQL data directory on port 55442. The exact binaries/paths differ by platform, but the mechanism is the same.

initdb -D ./ch20_tls_pgdatapg_ctl -D ./ch20_tls_pgdata -o "-p 55442" -l ./ch20_tls.log start

2. Build a private lab CA and hostname-valid server certificate

This lab uses OpenSSL and a private test Certificate Authority (CA). Do not reuse its keys outside the disposable environment.

mkdir -p ./ch20_tls_certscd ./ch20_tls_certsopenssl req -new -x509 -nodes -days 30   -newkey rsa:3072   -keyout root.key   -out root.crt   -subj "/CN=ServiceHub Chapter20 Lab Root CA"   -addext "basicConstraints=critical,CA:TRUE"   -addext "keyUsage=critical,keyCertSign,cRLSign"chmod 600 root.key

openssl req -new -nodes   -newkey rsa:3072   -keyout server.key   -out server.csr   -subj "/CN=localhost"chmod 600 server.keycat > server.ext <<'EOF'subjectAltName=DNS:localhostextendedKeyUsage=serverAuthEOFopenssl x509 -req   -in server.csr   -CA root.crt   -CAkey root.key   -CAcreateserial   -days 30   -out server.crt   -extfile server.ext

The certificate deliberately has DNS:localhost but no IP-address Subject Alternative Name. That lets the lesson prove the difference between connecting to localhost and 127.0.0.1 under verify-full.

3. Install server files and enable TLS

cp server.crt ../ch20_tls_pgdata/server.crtcp server.key ../ch20_tls_pgdata/server.keychmod 600 ../ch20_tls_pgdata/server.key

On Unix systems, PostgreSQL rejects an overly permissive private key; 0600 is the standard direct-owner mode. Windows ACL handling differs. The private key proves possession of the server identity key and must not be exposed to database clients.

ssl = onssl_cert_file = 'server.crt'ssl_key_file = 'server.key'

# TYPE    DATABASE        USER             ADDRESS         METHODhostssl   servicehub_tls  ch20_tls_login   127.0.0.1/32    scram-sha-256hostssl   servicehub_tls  ch20_tls_login   ::1/128         scram-sha-256hostnossl all             all              127.0.0.1/32    rejecthostnossl all             all              ::1/128         reject

Restart the disposable server if ssl=on was not already active. HBA's hostssl restricts matching to TLS connections; a plain TCP attempt falls to the explicit hostnossl rejection.

4. Create the TLS database/login

CREATE DATABASE servicehub_tls;CREATE ROLE ch20_tls_login LOGIN;SET password_encryption = 'scram-sha-256';-- Set the password interactively in psql:-- \password ch20_tls_loginGRANT CONNECT ON DATABASE servicehub_tls TO ch20_tls_login;

5. sslmode=require encrypts but does not prove the host name

psql "host=127.0.0.1 port=55442 dbname=servicehub_tls user=ch20_tls_login sslmode=require application_name=ch20_tls_require" 

This can succeed even though the certificate says localhost, because require primarily insists on encryption. A security-sensitive client should explicitly request certificate verification rather than relying on historical/default behavior.

6. verify-ca validates the trust chain

psql "host=127.0.0.1 port=55442 dbname=servicehub_tls user=ch20_tls_login sslmode=verify-ca sslrootcert=./ch20_tls_certs/root.crt application_name=ch20_tls_verify_ca" 

verify-ca checks whether the presented server certificate chains to a trusted root. It does not require the connection's requested host name to match the certificate identity.

7. verify-full adds host identity

psql "host=localhost port=55442 dbname=servicehub_tls user=ch20_tls_login sslmode=verify-full sslrootcert=./ch20_tls_certs/root.crt application_name=ch20_tls_verify_full" 

psql "host=127.0.0.1 port=55442 dbname=servicehub_tls user=ch20_tls_login sslmode=verify-full sslrootcert=./ch20_tls_certs/root.crt application_name=ch20_tls_bad_name" 

The IP connection should fail certificate-name verification because the certificate has DNS:localhost and no matching IP SAN. The repair is not to weaken the client to require; issue a certificate whose SANs match the real names/IPs clients use, then connect with verify-full.

8. Observe the negotiated TLS channel

SELECT a.pid, a.usename, a.datname, a.application_name,       s.ssl, s.version, s.cipher, s.bits,       s.client_dn, s.client_serial, s.issuer_dnFROM pg_stat_activity AS aJOIN pg_stat_ssl AS s USING (pid)WHERE a.application_name LIKE 'ch20_tls_%'ORDER BY a.pid;

This proves that a backend is using TLS and exposes protocol/cipher information. A NULL client DN is expected because this lab uses password authentication, not mutual TLS client certificates.

9. Rotation is a file + reload + verification workflow

PostgreSQL reads TLS certificate/key/CA/CRL files at startup and on configuration reload. If replacement files are invalid during a reload, PostgreSQL logs the problem and keeps the previous SSL configuration rather than replacing it with broken credentials.

# 1. Issue a new certificate/key with the same required SANs.# 2. Validate key/certificate before installation.openssl x509 -in new-server.crt -noout -subject -issuer -datesopenssl pkey -in new-server.key -check -noout# 3. Atomically install with correct ownership/mode.cp new-server.crt ./ch20_tls_pgdata/server.crt.newcp new-server.key ./ch20_tls_pgdata/server.key.newchmod 600 ./ch20_tls_pgdata/server.key.newmv ./ch20_tls_pgdata/server.crt.new ./ch20_tls_pgdata/server.crtmv ./ch20_tls_pgdata/server.key.new ./ch20_tls_pgdata/server.key

SELECT pg_reload_conf();SELECT now() AS verified_at,       current_setting('ssl') AS ssl_enabled,       current_setting('ssl_cert_file') AS cert_file,       current_setting('ssl_key_file') AS key_file;

After reload, make a new verify-full connection and inspect pg_stat_ssl plus certificate dates externally. Existing TLS sessions keep the channel/certificate negotiation they already established.

10. Mutual TLS is a separate client-identity choice

If the server is configured with ssl_ca_file, a hostssl HBA rule can require a client certificate using cert authentication or clientcert=verify-ca/verify-full alongside another method. That is different from the server-certificate verification the client performs with sslmode=verify-full.

11. Cleanup and checkpoint

pg_ctl -D ./ch20_tls_pgdata stoprm -rf ./ch20_tls_pgdata ./ch20_tls_certs

Authoritative references

Authentication, TLS, authorization, and policy behavior is security- and version-sensitive. The lesson uses these PostgreSQL 18 primary sources.

• PostgreSQL 18 — Secure TCP/IP Connections with SSL/TLS
• PostgreSQL 18 — libpq SSL Support
• PostgreSQL 18 — Certificate Authentication
• PostgreSQL 18 — Monitoring Statistics / pg_stat_ssl