PostgreSQL TLS Setup

You've built a PostgreSQL instance, tuned your shared_buffers, and written some elegant SQL. But if your database is still speaking plaintext on the wire, every query you run — including passwords and customer data — is readable by anyone who can sniff your network. In this lesson, you'll learn how to configure PostgreSQL for TLS encryption, eliminating that glaring security hole and bringing your database in line with modern security expectations.

The problem this lesson solves

By default, PostgreSQL does not encrypt the connection between your client and the server. This is fine on a localhost or a trusted private network, but the moment your database becomes reachable from other machines — a staging server, a container, a cloud VPC, a remote developer's laptop — you're exposed.

Imagine an attacker on the same network segment capturing packets. They don't need to break into your server; they just watch the traffic. With plaintext connections, they see your SQL statements, the data you're reading, and even the password your application uses to connect. That's a catastrophic breach that never touches your server logs.

TLS (Transport Layer Security) solves this by encrypting every byte between client and server. It also provides authentication — your client can verify it's talking to your database and not a man-in-the-middle impostor.

Why now? Modern compliance frameworks (PCI-DSS, HIPAA, GDPR) often require encryption in transit. Even if you're not legally bound, exposing plaintext database traffic is a top-tier security smell. Enabling TLS is not optional for any PostgreSQL deployment beyond a toy project.

Core concept / mental model

Think of TLS as a secure tunnel between your application and the database. Before any SQL is exchanged, the two sides perform a handshake: they agree on a cipher, validate certificates, and generate a shared session key. From then on, every packet is encrypted with that key.

Here's the cast of characters you need to know:

• CA (Certificate Authority) — a trusted third party that signs certificates. For production, you'll likely use a public CA (like Let's Encrypt) or your company's internal CA.
• Server certificate — a public certificate that identifies your database server. It includes the server's hostname and is signed by the CA.
• Private key — the secret half of the server's key pair. Only the PostgreSQL server should ever see it.
• Client certificate (optional) — used in mutual TLS (mTLS) for even stronger authentication.
• pg_hba.conf — PostgreSQL's host-based authentication file. This is where you tell PostgreSQL which connection types require TLS.

A useful analogy: the server certificate is like a company ID badge that the database shows your client. The private key is the secret that only the real server possesses to prove the badge isn't fake. The CA is the HR department that issued the badge and vouches for its authenticity.

How it works step by step

Enabling TLS on PostgreSQL involves three main stages:

1. Obtain or generate certificates — you need a server certificate and a private key. For testing, you can self-sign; for production, use a real CA.
2. Configure PostgreSQL to use them — set the ssl related parameters in postgresql.conf and pg_hba.conf.
3. Restart and verify — apply the changes, test from a client, and ensure your clients use sslmode=require or higher.

Step 1: Generate a self-signed certificate (for testing)

Use openssl to create a self-signed certificate valid for 365 days. For production, replace this with a certificate from a trusted CA.

# Generate a private key (2048 bits)
openssl genrsa -out server.key 2048

# Create a self-signed certificate (CN should match your server hostname)
openssl req -new -x509 -key server.key -out server.crt -days 365 -subj "/CN=db.example.com"

Step 2: Set file permissions

PostgreSQL will refuse to start if the key file has overly permissive permissions. Ensure the key is only readable by the postgres user.

chown postgres:postgres server.key server.crt
chmod 600 server.key
chmod 644 server.crt

Step 3: Edit postgresql.conf

Enable SSL and point PostgreSQL to your certificate files. You'll also want to set a strong ssl_ciphers list (see the troubleshooting section for details).

# postgresql.conf
ssl = on
ssl_cert_file = '/etc/postgresql/server.crt'
ssl_key_file = '/etc/postgresql/server.key'
ssl_ciphers = 'HIGH:!aNULL:!MD5'

Step 4: Update pg_hba.conf

You need to tell PostgreSQL which connections should require TLS. A common pattern is to force TLS for all remote connections while allowing local trust for maintenance.

# TYPE  DATABASE  USER  ADDRESS      METHOD
# Local connections (unix socket) - trust or md5
local   all       all                trust

