Lab 4: Build a pg_hba.conf and prove which rule matched

Objective

pg_hba.conf is the file that decides who may connect. It is also the file most often edited by adding a line at the bottom and reloading, and that habit fails in a specific and confusing way: the new rule is correct, the reload succeeds, and the connection is still refused.

By the end of this lab you will have made that failure happen twice, on purpose, and diagnosed it both times from the server rather than by staring at the file. You will finish able to answer, for any refused connection, the only question that matters: which line decided it?

You will also see the three distinct failure messages the server produces, which are routinely treated as interchangeable and are not. One means no rule matched. One means a rule matched and said no. One means a rule matched, asked for a credential, and did not like it. Those are three different problems with three different fixes.

Architecture

One cluster listening on all interfaces, three roles, two databases, and connections arriving over both the Unix socket and TCP so that local and host rules can be exercised separately.

flowchart TD
    A["app_user\nover TCP 172.26.0.0/16"] --> H
    B["admin_user\nover TCP 172.26.0.0/16"] --> H
    C["batch_user\nover TCP"] --> H
    D["OS user 'deploy'\nover the Unix socket"] --> H
    H["pg_hba.conf\nevaluated top to bottom\nFIRST MATCH WINS"] --> R1["rule matched, auth ok\nconnection established"]
    H --> R2["no rule matched\nFATAL: no pg_hba.conf entry"]
    H --> R3["rule matched, method reject\nFATAL: pg_hba.conf rejects connection"]
    H --> R4["rule matched, credential bad\nFATAL: password authentication failed"]

Requirements

A PostgreSQL 18 cluster you can restart, and whose authentication you can break. Task 8 leaves it refusing to start.
Superuser access and shell access as the data directory owner.
A network address to connect from. The lab as executed uses the container’s own address on the Docker network, 172.26.0.4 in a 172.26.0.0/16 subnet. Substitute your own throughout; the addresses are not significant, only that they are not 127.0.0.1.
The lab continues on the rbpg-lab01 container from Labs 1 and 3.

Scenario

An application team reports that admin_user cannot connect. Someone adds a permissive rule for admin_user at the bottom of pg_hba.conf, reloads, and it still does not work. They add it again, more permissively. Still nothing.

The rule is correct. It is just never evaluated. You are going to build that situation deliberately, and learn the one-line diagnostic that would have ended it in thirty seconds.

Tasks

Task 1 — Read the rules the way the server sees them

LAB="$HOME/rbpg-lab-04"
mkdir -p "$LAB"

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT line_number, type, database, user_name, address, auth_method
  FROM pg_hba_file_rules ORDER BY rule_number;" | tee "$LAB/hba-rules.txt"

Read-only / Safethe rules the Debian package shipped— Captured from the lab run of 2026-08-28. Comments and blank lines are gone; only real rules appear.

$ psql -X -c "SELECT line_number, type, database, user_name, address, auth_method FROM pg_hba_file_rules ORDER BY rule_number;"

 line_number | type  |   database    | user_name  |  address  |  auth_method  
-------------+-------+---------------+------------+-----------+---------------
       118 | local | {all}         | {postgres} |           | peer
       123 | local | {all}         | {all}      |           | peer
       125 | host  | {all}         | {all}      | 127.0.0.1 | scram-sha-256
       127 | host  | {all}         | {all}      | ::1       | scram-sha-256
       130 | local | {replication} | {all}      |           | peer
       131 | host  | {replication} | {all}      | 127.0.0.1 | scram-sha-256
       132 | host  | {replication} | {all}      | ::1       | scram-sha-256
(7 rows)

Seven rules in a 5,934-byte file. Everything else is comment. Reading pg_hba_file_rules instead of the file removes the comments, resolves the include directives, and — the part that matters — shows you the rules in evaluation order, which is what rule_number is.

Note what is not there: no rule for any address outside the loopback addresses. A default Debian cluster accepts nothing from the network.

Task 2 — Create the roles and open the listening socket

