Essential Configuration Settings for Securing Your PostgreSQL Database

Securing PostgreSQL starts with a simple question: who is allowed to connect, from where, and with what authentication method? If pg_hba.conf is too broad or the server listens on more interfaces than it needs, your database has a larger attack surface than necessary.

This guide covers the PostgreSQL security settings you should review first: pg_hba.conf, SCRAM password authentication, TLS, network listeners, role permissions, and logs.

1. Hardening Client Authentication with pg_hba.conf

The Host-Based Authentication file (pg_hba.conf) dictates which hosts can connect, which PostgreSQL users they can connect as, which databases they can access, and most importantly, the authentication method required for that connection.

Understanding pg_hba.conf Structure

Each line in pg_hba.conf follows a specific format:

TYPE DATABASE USER ADDRESS METHOD [OPTIONS]

• TYPE: Connection type (e.g., local, host, hostnossl, hostssl).
• DATABASE: Target database name(s).
• USER: Target database role(s).
• ADDRESS: Client IP address range.
• METHOD: Authentication mechanism (e.g., scram-sha-256, md5, reject, trust).

Best Practices for Authentication Methods

Never use the trust method in production, as it allows anyone matching the connection criteria to connect without a password. The recommended modern authentication methods are:

Recommended: scram-sha-256

SCRAM (Salted Challenge Response Authentication Mechanism) offers significant improvements over older password methods like md5 by using stronger hashing and preventing replay attacks. This should be your default choice for remote connections.

# Enforce SCRAM for remote connections from an application subnet
host    appdb   app_user 10.20.30.0/24  scram-sha-256

Set password_encryption = 'scram-sha-256' in postgresql.conf before creating or rotating passwords. Existing passwords stored with older hashes are not automatically converted; reset them after enabling SCRAM.

password_encryption = 'scram-sha-256'

Avoid broad rules like this unless another network control already restricts access:

host    all     all     0.0.0.0/0       scram-sha-256

Local Connections

For connections originating from the server itself (e.g., applications running on the same machine), use local sockets. The most secure setting is often peer (for Unix domain sockets) or scram-sha-256 if sockets are disabled or restricted.

# Use peer authentication for local connections via Unix socket
local   all     all             peer

Explicitly Rejecting Connections

Use the reject method to explicitly block connections from dangerous or untrusted networks.

# Block all connections from a known insecure IP range
host    all     all     192.168.1.0/24  reject

Actionable Tip: After modifying pg_hba.conf, reload PostgreSQL for changes to take effect, for example with sudo systemctl reload postgresql or SELECT pg_reload_conf();.

2. Implementing SSL/TLS Encryption

To prevent sensitive data (including passwords) from being intercepted over the network, enforcing SSL/TLS encryption for all remote connections is mandatory.

Configuration in postgresql.conf

Ensure these parameters are set correctly in your primary configuration file:

• ssl = on: Enables SSL support globally.
• ssl_cert_file: Path to the server certificate file (e.g., server.crt).
• ssl_key_file: Path to the server private key file (e.g., server.key).

Enforcing SSL in pg_hba.conf

To force clients to use SSL, change the connection type in pg_hba.conf from host to hostssl:

# Only allow connections via SSL/TLS
hostssl all     all     0.0.0.0/0       scram-sha-256

hostssl only matches SSL connections. Make sure you do not also have a later or earlier host rule that allows the same users without TLS. To make the policy obvious, add a reject rule for non-SSL connections:

hostnossl all    all     0.0.0.0/0       reject

3. Minimizing the Attack Surface Area

Security involves reducing the exposure of the database server to external threats. This is primarily managed through network configuration and disabling unnecessary features.

Limiting Network Listen Address

PostgreSQL commonly defaults to listening on localhost, but packaged deployments and managed images can differ. Explicitly configure it to listen only on the specific interface that requires access, or keep it on localhost if only local applications connect.

In postgresql.conf:

# Listen only on localhost (127.0.0.1) if possible
listen_addresses = 'localhost'

# OR, listen only on a specific private network interface
# listen_addresses = '192.168.1.10'

Security Warning: If listen_addresses is set to *, all interfaces will be used. Ensure pg_hba.conf strictly controls which IP ranges can connect.

Disabling Unnecessary Extensions and Features

Every enabled extension adds potential complexity and attack vectors. Regularly audit installed extensions and remove any that are not actively used for your production workload. This minimizes the overall attack surface.

Password Security and Roles

Ensure all administrative roles (like the default postgres user) have strong, complex passwords set using ALTER USER:

ALTER USER postgres WITH PASSWORD 'YourStrongAndComplexPassword123!';

Use the principle of least privilege: application users should only have SELECT, INSERT, UPDATE, and DELETE permissions on the specific tables they need, rather than superuser status.

4. Auditing and Logging Configuration

While not strictly an access control mechanism, robust logging is crucial for detecting and investigating security incidents. Configure logging parameters in postgresql.conf to capture relevant events.

Key settings for security auditing:

• log_statement = 'ddl' or 'all': Logs all Data Definition Language (DDL) commands, such as CREATE TABLE and ALTER USER. Use 'all' carefully because it can create high log volume and may capture sensitive query text.
• log_connections = on: Logs every successful connection attempt.
• log_disconnections = on: Logs when clients disconnect.
• log_duration = on: Logs the execution time of all statements, which can sometimes reveal unusual activity patterns.

By combining strict access rules in pg_hba.conf, enforced encryption via SSL, a restricted listening address, and comprehensive logging, you establish a solid foundation for securing your PostgreSQL deployment.

Essential Security Steps

1. Update pg_hba.conf: Use scram-sha-256 or peer for authentication methods.
2. Enforce Encryption: Set ssl = on in postgresql.conf and use hostssl entries in pg_hba.conf.
3. Restrict Listening: Configure listen_addresses to only necessary interfaces, avoiding the default * if possible.
4. Enforce Least Privilege: Limit database roles to only the permissions absolutely required for their function.
5. Reload Configuration: Always reload or restart PostgreSQL after modifying security files to apply changes.

These settings are the foundation. After you apply them, test from an allowed client, test from a blocked client, and verify that logs show the connection behavior you expected.