PostgreSQL Security Hardening Checklist

Ahmed alkhair

Ahmed alkhair

— 3 min read

Introduction

As a senior DBA, I've seen too many production incidents caused by avoidable security gaps. PostgreSQL is powerful and secure by design, but you must harden configuration, access controls, and auditing to meet enterprise standards. This checklist focuses on practical, high-impact measures: SSL/TLS, authentication, row-level security (RLS), audit logging, encryption, and operational controls. Use these steps to reduce attack surface and improve compliance for on-premises and cloud deployments.

1. Enforce Strong Client Connections: SSL/TLS

\n

Encrypt client-server traffic to prevent eavesdropping and man-in-the-middle attacks. Enable SSL in postgresql.conf and require hostssl entries in pg_hba.conf.

-- postgresql.confssl = onssl_cert_file = '/etc/postgresql/certs/server.crt'ssl_key_file = '/etc/postgresql/certs/server.key'

# pg_hba.conf: require SSL and SCRAM authenticationhostssl    all    all    0.0.0.0/0    scram-sha-256hostssl    all    all    ::0/0        scram-sha-256

Generate and manage certificates using a trusted CA or use mutual TLS for even stronger authentication. For cloud-hosted deployments, leverage managed certificate options when available.

2. Use Modern Password Hashing: SCRAM-SHA-256

PostgreSQL supports SCRAM-SHA-256. Set it as the default password encryption method and rotate passwords regularly.

-- postgresql.confpassword_encryption = 'scram-sha-256'

\n

To convert existing passwords, force users to change credentials or recreate roles with new passwords.

3. Principle of Least Privilege and Role Management

Create roles for specific responsibilities instead of granting superuser. Avoid using the postgres superuser account for applications.

-- minimal role exampleCREATE ROLE app_read LOGIN PASSWORD '...';GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_read;ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_read;

Use separate roles for schema changes, backups, and monitoring. Revoke CONNECT where unnecessary and leverage REVOKE/GRANT to tighten access.

4. Enable Row-Level Security (RLS) for Multi-Tenancy and Data Segregation

RLS is essential when you need per-row access control (e.g., multi-tenant apps). Carefully design policies to avoid accidental data leakage.

-- Example: tenant isolationALTER TABLE orders ENABLE ROW LEVEL SECURITY;

Set current_setting('app.current_tenant') securely on each session (for example via SET) after authenticating the user. Test policies thoroughly — RLS misconfigurations can be subtle.

5. Audit Logging: Track Who Did What

Audit trails are crucial for security and compliance. Use the pgaudit extension for detailed session and object-level logging, and ensure logs are forwarded to a centralized system.

-- postgresql.confshared_preload_libraries = 'pgaudit'pgaudit.log = 'read, write, ddl, role'log_statement = 'none'     -- avoid excessive duplicationlog_connections = onlog_disconnections = on

-- enable the extensionCREATE EXTENSION pgaudit;

Ship logs to a SIEM or log store (ELK, Splunk, Amazon CloudWatch) and set retention/alerting policies. Ensure log integrity — use immutable storage if required by compliance.

6. Data-at-Rest Encryption: Disk, Backup, and Transparent Options

Encrypt disks using LUKS or cloud provider volume encryption. For stronger DB-level protection, consider Transparent Data Encryption (TDE) solutions to secure tablespaces and WAL files.

Worlber's PGEE (PostgreSQL Enterprise Edition) provides transparent data encryption tailored for PostgreSQL deployments if you need enterprise-grade TDE without major application changes.

7. Secure Backups and Restore Processes

Backups must be encrypted, access-controlled, and regularly tested. Automate backup verification and store at least one off-site, encrypted copy.

# example: pg_basebackup with compression + encryption (illustrative)pg_basebackup -D /var/lib/postgresql/backup -F tar -z -X stream | \  openssl enc -aes-256-cbc -e -salt -out backup.tar.gz.enc

8. Network Controls and Firewalling

Limit exposure with network ACLs and firewall rules. Only allow trusted application servers, bastion hosts, and monitoring systems to reach the PostgreSQL port. Use VPNs or private subnets for database traffic.

9. Monitoring, Alerts, and Regular Audits

Implement active monitoring for failed login attempts, sudden schema changes, high-privilege role creations, and anomalous query patterns. Tools like pg_stat_statements, pgAudit logs, and external monitoring platforms help detect threats early.

10. Patch Management and Configuration Drift

Keep PostgreSQL and extensions patched. Automate configuration management (Ansible, Puppet, Terraform) to prevent drift. Review and test changes in staging before production rollout.

Putting It Together: An Example Hardening Checklist

  • Enable SSL/TLS and require hostssl entries for clients
  • Use SCRAM-SHA-256 and rotate passwords regularly
  • Apply least-privilege roles and avoid superuser for apps
  • Enable and test Row-Level Security where needed
  • Install pgaudit, centralize logs, and alert on suspicious events
  • Encrypt disks and backups; consider TDE (e.g., Worlber PGEE)
  • Restrict network access to known hosts/subnets
  • Monitor, alert, and perform quarterly security reviews
  • Automate patching and configuration management

Hardening PostgreSQL is an ongoing process. Start with these high-impact controls, automate where possible, and iteratively improve. If you operate in Saudi Arabia or the Middle East and need help implementing enterprise-grade encryption, audit logging, or a managed PostgreSQL platform, Worlber offers solutions like PGEE for TDE, Quick Deploy for rapid DaaS deployments, and Carbonate for fully managed cloud databases.

Ready to Transform Your Database Infrastructure?

Worlber helps companies across Saudi Arabia and the Middle East build reliable, secure, and cost-effective database solutions.

📧 Email us: contactus@worlber.com

🌐 Visit: worlber.sa/contact

📞 Call: Talk to our database experts today

Worlber - Your trusted PostgreSQL partner in the Middle East

Read more