SSL/TLS Encryption in Transit & Certificate Management

Why Encrypt Database Connections?

When a client application connects to a database server over a network, the data packets travel through switches, routers, and potentially untrusted segments. Without encryption, any device on that path can read usernames, passwords, queries, and result sets in plain text. This is called an on-path (or man-in-the-middle) attack. SSL/TLS wraps the connection in an encrypted tunnel so that intercepted packets are unreadable without the session keys.

Encryption in transit is different from encryption at rest. In-transit encryption protects data moving across a network. At-rest encryption (such as Microsoft's Transparent Data Encryption, covered later) protects data stored on disk. A database system needs both layers to be fully protected.

How SSL/TLS Works in PostgreSQL

PostgreSQL uses the OpenSSL library to provide TLS support. When SSL is enabled, the server presents a certificate to the client during the handshake. The certificate contains the server's public key and is signed by a Certificate Authority (CA). The client checks that signature to confirm it is talking to the right server, not an impersonator.

Three files control SSL in PostgreSQL:

• server.crt — the server's public certificate, shown to clients.
• server.key — the server's private key; never shared. Access must be restricted.
• root.crt — the CA certificate used to verify client certificates (required only for mutual/client-certificate authentication).

These files must sit in the PostgreSQL data directory (the folder that contains `postgresql.conf` and `pg_hba.conf`). The private key file must be owned by the `postgres` OS user and have permissions 0600 (read and write for owner only). If the permissions are too open, PostgreSQL will refuse to start with SSL enabled. This is a security control, not an accident — a world-readable private key is a compromised private key.

Generating a Self-Signed Certificate with OpenSSL

In a production environment you would obtain a certificate from a trusted CA such as DigiCert or your organization's internal CA. For lab and testing purposes a self-signed certificate works just as well to practice the configuration steps. The steps are:

1. Generate a CA key and self-signed CA certificate.

2. Generate a server key and a Certificate Signing Request (CSR).

3. Sign the CSR with the CA to produce the server certificate.

4. Copy `server.crt` and `server.key` to the data directory and set ownership and permissions.

See the Worked Examples section below for the exact OpenSSL commands.

Configuring postgresql.conf and pg_hba.conf

Once the certificate files are in place, open `postgresql.conf` and set:

```

ssl = on

```

Then reload the server (`pg_ctlcluster reload` on Debian/Ubuntu systems, or `SELECT pg_reload_conf();` from a superuser session). PostgreSQL does not require a full restart for this change.

Next, edit `pg_hba.conf` to require SSL for specific connections by using the hostssl record type instead of host. A `hostssl` line accepts only encrypted connections; a plain `host` line accepts both. If a client tries to connect with `sslmode=disable` to a `hostssl` entry, the server rejects the connection. This is how you enforce encryption — not just offer it.

Example `pg_hba.conf` line:

```

hostssl all all 0.0.0.0/0 scram-sha-256

```

Verifying the Connection

After reloading, connect with psql using:

```

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

```

Inside the session, run `\conninfo`. The output will include the phrase SSL connection (protocol: TLSv1.3, ...) confirming the tunnel is active.

You can also verify from outside psql using the OpenSSL client tool:

```

openssl s_client -connect localhost:5432 -starttls postgres

```

This shows the full TLS handshake, the certificate chain, and the cipher suite negotiated.

To confirm enforcement, set `sslmode=disable` in the connection string. When `pg_hba.conf` uses `hostssl`, the server returns a connection-refused message — proof that unencrypted connections are blocked.

Certificates and Client Authentication

Certificates are not only for server identity. PostgreSQL supports certificate-based client authentication, where each client presents its own certificate signed by a trusted CA. The server checks that certificate instead of (or in addition to) a password. This is stronger than password authentication because the private key never travels over the network. When configured, `pg_hba.conf` uses the `cert` authentication method on a `hostssl` line.

Comparison: In-Transit vs. At-Rest Encryption

The DP-300 exam covers Transparent Data Encryption (TDE) in Azure SQL Database. TDE encrypts data files, log files, and backups on disk automatically. It does not protect data as it moves between the server and a client — that is the job of TLS. Key distinction: TLS protects data on the wire; TDE protects data on the drive.

Safety and Operational Notes

• Treat private key files with the same care as passwords. Limit file system access strictly.
• Rotate certificates before they expire. Expired certificates will break client connections.
• Keep a copy of CA certificates in a secure backup. Losing the CA means you cannot sign new server or client certificates without rebuilding the trust chain.
• Document every change to `postgresql.conf` and `pg_hba.conf` in your change-management system before applying it to production.