docker exec -i -u postgres rbpg-lab01 psql -X <<'SQL'
CREATE ROLE app_user   LOGIN PASSWORD 'lab04-app-not-a-real-secret';
CREATE ROLE admin_user LOGIN PASSWORD 'lab04-admin-not-a-real-secret';
CREATE ROLE batch_user LOGIN PASSWORD 'lab04-batch-not-a-real-secret';
CREATE DATABASE appdb;
CREATE DATABASE reportdb;
ALTER SYSTEM SET listen_addresses = '*';
SQL

docker exec rbpg-lab01 pg_ctlcluster 18 main restart

docker exec -u postgres rbpg-lab01 psql -X -c "SHOW listen_addresses;"
docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' rbpg-lab01

listen_addresses is a postmaster parameter — Lab 3 covered why that means a restart and not a reload. Record the address the last command prints; the rest of the lab uses 172.26.0.4.

Task 3 — Failure one: no rule matched

docker exec -u postgres -e PGPASSWORD=lab04-app-not-a-real-secret rbpg-lab01 \
  psql -X -h 172.26.0.4 -U app_user -d appdb -c "SELECT 1;" \
  2>&1 | tee "$LAB/auth-outcomes.txt"

docker exec rbpg-lab01 tail -2 /var/log/postgresql/postgresql-18-main.log \
  | tee "$LAB/matched-rules.txt"

Read-only / Safethe first of three distinct failure messages— Captured from the lab run of 2026-08-28. One connection attempt, two FATAL lines.

$ psql -h 172.26.0.4 -U app_user -d appdb -c 'SELECT 1;'

psql: error: connection to server at "172.26.0.4", port 5432 failed: FATAL:  no pg_hba.conf entry for host "172.26.0.4", user "app_user", database "appdb", SSL encryption
connection to server at "172.26.0.4", port 5432 failed: FATAL:  no pg_hba.conf entry for host "172.26.0.4", user "app_user", database "appdb", no encryption

“no pg_hba.conf entry” means the server walked the entire rule list and nothing matched the combination of connection type, database, role and address. The fix is to add a rule. Nothing is wrong with the credential; the server never got as far as asking for one.

Look closely at the output: one connection attempt produced two FATAL lines, one saying SSL encryption and one saying no encryption. That is libpq’s default negotiation. It tries SSL first, is refused, retries without SSL, is refused again, and reports both.

This doubling matters when you are reading logs. A brute-force attempt against a server with no matching rule produces twice the log lines you might expect, and the SSL encryption / no encryption suffix tells you which half of the pair you are looking at — not that the client did anything different.

Task 4 — Write three rules and watch them being chosen

docker exec rbpg-lab01 cp /etc/postgresql/18/main/pg_hba.conf \
                          /etc/postgresql/18/main/pg_hba.conf.lab04-backup

docker exec rbpg-lab01 bash -c 'cat >> /etc/postgresql/18/main/pg_hba.conf <<EOF

# --- lab04 rules, evaluated top to bottom, first match wins ---
host    appdb           app_user        172.26.0.0/16           scram-sha-256
host    all             admin_user      172.26.0.0/16           reject
host    all             all             172.26.0.0/16           scram-sha-256
EOF'

docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_reload_conf();"

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT rule_number, line_number, type, database, user_name, address, netmask, auth_method
  FROM pg_hba_file_rules WHERE line_number > 132 ORDER BY rule_number;" \
  | tee -a "$LAB/hba-rules.txt"

Configuration changethree new rules, numbered 8 to 10 in evaluation order— Captured from the lab run of 2026-08-28. Note the CIDR has been expanded into address and netmask.

$ psql -X -c "SELECT rule_number, line_number, type, database, user_name, address, netmask, auth_method FROM pg_hba_file_rules WHERE line_number > 132 ORDER BY rule_number;"

 rule_number | line_number | type | database |  user_name   |  address   |   netmask   |  auth_method  
-------------+-------------+------+----------+--------------+------------+-------------+---------------
         8 |         135 | host | {appdb}  | {app_user}   | 172.26.0.0 | 255.255.0.0 | scram-sha-256
         9 |         136 | host | {all}    | {admin_user} | 172.26.0.0 | 255.255.0.0 | reject
        10 |         137 | host | {all}    | {all}        | 172.26.0.0 | 255.255.0.0 | scram-sha-256
