pg_hba.conf: Who Can Connect

Host-Based Authentication (HBA)

The mechanism PostgreSQL uses to decide whether a connection is permitted and how the client must authenticate. Rules are stored in pg_hba.conf and matched against each incoming connection's type, target database, username, and client address. The first matching rule determines the outcome — subsequent rules are ignored. If no rule matches, the connection is rejected.

scram-sha-256

The recommended authentication method for password-based access in PostgreSQL 10+. It performs a challenge-response exchange so the password is never sent in plaintext. It replaced MD5 as the preferred method because MD5 hashes can be cracked with modern hardware and the MD5 protocol sends the hash over the wire, enabling replay attacks.

🔌 Connect as postgres

Connect to the postgres database as the postgres superuser.

psql -U postgres

psql (18.4) Type "help" for help. postgres=#

📋 Read the HBA File Path

Use SHOW hba_file to find where pg_hba.conf lives on disk. This is useful when you need to edit it directly.

SHOW hba_file;

hba_file ---------------------------- /var/lib/postgresql/18/data/pg_hba.conf (1 row)

🔍 Query pg_hba_file_rules

Query pg_hba_file_rules to see all active authentication rules. This is the live, parsed view of pg_hba.conf — no need to cat the file.

SELECT line_number, type, database, user_name, address, auth_method FROM pg_hba_file_rules ORDER BY line_number;

line_number | type | database | user_name | address | auth_method -------------+-------+---------------+-----------+-----------+------------- 117 | local | {all} | {all} | | trust 119 | host | {all} | {all} | 127.0.0.1 | trust 121 | host | {all} | {all} | ::1 | trust 124 | local | {replication} | {all} | | trust 125 | host | {replication} | {all} | 127.0.0.1 | trust 126 | host | {replication} | {all} | ::1 | trust (6 rows)

🚫 Identify a reject Rule

Write a query that filters pg_hba_file_rules to show only rules that would explicitly block connections — those where auth_method = 'reject'.

SELECT line_number, type, database, user_name, address, auth_method FROM pg_hba_file_rules WHERE auth_method = 'reject';

line_number | type | database | user_name | address | auth_method -------------+------+----------+-----------+---------+------------- (0 rows)

🔑 Check Authentication Method for Local Connections

Query pg_hba_file_rules to find all rules that apply to local Unix socket connections (type = 'local'). Local connections typically use trust or peer for the postgres superuser.

SELECT line_number, database, user_name, auth_method FROM pg_hba_file_rules WHERE type = 'local' ORDER BY line_number;

line_number | database | user_name | auth_method -------------+---------------+-----------+------------- 117 | {all} | {all} | trust 124 | {replication} | {all} | trust (2 rows)

🕵️ Audit for Legacy md5 Rules

Audit the file for outdated authentication methods. Query pg_hba_file_rules for any rule still using md5 — a method that predates scram-sha-256 and is weaker against offline password cracking.

SELECT line_number, type, database, user_name, address, auth_method FROM pg_hba_file_rules WHERE auth_method = 'md5';

line_number | type | database | user_name | address | auth_method -------------+------+----------+-----------+---------+------------- (0 rows)

🔐 Harden a Network trust Rule to scram-sha-256

Line 119 grants trust to any user, on any database, connecting over TCP to 127.0.0.1 — no password required. Edit pg_hba.conf to change that rule's method to scram-sha-256, then reload to apply it without restarting the server. Target it by matching the line's content, not its line number — line numbers shift as a file changes, so a hardcoded '119s/.../' would silently edit the wrong line if anything above it were ever added or removed. The student account cannot write to PGDATA directly, so use the as-postgres helper. It uses passwordless sudo in this lab, so no Linux password is needed; PostgreSQL password authentication is configured separately.

\! as-postgres sed -i '/^host.*127\.0\.0\.1.*trust$/s/trust$/scram-sha-256/' /var/lib/postgresql/18/data/pg_hba.conf

SELECT pg_reload_conf();

\! PGPASSWORD= PGCONNECT_TIMEOUT=3 psql -w -h 127.0.0.1 -U student -d postgres -c "SELECT 1;"

pg_reload_conf: t; psql: error: fe_sendauth: no password supplied (expected after hardening)

➕ Add a New Rule for dev_user

A developer needs access to appdb from the office network, 192.168.1.0/24. Append a new rule granting dev_user that access using scram-sha-256.

\! echo "host    appdb    dev_user    192.168.1.0/24    scram-sha-256" | as-postgres tee -a /var/lib/postgresql/18/data/pg_hba.conf

host appdb dev_user 192.168.1.0/24 scram-sha-256

🔄 Reload Without Restarting

Apply the new rule with SELECT pg_reload_conf(). pg_hba.conf changes only ever need a reload — never a restart.

SELECT pg_reload_conf();

pg_reload_conf ---------------- t (1 row)

✅ Verify the New Rule Is Live

Confirm the dev_user rule appears in the live, parsed rule set returned by pg_hba_file_rules.

SELECT line_number, type, database, user_name, address, auth_method FROM pg_hba_file_rules WHERE 'dev_user' = ANY(user_name);

line_number | type | database | user_name | address | auth_method -------------+------+----------+------------+-------------+--------------- 127 | host | {appdb} | {dev_user} | 192.168.1.0 | scram-sha-256 (1 row)

👥 Confirm Existing Sessions Were Unaffected

Check pg_stat_activity for connections that were already established before the reload. A reload only changes how future connection attempts are evaluated — it never touches backends that are already running.

SELECT pid, usename, state, backend_start FROM pg_stat_activity WHERE pid <> pg_backend_pid();

pid | usename | state | backend_start -----+----------+-------+------------------------------- 92 | | | 2026-06-26 16:02:25.180683+00 93 | postgres | | 2026-06-26 16:02:25.181538+00 88 | | | 2026-06-26 16:02:25.108964+00 91 | | | 2026-06-26 16:02:25.175418+00 89 | | | 2026-06-26 16:02:25.111631+00 (5 rows)