SSL/TLS Certificates for Databases: PostgreSQL and MySQL

Which SSL certificate does a database actually need?

An ordinary TLS server certificate, with nothing special about it. It needs the serverAuth extended key usage, a subject alternative name matching the hostname clients dial, and a chain the clients can build to a root they trust. No certificate authority sells a “database certificate” because there is no such field in X.509 to sell. Validation level is a business question, not a technical one: DV encrypts and identifies the name exactly as well as OV does.

What changes is who is looking. A web certificate is judged by browsers, which ship a trust store, update it silently, and complain loudly in a way a human notices. A database certificate is judged by client libraries — libpq, JDBC, psycopg, Npgsql, the MySQL connectors — each with its own trust configuration, its own defaults, and no interface to shout at anybody. The failure mode is not a warning page. It is a connection that either fails at three in the morning or silently skips verification for a year.

One structural point is worth settling early because it changes what you buy. A TLS endpoint is a hostname, and each hostname that terminates its own connection needs a certificate valid for it. A primary at db.example.com, a read replica at replica.example.com and a PgBouncer in front at pool.example.com are three endpoints. Putting the three names on one multi-domain certificate gives you one renewal instead of three, which matters more every year as the windows shrink.

When is a publicly trusted certificate the right choice?

When the hostname is publicly resolvable and the clients are not all yours to configure. A publicly trusted certificate is verified against the trust store the operating system already ships, so a contractor's laptop, a customer's ETL job and a managed analytics service all validate it without you touching them. That is the entire benefit, and on an estate with more than a handful of client types it is a large one.

The constraint is the name. Publicly trusted CAs have been barred from issuing for internal names and reserved IP ranges since 2015, so pg-01.internal and 10.0.4.19 are out, and domain control validation could not succeed for them in any case. What people miss is that this rules out the name, not the address: a database that only listens on a private IP can still hold a certificate for db.example.com, because validation happens in DNS and never involves connecting to the host. Split-horizon DNS with a public record for validation and a private answer for clients is a normal, supported arrangement, and our guide to certificates for internal server names works through the mechanics.

A private CA earns its place when the estate is closed and uniform: every client is yours, every trust store is centrally managed, and the hostnames are internal by design. It also handles cases a public CA cannot touch, which now includes client certificates. The cost is real and recurring — root distribution into every JVM keystore, container image and language runtime, plus revocation and root rollover as ongoing work — and it lands on the same team either way.

Certificate Transparency is the third input to this decision and the one that surprises people. Every publicly trusted certificate is logged, publicly and permanently, so issuing for payments-db-prod.example.com publishes that hostname to anyone reading the logs. That is not a reason to avoid public certificates. It is a reason to pick database hostnames that you are content to have read, which costs nothing if you decide it before the first order rather than after.

verify-ca vs verify-full: the weaker setting is the dangerous one

Use verify-full. It is the only PostgreSQL mode that checks the hostname you dialled against the certificate you were served; verify-ca stops after the chain check. With a private CA that gap is narrow, because only your own CA's certificates pass. With a publicly trusted CA the gap is the whole internet: any certificate that CA has issued to anyone, for any name, satisfies verify-ca. The PostgreSQL manual says so directly.

MySQL draws the same line one step further along its ladder. VERIFY_CA validates the chain, and VERIFY_IDENTITY adds the hostname check. The trap in MySQL is different and more common: the server generates a self-signed certificate at first startup if none is configured, and that certificate carries no server name, so VERIFY_IDENTITY cannot succeed against it. Teams hit the failure, drop to VERIFY_CA to make it go away, and ship that.

Both defaults deserve a moment of attention, because they are the reason so many production connections are unverified. libpq defaults to prefer and MySQL to PREFERRED, and neither will tell you it downgraded. A connection string with no explicit mode in it is a connection you have made no promises about.

How to set up TLS on PostgreSQL

Three settings in postgresql.conf and one file permission. Set ssl = on, point ssl_cert_file at the certificate concatenated with its intermediates and ssl_key_file at the private key, then reload. The key must not be readable by group or world: PostgreSQL refuses to start otherwise, and that single check accounts for most first-attempt failures.