(3 rows)

Now exercise all three:

PWAPP="lab04-app-not-a-real-secret"
PWADM="lab04-admin-not-a-real-secret"

# a) app_user to appdb   -> rule 8 matches on database and role
docker exec -u postgres -e PGPASSWORD="$PWAPP" rbpg-lab01 \
  psql -X -h 172.26.0.4 -U app_user -d appdb \
  -c "SELECT current_user, current_database();"

# b) app_user to reportdb -> rule 8 fails on database, falls to rule 10
docker exec -u postgres -e PGPASSWORD="$PWAPP" rbpg-lab01 \
  psql -X -h 172.26.0.4 -U app_user -d reportdb \
  -c "SELECT current_user, current_database();"

# c) admin_user anywhere  -> rule 9 rejects before rule 10 can allow
docker exec -u postgres -e PGPASSWORD="$PWADM" rbpg-lab01 \
  psql -X -h 172.26.0.4 -U admin_user -d appdb -c "SELECT 1;"

Read-only / Safetwo allowed, one rejected, all by first match— Captured from the lab run of 2026-08-28.

$ three connection attempts differing only in role and database

a)
current_user | current_database 
--------------+------------------
app_user     | appdb
(1 row)

b)
current_user | current_database 
--------------+------------------
app_user     | reportdb
(1 row)

c)
psql: error: connection to server at "172.26.0.4", port 5432 failed: FATAL:  pg_hba.conf rejects connection for host "172.26.0.4", user "admin_user", database "appdb", SSL encryption
connection to server at "172.26.0.4", port 5432 failed: FATAL:  pg_hba.conf rejects connection for host "172.26.0.4", user "admin_user", database "appdb", no encryption

“pg_hba.conf rejects connection” is the second failure message, and it is a different situation from Task 3. A rule did match. Its method was reject, so the server stopped there and refused. The fix is to change or reorder a rule, not to add one.

Case (b) is the mechanic worth internalising. Rule 8 named appdb, so for a connection to reportdb it did not match at all, and evaluation continued to rule 10 which did. A rule that fails to match is skipped silently. A rule that matches ends the search — whatever its verdict.

Task 5 — Failure two: the fix that changes nothing

Put yourself in the position of the engineer from the scenario. Add a permissive rule for admin_user at the bottom of the file:

docker exec rbpg-lab01 bash -c 'cat >> /etc/postgresql/18/main/pg_hba.conf <<EOF
# the "fix" an operator appends when admin_user cannot connect
host    all             admin_user      172.26.0.0/16           scram-sha-256
EOF'

docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_reload_conf();"

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT rule_number, line_number, user_name, auth_method
  FROM pg_hba_file_rules WHERE line_number > 132 ORDER BY rule_number;"

docker exec -u postgres -e PGPASSWORD="$PWADM" rbpg-lab01 \
  psql -X -h 172.26.0.4 -U admin_user -d appdb -c "SELECT 1;"

Read-only / Safethe rule is present, loaded, correct — and useless— Captured from the lab run of 2026-08-28. Rule 11 exists. Rule 9 still decides.

$ reload, list the rules, then retry the admin_user connection

 rule_number | line_number |  user_name   |  auth_method  
-------------+-------------+--------------+---------------
         8 |         135 | {app_user}   | scram-sha-256
         9 |         136 | {admin_user} | reject
        10 |         137 | {all}        | scram-sha-256
        11 |         139 | {admin_user} | scram-sha-256

psql: error: connection to server at "172.26.0.4", port 5432 failed: FATAL:  pg_hba.conf rejects connection for host "172.26.0.4", user "admin_user", database "appdb", SSL encryption

Rule 11 is exactly what was wanted. It is loaded. It is syntactically perfect. It will never be consulted, because rule 9 matches the same connection and rule 9 comes first.

Task 6 — The diagnostic: ask the server which rule matched

Everything above is diagnosable in one step. Trigger a failed authentication and read the DETAIL line the server logs:

docker exec -u postgres -e PGPASSWORD=wrong-password rbpg-lab01 \
  psql -X -h 172.26.0.4 -U app_user -d appdb -c "SELECT 1;"

