Authentication: pg_hba.conf, Password Policies & Scram-SHA-256

What Is pg_hba.conf?

PostgreSQL controls who may connect to the server through a file called pg_hba.conf (Host-Based Authentication configuration). Every incoming connection is checked against this file from top to bottom; the first matching record wins. If no record matches, the connection is refused. Understanding this file is the single most important step in locking down a PostgreSQL installation.

The default location on Debian/Ubuntu-based systems is `/etc/postgresql/16/main/pg_hba.conf`. On Red Hat-based systems it is typically inside the data directory, often `/var/lib/pgsql/16/data/pg_hba.conf`.

Record Format

Each non-comment line in pg_hba.conf has five fields:

```

TYPE DATABASE USER ADDRESS METHOD

```

• TYPE — connection type: `local` (Unix-domain socket), `host` (TCP/IP, plain or SSL), `hostssl` (SSL only), `hostnossl` (no SSL).
• DATABASE — the database name, `all`, or a comma-separated list.
• USER — the PostgreSQL role name, `all`, or a group prefixed with `+`.
• ADDRESS — an IP address with CIDR mask for `host` lines; omitted for `local` lines.
• METHOD — how credentials are verified (see below).

Blank lines and lines beginning with `#` are ignored.

Authentication Methods

trust

Accepts the connection with no password. Use only on tightly controlled local sockets where the OS already restricts access, or in a disposable development container. Never use trust over a network interface in production.

peer

Applicable only to `local` (Unix-socket) connections. PostgreSQL asks the OS for the connecting process's Unix username and compares it to the requested database role. This is safe for DBA logins on the same machine because OS-level user management enforces identity.

md5

The client sends an MD5-hashed password. MD5 is still widely supported but is considered cryptographically weak. Prefer scram-sha-256 for all new deployments.

scram-sha-256

Introduced in PostgreSQL 10 and the recommended method as of PostgreSQL 16. Uses the Salted Challenge Response Authentication Mechanism (SCRAM) with SHA-256. The password is never transmitted in plain text, and replay attacks are prevented by a server-generated nonce. Passwords stored in `pg_authid` are hashed using SCRAM, making a database dump far less dangerous than one containing MD5 hashes.

reject

Immediately refuses the connection with an error. Placing a `reject` line above a broader `host all all 0.0.0.0/0` rule is a common pattern for blocking specific hosts or users while still allowing others.

Editing and Reloading

Always make a backup before editing:

```bash

sudo cp /etc/postgresql/16/main/pg_hba.conf \

/etc/postgresql/16/main/pg_hba.conf.bak

```

After editing, check your syntax mentally, then reload without a full restart:

```bash

sudo pg_ctlcluster 16 main reload

```

On Red Hat systems: `sudo systemctl reload postgresql-16`

A reload re-reads pg_hba.conf and `postgresql.conf` without dropping existing connections. New connections immediately use the updated rules.

Verifying Configuration

Inside psql, two tools help you confirm what is active:

• `\conninfo` — prints the current session's connection type, user, database, host, and port.
• `SELECT * FROM pg_hba_file_rules;` — shows the parsed, active contents of pg_hba.conf as a query result. This is the authoritative view of what PostgreSQL is actually enforcing right now.

Password Policies and scram-sha-256 in Practice

Creating a user with scram-sha-256 storage requires that the server parameter `password_encryption` is set to `scram-sha-256` (the default in PostgreSQL 16) before you issue `CREATE ROLE` or `\password`. Verify with:

```sql

SHOW password_encryption;

```

You can also set it per session:

```sql

SET password_encryption = 'scram-sha-256';

```

Always set a strong password and, where policy requires it, combine PostgreSQL authentication with network-layer controls such as firewalls and TLS certificates.

Cross-Platform Perspective

Oracle Database enforces password complexity, reuse limits, and expiration through password profiles assigned to users (`CREATE PROFILE … LIMIT PASSWORD_LIFE_TIME …`). Microsoft Azure SQL Database offers two authentication modes: SQL authentication (username/password stored in the database) and Microsoft Entra ID authentication (formerly Azure Active Directory), which allows centralized identity management and multi-factor authentication. PostgreSQL relies on pg_hba.conf combined with OS controls and, for enterprise deployments, LDAP or GSSAPI integration. All three platforms share the underlying goal: verify identity before granting database access.

Safety Notes

• Least privilege: create separate roles for each application; never run applications as the superuser `postgres`.
• No trust over the network: a misconfigured `host all all 0.0.0.0/0 trust` line exposes every database on the server to anyone who can reach the port.
• Version control your pg_hba.conf: treat it like code. Keep dated backups and use a change log so you can audit who changed what and when.
• TLS in transit: pairing `hostssl` with scram-sha-256 ensures both the credential exchange and the session data are encrypted.