Audit PostgreSQL Authentication with pg_hba_file_rules

Audit PostgreSQL Authentication with pg_hba_file_rules

Validating a pg_hba.conf edit before trusting it in production usually means reading the raw file line by line and hoping the parser interprets every rule the way it looks on screen. pg_hba_file_rules skips that guesswork — it is a system view that shows exactly how the server parsed the file after the last reload, one row per rule, with a dedicated error column that flags any line PostgreSQL could not apply.

Purpose and Overview

pg_hba.conf is the file that decides who can connect to a PostgreSQL cluster, from where, against which database, and with which authentication method. Each line is a compact rule with several positional fields — connection type, database, user, client address, and auth method, plus optional method-specific settings — and a small typo in any field can silently break a rule or, worse, leave it matching more traffic than intended. Reading the file by eye does not tell you which interpretation the parser actually settled on.

pg_hba_file_rules closes that gap by exposing the server's own parsed understanding of the file. Instead of trusting a manual read-through, a query against the view returns every rule the server currently has loaded, in file order, with each field resolved to the value PostgreSQL will actually compare against an incoming connection. The error column is the feature that makes it more than a pretty-print of the file: any line the parser could not turn into a usable rule shows up with a description of the problem, in the same query, without needing to grep the server log for a rejected-connection message.

This is a different angle on authentication than reading pg_settings for GUCs such as password encryption defaults or connection timeouts. Those settings describe policy the server applies once a rule has matched. pg_hba_file_rules describes the ruleset itself — which connections are even eligible to be evaluated, and whether every line in the file that is supposed to grant or restrict access actually made it into the live configuration.

Sample Code

 1SELECT
 2    line_number,
 3    type,
 4    database,
 5    user_name,
 6    address,
 7    netmask,
 8    auth_method,
 9    error
10FROM
11    pg_hba_file_rules
12ORDER BY
13    line_number;

Filtered variant, for a fast sweep after any edit or reload:

 1SELECT
 2    line_number,
 3    type,
 4    database,
 5    user_name,
 6    auth_method,
 7    error
 8FROM
 9    pg_hba_file_rules
10WHERE
11    error IS NOT NULL
12ORDER BY
13    line_number;

Notes: Querying pg_hba_file_rules requires superuser privileges. The error column is NULL for every rule that parsed cleanly; a non-null value means PostgreSQL skipped that line entirely when it built the currently active authentication rule set, so any connection that was supposed to rely on that line is not protected — or not permitted — the way the file suggests.

Code Breakdown

The base query is a full scan of the view; the filtered variant narrows it to the rows that actually need attention.

Reading the Parsed Rule Fields

1type, database, user_name, address, netmask, auth_method

These columns mirror the positional fields of a pg_hba.conf line, but resolved rather than literal. A CIDR-format address such as 192.168.1.0/24 is split into its constituent address and netmask values; keywords like all or replication in the database or user position show up as themselves, since PostgreSQL treats them as special tokens rather than literal names to resolve. Reading these columns instead of the raw file confirms what the server will actually compare an incoming connection against, not what a human reader assumes the line means.

Filtering on error IS NOT NULL

1WHERE error IS NOT NULL

This is the fastest way to answer one question: did every line in pg_hba.conf load correctly after the last reload? A malformed CIDR block, an unrecognized auth_method keyword, or a line with the wrong number of fields all produce a non-null error value describing the specific problem, rather than a silent parse failure that only surfaces later as an unexpected connection rejection.

Ordering by line_number

1ORDER BY line_number

pg_hba.conf is evaluated top to bottom, and PostgreSQL uses the first rule that matches a connection's type, database, user, and address — every later matching rule is ignored. Reviewing the result in file order is what makes it possible to reason about that first-match behavior directly from the query output, rather than reconstructing rule precedence from a mental model of the file.

Key HBA Rule Fields

type — Connection Type

The type column distinguishes how a client is reaching the server. local covers Unix-domain socket connections; host covers plain TCP/IP; hostssl and hostnossl require or forbid SSL respectively; additional variants exist for GSSAPI-encrypted connections where the server supports them. A rule's type is the first thing PostgreSQL checks when deciding whether the rule even applies to a given connection attempt.

database and user_name — Matching Scope