docker exec rbpg-lab01 tail -1 /var/log/postgresql/postgresql-18-main.log \
  | tee -a "$LAB/matched-rules.txt"

Read-only / Safethe third failure message, and the line that answers everything— Captured from the lab run of 2026-08-28. The DETAIL quotes the matching rule verbatim.

$ a connection with a bad password, then the last log line

psql: error: connection to server at "172.26.0.4", port 5432 failed: FATAL:  password authentication failed for user "app_user"

2026-08-28 00:22:08.172 UTC [8310] app_user@appdb DETAIL:  Connection matched file "/etc/postgresql/18/main/pg_hba.conf" line 135: "host    appdb           app_user        172.26.0.0/16           scram-sha-256"

“password authentication failed” is the third message: a rule matched, the method asked for a credential, and the credential was wrong. This is the only one of the three that indicates a problem with the user’s password rather than with your configuration.

And the DETAIL line is the whole diagnosis: file, line number, and the text of the rule. When somebody cannot connect, this line tells you whether they hit the rule you think they hit. In Task 5 it would have said line 136 and the investigation would have been over.

Task 7 — Failure three: a typo, and how the two files differ

docker exec rbpg-lab01 bash -c 'cat >> /etc/postgresql/18/main/pg_hba.conf <<EOF
host    all             all             172.26.0.0/16           scram-sha-255
EOF'

docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_reload_conf();"

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT rule_number, line_number, auth_method, error
  FROM pg_hba_file_rules WHERE line_number > 132 ORDER BY line_number;" \
  | tee "$LAB/hba-failure.txt"

docker exec rbpg-lab01 tail -3 /var/log/postgresql/postgresql-18-main.log

Configuration changeone bad line, and the whole file is refused— Captured from the lab run of 2026-08-28. Note the final log line.

$ reload after adding an invalid auth method, then inspect pg_hba_file_rules and the log

 rule_number | line_number |  auth_method  |                     error                     
-------------+-------------+---------------+-----------------------------------------------
         8 |         135 | scram-sha-256 | 
         9 |         136 | reject        | 
        10 |         137 | scram-sha-256 | 
        11 |         139 | scram-sha-256 | 
           |         140 |               | invalid authentication method "scram-sha-255"
(5 rows)

2026-08-28 00:22:20.754 UTC [8189] LOG:  invalid authentication method "scram-sha-255"
2026-08-28 00:22:20.754 UTC [8189] CONTEXT:  line 140 of configuration file "/etc/postgresql/18/main/pg_hba.conf"
2026-08-28 00:22:20.754 UTC [8189] LOG:  /etc/postgresql/18/main/pg_hba.conf was not reloaded

The bad line has a NULL rule_number — it is not a rule, it is an error — and the log says “was not reloaded”.

That phrasing is the important contrast with Lab 3. When postgresql.conf contains a bad value, the reload applies every good line and skips the bad one: “contains errors; unaffected changes were applied”. When pg_hba.conf contains a bad line, the entire file is discarded and the server keeps the rule set it already had.

Confirm the server is still serving the old, good rules:

docker exec -u postgres -e PGPASSWORD="$PWAPP" rbpg-lab01 \
  psql -X -h 172.26.0.4 -U app_user -d appdb -c "SELECT 'still working' AS status;"

Read-only / Safethe previous rule set is still in force— Captured from the lab run of 2026-08-28.

$ an app_user connection after the failed reload

    status     
---------------
still working
(1 row)

This all-or-nothing behaviour is the safer design for an authentication file. A partially applied pg_hba.conf could silently drop a reject rule and leave a wider rule below it in force, which is precisely the failure mode you do not want in the file that decides access.

Task 8 — And the same asymmetry: fatal at startup

docker exec rbpg-lab01 pg_ctlcluster 18 main restart
docker exec rbpg-lab01 pg_lsclusters | head -2
docker exec rbpg-lab01 tail -4 /var/log/postgresql/postgresql-18-main.log \
  | tee -a "$LAB/hba-failure.txt"

