PostgreSQL Security Hardening: CIS Benchmark Controls, RLS, and pgaudit
Retool's new app builder is where AI-generated code ships safely
Building apps with AI is easy. Getting them to production safely is another story.
A default PostgreSQL installation running on a Linux host authenticates local connections via the trust method (no password required for OS users), hashes remote passwords with MD5 (broken for offline attacks), has no audit logging, and gives every user access to the public schema by default. Against a CIS Benchmark v15 scan, this configuration fails the majority of controls. This guide covers the changes that close the highest-risk gaps: replacing trust and MD5 with SCRAM-SHA-256 in pg_hba.conf, enabling pgaudit for DDL and DML audit logging, implementing row-level security for multi-tenant data, locking down role permissions, and enforcing SSL with TLSv1.2 minimum. All controls reference the CIS PostgreSQL Benchmark v15 control numbering where applicable.
Harden pg_hba.conf Authentication Rules
pg_hba.conf is the first line of PostgreSQL authentication defense. Every entry in this file defines a connection type, database scope, user scope, address range, and authentication method. Production configurations must eliminate trust and MD5 entries.
Replace trust authentication on local connections
The default pg_hba.conf on most Linux distributions includes `local all all trust` or `local all postgres peer`. The peer method is safer (requires matching OS username) but still allows password-free access. Replace with: `local all all scram-sha-256`. This requires even local connections to authenticate with a password. For the postgres system user running maintenance scripts, use a `.pgpass` file or `PGPASSWORD` environment variable rather than trust. Reload pg_hba.conf: `pg_ctl reload` or `SELECT pg_reload_conf();`.
Require SCRAM-SHA-256 for all remote connections
Replace any `host` entries using `md5` or `password` authentication with `hostssl all all 0.0.0.0/0 scram-sha-256`. The `hostssl` type rejects connections that do not use SSL. Set `password_encryption = scram-sha-256` in postgresql.conf before updating pg_hba.conf -- existing MD5 passwords must be reset by the user after this change. Notify users and application teams before switching: applications using hardcoded MD5 hash comparison in their PostgreSQL driver must be updated to support SCRAM.
Restrict pg_hba.conf by source IP range
Avoid wildcard address (`0.0.0.0/0`) for application connections. Specify the exact CIDR ranges of application server subnets: `hostssl appdb appuser 10.0.1.0/24 scram-sha-256`. For database administration access from bastion hosts, create a separate entry for the bastion CIDR with a stricter timeout. Zero-trust environments using certificate-based authentication should use `hostssl all all 0.0.0.0/0 cert clientcert=verify-full` to require client certificates regardless of source IP.
Enable pgaudit for Compliance Logging
pgaudit provides the structured DDL and DML audit log that PostgreSQL's built-in logging cannot match. It is required by most compliance frameworks that cover PostgreSQL databases.
Install and load pgaudit
Install the pgaudit package for your PostgreSQL version (e.g., `apt install postgresql-16-pgaudit` or `yum install pgaudit16_16`). Add to postgresql.conf: `shared_preload_libraries = 'pgaudit'`. Restart PostgreSQL to load the extension. In the target database: `CREATE EXTENSION pgaudit;`. Verify the extension loaded: `SELECT * FROM pg_available_extensions WHERE name = 'pgaudit';`.
Configure session-level audit classes
Set `pgaudit.log = 'ddl, write, role'` in postgresql.conf. DDL audits CREATE, ALTER, DROP, and TRUNCATE operations. WRITE audits INSERT, UPDATE, DELETE, and COPY. ROLE audits GRANT, REVOKE, and user/role changes. For environments requiring full query audit (PCI DSS, HIPAA), add READ: `pgaudit.log = 'read, write, ddl, role'`. Note that READ logging in high-traffic environments produces significant log volume -- evaluate storage and log retention capacity before enabling.
Configure log_connections and log_disconnections
In postgresql.conf, set `log_connections = on` and `log_disconnections = on`. These log every connection attempt (including failed authentications) and every session termination with duration. This is CIS control 3.2 and provides the session-level audit trail required for detecting credential stuffing and unauthorized access attempts. Also set `log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '` to include timestamp, PID, user, database, application name, and client host in every log line.
Ship audit logs to a SIEM or S3
Configure PostgreSQL to write logs in JSON format for SIEM ingestion: `log_destination = 'jsonlog'` (PostgreSQL 15+). Point your log shipper (Filebeat, Fluentd, CloudWatch Agent) at the log directory. Create a log rotation policy: `log_rotation_age = 1d` and `log_rotation_size = 100MB`. For AWS RDS PostgreSQL, enable the PostgreSQL log export to CloudWatch Logs in the RDS parameter group and create CloudWatch Log metric filters for failed authentication events.
Briefings like this, every morning before 9am.
Threat intel, active CVEs, and campaign alerts, distilled for practitioners. 50,000+ subscribers. No noise.
Implement Row-Level Security for Multi-Tenant Data
Row-level security enforces data isolation at the database engine level, independent of application logic. It is the correct pattern for multi-tenant PostgreSQL deployments where multiple tenants share tables.
Enable RLS on sensitive tables
For any table containing tenant-specific or user-specific data: `ALTER TABLE customer_records ENABLE ROW LEVEL SECURITY; ALTER TABLE customer_records FORCE ROW LEVEL SECURITY;`. The FORCE option applies RLS to table owners (including superusers other than the table owner), preventing privilege escalation from bypassing tenant isolation. Create a policy: `CREATE POLICY tenant_isolation_policy ON customer_records USING (tenant_id = current_setting('app.current_tenant', true)::bigint);`.
Set tenant context in application connection code
Applications must set the tenant context variable before any query: in a transaction, execute `SET LOCAL app.current_tenant = '<tenant_id>'` before the first data query. `SET LOCAL` scopes the variable to the transaction, preventing accidental cross-tenant data access if connection pooling returns a connection with a stale session variable. Test RLS enforcement by connecting as the application role and attempting to query rows belonging to a different tenant_id than the one set in the session variable.
Create separate USING and WITH CHECK policies for read vs. write isolation
A single USING policy applies to both reads and writes. For explicit write control, create separate policies: `CREATE POLICY tenant_select ON customer_records FOR SELECT USING (tenant_id = current_setting('app.current_tenant')::bigint); CREATE POLICY tenant_insert ON customer_records FOR INSERT WITH CHECK (tenant_id = current_setting('app.current_tenant')::bigint);`. This prevents a tenant from inserting rows with a different tenant_id even if they have INSERT privilege on the table.
Apply Role-Based Access Control and Revoke Default Privileges
PostgreSQL's default PUBLIC role grants broad schema access that violates least privilege. Explicit RBAC patterns replace these defaults with controlled access grants.
Revoke default PUBLIC schema privileges
By default, all PostgreSQL users can create objects in the public schema (`GRANT CREATE ON SCHEMA public TO PUBLIC` is set by default in PostgreSQL 14 and earlier). Revoke this: `REVOKE CREATE ON SCHEMA public FROM PUBLIC; REVOKE ALL ON SCHEMA public FROM PUBLIC;`. Grant schema usage only to the specific application roles that need it: `GRANT USAGE ON SCHEMA public TO app_role;`. In PostgreSQL 15+, the PUBLIC CREATE on public schema was revoked by default.
Use role hierarchies for permission management
Create base roles with specific privileges and grant them to named user roles: `CREATE ROLE app_readonly; GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly; CREATE USER reporting_user LOGIN; GRANT app_readonly TO reporting_user;`. This pattern means adding a new reporting user requires only `CREATE USER ... LOGIN; GRANT app_readonly TO ...;` -- privileges are inherited from the role, not granted individually.
Audit and revoke excessive privileges quarterly
Run a quarterly privilege audit: `SELECT grantee, table_schema, table_name, privilege_type FROM information_schema.role_table_grants WHERE grantee NOT IN ('pg_monitor', 'pg_read_all_settings') ORDER BY grantee, table_name;`. Look for application roles with INSERT/UPDATE/DELETE on tables they should only read, and for direct grants to individual users rather than to roles. Use `REVOKE <privilege> ON <table> FROM <role>;` to remove excessive grants. Document all privilege grants and their business justification in a database access register.
The bottom line
PostgreSQL security hardening is a configuration discipline, not a product purchase. The CIS Benchmark v15 controls -- SCRAM-SHA-256 in pg_hba.conf, pgaudit session logging, row-level security on multi-tenant tables, public schema privilege revocation, and SSL with TLSv1.2 minimum -- are all native PostgreSQL features requiring no third-party tools. Apply them in order of risk: authentication hardening first, then audit logging, then access control cleanup. Run a CIS-CAT or open-source pg_audit_config check against every production PostgreSQL instance quarterly.
Frequently asked questions
What is the difference between md5 and scram-sha-256 in pg_hba.conf?
MD5 in pg_hba.conf stores password hashes using the MD5 algorithm, which is fast to compute and therefore vulnerable to offline dictionary attacks if an attacker obtains the pg_authid system table. An MD5 hash can be cracked in seconds with modern GPU hardware for common passwords. SCRAM-SHA-256 (RFC 5802) uses a salted challenge-response protocol that stores a salted hash with a per-user salt, is resistant to replay attacks, and protects the password even if the hash is exposed -- the hash is computationally expensive to crack. Set `password_encryption = scram-sha-256` in postgresql.conf and use `scram-sha-256` in pg_hba.conf. Existing MD5-hashed passwords must be reset to migrate to SCRAM.
How does row-level security work in PostgreSQL?
Row-Level Security (RLS) adds a predicate filter to every SELECT, INSERT, UPDATE, and DELETE on a table that enforces per-row access control based on the current database role or session variables. Enable RLS on a table: `ALTER TABLE customer_data ENABLE ROW LEVEL SECURITY`. Then create a policy: `CREATE POLICY tenant_isolation ON customer_data USING (tenant_id = current_setting('app.tenant_id')::int)`. Applications must set `SET LOCAL app.tenant_id = <id>` before any query. Superusers bypass RLS by default -- use `FORCE ROW LEVEL SECURITY` to apply RLS to table owners too. RLS is enforced at the PostgreSQL engine level, making it a defense-in-depth control that operates even if application-layer authorization has bugs.
What does pgaudit log and how is it different from standard PostgreSQL logging?
Standard PostgreSQL logging (log_statement = all) logs query text but lacks structured audit fields and has no per-object granularity. pgaudit provides session-level and object-level audit logging: session audit logs all SQL in a session (grouped by log class: READ, WRITE, DDL, ROLE, FUNCTION, MISC), while object audit logs access to specific tables, views, and sequences regardless of which user executes the query. pgaudit output is in a structured format parseable by SIEMs, includes object names and audit event types, and is designed for compliance logging (PCI DSS, SOC 2, HIPAA) rather than debugging.
How do you implement least-privilege roles in PostgreSQL?
Create application-specific roles with minimal privileges. For a read-only reporting role: `CREATE ROLE reporting_user LOGIN PASSWORD 'xxx'; GRANT CONNECT ON DATABASE appdb TO reporting_user; GRANT USAGE ON SCHEMA public TO reporting_user; GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_user; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reporting_user;`. The `ALTER DEFAULT PRIVILEGES` ensures new tables created in the schema are also accessible to the role. Never use the postgres superuser account for application connections. Revoke the PUBLIC schema usage if not needed: `REVOKE ALL ON SCHEMA public FROM PUBLIC;`.
What is PgBouncer and what are the security implications of using it?
PgBouncer is a connection pooler that sits between applications and PostgreSQL, multiplexing many application connections into a smaller pool of persistent database connections. Security implications: PgBouncer can be configured to require SCRAM authentication from clients while using a different credential to connect to the backend PostgreSQL instance, centralizing credential management. However, PgBouncer in `pool_mode=transaction` does not support session-level features like prepared statements and row-level security via SET LOCAL -- use `pool_mode=session` for RLS compatibility. Secure PgBouncer itself by binding to 127.0.0.1 or a private interface, never 0.0.0.0, and configure TLS between PgBouncer and both clients and backend.
How do you enforce SSL in pg_hba.conf?
Use the `hostssl` connection type in pg_hba.conf to require SSL for remote connections: `hostssl all all 0.0.0.0/0 scram-sha-256`. This accepts remote connections only over SSL with SCRAM authentication. Remove or replace any `host` entries (which accept both SSL and non-SSL) with `hostssl`. Also set `ssl = on` and `ssl_min_protocol_version = TLSv1.2` in postgresql.conf. For client certificate authentication (mutual TLS), use `hostssl all all 0.0.0.0/0 cert clientcert=verify-full` which requires a valid client certificate signed by the configured CA.
What CIS PostgreSQL Benchmark controls have the highest impact?
The highest-impact CIS PostgreSQL Benchmark v15 controls are: (1) ensure password_encryption is set to scram-sha-256, (2) ensure pg_hba.conf does not use trust or password authentication for remote connections, (3) ensure the PostgreSQL superuser account is not used for routine database operations (create a named admin role instead), (4) ensure pgaudit is installed and log_connections and log_disconnections are enabled, (5) ensure row-level security is enabled on all multi-tenant tables, (6) ensure postgresql.conf sets ssl=on and ssl_min_protocol_version=TLSv1.2, and (7) ensure public schema privileges are revoked from the PUBLIC role. These seven controls address the majority of the credential and access control risk in a typical PostgreSQL deployment.
Sources & references
Free resources
Critical CVE Reference Card 2025–2026
25 actively exploited vulnerabilities with CVSS scores, exploit status, and patch availability. Print it, pin it, share it with your SOC team.
Ransomware Incident Response Playbook
Step-by-step 24-hour IR checklist covering detection, containment, eradication, and recovery. Built for SOC teams, IR leads, and CISOs.
Get threat intel before your inbox does.
50,000+ security professionals read Decryption Digest for early warnings on zero-days, ransomware, and nation-state campaigns. Free, daily, no spam.
Unsubscribe anytime. We never sell your data.
Founder & Cybersecurity Evangelist, Decryption Digest
Cybersecurity professional with expertise in threat intelligence, vulnerability research, and enterprise security. Covers zero-days, ransomware, and nation-state operations for 50,000+ security professionals every morning.
Win a $2,495 Black Hat pass.
Full-access to Black Hat USA 2026 in Las Vegas. Subscribe free to enter.