DBAtlas · Database operations atlas

Validate pg_hba.conf rule order and reload safely

PostgreSQLSecurity

Command

-- File location
SHOW hba_file;

# Rules are read top to bottom and the FIRST matching rule wins;
# specific rules must come BEFORE general ones:
#   hostssl  appdb  web_app  10.10.0.0/16  scram-sha-256
#   host     all    all      10.10.0.0/16  reject
#   host     all    all      0.0.0.0/0     scram-sha-256

-- Validate BEFORE reloading: pg_hba_file_rules reads the file on disk
SELECT line_number, type, database, user_name, address, netmask, auth_method, error
FROM pg_hba_file_rules
ORDER BY line_number;

-- Apply if the error column is empty
SELECT pg_reload_conf();

What it does

Shows where pg_hba.conf lives, how PostgreSQL interprets it (line, type, database, user, address, method) and any syntax errors before the reload; then reloads the configuration.

When to use it

When adding a new client network/application, migrating from md5 to scram-sha-256, or investigating 'no pg_hba.conf entry for host' errors.

Caution

pg_hba_file_rules shows the file as it is now, not the active rules. If any line has a non-empty error, a reload does NOT load pg_hba.conf at all and the old rules stay in effect (the log says 'pg_hba.conf was not reloaded'). Changes affect new connections only. A broad rule placed early (e.g. host all all 0.0.0.0/0 trust) neutralizes every restriction after it. With SSL off, hostssl lines cannot match and show a message in the error column. By default only superusers can read the view. Make sure tools editing the file as root do not change its ownership; if the postgres user cannot read it, the reload fails.

Open in DBAtlas →

Related