Service impact possiblethe server binds its sockets, reads pg_hba, and gives up— Captured from the lab run of 2026-08-28. The cluster is left down.

$ docker exec rbpg-lab01 pg_ctlcluster 18 main restart && docker exec rbpg-lab01 pg_lsclusters

Error: /usr/lib/postgresql/18/bin/pg_ctl ... exited with status 1: 
2026-08-28 00:22:32.197 UTC [8362] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2026-08-28 00:22:32.201 UTC [8362] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2026-08-28 00:22:32.203 UTC [8362] LOG:  invalid authentication method "scram-sha-255"
2026-08-28 00:22:32.203 UTC [8362] CONTEXT:  line 140 of configuration file "/etc/postgresql/18/main/pg_hba.conf"
2026-08-28 00:22:32.203 UTC [8362] FATAL:  could not load /etc/postgresql/18/main/pg_hba.conf
2026-08-28 00:22:32.204 UTC [8362] LOG:  database system is shut down

Ver Cluster   Port Status Owner    Data directory                   Log file
18  main      5432 down   postgres /var/lib/postgresql/18/main      /var/log/postgresql/postgresql-18-main.log

Same shape as Lab 3, different message: FATAL: could not load /etc/postgresql/18/main/pg_hba.conf. Note that the server got further this time — it bound both TCP sockets and the Unix socket before reading the HBA file, then shut all of that down again.

Fix it and confirm the pre-flight query is clean:

docker exec rbpg-lab01 sed -i '/scram-sha-255/d' /etc/postgresql/18/main/pg_hba.conf
docker exec rbpg-lab01 pg_ctlcluster 18 main start

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT line_number, error FROM pg_hba_file_rules WHERE error IS NOT NULL;"

Read-only / Safethe pre-flight check for authentication rules— Captured from the lab run of 2026-08-28. Empty means the file will load at the next restart.

$ psql -X -c "SELECT line_number, error FROM pg_hba_file_rules WHERE error IS NOT NULL;"

 line_number | error 
-------------+-------
(0 rows)

Lab 3 gave you pg_file_settings WHERE error IS NOT NULL for parameters. This is its counterpart for authentication, and there is a third for user maps: pg_ident_file_mappings WHERE error IS NOT NULL. All three should be empty before any planned restart.

Task 9 — Map an operating system user to a different role

peer authenticates by asking the kernel which operating system user opened the socket, and then requires that name to equal the requested role. A user map lifts that restriction in a controlled way.

docker exec rbpg-lab01 useradd -m deploy

docker exec rbpg-lab01 bash -c 'cat >> /etc/postgresql/18/main/pg_ident.conf <<EOF
# MAPNAME   SYSTEM-USERNAME   PG-USERNAME
lab04map    deploy            app_user
EOF'

Now — knowing what Task 5 taught — think about where the HBA rule has to go. The package default at line 123 is local all all peer, which matches every local connection from every user to every database.

Insert the mapped rule above it:

docker exec rbpg-lab01 sed -i \
  '123i local   appdb           app_user                                peer map=lab04map' \
  /etc/postgresql/18/main/pg_hba.conf

docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_reload_conf();"

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT rule_number, line_number, type, database, user_name, auth_method, options
  FROM pg_hba_file_rules WHERE line_number BETWEEN 120 AND 126 ORDER BY line_number;"

Configuration changethe mapped rule, placed before the catch-all it would otherwise lose to— Captured from the lab run of 2026-08-28. The map appears in the options column.

$ psql -X -c "SELECT rule_number, line_number, type, database, user_name, auth_method, options FROM pg_hba_file_rules WHERE line_number BETWEEN 120 AND 126 ORDER BY line_number;"

 rule_number | line_number | type  | database | user_name  |  auth_method  |    options     
-------------+-------------+-------+----------+------------+---------------+----------------
         2 |         123 | local | {appdb}  | {app_user} | peer          | {map=lab04map}
         3 |         124 | local | {all}    | {all}      | peer          | 
         4 |         126 | host  | {all}    | {all}      | scram-sha-256 | 
(3 rows)

docker exec rbpg-lab01 su - deploy -c \
  "psql -X -U app_user -d appdb -c \"SELECT current_user, session_user;\""

