Scope pg_hba.conf rules to real clients

The fixpg_hba.conf

# TYPE     DATABASE  USER   ADDRESS         METHOD
local      all       all                    peer
hostssl    appdb     appuser 10.0.1.0/24    scram-sha-256
hostssl    appdb     readonly 10.0.2.5/32   scram-sha-256
host       all       all    127.0.0.1/32    scram-sha-256
# no catch-all rule below this line

SELECT pg_reload_conf();

Why it matters

host all all 0.0.0.0/0 scram-sha-256 is a rule that says: any role may connect to any database from anywhere, provided they have the password. It appears in a great many clusters because it is the rule that stops the errors.

It also throws away three of the four columns Postgres gives you. A password is the only thing left between an attacker and every database on the server — and passwords leak. pg_hba.conf is where you decide that a leaked reporting credential cannot be used to reach production data from an unknown address.

Every column you narrow is a layer that survives a credential leak:

• ADDRESS — the leaked password is useless from outside your subnet.
• DATABASE — the reporting role cannot touch the app database at all.
• USER — one role, one rule; revoking one client doesn’t disturb others.

The first matching rule wins

This is the behaviour that defeats otherwise careful configs, and it is worth stating precisely: PostgreSQL walks the file top to bottom and uses the first rule whose type, database, user and address all match. It does not look for the most specific match, and it does not keep looking if authentication fails.

Two consequences:

• A broad rule near the top shadows every specific rule below it. Your carefully scoped hostssl appdb appuser 10.0.1.0/24 line does nothing if host all all 0.0.0.0/0 trust sits above it.
• If the first matching rule rejects you, that is the end. Postgres will not fall through to a later rule that would have let you in — which is why “I added a rule and it still fails” is nearly always a shadowing problem.

Order specific rules above general ones, and keep the catch-all — if you need one at all — at the bottom.

Use hostssl, not host

host matches both encrypted and unencrypted connections. A rule written as host permits a client to connect in plaintext even with ssl = on, because enabling TLS offers it rather than requiring it.

hostssl matches only TLS connections. For anything crossing a network, that is the type to use — see Enable TLS.

Verify it worked

Look for catch-all rules the server actually parsed:

SELECT line_number, type, database, user_name, address, auth_method
FROM pg_hba_file_rules
WHERE address IN ('0.0.0.0/0', '::/0', 'all')
   OR database = '{all}';

Read the whole ruleset in order — shadowing is only visible in sequence:

SELECT line_number, type, database, user_name, address, auth_method
FROM pg_hba_file_rules
WHERE error IS NULL
ORDER BY line_number;

Then prove a rule denies what you think it denies. From a host outside the allowed range:

psql "host=your.db port=5432 dbname=appdb user=appuser" -c 'SELECT 1'

Expect no pg_hba.conf entry for host .... That error means the scoping worked — it is the message you want to see.

Before you apply this

A bad pg_hba.conf locks out applications at their next connection, and connection pools reconnect constantly, so “next” means seconds.

Two protections:

Check before you reload. Postgres parses the file into a view whether or not it is live:

SELECT line_number, error FROM pg_hba_file_rules WHERE error IS NOT NULL;

A syntax error here means a reload leaves the old rules in place and logs a complaint — recoverable. A file that parses cleanly but is logically wrong is the dangerous case, because it applies immediately.

Know who is connecting before you narrow. This tells you the addresses and roles actually in use, which is what your rules must cover:

SELECT DISTINCT client_addr, usename, datname
FROM pg_stat_activity
WHERE client_addr IS NOT NULL
ORDER BY 1;

Anything missing from that list — a nightly batch job, a monitoring agent, a failover replica — will not appear, because it is not connected right now. Check your connection strings too, not just live sessions.

An established session survives a reload, so keep one open.

Related

• Remove trust authentication — the worst thing a wide rule can be paired with.
• Enable TLS — why these rules say hostssl.