PostgreSQL Database Hardening: A Security Best Practices Guide

In early 2024, a fintech startup discovered their PostgreSQL instance had been exfiltrating customer records for three weeks. The attacker had connected using a default postgres superuser account with a weak password, accessed over a publicly-exposed port with no SSL and no audit trail. The breach cost them $2.1M in regulatory fines and customer remediation — all of it preventable with a single afternoon of security hardening. PostgreSQL ships with sensible defaults for development but dangerously permissive defaults for production. Tightening authentication, encrypting connections, enforcing least-privilege access, and enabling audit logging are not optional extras — they are the baseline for any production deployment handling real data.

Why PostgreSQL Security Matters

PostgreSQL is trusted by banks, healthcare providers, and SaaS companies precisely because it offers enterprise-grade security primitives — but those primitives must be explicitly enabled. A default installation on many Linux distributions will accept local connections as the postgres superuser without a password, and may allow password-based connections from any host with no transport encryption.

The threat model for a production database includes external attackers who discover an exposed port, compromised application credentials used to perform lateral movement, malicious insiders running unauthorized queries, and compliance auditors who need evidence of access controls. Each of these requires a different defense layer, and PostgreSQL has native tooling for all of them.

Network and Transport Security (SSL/TLS)

All connections to PostgreSQL in production must be encrypted. Without SSL/TLS, credentials and query data travel in plaintext across the network, making them trivially interceptable by anyone with access to packet captures — including cloud provider staff, other tenants on a shared network, or a compromised load balancer.

Enabling SSL in postgresql.conf