docker exec rbpg-lab01 su - deploy -c \
  "psql -X -U batch_user -d appdb -c \"SELECT 1;\""

Read-only / Safethe map allows exactly one substitution and no others— Captured from the lab run of 2026-08-28.

$ OS user deploy connecting as app_user, then as batch_user

 current_user | session_user 
--------------+--------------
app_user     | app_user
(1 row)

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "batch_user"

The operating system user deploy connected as the database role app_user, with no password anywhere, and cannot connect as any other role. That is the useful shape for a deployment account: the identity is proven by the kernel, the mapping is explicit, and there is no credential to leak.

What the map is actually compared against

peer obtains the client’s operating system user name from the kernel via the socket — the client cannot choose or forge it, which is what makes peer strong on a local socket and unavailable over TCP.

With map=lab04map, the server then looks for an entry in pg_ident.conf whose map name matches, whose system user name matches the name the kernel supplied, and whose PostgreSQL user name matches the role being requested. All three must line up. There is no implicit identity entry, which is why deploy connecting as deploy would also need its own line if you wanted that to work.

The same mapping machinery serves gss, sspi, cert and ldap authentication, where the “system user name” is whatever principal, certificate common name or directory attribute that method produced.

Validation

test -s "$LAB/hba-rules.txt"     && echo "OK hba-rules"
test -s "$LAB/auth-outcomes.txt" && echo "OK auth-outcomes"
test -s "$LAB/matched-rules.txt" && echo "OK matched-rules"
test -s "$LAB/hba-failure.txt"   && echo "OK hba-failure"

grep -q "no pg_hba.conf entry"          "$LAB/auth-outcomes.txt" && echo "OK failure 1 captured"
grep -q "Connection matched file"       "$LAB/matched-rules.txt" && echo "OK DETAIL line captured"
grep -q "invalid authentication method" "$LAB/hba-failure.txt"   && echo "OK bad rule captured"

# All three pre-flight checks must be clean.
docker exec -i -u postgres rbpg-lab01 psql -X <<'SQL'
SELECT count(*) AS bad_settings FROM pg_file_settings      WHERE error IS NOT NULL;
SELECT count(*) AS bad_hba      FROM pg_hba_file_rules     WHERE error IS NOT NULL;
SELECT count(*) AS bad_ident    FROM pg_ident_file_mappings WHERE error IS NOT NULL;
SQL

Questions to answer without looking anything up:

A connection fails with no pg_hba.conf entry. Is that a credential problem? What about pg_hba.conf rejects connection?
Why did one connection attempt in Task 3 produce two FATAL log lines?
You add a correct rule at the bottom of pg_hba.conf, reload, and the connection still fails. What is the one command that tells you why?
A bad line in postgresql.conf and a bad line in pg_hba.conf are both reloaded. What is different about the outcome?
Where must a peer ... map= rule go relative to the packaged local all all peer line, and why?

Expected Outcome

You have produced all three authentication failure messages deliberately, distinguished them, and diagnosed a rule ordering problem from the server log rather than from the file.

The three things to carry away:

-- The rules, in the order the server evaluates them.
SELECT rule_number, type, database, user_name, address, auth_method, error
FROM pg_hba_file_rules ORDER BY rule_number;

-- Will the server start? (run all three before any restart)
SELECT * FROM pg_file_settings       WHERE error IS NOT NULL;
SELECT * FROM pg_hba_file_rules      WHERE error IS NOT NULL;
SELECT * FROM pg_ident_file_mappings WHERE error IS NOT NULL;

And from the log, for any refused connection: DETAIL: Connection matched file "..." line N: "...".

Troubleshooting

no pg_hba.conf entry for host .... No rule matched. Check the connection type first: a hostssl rule does not match a client connecting without TLS, and a host rule matches either. The message’s trailing clause — no encryption or SSL on — tells you which the server saw.

You added a correct rule and the connection still fails. An earlier rule matched first. pg_hba.conf is first-match-wins, not best-match-wins. SELECT rule_number, type, database, user_name, address, auth_method FROM pg_hba_file_rules ORDER BY rule_number; shows the evaluation order, and the log’s DETAIL: Connection matched file "..." line N names the line that actually decided.

