Runbook: Change a pg_hba.conf Rule Without Locking Anybody Out

1 · Prerequisites

Confirm every item is in place before any state change.

• The exact client the change is for: source address or range, database, role, and the authentication method intended
• Shell access to the database host as a user who can edit pg_hba.conf and read the server log
• A database connection that is already open and will survive the change, so a mistake does not lock you out entirely
• A test client on the same network path as the real client, or somebody who can run a connection attempt from there
• Knowledge of whether configuration management owns pg_hba.conf, and if so where its source of truth lives
• The current contents of pg_hba.conf, saved, before any edit

2 · Pre-checks

Read-only diagnostic commands. If any of these don't match expected output, stop and investigate further.

• · Read the parsed rules, not the file. SELECT rule_number, type, database, user_name, address, netmask, auth_method, options, error FROM pg_hba_file_rules ORDER BY rule_number; This is what the server actually loaded, with a non-null error on any line it could not parse. A file that looks right and a rule set that is right are different claims.
• · Establish which rule the client currently matches. Rules are evaluated top to bottom and the first match wins, with no fall-through. A permissive rule above your new one makes the new one dead, and a restrictive rule above it makes the client fail regardless of what you add.
• · **Look specifically for trust rules.** SELECT rule_number, type, address, auth_method FROM pg_hba_file_rules WHERE auth_method = 'trust'; Loopback trust is present in most default installations and it makes local testing pass unconditionally, which will invalidate your verification later.
• · Confirm you have a second way in. Keep an existing superuser session open in another window for the whole procedure. If a reload leaves you unable to authenticate, that session is the difference between an edit and an incident.
• · Confirm whether the client needs TLS. hostssl matches only encrypted connections and hostnossl only unencrypted ones; plain host matches both. Choosing the wrong one produces no pg_hba.conf entry for host ... with a trailing no encryption that names the cause.
• · Save a copy of the file with a timestamp. cp pg_hba.conf pg_hba.conf.$(date -u +%Y%m%dT%H%M%SZ). The rollback in this procedure is a file copy, and it only works if the copy exists.
• · Confirm the change is not already made. Two people adding the same rule produces two rules, of which the second is unreachable, and a puzzling file six months later.

3 · Procedure

Execute each step in order. Verify the expected output of a step before moving to the next.

1. 1Write the rule down in full before editing anything: type, database, user, address, method, and any options. A rule composed in the editor tends to acquire an all that nobody intended.
2. 2Prefer the narrowest rule that works. A specific database and a specific role beat all all, and a /32 beats a /16. Breadth in pg_hba.conf is permanent in practice: nobody ever comes back to narrow it.
3. 3Decide the position deliberately, and say why. The new rule must sit above any rule that would match the same client first, and below any rule that must keep precedence. Position is the whole semantics of this file.
4. 4**Prefer hostssl to host for anything crossing a network.** host accepts a plaintext connection, and a client whose driver defaults to sslmode=prefer will take it silently.
5. 5**Prefer scram-sha-256 to any other password method.** md5 is present in old files and old runbooks; it is weaker and there is no reason to add a new rule that uses it.
6. 6Make the edit in the source of truth. If configuration management owns the file, edit the template and let the tool converge. Editing the file directly under a tool that manages it produces a rule that disappears at the next run, usually at the least convenient moment.
7. 7**Reload, and check pg_hba_file_rules for parse errors before testing anything.** SELECT pg_reload_conf(); then SELECT rule_number, error FROM pg_hba_file_rules WHERE error IS NOT NULL;. A malformed line means the server kept the previous rules, which is safe and looks exactly like success from the client side.
8. 8Read the server log for the reload. grep -E "received SIGHUP|pg_hba|authentication file" /var/log/postgresql/postgresql-18-main.log | tail -10. This is where a rejected file is reported.
9. 9Test the positive case from the real client path. Connect as the intended role, to the intended database, from the intended source address — not from the database host over loopback, where a trust rule may accept anybody.
10. 10Test the negative case. Attempt a connection that the rule set is supposed to refuse: a wrong password, or a source the rule does not cover. A rule set that has only ever been tested with something that should succeed has not been tested.
11. 11Read the server log for the successful attempt. With log_connections on, PostgreSQL records which rule matched: connection matched file "..." line N. That line is the proof that the rule you added is the rule that fired, rather than one above it.
12. 12Record the change: the rule, its position, the two test results, and the log line naming the matched rule.

