Skip to main content
RunBook Academy

← All break/fix scenarios in PostgreSQL

intermediatepg-hba~35 min

The reporting service could not connect, and the rule granting it access was correct and useless

Reported symptoms

  • ●A new reporting service deployed at 09:40 cannot connect to the analytics database and returns FATAL: pg_hba.conf rejects connection for host 10.44.9.12
  • ●The engineer appends a host rule for the reporting role and network to pg_hba.conf, reloads, and the message is unchanged
  • ●A second, broader rule is appended granting the whole 10.44.0.0/16 range, reloaded, and the message is still unchanged
  • ●A third rule is appended using the all keyword for both database and user, and the connection is still refused with the identical message
  • ●Other services on the same subnet connect to the same database without difficulty, including one deployed the previous week
  • ●The reporting service connects successfully to a staging cluster whose pg_hba.conf was copied from production eight months ago
  • ●By 10:20 someone proposes restarting PostgreSQL on the theory that the reload is not taking effect

Evidence

  • · pg_hba_file_rules shows fourteen rules; the three appended during the incident are rule_number 12, 13 and 14 and all report auth_method scram-sha-256 with no error
  • · Rule number 6, at line 96, is host all reporting_svc 10.44.0.0/16 reject, added during a security review four months earlier
  • · The server log DETAIL line for each failed attempt reads Connection matched file "/etc/postgresql/18/main/pg_hba.conf" line 96, naming the reject rule directly
  • · The client error is pg_hba.conf rejects connection, which is a different message from no pg_hba.conf entry and indicates a rule matched and refused
  • · SELECT pg_reload_conf() returns true each time and the log records received SIGHUP, reloading configuration files, so the reloads are taking effect
  • · The services that connect successfully use different roles, none of which appears in rule 6
  • · The staging cluster predates the security review and has no reject rule, which is why the same client configuration works there
  • · The security review ticket records the reject rule as a temporary measure pending a decision that was never recorded as made
Diagnosis and resolutionclick to reveal

Root cause

A `reject` rule for the `reporting_svc` role was added four months earlier and had been sitting at rule number 6 ever since. Every rule appended during the incident was appended **below** it, and `pg_hba.conf` stops at the first rule that matches. This is the defining property of the file and the one most often misunderstood. `pg_hba.conf` is not a set of permissions that combine; it is an ordered list evaluated top to bottom, and evaluation stops at the first rule whose connection type, database, user and address all match the incoming connection. Whatever that rule says is the verdict. Rules below it are not consulted, are not partially applied, and cannot rescue a connection the earlier rule refused. Rule 6 matched: the connection type was `host`, the database keyword was `all`, the user was `reporting_svc`, and the address was inside `10.44.0.0/16`. Its method was `reject`, so the server refused and stopped looking. Rules 12, 13 and 14 were loaded, valid and irrelevant. The escalation made it worse in a way nobody noticed. Each successive rule was broader than the last in an attempt to force a match, and the final one used `all` for both database and user. That rule grants every role on the estate access to every database from that network. It did not fix the reported problem and it widened access for everybody else. The instinct behind all three appends is the one to unlearn: that access is additive, and that a more permissive rule added later overrides a restrictive one added earlier. It is the opposite. Position decides, and later means weaker.

Remediation

Read the server log first. The `DETAIL` line under every authentication failure names the file and the exact line that decided it: ```text DETAIL: Connection matched file "/etc/postgresql/18/main/pg_hba.conf" line 96 ``` That single line ends the investigation. It would have ended it at 09:42. Remove the three rules appended during the incident. They are ineffective for the reported problem and the third one is a genuine widening of access that must not be left in place. Removing them first also makes the file legible again before you change anything that matters. Then decide what rule 6 should be, which is a policy question rather than a technical one. Consult the security review ticket that introduced it. The three possibilities are: the reject was correct and the reporting service should not have access, in which case the outage is a deployment that should not have been attempted; the reject was a temporary measure that outlived its purpose, in which case remove it; or the reject was intended to be narrower than it is, in which case tighten it so that it names the databases it was meant to protect rather than `all`. Whichever it is, make the change **at rule 6's position**, not by appending. If the reporting service is to be allowed, the allow rule must appear above the reject or the reject must go. Reload and re-test, then re-read the `DETAIL` line for a failed attempt to confirm which rule is deciding now.

