Secure Connections with SSL/TLS

You've built a solid PostgreSQL setup, but every connection to your database is silently traveling in plain text — readable by anyone who can sniff your network. That's a nightmare for compliance (think PCI-DSS or GDPR), a gift to attackers, and a blunt invitation for data leaks. This lesson fixes that pain: you'll learn to secure connections with SSL/TLS, turning insecure, eavesdroppable sessions into encrypted, verified ones. By the end, you'll have a PostgreSQL server that refuses plaintext connections, clients that verify server identity, and the confidence to configure TLS in any environment.

The problem this lesson solves

Imagine a busy coffee shop. You're working remotely, and your laptop connects to your office PostgreSQL server across the public internet. Without any protection, every SQL query you run — SELECT, INSERT, even your database password — is broadcast as raw text. Anyone with a packet sniffer (Wireshark, tcpdump) sees everything, like reading a postcard that anyone can pick up and read.

The core problem: PostgreSQL, by default, sends data unencrypted over the network. If you haven't explicitly enabled SSL/TLS, your client and server negotiate a plaintext connection. That's a ticking time bomb for:

• Data breaches: Credit card numbers, personal emails, health records flying naked.
• Credential theft: Your DB password is transmitted in plain text during authentication.
• Man-in-the-middle (MITM) attacks: An attacker can intercept, modify, or inject queries between your app and database.
• Regulatory fines: PCI-DSS, GDPR, HIPAA all mandate encryption for sensitive data at rest and in transit.

This lesson gives you a concrete, step-by-step path to kill that vulnerability — you'll configure PostgreSQL to require TLS, generate certificates, and verify connections.

Core concept / mental model

Think of SSL/TLS as a secure armored truck for your data. Your SQL queries are valuable packages. Without TLS, they're thrown into a regular mail van — reliable, but any thief can break in. TLS wraps each package in a tamper-proof, encrypted container that only the intended recipient can open, and it includes a tamper-evident seal to prove the container hasn't been swapped.

The security works in two layers:

1. Encryption: All bytes between client and server are scrambled using symmetric keys (AES-256, for example). Even if an attacker intercepts the traffic, it's meaningless without the key.
2. Authentication: The server presents a digital certificate signed by a Certificate Authority (CA). The client verifies the certificate's signature. If valid, the client knows it's talking to the real server, not a fake one. This prevents MITM attacks.

Here's the mental model in words:

• Certificate = Your server's passport with a public key.
• Private key = The secret key that proves the passport is real.
• Certificate Authority (CA) = The trust anchor that vouches for the passport.
• Verification = Checking the passport's signature at the border.

How it works step by step

Before hands-on, understand the negotiation flow:

1. Client initiates connection — psql or your app connects to port 5432.
2. Server advertises SSL support — if ssl = on, the server sends a message saying it can accept TLS.
3. Client requests SSL — the client replies "let's encrypt."
4. TLS handshake — they exchange keys, server sends its certificate.
5. Certificate verification — the client checks the CA signature and hostname.
6. Secure session — all subsequent traffic is encrypted.

If any step fails, the connection aborts with a clear error message.

The key configuration files

PostgreSQL TLS settings live in postgresql.conf and pg_hba.conf:

• postgresql.conf — server-side TLS parameters (ssl, cert paths, ciphers).
• pg_hba.conf — host-based access rules, where you decide which connection types require SSL (hostssl vs host).

You'll also need a certificate chain: a CA key, a CA certificate, a server key, and a server certificate signed by that CA. For production, use a public CA (Let's Encrypt) and register the server hostname. For local/private networks, create your own self-signed CA.

Hands-on walkthrough

This walkthrough sets up a local test environment with a self-signed CA. You'll learn the commands and see real output.

1. Generate certificates

Let's create a CA, a server key, and a server certificate. Open a terminal and run:

# Create a directory for certs
mkdir ~/pg-ssl && cd ~/pg-ssl

# Create CA private key (no passphrase for simplicity)
openssl genrsa -out ca.key 2048

