A beginner’s guide to encrypting your database connections

“’SSL is enabled’ and ‘SSL is actually working’ are two very different things.”

1. What is SSL, and why does a database need
it?

SSL stands for Secure Sockets Layer. Its successor is TLS (Transport Layer Security), but in the PostgreSQL world, and in most documentation, people still call it SSL out of old habit. Don’t let that confuse you. When someone says “SSL” in a Postgres context, they mean modern TLS-based encryption.

Here’s the problem it solves. By default, when your application connects to PostgreSQL, everything travels across the network in plain text. Usernames. Passwords. Every query you run. Every row of data that comes back. If anyone can intercept that traffic, someone on the same network, a compromised internal service, they can read all of it. A basic packet sniffer is enough. No special skills needed.

SSL wraps that connection in encryption before any data is exchanged. What travels on the wire becomes unreadable noise to anyone who doesn’t hold the session keys.

ℹ Note:  Even inside a private network or VPC, this matters. And the “it’s an internal network” line doesn’t protect you from lateral movement attacks, where an attacker is already inside the perimeter

2. How does SSL actually work?

When a client connects to PostgreSQL with SSL, before any database traffic is exchanged, this sequence happens: The client opens a plain TCP connection and signals it wants SSL. The server sends its certificate — a signed document that proves the server’s identity and contains its public key. The client checks whether that certificate was signed by a Certificate Authority it trusts, and whether the hostname in the certificate matches what it is connected to. If both checks pass, both sides negotiate a cipher and derive a shared session key. After that, all PostgreSQL traffic — authentication, queries, and results run inside the encrypted tunnel.

The certificate verification step is what separates real security from the feeling of it. Without verifying the cert, you could be encrypting traffic straight to an attacker’s machine. The certificate is proof that you’re talking to the right server.

3. Generating certificates with OpenSSL

PostgreSQL does not generate its own certificates. You do that with OpenSSL, the standard tool for this, and almost certainly already installed on your system. One important thing to know before you start: the certificate files must exist before you edit postgresql.conf. When PostgreSQL starts with ssl = on, it immediately looks for the cert and key files. If they are not there, it refuses to start entirely. Certs first, config second.

For development and internal tooling, a self-signed certificate is perfectly fine. You control both ends of the connection, so there’s no need for a third party to vouch for you:

# Step 1 — generate the private key
openssl genrsa -out server.key 4096

# Step 2 — generate a self-signed certificate valid for 365 days
#          CN should match the hostname clients will connect to
openssl req -new -x509 -days 365 -key server.key -out server.crt \
  -subj "/CN=db-hostname"

# Step 3 — with a self-signed cert, the cert itself is the CA
#          copy it as root.crt so clients can verify against it
cp server.crt root.crt

# Step 4 — lock down permissions (Postgres refuses to start if this is wrong)
chmod 0600 server.key
chown postgres:postgres server.key server.crt root.crt

Move all three files into PostgreSQL’s data directory. If you’re unsure where that is: psql -c “SHOW data_directory;”

✓ Why copy server.crt as root.crt?

When the client uses sslmode=verify-full, it needs a CA certificate to verify the server against. With a self-signed cert, the certificate is its own CA, so you hand the same file to the client as its trusted root. In production with a real CA, you’d distribute the CA’s certificate to clients instead, and they never need to see the server cert directly.

⚠ Production note:

For production, use a properly CA-signed certificate. Generate a private key and a Certificate Signing Request (CSR) with OpenSSL, submit the CSR to a Certificate Authority — internal or public — and use the signed certificate they return as your server.crt. This is what allows clients to verify the server’s identity against a trusted root without needing a copy of the cert itself.

4. Enabling SSL in postgresql.conf

Now that the certificate files exist, it’s safe to edit the config. Open postgresql.conf and set these values in the SSL section:

# postgresql.conf
ssl                      = on
ssl_cert_file            = 'server.crt'   # relative to data directory
ssl_key_file             = 'server.key'
ssl_ca_file              = 'root.crt'     # required for client verify-full to work
ssl_min_protocol_version = 'TLSv1.2'     # reject older, insecure protocol versions

The paths are relative to the data directory, which is exactly where you just placed the files. Setting ssl = on makes encrypted connections available. It does not make them required. That is pg_hba.conf’s job.

