PostgreSQL Database Security in Production: SSL Encryption, pg_hba.conf, and pgAudit

Table of Contents

The Real-World Problem: The Default Database Trap

Years ago, I deployed my first production PostgreSQL cluster for a high-traffic fintech service handling around 12,000 requests per minute. I followed a quick setup guide, opened port 5432, and connected the backend microservices.

Everything ran smoothly until our security team conducted our first penetration test. The findings were brutal: plaintext database credentials circulated across the private subnet, the authentication table allowed connections from broad IP ranges, and we had no record of who modified critical records.

Engineering teams walk into this trap all the time. Engineers spin up a managed instance or virtual machine and assume VPC boundaries alone keep attackers out. They don’t. A single compromised container in your cluster gives an attacker a foothold to sniff database packets, intercept passwords, and execute undetected table drops.

Root Cause Analysis: Why Default PostgreSQL Settings Fall Short

PostgreSQL ships with defaults optimized for zero-friction local development. While this gets you up and running in minutes, it leaves gaping holes in production environments:

Unencrypted Transport by Default: Modern PostgreSQL binaries include native SSL support. Even so, vanilla configurations often leave ssl = off or allow clients to silently fall back to unencrypted TCP connections without warning.
Permissive Authentication Rules: Legacy defaults in pg_hba.conf frequently rely on weak hashing algorithms like MD5 or even trust authentication on local interfaces. These configurations leave password hashes susceptible to offline dictionary attacks.
Blind Spots in Standard Logging: PostgreSQL logs syntax errors, connection drops, and slow queries over log_min_duration_statement thresholds. However, it completely skips granular DDL and DML tracking. That means you cannot prove compliance for HIPAA, SOC 2, or PCI-DSS requirements.

Defense Strategies Compared

Securing a production cluster means picking an architecture that protects your data without destroying query throughput or complicating deployment pipelines.

Security Strategy	Pros	Cons	Suitability
Network Perimeter Only (VPC / Security Group)	Zero config changes; fast initial setup.	Zero defense against compromised peer instances; no packet encryption; no audit trail.	Development and sandbox environments only.
Standard Engine Logging + Passwords	Low overhead (~1% CPU impact); logs failed logins and slow queries.	No granular table-level audit trails; passwords still vulnerable in transit without SSL.	Non-critical staging workloads.
Layered Hardening (TLS 1.3 + SCRAM-SHA-256 + pgAudit)	Full transit encryption, strong cryptographic hashing, and tamper-evident compliance logs.	Requires certificate renewal automation and ~3–7% logging storage overhead.	Mandatory for all production databases.

The Best Approach: Step-by-Step Hardening Guide

Follow these steps to take a stock PostgreSQL 16 installation and harden it for mission-critical production use.

Step 1: Enforce TLS 1.3 Transport Encryption

Start by generating or provisioning a TLS certificate to encrypt all traffic between your application servers and the database engine. For internal infrastructure, use certificates issued by your organization’s internal Certificate Authority (such as HashiCorp Vault or cert-manager) or create a dedicated 4096-bit key pair.

# Navigate to the PostgreSQL configuration directory
cd /etc/postgresql/16/main

# Generate a 4096-bit RSA private key
openssl genrsa -out server.key 4096

# Lock down file permissions (PostgreSQL refuses keys with loose permissions)
chmod 600 server.key
chown postgres:postgres server.key

# Generate a self-signed certificate valid for 365 days
openssl req -new -x509 -days 365 -key server.key -out server.crt \
  -subj "/CN=db-primary.internal.infra"

Next, edit postgresql.conf to enable TLS, specify modern protocol versions, and bind the certificate files:

# In /etc/postgresql/16/main/postgresql.conf
ssl = on
ssl_cert_file = '/etc/postgresql/16/main/server.crt'
ssl_key_file = '/etc/postgresql/16/main/server.key'
ssl_min_protocol_version = 'TLSv1.3'
ssl_ciphers = 'HIGH:!aNULL:!MD5:!3DES'

Step 2: Tighten Client Access with pg_hba.conf and SCRAM

The pg_hba.conf file is your database firewall. We will ban legacy MD5 authentication, mandate scram-sha-256, and force remote clients through the hostssl directive.

Before provisioning application users, generate strong, cryptographically secure passwords. A browser-side generator like the one at toolcraft.app/en/tools/security/password-generator works well for generating high-entropy 32-character strings without leaking secrets over a network. Once generated, create your application role:

-- Ensure passwords use SCRAM hashing before role creation
SET password_encryption = 'scram-sha-256';

-- Create the application role with least-privilege permissions
CREATE ROLE api_gateway WITH LOGIN PASSWORD 'xK9#mQ2$vL8*pZ4!wR7@yN1&jB6^tE5(';
GRANT CONNECT ON DATABASE orders_prod TO api_gateway;

Now, open pg_hba.conf and replace the default rules with explicit, narrow CIDR blocks:

# TYPE     DATABASE        USER            ADDRESS                 METHOD

# Local admin maintenance via Unix socket
local    all             postgres                                peer

# Reject all unencrypted TCP traffic, require SCRAM from the API subnet only
hostssl  orders_prod     api_gateway     10.0.4.0/24             scram-sha-256

# Explicitly drop everything else
host     all             all             0.0.0.0/0               reject
host     all             all             ::0/0                   reject

Step 3: Track Database Modifications with pgAudit

Standard logs tell you when a query failed, but they won’t help during a breach investigation. The pgAudit extension generates structured audit logs detailing every INSERT, UPDATE, DELETE, and ALTER against sensitive tables.

Install the extension package corresponding to your PostgreSQL version:

# Ubuntu / Debian
sudo apt-get update && sudo apt-get install -y postgresql-16-pgaudit

Configure PostgreSQL to preload the shared library and log administrative changes and writes:

# In /etc/postgresql/16/main/postgresql.conf
shared_preload_libraries = 'pgaudit'

# Audit configuration
pgaudit.log = 'ddl, role, write'
pgaudit.log_catalog = off
pgaudit.log_parameter = on
pgaudit.log_relation = on
pgaudit.log_statement_once = off

Restart the PostgreSQL service to load the shared library:

sudo systemctl restart postgresql

Connect to the target database and activate the extension:

\c orders_prod
CREATE EXTENSION IF NOT EXISTS pgaudit;

Step 4: Verify and Test the Hardened Setup

Never assume security rules work without active verification. Test your remote connection using strict SSL validation flags:

# Verify that connection succeeds with mandatory SSL
psql "host=10.0.4.50 port=5432 dbname=orders_prod user=api_gateway sslmode=require"

Test audit logging by running a table modification inside the database:

CREATE TABLE audit_check (id serial PRIMARY KEY, verified_at timestamptz DEFAULT now());

Inspect your active PostgreSQL log file in /var/log/postgresql/ to verify the structured pgAudit output:

LOG:  AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.audit_check,CREATE TABLE audit_check (id serial PRIMARY KEY, verified_at timestamptz DEFAULT now());,<not logged>

TLS 1.3 keeps wiretappers out. Restrictive pg_hba.conf rules shut down lateral movement. Meanwhile, pgAudit gives you an indisputable paper trail for every query that matters. Together, these three layers turn an easy target into a resilient, production-ready system.