# Remote connections must use TLS
hostssl all       all   0.0.0.0/0   scram-sha-256

Step 5: Restart PostgreSQL

Apply the changes by restarting the service.

sudo systemctl restart postgresql

Hands-on walkthrough

Let's do a complete exercise: we'll set up TLS on a fresh PostgreSQL instance in a Docker container, test it, and confirm encryption is active.

1. Start a PostgreSQL container with TLS files

Assume you've already generated server.crt and server.key in the current directory. Mount them into the container and pass the configuration via command-line flags.

docker run -d \
  --name pg-tls \
  -e POSTGRES_PASSWORD=secret \
  -v $(pwd)/server.crt:/etc/postgresql/server.crt \
  -v $(pwd)/server.key:/etc/postgresql/server.key \
  -p 5432:5432 \
  postgres:16 \
  -c ssl=on \
  -c ssl_cert_file=/etc/postgresql/server.crt \
  -c ssl_key_file=/etc/postgresql/server.key

2. Verify TLS is on

Connect to the container and query for SSL status.

docker exec -it pg-tls psql -U postgres -c "SHOW ssl;"

You should see on.

3. Test from a client with sslmode

From your host machine, connect using psql with sslmode=require. Because the container IP is different from the certificate's CN (db.example.com), you'll see a hostname mismatch error. For testing, we'll use sslmode=require which still encrypts but doesn't verify the hostname.

psql "host=localhost port=5432 user=postgres dbname=postgres sslmode=require password=secret"

If the connection succeeds, check the connection info:

\conninfo

Output should include: SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)

4. Force TLS and see rejection

Edit pg_hba.conf to use hostssl and remove any host (non-SSL) lines. Then restart. Now any client trying plaintext will fail.

psql "host=localhost port=5432 user=postgres dbname=postgres sslmode=disable password=secret"

You'll get an error like: FATAL: no pg_hba.conf entry for host ... or FATAL: SSL connection is required.

Compare options / when to choose what

There are several ways to set up TLS, each with trade-offs:

• Self-signed is fine for learning and internal dev, but you must distribute the certificate to every client or they'll get warnings.
• Public CA is the standard for anything exposed to the internet. Let's Encrypt is free and automated via certbot.
• mTLS is used in high-security environments where you want to authenticate both sides of the connection, not just the server.

Troubleshooting & edge cases

"connection requires a valid client certificate"

If you've enabled verify-full or verify-ca client-side, you must provide a client certificate. Add sslcert and sslkey to your connection string. For server-side, ensure pg_hba.conf doesn't require client certs unless you've set them up.

"FATAL: no pg_hba.conf entry for host ..."

Your pg_hba.conf probably has no hostssl line for the client's IP/user. Remember that host rules match both SSL and non-SSL; if you only want SSL, use hostssl and remove the matching host lines.

"server certificate does not match hostname"

When using sslmode=verify-full, the client checks that the certificate's CN/SAN matches the hostname. If you're connecting to localhost but the cert says db.example.com, you'll get this error. Fix: use the exact hostname, or regenerate the cert with the correct CN/SAN.

Weak cipher/encryption errors

Older clients might complain about weak ciphers if your ssl_ciphers is too restrictive. Use a well-known secure set: 'HIGH:!aNULL:!MD5' or the more modern 'TLSv1.2+TLSv1.3:!aNULL:!eNULL:!MD5'. If you drop TLSv1.2, very old clients will fail.

Certificate permission errors on startup

PostgreSQL will refuse to start if server.key is world-readable. Ensure chmod 600. Also verify the postgres user can read the file.

What you learned & what's next

In this lesson you learned how to configure PostgreSQL for TLS encryption — from generating a certificate to editing postgresql.conf and pg_hba.conf, and verifying that connections are actually encrypted. You now understand the difference between self-signed, CA-signed, and mutual TLS, and you can troubleshoot common TLS pitfalls.

Now that your data is encrypted in transit, your next step is to harden authentication further. In the next lesson, you'll explore client certificate authentication in depth — how to create client certificates, configure pg_hba.conf to require them, and set up mutual TLS for that extra layer of trust.