Verification

The reporting service connects and can run its query. Verify as the service, not as a superuser: `psql -h <host> -U reporting_svc -d analytics -c "SELECT 1;"`. `pg_hba_file_rules` shows the intended rule set and no rules left over from the incident. Compare the rule count before and after; it should be back to eleven plus whatever single change was deliberate. A deliberately failed connection — wrong password, or a role that should not have access — produces a `DETAIL` line naming the rule you expect, confirming that the rule you think is deciding is the rule that is deciding. `SELECT count(*) FROM pg_hba_file_rules WHERE error IS NOT NULL` returns zero, so the next restart will not fail on this file. No rule in the final file grants `all` on `all` from a network range unless that is a deliberate, documented decision.

Prevention

**Make the DETAIL line the first step of every access investigation.** It is available at default logging settings and it names the deciding rule. Put it at the top of the runbook for "service cannot connect", above anything about firewalls. **Never append to pg_hba.conf as a diagnostic step.** Appending can only help when nothing above matches, which is the case you can confirm in advance by reading the `DETAIL` line. Appending blindly is how a widened rule ends up in production. **Manage the file as an ordered document, not a log.** Generate it from configuration management with the ordering explicit, so that adding a rule means deciding where it goes. Reviewing a diff of a generated file also makes an `all`/`all` rule visible in review rather than in an audit. **Review reject rules on a schedule.** Rule 6 was introduced as temporary, the decision that would have removed it was never recorded, and it survived four months and one outage. A temporary rule needs an expiry date and an owner. **Test the rule set before it reaches production.** `pg_hba_file_rules` can be read on a staging cluster loaded with the production file, and a small script that attempts each expected connection turns rule ordering into a test rather than a discovery.

Reported symptoms

A reporting service is deployed at 09:40 and cannot reach the analytics database:

FATAL:  pg_hba.conf rejects connection for host "10.44.9.12", user "reporting_svc", database "analytics", SSL encryption

An engineer appends a rule for that role and network, reloads, and retries. Identical message. Appends a broader rule for the whole 10.44.0.0/16. Identical message. Appends a rule using all for both database and user. Identical message.

Other services on the same subnet connect to the same database without trouble, including one deployed a week earlier. The reporting service connects successfully to staging, whose pg_hba.conf was copied from production eight months ago.

At 10:20, forty minutes in, somebody proposes restarting PostgreSQL because “the reload must not be taking effect”.

Evidence provided

Read-only / Safethe rules the server has loaded, in evaluation order
$ psql -c "SELECT rule_number, line_number, type, database, user_name, address, auth_method FROM pg_hba_file_rules ORDER BY rule_number;"
 rule_number | line_number | type |  database   |    user_name     |   address   |  auth_method  
-------------+-------------+------+-------------+------------------+-------------+---------------
         6 |          96 | host | {all}       | {reporting_svc}  | 10.44.0.0   | reject
        12 |         141 | host | {analytics} | {reporting_svc}  | 10.44.9.0   | scram-sha-256
        13 |         142 | host | {analytics} | {reporting_svc}  | 10.44.0.0   | scram-sha-256
        14 |         143 | host | {all}       | {all}            | 10.44.0.0   | scram-sha-256

Illustrative output

The server log for each attempt:

