SimpleACME
Sign inCreate account →
Software certificate guides

PostgreSQL native TLS: verified clients and safe certificate renewal

Configure native PostgreSQL TLS, ordered hostssl rules, verify-full clients and reversible certificate deployment without a public HTTP challenge.

PostgreSQL speaks its own protocol on port 5432. Putting HTTPS on an adjacent administration website does not encrypt database connections, and installing a certificate on a reverse proxy does not automatically change libpq clients. This guide enables TLS inside PostgreSQL, requires encrypted network sessions for a defined application network, and shows how to deploy renewed certificates without restarting the database for every renewal.

The examples target PostgreSQL 16–18 on a Debian-style Linux installation. Replace db.example.com, the cluster version, database, role, and application subnet with your actual values. An upstream container usually keeps its configuration elsewhere. Discover the running server's paths instead of copying Debian paths into a container. This is a deployment procedure to adapt and validate, not a claim that these commands have been executed against your database.

Step 1: Map the database identities and connection paths

Start with the name clients use. If applications connect to db.example.com, that name belongs in the certificate's subject alternative names. A certificate for the machine's unrelated management hostname will not satisfy strict hostname verification. Prefer a stable DNS service name over a numeric address; an ordinary domain certificate does not authenticate arbitrary IP addresses. Record connections from applications, backup jobs, monitoring, connection poolers, and replicas separately.

Use an existing administrative session to inspect configuration locations and retain that session while changing access rules. Local Unix-socket administration provides a useful recovery route because it is governed by local authentication records rather than the network rules being changed. Check that this route actually works before relying on it during a maintenance window.

SHOW server_version;
SHOW config_file;
SHOW hba_file;
SHOW data_directory;
SHOW ssl;

Inventory poolers carefully. If PgBouncer terminates client TLS, the PgBouncer-to-PostgreSQL connection is a second connection with its own encryption and verification policy. Changing the database certificate alone does not secure the first leg. Similarly, a replication connection needs explicit verification settings on the replica; it does not inherit an application's connection string.

Step 2: Obtain the server certificate through delegated DNS validation

Prepare an existing SimpleACME registration for the challenge owner associated with db.example.com. Add its assigned CNAME at your existing authoritative DNS provider and verify the delegation before requesting a certificate. Paid SimpleACME access permits authenticated TXT updates. Your ACME client performs issuance, stores the private key, and decides when renewal is necessary; SimpleACME does not become a database certificate authority or hold the database key.

Follow the Certbot integration guide or the active subscriber setup library for the authentication hook and assigned credentials. Use an automated hook rather than interactive manual DNS entry if unattended renewal is the goal. DNS validation is especially useful here because PostgreSQL does not need to publish an HTTP challenge listener merely to prove control of its hostname.

Treat Certbot's lineage name as configuration, not an assumption. The path could be db.example.com-0001 if earlier requests created another lineage. Inspect certbot certificates and write down the actual name. Keep the database service away from ACME account credentials and authentication-hook secrets. The deployment boundary should deliver only the resulting server chain and private key to PostgreSQL.

Step 3: Install a matched certificate and key with database-safe permissions

Use a dedicated directory outside PostgreSQL's data files. PostgreSQL's Unix key permission checks matter: a key owned by the server account can be mode 0600, while a root-owned key may use mode 0640 with a group readable by the server. Avoid making the entire Certbot archive readable to the database service just to fix one permission error.

sudo install -d -o root -g postgres -m 0750 /etc/postgresql/tls
sudo install -o root -g postgres -m 0644 \
  /etc/letsencrypt/live/db.example.com/fullchain.pem \
  /etc/postgresql/tls/server.crt
sudo install -o root -g postgres -m 0640 \
  /etc/letsencrypt/live/db.example.com/privkey.pem \
  /etc/postgresql/tls/server.key

For an existing installation, back up the previous pair and configuration before replacing either file. Compare public keys using an algorithm-neutral method: derive the public key from the certificate and private key, normalize both to DER, and compare their digests. This works for RSA and elliptic-curve keys, whereas a modulus comparison is an RSA-specific shortcut.

openssl x509 -in /etc/postgresql/tls/server.crt -pubkey -noout |
  openssl pkey -pubin -outform DER | sha256sum
sudo openssl pkey -in /etc/postgresql/tls/server.key -pubout -outform DER |
  sha256sum
openssl x509 -in /etc/postgresql/tls/server.crt \
  -noout -subject -issuer -dates -ext subjectAltName

The first certificate in server.crt should be the server leaf followed by the issuing intermediates. A trust root generally belongs in the client's trust store, not appended as a substitute for a missing intermediate. Review mandatory access controls if ordinary Unix permissions look correct but PostgreSQL still cannot read the files.

Step 4: Enable TLS and make network authentication rules explicit

Add the following settings to the configuration discovered earlier. These paths are absolute, so they remain unambiguous if your distribution separates configuration and data directories. The minimum protocol is an example baseline for supported modern clients; test your actual client libraries before changing it on an established service.

ssl = on
ssl_cert_file = '/etc/postgresql/tls/server.crt'
ssl_key_file = '/etc/postgresql/tls/server.key'
ssl_min_protocol_version = 'TLSv1.2'

