Securing PostgreSQL 101

Suggest edits

The following are basic practices for securing your PostgreSQL installation.

Install the latest version

  • Always use the most recent version. Regularly update PostgreSQL to the latest stable release. For EDB releases, see the EDB repositories.

  • Apply security patches. Ensure security patches are applied promptly. For EDB security vulnerabilities and advisories, see the EDB Vulnerability disclosure policy.

Use strong authentication methods

PostgreSQL supports several authentication methods. Always use the most secure option available.

  • Password authentication. Ensure that all users authenticate with strong passwords. Because it provides stronger hashing, use scram-sha-256 for password hashing instead of md5.

  • LDAP/Kerberos/SSO. Integrate centralized authentication systems like LDAP, Kerberos, or single sign-on (SSO) for enhanced security.

Limit access with pg_hba.conf

PostgreSQL’s host-based access control file (pg_hba.conf) is your first line of defense for controlling who can connect to the database. To ensure security:

  • Restrict host connections. Allow only trusted hosts.

  • Use CIDR notation. Limit access to specific IP ranges in pg_hba.conf. Example:

host all all 192.168.1.0/24 scram-sha-256
  • Use local method. For connections from the same machine, use Unix domain sockets with peer authentication, limiting connections to system users.

Enforce SSL/TLS connections

Encrypt traffic between the client and PostgreSQL server using SSL. This practice can prevent sensitive data (like passwords and query results) from being intercepted.

  • Enable SSL. Ensure that ssl = on in postgresql.conf.

  • Use valid SSL certificates. Use certificates for secure communication (self-signed or CA-signed).

  • Force SSL. Ensure all connections use SSL via pg_hba.conf. Example:

hostssl all all 0.0.0.0/0 scram-sha-256

Use role-based access control (RBAC)

PostgreSQL implements a robust role-based access control system. Some key practices include:

  • Principle of least privilege. Grant roles the minimum permissions necessary.

  • Separate roles for users/applications. Avoid using superuser accounts or the default postgres role for daily operations.

  • Use GRANT/REVOKE. Assign specific privileges to roles. Example:

GRANT SELECT, INSERT ON my_table TO my_user;

Use encrypted passwords

Make sure that passwords are stored using secure hashing methods (scram-sha-256 in modern PostgreSQL versions).

  • Enable scram-sha-256. Configure PostgreSQL to store passwords securely by setting password_encryption = 'scram-sha-256' in your postgresql.conf file:
password_encryption = 'scram-sha-256'

Audit and monitor database activity

Enable logging and auditing to keep track of database activity.

  • Enable logging. Log all user connections and queries.

  • Track role changes. Regularly audit role modifications and permissions to detect unauthorized changes.

  • Use pgAudit. Third-party tools like pgAudit can enable detailed audit logging.

  • Enable connection and query logs. Capture login attempts, successful connections, and queries executed using settings in postgresql.conf:

log_connections = on
log_disconnections = on
log_statement = 'all'

Regular backups and secure backup storage

Backups are crucial, but they must also be secured. Be sure to:

  • Use encrypted backups. Encrypt database backups to reduce the chance of unauthorized access.

  • Restrict backup access. Allow only authorized personnel to access, view, or restore backups.

  • Test restores. Regularly test backups to ensure they're complete and can be restored properly without any data integrity issues.

Disable unnecessary features

Reduce your attack surface by disabling unused features:

  • Remove unused extensions. Disable any extensions that aren't actively used.

  • Disable trust authentication. Ensure trust authentication isn't used in production as it allows users to log in without a password.

  • Disable untrusted languages. Prevent the use of languages that allow arbitrary code execution, such as PL/Python.

Vulnerability scanning and penetration testing

  • Regularly scan for vulnerabilities. Use security scanners to find vulnerabilities.

  • Penetration resting. Test the security of your PostgreSQL instance. You may need to hire security professionals to test your database security periodically.

Network security controls

Strengthen PostgreSQL’s security by securing the network it operates in.

  • Set firewall rules. Restrict database access to necessary ports.

  • Limit network exposure. Use VPNs or internal networks for database access. Avoid exposing PostgreSQL directly to the internet.

  • Use intrusion detection. Use IDS tools to monitor for suspicious activity.

Regularly review user permissions

  • Develop a review cadence. Regularly review user and role permissions to ensure no unnecessary privileges were granted.

  • Remove unnecessary privileges. Periodically review and revoke unnecessary privileges. Remove access immediately when a user no longer needs it.

Secure OS and file permissions

PostgreSQL runs on an operating system that also needs to be secured.

  • Restrict file access. Ensure that only the PostgreSQL service user can access critical files such as the data directory and logs. Set restrictive permissions (700) on the data directory.

  • Harden the OS. Apply operating system hardening practices, including disabling unnecessary services and ensuring regular OS updates.

Could this page be better? Report a problem or suggest an addition!