No DETAIL: Connection matched line appears in the log. That detail is emitted for a rule that matched and refused. If no rule matched there is nothing to name, which is itself the diagnosis.

password authentication failed for user .... A rule matched, asked for a credential, and rejected it. This is a different problem from the two above and no amount of editing pg_hba.conf will fix it.

The server will not start after Task 7, which is the point. A syntactically invalid pg_hba.conf is FATAL at startup, exactly as an invalid postgresql.conf is — but the reload behaves differently, and measured on 18.6 the server says so outright:

LOG:  invalid authentication method "nosuchmethod"
CONTEXT:  line 129 of configuration file ".../pg_hba.conf"
LOG:  .../pg_hba.conf was not reloaded

The whole file is rejected and the previously loaded rules stay in force. Tested by putting local all all reject at the top of a file that also contained the bad line: after the reload, a local connection still succeeded, because none of the file was applied.

pg_hba_file_rules after a failed reload shows the file, not what is in force. In the same capture it listed all seven valid rules plus an eighth row with a null rule_number carrying invalid authentication method "nosuchmethod". Read the error column first: if any row has one, the rules you are looking at are not the rules the server is using.

A peer ... map= rule has no effect. It is below the packaged local all all peer line, which matched first. Rules that narrow behaviour must go above the broad rule they are narrowing, and pg_ident_file_mappings will show whether the map itself parsed.

Cleanup

docker exec rbpg-lab01 cp /etc/postgresql/18/main/pg_hba.conf.lab04-backup \
                          /etc/postgresql/18/main/pg_hba.conf
docker exec rbpg-lab01 sed -i '/lab04map/d' /etc/postgresql/18/main/pg_ident.conf
docker exec rbpg-lab01 userdel -r deploy

docker exec -i -u postgres rbpg-lab01 psql -X <<'SQL'
ALTER SYSTEM RESET listen_addresses;
DROP DATABASE IF EXISTS appdb;
DROP DATABASE IF EXISTS reportdb;
DROP ROLE IF EXISTS app_user;
DROP ROLE IF EXISTS admin_user;
DROP ROLE IF EXISTS batch_user;
SQL

docker exec rbpg-lab01 pg_ctlcluster 18 main restart

# Back to the seven rules the package shipped.
docker exec -u postgres rbpg-lab01 psql -X -c \
  "SELECT count(*) AS rules FROM pg_hba_file_rules;"
docker exec -u postgres rbpg-lab01 psql -X -c "SHOW listen_addresses;"

The rule count should be 7 and listen_addresses back to localhost.

Production notes

Never test authentication over loopback on a host whose pg_hba.conf grants trust there — and check, because the official postgres:18 image ships exactly that rule. A credential test that has only ever run over 127.0.0.1 on that image has proved nothing at all.
Run all three pre-flight checks before any restart, not one: pg_file_settings, pg_hba_file_rules and pg_ident_file_mappings each report errors the others cannot see.
pg_hba_file_rules belongs in change review. A proposed rule change can be reasoned about from the rendered table in evaluation order, which is far more reliable than reading a diff of the file.
Adding rules at the bottom of the file is the habit that produces the “correct rule, refused connection” incident. Where an estate manages this file with a template, order the rules deliberately in the template rather than appending.

What You Learned

pg_hba.conf is first-match-wins. A correct rule below a broader rule never runs.
Three refusal messages, three different problems. no pg_hba.conf entry means nothing matched; pg_hba.conf rejects connection means a rule matched and said no; password authentication failed means a rule matched and the credential was wrong.
The server names the deciding line in the log’s DETAIL: Connection matched file "..." line N, which ends the argument about which rule applied.
pg_hba_file_rules renders the file in evaluation order, which is the order that matters and not the order it is convenient to read.
A broken pg_hba.conf is fatal at startup and, on reload, leaves the previous rules in force — so a reload that “worked” may have applied nothing.
hostssl and host match different clients, and the trailing clause of the refusal message tells you which the server saw.

Objectives

Prerequisites