PostgreSQL DBA Roles and Authentication Security Hardening Guide

A practical guide to securing PostgreSQL by configuring pg_hba.conf, enforcing SCRAM-SHA-256, and applying least privilege to database roles.

Configuring pg_hba.conf for Strict Access Control

The pg_hba.conf file controls which clients connect to the PostgreSQL server. It uses a first-match-wins logic, so entry order matters. Each line defines the connection type, database, user, address, and authentication method. Production defaults often allow local peer authentication and remote scram-sha-256 or md5 connections. Defaults can leave gaps, such as trust authentication for local users or broad IP ranges.

Restrict the address column to specific subnets or hostnames. Avoid 0.0.0.0/0 or ::/0 unless a public-facing service requires it. Use specific CIDR blocks for internal applications. If the database uses only Unix domain sockets, set local entries to peer or cert authentication to verify the operating system user. Do not use trust authentication for persistent connections. Review listen_addresses in postgresql.conf to ensure the server does not listen on unnecessary interfaces.

Review pg_hba.conf entries to ensure no trust authentication is used for persistent connections.
Restrict address fields to specific IP ranges or hostnames to minimize the attack surface.
Verify that listen_addresses in postgresql.conf does not expose the server to unnecessary network interfaces.

Enforcing SCRAM-SHA-256 for Password Authentication

PostgreSQL supports md5 and scram-sha-256 password authentication. The md5 method is legacy and vulnerable to replay attacks. It lacks forward secrecy. SCRAM-SHA-256 uses a challenge-response protocol that protects against these issues. Set password_encryption in postgresql.conf to scram-sha-256 for new roles and migrations. This stores new passwords using the stronger algorithm.

Update existing roles with md5-hashed passwords to SCRAM. Reset the password for each role to re-hash the value using the current password_encryption setting. Verify that client libraries support SCRAM-SHA-256, as older clients may fail to authenticate. Remove md5 from pg_hba.conf after migration to prevent fallback to the weaker algorithm.

Set password_encryption to scram-sha-256 in postgresql.conf to enforce strong hashing for new passwords.
Reset passwords for existing roles to convert their hashes from md5 to SCRAM-SHA-256.
Remove md5 from pg_hba.conf authentication methods to prevent use of the legacy algorithm.

Applying Least Privilege to Database Roles

PostgreSQL uses roles to manage access permissions. A role acts as a user or a group. Privileges apply to specific objects like tables, schemas, and functions. Least privilege requires each role to hold only the permissions needed for its function. Broad privileges like ALL or PUBLIC often lead to unintended access. For example, granting CREATE on a schema lets a role create new objects, which a read-only application user does not need.

Create specific roles for different functions, such as app_read, app_write, and dba_admin. Grant only necessary privileges on specific objects. An application user may need SELECT on certain tables and INSERT on others, but not TRUNCATE or REFERENCES. Use GRANT and REVOKE commands to manage permissions. Audit roles regularly to remove unnecessary privileges. This reduces data exposure risk and simplifies access management during security incidents.

Create distinct roles for different functions, such as read-only, read-write, and administrative access.
Grant specific privileges on specific objects rather than using broad grants like ALL or PUBLIC.
Audit roles periodically to remove unnecessary privileges and ensure compliance with least privilege.

Integrating External Authentication Methods

Enterprise environments often integrate PostgreSQL with centralized identity systems like LDAP, Kerberos, or certificate-based authentication. LDAP lets users authenticate with directory credentials, removing the need for separate database passwords. Kerberos provides mutual authentication and encryption for secure networks. Certificate authentication uses X.509 certificates to verify client identity, often combined with TLS.

Configure the ldap method in pg_hba.conf and ensure the LDAP server is accessible from the database server. For Kerberos, configure the GSSAPI method and set up the Kerberos keytab correctly. For certificate authentication, use the cert method and ensure client certificates are signed by a trusted CA. These methods reduce password-based attack risks and provide a reliable authentication framework.

Use LDAP authentication to integrate with centralized identity management systems.
Configure GSSAPI for Kerberos-based authentication in secure network environments.
Implement certificate authentication using the cert method for strong client identity verification.

Monitoring and Auditing Authentication Events

Monitoring authentication events detects unauthorized access attempts and maintains a security audit trail. PostgreSQL logging options record connection attempts, disconnections, and authentication failures. Enable log_connections and log_disconnections to track user access. Set log_statement to record all statements for auditing, though this generates significant log volume. Use extensions like pgAudit for fine-grained logging of database activities.

Review logs regularly for suspicious activity, such as repeated failed logins or access from unexpected IP addresses. Set alerts for critical events like authentication failures for administrative roles. This helps identify and respond to threats quickly. Protect log files and retain them according to organizational compliance requirements.

Enable log_connections and log_disconnections to track user access patterns.
Use pgAudit or similar extensions for detailed auditing of database activities.
Set up alerts for authentication failures and suspicious access patterns to enable rapid response.