Read-only / Safethe line that has been deciding this since 09:40
$ grep -A1 'rejects connection' /var/log/postgresql/postgresql-18-main.log | tail -2
2026-08-28 10:18:32.104 UTC [8812] reporting_svc@analytics FATAL:  pg_hba.conf rejects connection for host "10.44.9.12", user "reporting_svc", database "analytics", SSL encryption
2026-08-28 10:18:32.104 UTC [8812] reporting_svc@analytics DETAIL:  Connection matched file "/etc/postgresql/18/main/pg_hba.conf" line 96: "host    all             reporting_svc   10.44.0.0/16            reject"

Illustrative output

Rule 6 was added four months earlier by a security review. The ticket describes it as “temporary pending a decision on reporting access”.

Work the evidence before reading on

  1. The client message is pg_hba.conf rejects connection, not no pg_hba.conf entry. What is the difference, and what does it tell you before you look at anything else?
  2. Three rules were appended and none changed the outcome. What single fact about the file explains that?
  3. Rule 14 was added to force a match. What else did it do?
  4. Staging works. Why is that not evidence about the client?

Root cause

First match wins, and the first match was a reject

pg_hba.conf is evaluated top to bottom. The server takes the incoming connection’s type, database, user and address, walks the rule list, and stops at the first rule where all four match. That rule’s method is the verdict.

Rule 6 matched. Type host, database all, user reporting_svc, address inside 10.44.0.0/16. Its method is reject. The server refused and stopped.

Rules 12, 13 and 14 were loaded and valid, and were never consulted for this connection. They sit below a rule that already matched.

The escalation widened access without fixing anything

Each append was broader than the last, on the theory that a sufficiently permissive rule would eventually take effect. Rule 14 is the end of that progression:

host    all             all             10.44.0.0/16            scram-sha-256

Every role, every database, from a /16. It did not help the reporting service and it granted access to every other role on the estate from that entire network range.

Staging worked because staging is four months stale

The staging file was copied before the security review. It has no rule 6. That is not evidence that the client is correctly configured; it is evidence that the two files differ, which is its own finding.

Resolution

Read the DETAIL line. It names line 96. That is the whole diagnosis and it was available at 09:42.

Remove the three rules added during the incident, starting with rule 14. They are ineffective and one of them is a real widening of access.

sed -i '141,143d' /etc/postgresql/18/main/pg_hba.conf
psql -c "SELECT pg_reload_conf();"
psql -c "SELECT rule_number, line_number, user_name, auth_method
         FROM pg_hba_file_rules ORDER BY rule_number;"

Then decide what rule 6 should be. This is a policy question and it needs the security review’s owner, not an engineer at 10:30. The three outcomes:

  • The reject is correct. The reporting service should not have this access and the deployment was the mistake. Close the incident against the deployment.
  • The reject is stale. The decision it was waiting for was made informally and never recorded. Remove it.
  • The reject is too broad. It was meant to protect specific databases and was written with all. Narrow it.

Whichever it is, make the change at rule 6’s position. If the service is to be allowed, either the reject goes or an allow rule goes above it.

Verification

The reporting service connects, as itself:

PGPASSWORD=... psql -h db-prod-01 -U reporting_svc -d analytics -c "SELECT 1;"

pg_hba_file_rules shows the intended rule set with the incident’s rules gone. The count is back to eleven plus one deliberate change.

A deliberately failed attempt produces a DETAIL line naming the rule you expect. This is the check that confirms your mental model of the file matches the server’s.

SELECT count(*) FROM pg_hba_file_rules WHERE error IS NOT NULL returns zero.

No rule in the final file grants all on all from a network range.

Prevention

Put the DETAIL line at the top of the “cannot connect” runbook. It is available by default, it names the deciding rule, and it converts a forty-minute investigation into a thirty-second one.

Forbid appending as a diagnostic step. The only time appending helps is the one case you can rule in or out first.

Generate the file from configuration management, so that adding a rule means choosing a position, and so that a diff showing all/all gets seen in review.

Give temporary rules an expiry and an owner. Rule 6 was temporary four months and one outage ago.

Test the rule set. A staging cluster loaded with the production file plus a script that attempts each expected connection turns rule ordering into something you find in CI instead of at 09:40.