After editing a PostgreSQL pg_hba.conf, how do you use pg_hba_file_rules and the server's FATAL messages to check the change before and after reloading?
answer
- the view reads the file on disk
- error column per line
- a broken file is not reloaded
- the matched line is in the log
basics
~20 sBefore reloading, query pg_hba_file_rules: it parses the file on disk and fills error for broken lines, and a broken file is not reloaded. Afterwards, the FATAL text and the log's Connection matched detail show which record decided.
solid answer
~50 s`pg_hba_file_rules` (superuser-only by default) parses the `pg_hba.conf` that is **on disk now**. It returns one row per record with `rule_number`, `line_number`, `type`, `database`, `user_name`, `address`, `auth_method` and `error`. Run it before reloading. A non-null `error` is usually a parse error that would make the reload fail, occasionally only a warning, such as a `hostssl` record while `ssl = off`. When a reload fails, PostgreSQL keeps the whole previously loaded rule set and logs that the file was not reloaded; at start-up the same error stops the server. After the reload, read the failure text. `no pg_hba.conf entry for host ...` means no record matched. `pg_hba.conf rejects connection ...` means a `reject` record matched. `password authentication failed for user ...` means a record matched and its method failed. Only the server log adds a `Connection matched file ... line N` detail. `pg_ident_file_mappings` does the same job for `pg_ident.conf`.
code
sql · 6 linesSELECT rule_number, line_number, type, database, user_name,
address, auth_method, options, error
FROM pg_hba_file_rules
WHERE error IS NOT NULL
OR 'clinic' = ANY (database)
ORDER BY rule_number NULLS FIRST;go deeper
Know that pg_hba_file_rules exists, that its error column flags broken lines, and that pg_hba.conf edits need a reload.
Explain that the view reads the file on disk, that a failed reload keeps the old rules, and how the no-entry, rejects and password-failed messages differ.
Describe a safe change routine (query the view, reload, test from the client subnet, read the Connection matched detail) and how it prevents a lockout at the next restart.
Decide how authentication-file changes are reviewed and rolled out across clusters, for example generated from templates and checked through pg_hba_file_rules before deployment.
## Why a checking view exists A mistake in `pg_hba.conf` can lock everyone out, including the administrator who made it. PostgreSQL therefore lets you inspect the file **as the server would parse it** before you apply it, and it reports authentication failures in a way that tells an operator, but not an attacker, which record was involved. ## What pg_hba_file_rules shows The system view `pg_hba_file_rules` returns one row for each non-empty, non-comment line of the file, including lines pulled in by `include` directives. - `rule_number` is the order in which the record is considered during authentication. It is NULL for a line that could not be parsed. - `file_name` and `line_number` locate the line, which matters once includes are used. - `type`, `database` (an array), `user_name` (an array), `address`, `netmask`, `auth_method` and `options` show how the line was understood. - `error` is non-null when something is wrong with the line. For a line that cannot be parsed, the row usually has only `line_number` and `error` filled in. A few messages are warnings rather than load failures, for example `hostssl record cannot match because SSL is disabled`. Two properties matter in practice. First, the view reads the **current file on disk**, not the rules the server last loaded. Second, only superusers can read it by default. ## The reload trap Loading is all-or-nothing: 1. You edit the file and query the view. A row with an `error` tells you the edit needs another look before you reload. 2. If you reload anyway, the server parses the whole file. Finding one bad line, it discards the new version, **keeps every previously loaded record**, and logs that the file was not reloaded. 3. The view now shows the edited file while the server still enforces the old one, so the two disagree until the error is fixed. 4. At start-up there is no older version to fall back on. If the file cannot be loaded, the server reports that it `could not load` the file and exits. So a broken edit is harmless at a reload but fatal at the next restart, which may come weeks later. The view lets you catch it either way. ## Reading the failure messages | Message the client sees | What it means | SQLSTATE | |---|---|---| | `no pg_hba.conf entry for host "…", user "…", database "…", no encryption` | no record matched type, database, user and address | 28000 | | `pg_hba.conf rejects connection for host …` | a `reject` record matched first | 28000 | | `password authentication failed for user "…"` | a password record matched; the exchange failed | 28P01 | | `Peer authentication failed for user "…"` | a `peer` record matched; the OS user did not fit | 28000 | The last part of the `no pg_hba.conf entry` message reports the encryption state (`no encryption`, `SSL encryption` or `GSS encryption`), which quickly shows a client trying plaintext after you moved everything to `hostssl`. Replication connections get their own variant that names no database. Applications can branch on the SQLSTATE: `28P01` means a password was wrong, while `28000` covers the other refusals, so a retry loop can stop at once on a bad credential. ## The server log knows more When a matched record's method fails, the server logs a DETAIL that the client never receives: - `Connection matched file "…" line N: "…"` quotes the exact record that was used. - A reason line comes before it, such as `Password does not match for user …`, `Role … does not exist.` or `User … does not have a valid SCRAM secret.` The client gets only the generic FATAL line on purpose, so someone probing the server cannot tell a missing role from a wrong password. ## pg_ident.conf has the same pair User-name maps in `pg_ident.conf` are reloaded together with `pg_hba.conf` and follow the same all-or-nothing rule. Their checking view, `pg_ident_file_mappings`, has `map_number`, `file_name`, `line_number`, `map_name`, `sys_name`, `pg_username` and `error`. A good change routine for the clinic cluster after its audit looks like this: 1. Edit `pg_hba.conf` and `pg_ident.conf`, keeping the previous versions. 2. Query `pg_hba_file_rules` and `pg_ident_file_mappings` and confirm no row has an `error`. 3. Reload with `pg_ctl reload` or `SELECT pg_reload_conf()`, and check the log for a 'was not reloaded' line. 4. Connect from the application subnet and from an administrator's workstation, keeping an existing superuser session open as a way back in. 5. Read the log for `Connection matched` details on any failure.
- Why can pg_hba_file_rules disagree with what the server enforces?It parses the file on disk at query time instead of showing the rules the postmaster holds. If the last reload failed on a bad line, the server still runs the previous version while the view already shows the edited file. That is exactly when its `error` column matters.
- Why is a broken pg_hba.conf more dangerous at a restart than at a reload?On a reload, PostgreSQL keeps the rules it already had and logs that the file was not reloaded, so nobody is locked out. At start-up there is nothing to fall back on: the server reports that it could not load the file and exits. The cluster then stays down until someone fixes the file on the host.
- pg_hba_file_rules shows exactly what the server is enforcing right now.
- A reload skips a bad pg_hba.conf line and applies the rest.
- 'password authentication failed' means no pg_hba.conf record matched.
- The client's error message names the pg_hba.conf line that matched.
- On Linux, pg_hba.conf edits apply to new connections without a reload.