# Create CA certificate (common name = our CA)
openssl req -new -x509 -days 3650 -key ca.key -out ca.crt -subj "/CN=MyTestCA"

# Create server private key
openssl genrsa -out server.key 2048

# Create certificate signing request (CSR)
openssl req -new -key server.key -out server.csr -subj "/CN=localhost"

# Sign the CSR with our CA to get the server certificate, with SAN for localhost
cat > server.ext <<EOF
subjectAltName=DNS:localhost,IP:127.0.0.1
EOF
openssl x509 -req -in server.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out server.crt -days 365 -extfile server.ext

# Set proper permissions
chmod 600 server.key ca.key

2. Configure PostgreSQL to use the certs

Edit postgresql.conf (find it with SHOW config_file;). Add or uncomment:

ssl = on
ssl_cert_file = '/home/youruser/pg-ssl/server.crt'
ssl_key_file = '/home/youruser/pg-ssl/server.key'
ssl_ca_file = '/home/youruser/pg-ssl/ca.crt'

Restart PostgreSQL:

sudo systemctl restart postgresql

3. Force SSL for all host connections

Edit pg_hba.conf and replace host lines with hostssl to require encryption:

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

Reload configuration:

sudo systemctl reload postgresql

Now, any non-SSL connection will be rejected.

4. Connect with SSL and verify

Connect using psql, explicitly requiring SSL and CA verification:

psql "host=localhost dbname=mydb user=myuser sslmode=verify-full sslrootcert=~/pg-ssl/ca.crt"

Once connected, check the SSL status:

SELECT ssl FROM pg_stat_ssl WHERE pid = pg_backend_pid();

Expected output:

 ssl 
-----
 t
(1 row)

You've just confirmed your connection is encrypted.

5. Test that plaintext connections fail

Try connecting without SSL:

psql "host=localhost dbname=mydb user=myuser sslmode=disable"

You should get an error:

psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "myuser", database "mydb", no encryption

That error is the server telling you: plaintext is not welcome.

Compare options / when to choose what

You have several ways to implement SSL/TLS in PostgreSQL. Here's a comparison table:

Decision rule: For any production database, use sslmode=verify-full with a public or private CA. For local development, sslmode=require is acceptable — but never ship that to production.

Troubleshooting & edge cases

Here are common issues and how to fix them:

1. "FATAL: no pg_hba.conf entry"

Problem: Your pg_hba.conf requires hostssl, but the client connection isn't using SSL. Fix: Ensure your client sets sslmode=require or higher. Also check the address ranges — 0.0.0.0/0 matches all IPv4, but not IPv6 (you need ::0/0).

2. "root certificate file does not exist or is unreadable"

Problem: The sslrootcert path is wrong or file permissions are too open. Fix: Check the path in your client command; ensure the file is readable by your user. chmod 600 is fine.

3. Certificate validation fails: "server certificate for 'localhost' does not match host name"

Problem: The server cert's Subject Alternative Name (SAN) doesn't include the host you're connecting to. Fix: Regenerate the cert with the correct SAN. Use a server.ext file with subjectAltName=DNS:yourhostname,IP:127.0.0.1.

4. "SSL error: certificate verify failed"

Problem: Client doesn't trust your CA cert. Fix: Set sslrootcert to your CA cert path. For self-signed, also ensure the CA cert is in the client's trust store.

Pro tip: Always test your configuration with sslmode=verify-full locally. You'll catch certificate issues early and avoid production surprises.

What you learned & what's next

You've conquered a critical security gap: your PostgreSQL connections are now encrypted and verified. You understand how SSL/TLS works, you generated certificates, configured the server, enforced SSL via pg_hba.conf, and connected with client-side verification. You also know how to troubleshoot common errors and choose the right SSL mode for each scenario.

Next lesson: You're ready to dig into connection pooling and performance tuning. With TLS active, you'll want to manage connection overhead efficiently using PgBouncer, and then tune your server for high concurrency. The skills you've built — certificate handling, secure config, and verification — will be the foundation for those advanced topics.