The password was rotated three times and authentication kept failing, and every test from the database host succeeded

Reported symptoms

The nightly batch job fails at 01:00 with password authentication failed for user "app_batch". It has failed every night for four nights.

The password was rotated on the first night. And again on the second. And a third time on the third — each time in both the database and the secrets store.

After every rotation an engineer logged into the database host and confirmed the new password worked with psql. It did.

The application team confirms the value in the secrets store matches what the job reads at runtime. Other applications on the same database are unaffected. The role exists with the expected grants.

Nothing changed in the days before the first failure.

Evidence provided

$ grep -A2 'app_batch' /var/log/postgresql/postgresql-18-main.log | tail -3

2026-08-28 01:00:00.974 UTC [256] FATAL:  password authentication failed for user "app_exp"
2026-08-28 01:00:00.974 UTC [256] DETAIL:  User "app_exp" has an expired password.
Connection matched file "/var/lib/postgresql/18/docker/pg_hba.conf" line 128: "host all all all scram-sha-256"

That DETAIL line is present on all four nights, including the nights after each rotation.

$ psql -c "SELECT rolname, rolcanlogin, rolvaliduntil, rolvaliduntil < now() AS expired FROM pg_authid WHERE rolname='app_batch';"

  rolname  | rolcanlogin |     rolvaliduntil      | expired 
-----------+-------------+------------------------+---------
app_batch | t           | 2026-01-01 00:00:00+00 | t
(1 row)

And the reason every verification passed:

$ grep -vE '^\\s*#|^\\s*$' pg_hba.conf

local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
host    all             all             all                     scram-sha-256

Every verification was run on the database host against 127.0.0.1. The batch job connects from a separate host.

Work the evidence before reading on

1. The DETAIL line was there on night one. What would it have saved?
2. Three password rotations changed nothing. What does that tell you about the password?
3. Every manual test succeeded. Under what conditions would that test have failed?
4. Which pg_hba.conf line does the batch job match?

Root cause

The password was never wrong

app_batch had VALID UNTIL '2026-01-01', set eighteen months earlier when the role was created, and forgotten. On the night that date passed, the role stopped being able to authenticate.

Nobody read the server log for four days. The team worked from the only message they could see, and that message says password, so they rotated the password.

The verification could not fail

host all all 127.0.0.1/32 trust sits ahead of the scram-sha-256 rule. A loopback connection is accepted without any check at all.

That test would have passed with the old password, a wrong password, or no password. It proved nothing, and it did worse than nothing: it told the team the credential was good while the real path kept rejecting it.

Resolution

Read the server log. It names the fault and the pg_hba.conf line that matched:

FATAL:  password authentication failed for user "app_batch"
DETAIL:  User "app_batch" has an expired password.
        Connection matched file "/etc/postgresql/18/main/pg_hba.conf" line 128:
        "host all all all scram-sha-256"

Confirm from the catalog:

SELECT rolname, rolcanlogin, rolvaliduntil,
       rolvaliduntil < now() AS expired, rolconnlimit
FROM pg_authid WHERE rolname = 'app_batch';

Fix the expiry deliberately — a real future date somebody has agreed to renew, or no expiry at all if that is the honest policy:

ALTER ROLE app_batch VALID UNTIL '2027-09-01';
-- or
ALTER ROLE app_batch VALID UNTIL 'infinity';

Do not rotate the password again. It is already correct in both places; a fourth rotation is a fourth chance for them to diverge.

Repair the verification — test from a host that matches the same rule the application matches:

# from an application host, not from the database server
PGPASSWORD="$secret" psql -h db-primary-01.internal -U app_batch -d orders -c 'SELECT 1'

Then audit every other role before the next one fires:

SELECT rolname, rolvaliduntil
FROM pg_authid
WHERE rolcanlogin AND rolvaliduntil IS NOT NULL
ORDER BY rolvaliduntil;

Verification

The batch job authenticates and completes, over the real connection path.

A manual connection from an application host succeeds and the server log records it with no FATAL.

rolvaliduntil is in the future, or infinity.

A deliberately wrong password from an application host now fails, with DETAIL: Password does not match for user "app_batch" in the log. This is the step that proves the new verification can fail.

pg_hba_file_rules confirms which rule the application matches, and that no trust rule sits in front of it for any address a real client can reach:

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

 rule_number | type  |   database    | user_name |  address  |  auth_method  | error 
-------------+-------+---------------+-----------+-----------+---------------+-------
         1 | local | {all}         | {all}     |           | trust         | 
         2 | host  | {all}         | {all}     | 127.0.0.1 | trust         | 
         3 | host  | {all}         | {all}     | ::1       | trust         | 
         4 | local | {replication} | {all}     |           | trust         | 
         5 | host  | {replication} | {all}     | 127.0.0.1 | trust         | 
         6 | host  | {replication} | {all}     | ::1       | trust         | 
         7 | host  | {all}         | {all}     | 10.99.0.0 | scram-sha-256 | 
(7 rows)

Prevention

Alert on roles approaching rolvaliduntil. PostgreSQL will not warn you; this is a scheduled outage with no notice:

SELECT rolname, rolvaliduntil
FROM pg_authid
WHERE rolcanlogin
  AND rolvaliduntil IS NOT NULL
  AND rolvaliduntil < now() + interval '90 days'
ORDER BY rolvaliduntil;

Never verify a credential over a path that does not authenticate. Verification must traverse the same pg_hba.conf rule the application traverses.

Prove the negative case once, and keep it in the runbook.

Alert on FATAL: password authentication failed for service accounts. Four nights of those lines carried the answer and nobody was looking.

Read DETAIL, not just FATAL.

Decide VALID UNTIL policy once, per role class. Either service accounts carry expiries and something renews them, or they do not. An expiry set at creation and never revisited is a timer with nobody watching it.

Do not rotate a secret to diagnose an authentication failure. Rotation is a change; diagnosis is a read. Doing the change first destroys the evidence about whether the secret was ever the problem.