In pg_hba.conf, put an explicit plaintext rejection before the scoped TLS rule. Preserve required local administration and unrelated rules. Authentication records are evaluated in order; a broad earlier host rule could otherwise admit the connection before your carefully written hostssl record is reached.

# Example application network only; preserve existing local recovery rules.
hostnossl appdb appuser 10.20.0.0/24 reject
hostssl   appdb appuser 10.20.0.0/24 scram-sha-256

SCRAM authentication and TLS solve different problems. SCRAM does not replace transport encryption, and encrypting the connection does not grant a role access to every database. Keep database permissions, role membership, firewall policy, and connection limits aligned with the application's requirements. Do not broaden the example subnet to the whole internet to make a failed test disappear.

Inspect parsed access rules before reloading. For a Debian cluster, use its matching cluster command; elsewhere use the platform's service management or pg_ctl. A configuration reload should be followed by log inspection, because a successful signal does not prove every new setting was accepted.

SELECT line_number, error FROM pg_hba_file_rules WHERE error IS NOT NULL;
SELECT pg_reload_conf();

Step 5: Verify hostname checks and the plaintext rejection separately

Test from a representative application host, not just from the database's own shell. Use sslmode=verify-full so a successful connection demonstrates both trust-chain validation and hostname matching. sslmode=require can establish encryption without providing the same identity guarantees, and an application's default mode may allow fallback that your intended policy forbids.

psql "host=db.example.com dbname=appdb user=appuser \
sslmode=verify-full sslrootcert=/etc/ssl/certs/ca-certificates.crt" \
  -c 'SELECT ssl, version, cipher FROM pg_stat_ssl WHERE pid=pg_backend_pid();'

The CA bundle path shown is appropriate to many Debian-family systems; other platforms use another bundle or trust mechanism. Supply passwords through your normal protected credential mechanism, not inside a copied command line. Next attempt the same scoped network connection with sslmode=disable. It should fail because of the plaintext rejection. That negative test distinguishes an encrypted client preference from a server-enforced requirement.

Update application connection pools deliberately. Existing connections can remain open across a certificate reload, so they cannot demonstrate which certificate a fresh handshake receives. Open a new session, then roll pool instances gradually. Monitor connection failures and authentication errors while doing so. A verified administrative test is necessary but cannot substitute for testing the exact driver and options used by the application.

Connection strings deserve their own configuration review. A JDBC application, a libpq application, and a backup utility may express trust-store paths differently even when they all connect to PostgreSQL. Keep the successfully tested values in the deployment configuration for each component rather than documenting only a command that an operator ran once. Pay particular attention to environment variables injected by container orchestration, because a default connection URL can override an otherwise correct certificate-verification setting.

For a database behind a TCP load balancer, test every backend while preserving the expected service hostname. One healthy backend can hide another that still serves a previous certificate. Also confirm whether the load balancer passes TLS through or terminates it; those arrangements have different certificate owners. If termination occurs upstream, a query of pg_stat_ssl reports the database-side connection, not the browser or application connection before the proxy. Record both legs explicitly so monitoring results are interpreted correctly.

Step 6: Make renewal deployment reversible and observable

A renewal deploy hook should act only on this lineage, validate the candidate pair, install it with the existing ownership policy, and request a PostgreSQL reload. Stage both files before activation, retain the previous matched pair, and serialize deployments so two renewal processes cannot interleave certificate and key replacements. An atomic rename is useful for each staged file; orchestration still must keep the pair consistent while the service reloads.

PostgreSQL can reload SSL files for new connections. If it rejects a replacement, investigate its logs and the certificate actually served rather than assuming that a successful Certbot run updated the database. Monitor the database endpoint's expiry independently of the certificate files on disk. This catches a missed reload, incorrect path, or forgotten replica that still presents an older certificate.

Rollback means restoring the previous certificate and its corresponding private key, restoring any changed access rules if necessary, reloading, and testing a fresh verified connection. Keep the previous files until new sessions work from the application network. A rollback must not silently reintroduce plaintext access; if that becomes necessary for recovery, document and time-limit the exception explicitly.

Frequently asked questions

Does PostgreSQL need port 443 for a public certificate?

No. The certificate can be obtained with delegated DNS validation while PostgreSQL remains on its normal database port. Network access to the database should follow application requirements, independently of the ACME validation method.

Can I use one wildcard certificate for several database aliases?

A wildcard can match qualifying names at one subdomain level, but it does not cover the apex or deeper names automatically. Separate database keys often provide a smaller compromise boundary than sharing one wildcard key across unrelated servers.

Why does encryption work while verify-full fails?

Common causes are connecting through an alias absent from the certificate, an incomplete intermediate chain, or a client using an outdated CA bundle. Fix the identity or trust problem instead of disabling hostname verification permanently.

Does a SimpleACME TXT update confirm successful database renewal?

No. It records DNS challenge activity. Certificate issuance belongs to the ACME client, and successful deployment is demonstrated by a fresh PostgreSQL connection presenting the expected valid certificate.

Sources

PostgreSQL native TLS configuration documents certificate loading and key permissions. PostgreSQL authentication records explains ordered network rules and their reload behavior.

KEEP EXPLORING

A little more clarity.

ONE CNAME. A REPEATABLE WORKFLOW.

Your next certificate. A little less complicated.

Keep your DNS provider and your ACME client.
Prepare your domain before activating paid updates.

Create your account →Explore the integrations