Generate a server certificate and key (or use your organization's PKI), then configure PostgreSQL to require them:

# postgresql.conf
ssl = on
ssl_cert_file = '/etc/postgresql/ssl/server.crt'
ssl_key_file  = '/etc/postgresql/ssl/server.key'
ssl_ca_file   = '/etc/postgresql/ssl/ca.crt'   # for client cert verification

# Enforce minimum TLS version — reject TLS 1.0 and 1.1
ssl_min_protocol_version = 'TLSv1.2'

# Strong cipher suite list (disable RC4, DES, export ciphers)
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'

Set tight permissions on the key file — PostgreSQL will refuse to start if the private key is world-readable:

chmod 600 /etc/postgresql/ssl/server.key
chown postgres:postgres /etc/postgresql/ssl/server.key

Requiring SSL in pg_hba.conf

Enabling SSL in postgresql.conf makes SSL available, but clients can still connect without it unless you enforce it in pg_hba.conf. Use the hostssl record type to require encrypted connections:

# pg_hba.conf — require SSL for all non-local connections
hostssl  all  all  0.0.0.0/0  scram-sha-256
hostssl  all  all  ::/0       scram-sha-256

# Reject any non-SSL host connection attempt
host     all  all  0.0.0.0/0  reject

Authentication with pg_hba.conf

The pg_hba.conf file is PostgreSQL's host-based authentication rulebook. Every connection attempt is matched against it from top to bottom, and the first matching rule wins. A poorly ordered or overly permissive pg_hba.conf is one of the most common sources of unauthorized access.

Switch from md5 to scram-sha-256

The md5 authentication method transmits an MD5 hash of the password, which is susceptible to offline cracking and replay attacks. scram-sha-256 uses a challenge-response mechanism that never transmits the actual password hash:

# postgresql.conf — set the default for new passwords
password_encryption = scram-sha-256

Existing users who were created with md5 passwords will need their passwords reset to pick up the new hash type:

-- Reset password to store it as scram-sha-256
ALTER ROLE appuser PASSWORD 'new-strong-password-here';

A Hardened pg_hba.conf Example

# TYPE   DATABASE     USER          ADDRESS          METHOD

# Local connections for administrative tasks via Unix socket only
local    all          postgres                       peer
local    all          all                            reject

# Replication connections — restrict to replication user and known standby IPs
hostssl  replication  replicator    10.0.1.5/32      scram-sha-256

# Application connections — restrict to specific database and app user
hostssl  appdb        appuser       10.0.2.0/24      scram-sha-256

# Reject everything else
host     all          all           0.0.0.0/0        reject
host     all          all           ::/0             reject

Key principles: restrict by database name (not all), restrict by username, restrict by IP range, and reject explicitly at the end rather than relying on implicit denial.

Role-Based Access Control (Least Privilege)

PostgreSQL's role system is the primary mechanism for enforcing least-privilege access. Every connection should use a dedicated role scoped to exactly the permissions that connection requires — nothing more.

Create Purpose-Built Roles

-- Read-only reporting role
CREATE ROLE reporting_ro NOLOGIN;
GRANT CONNECT ON DATABASE appdb TO reporting_ro;
GRANT USAGE ON SCHEMA public TO reporting_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO reporting_ro;

-- Application write role (no DDL, no superuser)
CREATE ROLE appuser LOGIN PASSWORD 'strong-password' NOINHERIT;
GRANT CONNECT ON DATABASE appdb TO appuser;
GRANT USAGE ON SCHEMA public TO appuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO appuser;

-- Assign the reporting role to a login user
CREATE ROLE analyst LOGIN PASSWORD 'analyst-password' NOINHERIT;
GRANT reporting_ro TO analyst;

Lock Down the Superuser

-- Set a strong password on the postgres superuser
ALTER ROLE postgres PASSWORD 'long-random-password-stored-in-vault';

-- Do not use the postgres role for application connections — ever
-- Create a schema owner role for DDL operations during migrations
CREATE ROLE schema_owner NOLOGIN NOINHERIT;
GRANT ALL ON SCHEMA public TO schema_owner;

CREATE ROLE migrator LOGIN PASSWORD 'migration-password' NOINHERIT;
GRANT schema_owner TO migrator;

Row-Level Security (RLS) for Multi-Tenant Data

Row-Level Security allows PostgreSQL to enforce data isolation at the storage layer, independent of the application. Even if a bug in your application logic causes it to issue an unfiltered query, RLS policies ensure each tenant only sees their own rows.

Enabling and Defining RLS Policies

-- Enable RLS on the tenant data table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

-- Create a policy using the current_setting() function
-- The application sets this at the start of each session
CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- Grant SELECT to the application role — RLS will filter rows automatically
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO appuser;

In the application, set the tenant context at the start of each transaction:

-- Run at the start of every transaction before executing queries
SET LOCAL app.current_tenant_id = '550e8400-e29b-41d4-a716-446655440000';

Audit Logging with pgaudit

PostgreSQL's default logging captures connection events and slow queries, but it does not produce a structured audit trail of who accessed what data and when. The pgaudit extension fills this gap, logging DDL operations, role changes, and data access events in a format suitable for compliance reporting.

Installing and Configuring pgaudit

# On Debian/Ubuntu
apt install postgresql-16-pgaudit

# On RHEL/Rocky/Amazon Linux
dnf install pgaudit_16

# postgresql.conf — load pgaudit at startup
shared_preload_libraries = 'pgaudit'

# Audit all DDL (CREATE, ALTER, DROP) and role changes
pgaudit.log = 'ddl, role'

# For high-compliance environments, also audit data reads and writes
# pgaudit.log = 'all'   # high-volume — filter carefully

pgaudit.log_catalog = off   # avoid flooding logs with system catalog queries
pgaudit.log_relation = on   # include the relation name in each audit record
pgaudit.log_statement_once = off

-- Per-role audit (audit all queries from the DBA login)
ALTER ROLE dba_user SET pgaudit.log = 'all';

Sample pgaudit Log Entry

2024-03-15 14:23:01 UTC [12345]: AUDIT: SESSION,1,1,DDL,DROP TABLE,TABLE,
public.customer_pii,DROP TABLE customer_pii,

Ship these logs to a centralized SIEM (Datadog, Splunk, OpenSearch) rather than keeping them only on the database host. An attacker with file system access can delete local log files to cover their tracks.

Additional Hardening Steps

Restrict pg_stat_activity Visibility

By default, any user can query pg_stat_activity and see the currently-executing queries of other sessions, including queries that contain sensitive data in their text:

-- Revoke access to monitoring views from unprivileged roles
REVOKE pg_monitor FROM PUBLIC;

-- Grant to a dedicated monitoring role only
CREATE ROLE monitoring_user LOGIN PASSWORD 'monitor-password';
GRANT pg_monitor TO monitoring_user;

Set Connection Limits per Role

Without connection limits, a single compromised credential or runaway connection pool can exhaust max_connections and deny service to all other users:

-- Limit application user to 50 connections
ALTER ROLE appuser CONNECTION LIMIT 50;

-- Limit analyst connections to avoid impacting production workloads
ALTER ROLE analyst CONNECTION LIMIT 5;

Restrict Search Path to Prevent Schema Injection

Leaving search_path at its default allows a user to create a schema that shadows public functions, enabling privilege escalation through function hijacking:

-- postgresql.conf
search_path = '"$user"'   # removes public from the default search path

-- Or set it per role
ALTER ROLE appuser SET search_path = public;

Disable Unused Extensions and Procedural Languages

-- Remove languages not in use — each one is an attack surface
DROP LANGUAGE plpython3u;   -- untrusted language, allows OS-level code execution
DROP EXTENSION IF EXISTS dblink;  -- can bypass pg_hba.conf restrictions

-- List installed extensions to audit them
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL;

Tune Logging for Security Visibility

# postgresql.conf — log connection attempts and failed authentications
log_connections = on
log_disconnections = on
log_failed_connections = on

# Log queries that run longer than 1 second (catches slow injection attempts)
log_min_duration_statement = 1000

# Log lock waits (can indicate contention from unauthorized bulk queries)
log_lock_waits = on
deadlock_timeout = 1s

Working with JusDB on PostgreSQL Security

JusDB manages PostgreSQL security hardening for engineering teams who need production-grade protection without the overhead of security audits. Our DBAs handle SSL setup, pg_hba.conf configuration, role design, RLS policies, and audit logging — so your PostgreSQL clusters are compliant and secure from day one.

Explore JusDB PostgreSQL Management →  |  Talk to a DBA

Related reading:

• PostgreSQL Row-Level Security for Multi-Tenant Apps
• PostgreSQL Logical Replication: Setup and Use Cases
• Database Security: Encryption, Access Control, and Auditing