Essential PostgreSQL Security Checklist: Authentication, Encryption, Auditing and More

Database security is a top priority for enterprises, and this guide provides a detailed, step‑by‑step checklist for hardening PostgreSQL installations.

1. Authentication Security

Configure pg_hba.conf securely: disallow trust, allow superuser only local connections, restrict dbname+username+IP combos, and use md5 authentication.

Enforce strong password policies: minimum length, mixed case, digits, special characters, and exclude dictionary words.

Implement password rotation with VALID UNTIL and prevent reuse.

Store passwords encrypted (use ENCRYPTED PASSWORD) so pg_shadow.passwd holds an MD5 hash, not plain text.

Avoid logging passwords in log_statement or shell history.

Revoke public rights on pg_user_mappings to hide external table passwords.

Limit dblink usage: avoid exposing remote credentials; if required, grant minimal rights on target clusters.

If using external authentication (e.g., Active Directory), harden the corresponding authentication services.

Secure application configuration files that contain credentials.

2. Data Transmission Security

Encrypt network traffic, especially on untrusted networks, using tools such as OpenSSL.

Ensure authentication traffic is encrypted by using MD5 (configured via pg_hba.conf).

3. Data Security

Encrypt sensitive columns before storing them; keep encryption/decryption logic in the application layer when possible.

Track and retain undo information for critical DML operations (DELETE, TRUNCATE) to enable point‑in‑time recovery.

Consider encrypting stored procedure code (e.g., PL/pgSQL) to hide business logic; enterprise editions like EDB provide this feature.

Use a recycle‑bin‑style plugin so dropped objects are retained in a separate schema for easy restoration.

4. Permission Control

Create database objects (databases, schemas, tables, functions) with a privileged superuser, then grant minimal privileges to application roles.

Revoke public rights on databases, schemas, languages, and functions.

Grant application roles only the necessary SELECT/INSERT/UPDATE/DELETE or EXECUTE rights.

Use event triggers to block DDL statements from application roles.

Insert dummy rows with row‑level triggers that raise exceptions on unauthorized DELETE/UPDATE.

Restrict function language creation and enforce safe language usage.

Implement row‑level security policies to limit visible rows per user.

Apply column‑level permissions or views to restrict access to sensitive data.

5. Prevent Malicious Attacks

Mitigate view‑based attacks by restricting function creation privileges or using security barriers.

Defend against SQL injection at the application layer with parameterised queries and input sanitisation.

Introduce authentication delay ( auth_delay) to slow down brute‑force password attempts.

6. Backup, Disaster Recovery, and Recovery Testing

Adopt point‑in‑time, block‑level incremental backups (e.g., using file systems like Btrfs) and regularly test restore procedures.

7. Auditing

Track changes to postgresql.conf and other configuration files.

Record which user performed INSERT/UPDATE/DELETE on tables, using triggers or the pg_log_userqueries plugin.

Leverage the hstore extension with triggers to capture row‑level modifications.

Audit table creation and definition changes.

Implement fine‑grained audit policies for specific tables, users, and operations, and configure the system to log only committed transactions.

Use the pg_audit module for comprehensive audit logging.

8. Patching

Stay up‑to‑date with PostgreSQL releases; monitor the official Git repository (http://git.postgresql.org) for security patches and apply them promptly, especially after major vulnerability disclosures.

9. External Environment Security

Ensure the application itself is hardened.

Secure middleware components.

Lock down the operating system hosting PostgreSQL.

Protect the physical server and storage media.

Implement network security measures such as disabled unused ports, MAC filtering, and rack‑level access controls.

10. Resource Control

Limit total and active connections.

Set maximum query execution time, lock wait time, and idle transaction duration.

Regularly run VACUUM to reclaim space; be aware that long‑running transactions can prevent vacuum from cleaning up dead tuples, potentially exhausting transaction IDs.

11. Monitoring

Effective monitoring acts as the DBA’s eyes. Track key metrics (CPU, I/O, lock contention, replication lag, etc.) and perform routine health checks using the referenced checklists.

For visual reference, see the images embedded throughout the original article.