/PLAYBOOK · 2026-08-08

PostgreSQL Security Hardening in Practice: Access Control and Audit Configuration Guide

Harden PostgreSQL across five dimensions: pg_hba.conf access control, scram-sha-256 strong authentication, SSL encryption, least privilege, and audit logging. Includes complete configs and verification commands to defend against unauthorized access and data leaks.

PostgreSQL security hardening is a critical layer of database defense. This article gives a copy-paste hardening configuration across five dimensions—access control, authentication, transport encryption, privilege management, and audit logging—to block unauthorized access, data leaks, and privilege abuse.

Applicable Scenarios

Security baseline before a PostgreSQL production database goes live
Remediation after scan/brute-force or unauthorized-access findings
Hardening database audit and access-control items for MLPS assessments
Least-privilege isolation in multi-tenant or shared-database environments

Prerequisites

Linux server with PostgreSQL installed (this tutorial uses PostgreSQL 15 + Ubuntu 22.04)
postgres OS user or superuser privileges
Restart or reload PostgreSQL after config changes

How It Works

PostgreSQL security is three layers: pg_hba.conf authenticates clients (who can connect, with which method); roles control what happens after login; SSL protects data in transit. The usual attack path is: listen on the public internet + weak password + deprecated md5/trust. Hardening therefore means: tighten listen addresses, force scram-sha-256, enable SSL, split roles by least privilege, and audit SQL.

Procedure

1. Tighten listen address and port

Edit `/etc/postgresql/15/main/postgresql.conf`:

`# Listen on loopback only (use an internal IP for internal apps; never 0.0.0.0)
listen_addresses = '127.0.0.1'
port = 5432`

If remote access is required, allow only application-server IPs in the cloud security group/firewall, and bind listen_addresses to that internal IP.

2. Enforce scram-sha-256 strong authentication

Edit `/etc/postgresql/15/main/pg_hba.conf` and set the auth method to scram-sha-256:

`# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     scram-sha-256
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
# Internal app access (as needed; replace with the actual CIDR)
host    all             app_user        10.0.0.0/8              scram-sha-256`

Migrate existing user passwords to scram:

`# Enter as the postgres user
sudo -u postgres psql
ALTER USER app_user WITH PASSWORD 'StrongPassword';
# Check password encryption method (should be SCRAM-SHA-256)
SELECT usename, substring(passwd from 1 for 20) FROM pg_shadow;`

In `pg_shadow`, a `passwd` value starting with `SCRAM-SHA-256$` means strong authentication is in use.

3. Enable SSL encrypted transport

`# Generate a self-signed certificate (production should use a trusted CA)
sudo openssl req -new -x509 -days 365 -nodes -text -out /etc/postgresql/15/main/server.crt -keyout /etc/postgresql/15/main/server.key -subj \"/CN=db.example.com\"
sudo chmod 600 /etc/postgresql/15/main/server.key
sudo chown postgres:postgres /etc/postgresql/15/main/server.key

# Enable SSL in postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'`

Prefix remote pg_hba.conf entries with `hostssl` (SSL only) and drop non-SSL:

`hostssl  all             app_user        10.0.0.0/8              scram-sha-256`

4. Least privilege: split roles

`sudo -u postgres psql

-- Create a read-only account
CREATE ROLE readonly LOGIN PASSWORD 'ReadOnlyPassword';
GRANT CONNECT ON DATABASE appdb TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

-- Restrict the read-only account login source (specify separately in pg_hba.conf)
-- hostssl appdb readonly 10.0.0.5/32 scram-sha-256`

Drop unused superuser roles. Business accounts must be least-privilege non-superusers:

`-- Check superusers
SELECT rolname FROM pg_roles WHERE rolsuper = true;
-- Privilege revocation example
REVOKE ALL ON DATABASE appdb FROM PUBLIC;`

5. Enable audit logging

Edit `postgresql.conf`:

`# Enable SQL statement logging (production may log only DDL and failed logins)
log_statement = 'ddl'        # none | ddl | mod | all
log_duration = on
log_connections = on
log_disconnections = on
log_min_duration_statement = 1000   # Log slow SQL exceeding 1 second
log_directory = 'log'
log_filename = 'postgresql-%a.log'
log_line_prefix = '%m [%p] %u@%d '  # timestamp, PID, user, database`

Restart so all config takes effect:

`sudo systemctl restart postgresql
sudo systemctl status postgresql`

Configuration Verification

Verify from four angles:

Strong authentication: `SELECT substring(passwd from 1 for 20) FROM pg_shadow;` output starts with SCRAM-SHA-256
SSL in effect: connect with `sslmode=require`, `psql \"host=10.0.0.x dbname=appdb user=app_user sslmode=require\"` succeeds, then `SELECT ssl FROM pg_stat_ssl;` returns `t`
Privilege isolation: `INSERT` as readonly should report `permission denied`
Audit logs: after a DDL statement, `tail /var/log/postgresql/postgresql-*.log` shows the statement and username

FAQ

FAQ 1: After changing pg_hba.conf, remote connections report no pg_hba.conf entry?

The request matched no pg_hba.conf rule. First-match, top-to-bottom: confirm the source IP is in the rule CIDR (`SELECT client_addr FROM pg_stat_activity;` for the real source); confirm the rule is `host` type with method scram-sha-256; confirm reload (`SELECT pg_reload_conf();`). hostssl with a client that has no SSL also fails to match; add `sslmode=require` to the connection string.

FAQ 2: Logs explode after enabling log_statement=all?

Full SQL logs burn disk and hurt performance in production. Use `log_statement = 'ddl'` for schema changes plus `log_min_duration_statement` for slow queries. For security audit, add pgAudit (`pgaudit.log = 'write,ddl'`) for role-granular logging, and cap rotation with `log_rotation_age` (e.g. 1d) and `log_truncate_on_rotation` so the disk does not fill.

Summary

PostgreSQL hardening is five steps: bind listen address to the internal network, unify pg_hba.conf on scram-sha-256, enable SSL, split read-only/read-write roles by least privilege, enable audit logging. That blocks weak-password brute force, plaintext eavesdropping, and unauthorized access. Put the config in the standard deploy path; recheck with `pg_shadow` password-policy and privilege reviews.