These columns can hold a literal name, a comma-separated list, or a keyword such as all, sameuser, samerole, or replication. A rule scoped too broadly here — all in both fields, for example — matches far more traffic than a narrowly scoped rule further down the file, which is exactly why file order and scope have to be reviewed together rather than in isolation.

auth_method and options

auth_method holds the value that determines how a matching connection proves its identity: trust, reject, md5, scram-sha-256, peer, ident, cert, and gss are the common values. The options column carries method-specific configuration attached to that rule — an LDAP server address for an ldap rule, or a clientcert requirement for a cert rule — and is where subtle misconfiguration often hides, since the base auth_method can look correct while an option string is malformed or missing.

The error Column

error is NULL for every line the parser accepted. When it is not null, the text describes exactly what went wrong on that line — an unrecognized auth method keyword, a malformed address or netmask, or a field count that does not match the expected format for that rule type. Because the column reports the failure at the specific line, there is no need to bisect the file manually to find the broken entry.

Practical Applications

Reading pg_hba_file_rules fits into both routine change management and reactive troubleshooting — the same query answers a pre-flight check and an incident-response question.

Catching Bad Rules Immediately After Reload

After editing pg_hba.conf and issuing a reload (SELECT pg_reload_conf(); or pg_ctl reload), running the filtered query for non-null error rows confirms whether every new or edited line actually took effect. This check takes a second and catches a broken rule long before a locked-out client reports a failed connection.

Reviewing Rule Order Before a Security Change

Before adding a new restrictive rule to tighten access, review the full ordered rule list first. Because PostgreSQL stops at the first matching line, a broad early rule — a permissive trust entry for a wide subnet, for instance — can silently shadow a narrower rule added later in the file, making the new restriction a no-op.

Confirming Auth Method Coverage Across Roles

Filtering the result by user_name and database confirms that every application role has a matching rule using an approved auth_method such as scram-sha-256, and that no role is falling through to a more permissive default entry sitting near the bottom of the file.

Documenting the Live Authentication Surface for Compliance Review

Exporting the full rule list gives a security reviewer a plain-text record of exactly which connection types, source networks, and auth methods are permitted — sourced from the server's own parsed understanding of the file, rather than a manually transcribed copy of pg_hba.conf that may not match what was actually loaded.

Version Compatibility

pg_hba_file_rules was added in PostgreSQL 10, part of a broader push to make server-side configuration state queryable rather than requiring a log-file read or a manual file review. Before PostgreSQL 10, confirming that a pg_hba.conf edit parsed correctly meant triggering a reload and then checking the server log for an "invalid entry" message — no in-database way existed to verify the rule set without leaving the SQL interface.

PostgreSQL 10 also introduced scram-sha-256 as a supported auth_method value, and rules using it appear in the auth_method column the same way any md5 or trust entry does. Later PostgreSQL releases extended pg_hba.conf itself to support included configuration via directives that pull in additional files, and the view's row set reflects entries contributed by every included file in resolved order, not only the top-level file — worth keeping in mind on a cluster whose configuration is split across multiple files rather than a single pg_hba.conf.

Best Practices

• Query immediately after every reload — a non-null error row after a config change is a blocking issue, not a warning to revisit later.
• Restrict who can query the view — access requires superuser, so treat the ability to read pg_hba_file_rules with the same care as any other privileged diagnostic surface.
• Review rule order narrow-to-broad — a permissive rule placed early in the file can shadow a more restrictive rule added later; the ordered view output is the fastest way to catch this.
• Prefer scram-sha-256 over md5 — when auditing auth_method values across rules, flag any remaining md5 entries as migration candidates.
• Cross-check against connection logs — a rule that parses cleanly can still be scoped incorrectly; pair the audit query with actual connection log review during a change window.
• Treat the audit query as part of the deployment pipeline — running it automatically after every pg_hba.conf deploy catches parsing regressions before they reach an on-call engineer.

References

• PostgreSQL Documentation — The pg_hba.conf File — full field-by-field reference for pg_hba.conf syntax and rule matching order.
• PostgreSQL Documentation — pg_hba_file_rules — column reference for the view, including the error-reporting behavior.
• PostgreSQL Documentation — Authentication Methods — trust, password, SCRAM, peer, ident, cert, GSSAPI, and LDAP authentication methods available in auth_method.
• Crunchy Data — PostgreSQL Engineering Blog — ongoing coverage of PostgreSQL security and authentication operations topics.