ℹ Note:  ssl_ca_file is frequently left out of examples, but it’s required if you want clients to use sslmode=verify-full. Without it, the server has no CA to cross-reference, and full verification won’t work correctly on the client side.

5. The one word that changes everything

The connection type column in pg_hba.conf has three values that matter here: host,hostssl and hostnossl

The default in most setups is host. Which means a client that does not request SSL still gets in. The server dutifully logs a successful connection. Nothing in the logs indicates it was unencrypted. Everything looks fine.

Changing host to hostssl for remote connections is the single edit that closes the gap. A client without SSL is rejected outright. There is no fallback, no silent downgrade, no option. The connection either comes in encrypted or it does not come in at all.

# pg_hba.conf
# TYPE     DATABASE  USER  ADDRESS      METHOD
hostssl    all       all   0.0.0.0/0    scram-sha-256

After editing both config files, restart PostgreSQL, not just reload. The ssl = on parameter requires a full restart to take effect.

6. The client side is where it gets interesting

The client controls its SSL behavior through the sslmode parameter in the connection string. There are six modes, and the difference between some of them is the difference between actual security and the feeling of it.

# What a complete connection string looks like
psql "host=db.example.com dbname=mydb user=appuser \
      sslmode=verify-full sslrootcert=/path/to/root.crt"

# Connection URL format (common in app configs and ORMs)
postgresql://appuser@db.example.com/mydb?sslmode=verify-full&sslrootcert=/path/to/root.crt

The combination of hostssl on the server and verify-full on the client is what “SSL is configured” should actually mean.

7. How to check what is really happening

Do not trust the configuration. After any SSL-related change, verify directly. PostgreSQL exposes a view called pg_stat_ssl that shows the SSL status of every active connection:

-- Is the current session encrypted?
SELECT ssl, version, cipher, bits
FROM   pg_stat_ssl
WHERE  pid = pg_backend_pid();

-- Are any remote connections currently running WITHOUT encryption?
SELECT pid, usename, application_name, client_addr, ssl
FROM   pg_stat_ssl
JOIN   pg_stat_activity USING (pid)
WHERE  ssl = false
  AND  client_addr IS NOT NULL;

If the second query returns any rows, those connections are going through in plain text right now. That query belongs in your monitoring stack. Run it on a schedule and alert on any results from non-local addresses. It takes minutes to set up and gives you continuous proof rather than a one-time config review that everyone forgets about.

To check when your certificate expires before it catches you off guard:

openssl x509 -in server.crt -noout -dates

Things not to forget and mistakes to avoid

• host instead of hostssl in pg_hba.conf. Already covered at length, but worth repeating because it is the most common gap. You can have a correct cert, a correct postgresql.conf, and still be accepting plain-text connections because of this one word.
• Leaving sslmode=prefer in your application. It’s the default in libpq and in most ORMs — Django, SQLAlchemy, ActiveRecord. Almost no one overrides it explicitly. If your app connects with prefer, an attacker who can intercept the connection just strips the SSL negotiation, and your app reconnects in plain text without a single log entry indicating anything went wrong.
• Using sslmode=require and thinking you’re done. require encrypts the traffic but doesn’t verify the server’s certificate at all. A man-in-the-middle can present their own certificate and your app will accept it. It’s better than prefer, but it is not the same as verify-full.
• Forgetting certificate expiry. Certificates have a fixed validity period. When one expires, PostgreSQL stops accepting SSL connections — at the worst possible time, on a day nobody planned for. Either automate certificate rotation or put a calendar reminder at least 30 days before the expiry date. The mental note is the one that fails.
• Not applying the same config to replicas. Teams tighten SSL on the primary, add a standby months later, and apply the right pg_hba.conf entries to the primary but not the new one. Read traffic goes through the replica. The replica is open. It happens because replicas attract far less attention than primaries — they’re quiet, they mostly just work, and they accumulate configuration drift over time.

If you take only a few things from this blog, let them be these:  generate certs first, then ssl = on in postgresql.conf, then hostssl in pg_hba.conf, then sslmode=verify-full in your connection string. Then run the pg_stat_ssl check to confirm. That sequence, done in that order, is what “SSL is configured” should actually mean.

Official documentation

https://www.postgresql.org/docs/current/ssl-tcp.html

http://postgresql.org/docs/current/encryption-options.html