4 · Verification

Confirm the procedure actually fixed the problem.

• ✓SELECT count(*) FROM pg_hba_file_rules WHERE error IS NOT NULL; returns zero.
• ✓The intended client connects successfully from its real network path, using the real credential and the real driver.
• ✓The server log for that connection names the rule number and line you added, not a different one.
• ✓A deliberately wrong credential from the same path is refused. If it succeeds, a trust rule is matching first and the whole verification is void.
• ✓No client that was working before the change has stopped working. Check pg_stat_activity for the applications you expect, and confirm the count of FATAL: no pg_hba.conf entry in the log has not increased.
• ✓SELECT rule_number, type, database, user_name, address, auth_method FROM pg_hba_file_rules ORDER BY rule_number; matches the intended rule set exactly, including position.
• ✓The timestamped backup of the previous file exists and is readable.

5 · Rollback

If verification fails, undo the procedure in reverse order.

• ↶Restore the timestamped copy over pg_hba.conf and reload: cp pg_hba.conf.<timestamp> pg_hba.conf && psql -c "SELECT pg_reload_conf();". Reloading is enough; pg_hba.conf never requires a restart.
• ↶If configuration management owns the file, revert the change in the source of truth instead. Restoring the file by hand under a converging tool produces a rollback that lasts until the tool next runs.
• ↶Confirm the rollback the same way you confirmed the change: read pg_hba_file_rules, then make a connection attempt from the real client path. A restored file that was not reloaded is not a rollback.
• ↶If a reload has locked out every remote client and you still hold a local session, you can edit and reload from that session's host. This is the reason the pre-check asks you to keep one open.
• ↶If you have locked out every client including yourself, the file can be edited with the server stopped and the server restarted. That is an outage, and it is why the negative test comes after the positive one rather than before.
• ↶If the change was a widening of access that has since been judged unnecessary, remove the rule rather than narrowing it. A rule that is left in place "in case" is how a 0.0.0.0/0 line survives an audit.

6 · Escalation

When the runbook isn't enough, contact:

• · A rule that looks correct does not match, and pg_hba_file_rules shows no error: escalate to whoever owns the network path. The client may be arriving from an address you did not expect, and the server log names the address it actually saw.
• · The change is to remove a trust rule and something breaks: escalate to the application owner before restoring it. A client that only works under trust has no working credential, and restoring the rule hides that fact rather than fixing it.
• · The rule set contains entries nobody can account for: escalate to security rather than deleting them. An unexplained pg_hba.conf entry is an access-control question with a history, and removing it destroys the evidence.
• · Configuration management reverts the change and the estate has no agreed owner for this file: escalate to the platform owner. Two writers will keep undoing each other.
• · The change is being made under incident pressure to restore a client that has stopped connecting: escalate to the incident owner and check whether the cause is expiry, a role change or a network change before widening any rule. Widening access to fix an authentication failure is how a temporary rule becomes permanent.
• · You cannot construct a rule narrow enough because the client's source address is not stable: escalate to the network owner. The answer is a stable egress address or a different authentication method, not a wider CIDR.

References

1. PostgreSQL 18 documentation, The pg_hba.conf File
2. PostgreSQL 18 documentation, pg_hba_file_rules
3. PostgreSQL 18 documentation, Authentication Methods
4. PostgreSQL 18 documentation, Secure TCP/IP Connections with SSL