Postgres security best practices

1. Secure Installation and Initial Configuration#

Minimal Installation: Only install the extensions and tools you need. Unused packages can increase the attack surface.

Keep PostgreSQL Updated: Use the latest stable release to benefit from security patches. Subscribe to PostgreSQL security announcements.

Initial Hardening Checklist:

• Change the default postgres superuser password immediately after installation.
• Set listen_addresses in postgresql.conf to specific IPs instead of *.
• Disable the trust method in pg_hba.conf, especially in production.
• Revoke unnecessary privileges from the public schema.

2. Authentication and User Management#

Role and User Management:

• Apply the principle of least privilege.
• Avoid shared accounts; create unique roles for each user and application.

Password Policies:

• Use strong, complex passwords.
• Store passwords using SCRAM-SHA-256 rather than MD5.
• Integrate with LDAP or PAM for centralized authentication.

Two-Factor Authentication (2FA):

While PostgreSQL doesn’t natively support 2FA, you can implement it at the network or OS layer using SSH, VPN, or identity providers.

Restrict Superuser Access:

• Use the postgres role only for critical maintenance.
• Monitor all superuser activity.

3. Authorization and Access Control#

Role-Based Access Control (RBAC):

• Use GRANT and REVOKE to assign only necessary permissions.
• Organize roles into groups for easier management.

Schema and Table Permissions:

• Lock down access to sensitive tables with REVOKE.
• Use SECURITY DEFINER functions with caution and never as superuser.

Row-Level Security (RLS):

• Implement RLS to enforce per-user or per-tenant access policies.
• Use CREATE POLICY and ALTER TABLE ENABLE ROW LEVEL SECURITY.

Public Schema:

• Revoke default access with: REVOKE ALL ON SCHEMA public FROM public;

4. Data Encryption#

Encryption in Transit:

• Enable SSL/TLS in postgresql.conf: ssl = on
• Require SSL for clients: sslmode=require
• Rotate certificates regularly.

Encryption at Rest:

• Use OS-level encryption (e.g., LUKS, EBS encryption).
• Consider PostgreSQL extensions like pgcrypto for column-level encryption.
• Evaluate third-party Transparent Data Encryption (TDE) solutions if compliance requires it.

5. Network Security#

Restrict Access:

• Use firewall rules or security groups to allow only trusted IP ranges.
• Never expose PostgreSQL directly to the public internet.

Configure pg_hba.conf Carefully:

• Prefer scram-sha-256 or md5, never trust in production.
• Limit IP ranges per user or role.

Additional Hardening:

• Use a VPN or SSH tunnel for remote access.
• Change the default port (5432) to reduce visibility to automated scans.

6. Auditing and Monitoring#

Enable Detailed Logging:

• log_connections = on
• log_disconnections = on
• log_statement = 'ddl'
• log_duration = on

Use Audit Tools:

• Install pgAudit for fine-grained auditing.
• Export logs to centralized systems (e.g., ELK, Splunk).

Intrusion Detection:

• Monitor for failed login attempts and role escalations.
• Set up alerts for suspicious activity.

7. Patching and Maintenance#

Apply Security Updates Promptly:

• Use automated patch management when available.
• Test patches in staging environments.

Stay Informed:

• Subscribe to pgsql-announce for security updates.
• Monitor CVEs related to PostgreSQL and dependencies.

8. Backup and Disaster Recovery#

Encrypted Backups:

• Use pg_dump or base backups with encrypted storage.
• Protect backup access credentials.

Restore Testing:

• Regularly test your restore process.
• Automate backup integrity checks.

Disaster Recovery Planning:

• Define RTO (Recovery Time Objective) and RPO (Recovery Point Objective).
• Store backups offsite and use redundant storage solutions.

9. Advanced Security Techniques#

OS-Level Protections:

• Use AppArmor or SELinux to restrict PostgreSQL process capabilities.

Connection Throttling:

• Deploy pgbouncer to pool and limit abusive connections.

Security Extensions:

• Leverage pgcrypto for encryption
• Consider sepgsql for mandatory access control

10. Common Mistakes and Vulnerabilities#

• Using trust authentication in production.
• Leaving the postgres role with default settings.
• Failing to restrict access to the public schema.
• Not using parameterized queries (risk of SQL injection).
• Ignoring patch announcements.