How to use this review
Quarterly, against the running cluster, with a client on the application’s own network path.
The review has three layers and they fail independently: who can reach the port, who can authenticate, and what they can do once inside. A cluster can be perfect at two of them and open at the third.
Reading the rules, not the file
pg_hba_file_rules shows what the server parsed, in evaluation order,
with a non-null error column for any line it rejected. Rules are
evaluated top to bottom and the first match wins with no fall-through, so
position is the semantics — a permissive rule above a restrictive one
makes the restrictive one dead.
Reading the file tells you what somebody wrote. Reading the view tells you what the server is enforcing.
What to do with a finding
Record it with a severity, an owner and a date. A critical finding
without a date is a decision to accept the risk that nobody has made
explicitly.
Diff this review against the last one. An estate where nobody can say what changed in three months is one where a grant made during an incident has quietly become permanent.
Where the numbers come from
The rules come from pg_hba_file_rules in evaluation order, not from
reading the file — the file’s order is the only order that matters and
it is the one the view renders. Encryption status comes from
pg_stat_ssl joined to pg_stat_activity, filtered to sessions with a
client_addr, because Unix-socket sessions are local and never TLS.
Password methods come from pg_authid.rolpassword’s prefix.
Certificate expiry comes from the certificate itself, read from the
endpoint with openssl s_client, not from the file somebody believes is
installed.
Access this needs
A role holding pg_monitor for the connection and TLS views and
pg_read_all_settings for the running configuration values in
pg_settings.
Reading pg_hba_file_rules, pg_ident_file_mappings and
pg_file_settings needs more than either of those. Measured on a stock
18.6 cluster, all three are refused for pg_monitor and for
pg_read_all_settings alike — permission denied for view pg_hba_file_rules — and they are superuser-only until granted
explicitly. GRANT SELECT on the view is not sufficient on its own
either; the view reads a set-returning function, and EXECUTE on
pg_hba_file_rules(), pg_ident_file_mappings() and
pg_show_all_file_settings() is required as well. Note that
pg_read_all_settings does cover pg_settings, the running values —
a different view from pg_file_settings, and the names are close enough
to mislead.
A non-privileged connection from the application’s own network path,
with the real driver and a deliberately wrong credential, because the
refusal cases cannot be exercised from an administrative session and
cannot be exercised over loopback on a host whose rules grant trust
there.
Read access to the TLS material on the host and to its expiry dates. Nothing here requires the authority to change a rule; a proposed change is a finding, not an action taken during the review.
What the review produces
A dated record naming the reviewer, the cluster, and the disposition of
every item, with the pg_hba_file_rules extract attached in evaluation
order and the three refusal messages captured verbatim — the one that
means no rule matched, the one that means a rule refused, and the one
that means the credential was wrong.
Findings go to the database owner and, for anything reaching authentication, encryption or superuser membership, to the security owner on the same day.
Sign-off
- Reviewer: ________________ Date: ___________
- Database owner: ___________ Date: ___________
- Security owner: ___________ Date: ___________
Every critical item must pass. A failing critical item is a blocker, not a note for the next sprint: record the date, the reviewer, the disposition of every item that did not pass, and the name of whoever accepted the residual risk.