# postgresql.conf
ssl = on
ssl_cert_file = '/etc/postgresql/tls/server.crt'   # leaf + intermediates
ssl_key_file  = '/etc/postgresql/tls/server.key'
ssl_ca_file   = '/etc/postgresql/tls/root.crt'     # only for client certs

The permission rule has a second form that matters far more than its obscurity suggests. The key may be 0600 owned by the database user, or 0640 owned by root with the database user in the owning group. That second form is what makes automated renewal workable, because ACME clients write as root and you would otherwise be chowning private keys in a deploy hook. Set it up once and every future renewal lands correctly on its own.

ssl_ca_file is not part of serving a certificate, which is a routine source of confusion. It is the root PostgreSQL uses to verify client certificates, so leave it unset unless you are doing mutual TLS. Requiring encryption is a separate job again, done in pg_hba.conf: change host lines to hostssl, or add a hostnossl ... reject line, otherwise a client that skips TLS is still welcome.

On the client side, the connection string carries the promise. Ask for sslmode=verify-full and give it a root to verify against with sslrootcert. PostgreSQL 16 added a much better answer for publicly trusted certificates: sslrootcert=system uses the operating system's own trust store, which means no CA file to ship, no path to keep in sync, and nothing to update when a root changes. Because a system store without a hostname check would be actively unsafe, libpq raises the default to verify-full when you use it.

# Publicly trusted certificate, PostgreSQL 16+ client
psql "host=db.example.com dbname=app user=app \
      sslmode=verify-full sslrootcert=system"

# Private CA, or a client older than 16
psql "host=db.example.com dbname=app user=app \
      sslmode=verify-full sslrootcert=/etc/ssl/certs/internal-root.crt"

How to set up TLS on MySQL

Point three variables at your files in the [mysqld] section and restart once. MySQL 8.0 will already be serving TLS when you get there, using a self-signed certificate it generated at first startup, so the job is usually replacing something rather than enabling it. That auto-generated certificate is also why VERIFY_IDENTITY fails before you start: it carries no server name.

# my.cnf
[mysqld]
ssl_cert = /etc/mysql/tls/server.crt   # leaf + intermediates
ssl_key  = /etc/mysql/tls/server.key
ssl_ca   = /etc/mysql/tls/root.crt     # only for client certs
require_secure_transport = ON

require_secure_transport = ON is the setting that turns encryption from available into mandatory; without it, clients that ask for plaintext get plaintext, and the server-side certificate work protects nobody in particular. Per-account rules give you a finer grip when a full switch would break a legacy job: ALTER USER ... REQUIRE SSL for one account, REQUIRE X509 when that account must also present a client certificate.

Clients then need to be told to check what they are given, and MySQL is quieter about this than PostgreSQL because its default is PREFERRED. Ask for the top rung explicitly:

mysql --host=db.example.com --user=app \
      --ssl-mode=VERIFY_IDENTITY \
      --ssl-ca=/etc/ssl/certs/ca-certificates.crt

# Confirm what you actually negotiated
mysql> SHOW STATUS LIKE 'Ssl_cipher';
mysql> SHOW STATUS LIKE 'Ssl_server_not_after';

MySQL has no equivalent of sslrootcert=system, so even a publicly trusted certificate needs --ssl-ca pointed at a CA bundle. On most Linux hosts the operating system bundle at /etc/ssl/certs/ca-certificates.crt is the right file, and using it rather than a copied-out root is what keeps the setup working through a root rollover you never hear about. Ssl_server_not_after is worth knowing on its own: it is the expiry of the certificate the server is serving right now, which is exactly what you check after a reload.

How to renew a database certificate without restarting

Write the new files over the old paths and issue one reload statement. PostgreSQL has re-read its TLS files on SIGHUP since version 10, so SELECT pg_reload_conf() is enough. MySQL 8.0.16 added ALTER INSTANCE RELOAD TLS, which rebuilds the TLS context in place and needs the CONNECTION_ADMIN privilege. MariaDB 10.4 added FLUSH SSL for the same purpose.

Two properties of these commands decide how you build the automation around them. The paths cannot change, because the variables holding them are not dynamic — the reload re-reads the files at the configured locations, so a renewal that writes to a new dated directory has to be followed by a symlink swap or a copy into place. And the new context applies to new connections only. Sessions already open finish on the certificate they negotiated, which is what makes the operation genuinely non-disruptive, and also why a pooled application may take a while to visibly pick up the new certificate.

