# Postgres security best practices

2025-04-09 | Source: https://www.bytebase.com/reference/postgres/how-to/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.

> **Note:** [Bytebase](/) enhances database security by streamlining access controls, data masking, audit trails, and ensuring data integrity across all changes.