How to Enable Encryption in Transit in PostgreSQL

Galaxy Glossary

How do I enable and verify SSL encryption for PostgreSQL connections?

PostgreSQL supports SSL/TLS so client-server traffic stays unreadable to eavesdroppers.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Table of Contents

What is "encryption in transit" for PostgreSQL?

Encryption in transit secures the network stream between client and server with SSL/TLS, blocking sniffing and man-in-the-middle attacks.

How do I turn on SSL on the PostgreSQL server?

Set ssl = on in postgresql.conf, place a valid server.key and server.crt in $PGDATA, then reload or restart the cluster.

Step 1 – Generate a self-signed certificate (quick test)

openssl req -new -x509 -days 365 -nodes -text \ -out $PGDATA/server.crt -keyout $PGDATA/server.key

Step 2 – Harden file permissions

chmod 600 $PGDATA/server.key $PGDATA/server.crt

Step 3 – Update `pg_hba.conf`

Add a hostssl line, for example:
hostssl ecommerce all 10.0.0.0/16 cert

How do clients enforce encrypted connections?

Use connection parameters sslmode=require|verify-ca|verify-full and supply CA certificates with sslrootcert.

Example psql call

psql "host=db.internal port=5432 dbname=ecommerce user=app sslmode=verify-full sslrootcert=$HOME/.postgresql/root.crt"

How can I confirm traffic is encrypted?

Run SELECT ssl, ssl_cipher FROM pg_stat_ssl WHERE pid = pg_backend_pid(); – ssl should be t.

Which ecommerce queries stay identical?

Any DML/DDL (e.g., SELECT id, email FROM Customers;) works the same; only the connection string changes.

Best practices for production

Use certificates signed by an internal or public CA.
Rotate keys regularly.
Set ssl_ciphers to modern suites only.
Create separate CA for client certs and require clientcert=verify-full in pg_hba.conf.

How to automate certificate renewal?

Combine certbot or corporate PKI with PostgreSQL ALTER SYSTEM SET ssl_reload_config = on; and signal pg_ctl reload.

What monitoring should I enable?

Query pg_stat_ssl and integrate metrics (cipher, version) into Prometheus or New Relic.

.

Why How to Enable Encryption in Transit in PostgreSQL is important

How to Enable Encryption in Transit in PostgreSQL Example Usage


psql "host=db.internal port=5432 dbname=ecommerce user=readonly sslmode=verify-full sslrootcert=/etc/ssl/root.crt" -c "SELECT id, email FROM Customers LIMIT 10;"

How to Enable Encryption in Transit in PostgreSQL Syntax


# postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file  = 'server.key'
ssl_ca_file   = 'root.crt'          # Needed for client-cert auth
ssl_ciphers   = 'TLSv1.3:TLSv1.2'

# pg_hba.conf
# Allow only SSL for application connections
hostssl  Customers,Orders,Products,OrderItems  app  10.0.0.0/16  cert clientcert=verify-full

# Client (psql, libpq)
psql "host=db.internal port=5432 dbname=ecommerce user=app \
      sslmode=verify-full sslrootcert=$HOME/.postgresql/root.crt"

Common Mistakes

Mistake: Using sslmode=require on the client while the server still advertises non-SSL; older drivers silently fall back to plaintext. Fix: use verify-full or set require with <code>PGSSLMODE=require</code> plus <code>require_ssl = on</code> in <code>postgresql.conf</code>.
Mistake: Adding plain <code>host</code> lines above <code>hostssl</code> entries in <code>pg_hba.conf</code>; PostgreSQL matches first, allowing insecure access. Fix: place <code>hostssl</code> rules before any unencrypted <code>host</code> rules.

Frequently Asked Questions (FAQs)

Can I retrofit SSL without downtime?

Yes. Copy certificates, enable ssl = on, and issue SELECT pg_reload_conf();. Existing sessions persist; new ones negotiate TLS.

Does SSL slow down queries?

Handshake adds milliseconds; after that, symmetric encryption overhead is small (<2%). Network latency usually dominates.

Is client certificate auth mandatory?

No, but combining TLS with client certs gives mutual authentication and stronger security than password-only logins.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.