MySQL rolls the reload back if the new files will not load, keeps serving the previous context, and returns an error. That is good behaviour and a bad trap for a script, because a half-written file from a renewal that was still running leaves you with a healthy database and a certificate that is not the one you think you deployed. Verify after the reload rather than trusting the exit status: SHOW STATUS LIKE 'Ssl_server_not_after' on MySQL, or an openssl s_client -starttls postgres probe against PostgreSQL.

A working ACME deploy hook for PostgreSQL is four lines, and the ownership line is the one people leave out:

#!/bin/sh
# certbot --deploy-hook, or the equivalent in acme.sh / lego
install -o root -g postgres -m 0640 "$LIVE/privkey.pem" /etc/postgresql/tls/server.key
install -o root -g postgres -m 0644 "$LIVE/fullchain.pem" /etc/postgresql/tls/server.crt
psql -U postgres -c 'SELECT pg_reload_conf();'

DNS-01 is nearly always the right challenge type here, because a database host has no reason to be serving HTTP on port 80 and every reason not to. Which ACME challenge your setup can actually use covers the trade-offs, including the delegation pattern that keeps DNS API credentials off the database host.

What 200-day certificates change for a database estate

They convert a background task into a scheduled one, and then into an automated one. A publicly trusted certificate issued today may last 200 days at most; from 15 March 2027 that becomes 100 days, and from 15 March 2029, 47 days. Domain validation reuse falls on the same schedule and lands much lower, at 10 days, so by 2029 each name is re-proved roughly 35 times a year rather than renewed 8 times.

Databases absorb this worse than web servers for reasons that have nothing to do with TLS. Change control is heavier, the maintenance window is a negotiation, and the fleet is long-lived: an instance provisioned in 2021 with a three-year certificate has never had a renewal, so nobody on the current team has done one. The first renewal under the new regime is therefore also the first test of a procedure that may not exist, and it arrives on a deadline.

The practical move is to decouple the two halves now, while there is slack. Automate acquisition — ACME with DNS-01, or a scripted order and install — and automate deployment with the reload commands above, then run the whole loop once on a replica with a deliberately short certificate to prove it works end to end. A rehearsal on a replica costs an afternoon. Our broader guide to preparing for shorter certificate lifetimes covers the same problem across the rest of the estate.

Monitoring needs one adjustment that is easy to miss. Alerting on the certificate file on disk tells you what the renewal process wrote, not what the server is serving, and after a failed reload those are different things for months. Probe the live port and read the certificate off the wire. On PostgreSQL that means an openssl s_client -starttls postgres check; on MySQL, Ssl_server_not_after straight from the running server.

The part that breaks: trust on every client

Server configuration is an afternoon; client trust is the project. A database is dialled by application pods, batch jobs, BI tools, admin laptops and whatever a contractor installed in 2023, and each one decides for itself which roots to trust and where to find them. A publicly trusted certificate collapses that work, because every one of those clients already ships a trust store containing the root.

A private CA does not get that for free, and the list of places its root has to be installed is longer than the plan assumes: thecacerts keystore of every JVM, container images that bake their own CA bundle, Python and Node.js runtimes with their own trust handling, Go binaries built with a pinned bundle, and every developer machine. None of these is difficult. There are simply many of them, they are discovered one at a time, and each is discovered by something breaking.

Mutual TLS is where the two routes stopped being interchangeable during 2026. Publicly trusted TLS certificates no longer carry the clientAuth extended key usage, so PostgreSQL clientcert=verify-full and MySQL REQUIRE X509 now need client certificates from a private CA regardless of what signs the server side. The removal of clientAuth from public TLS certificates sets out the dates and what replaces it. A public certificate on the server with a private CA for clients is a perfectly reasonable arrangement, and increasingly the only one available.

What each failure message is telling you

Database TLS errors are unusually literal, and the same handful account for nearly all of them. The table below maps each to its actual cause, which is rarely the certificate itself.

The last row is the one worth auditing deliberately, because nothing will ever surface it for you. Enforce the floor at the server with hostssl or require_secure_transport, so a client that forgot to ask for verification is refused rather than quietly accommodated.

FAQ