Scenario
A security team rotated all database passwords as part of a quarterly security review. The application uses environment variables for database credentials. The environment variables were updated in the deployment system, but some application servers weren’t restarted — they still have old connection pools using the old password. Errors: FATAL: password authentication failed for user "app_user". Meanwhile, pg_hba.conf uses scram-sha-256 authentication for all TCP connections.
How to Identify
Conditions:
FATAL: password authentication failed for user "app_user" in application or PostgreSQL logs- Password was recently changed but application(s) not restarted
pg_hba.conf uses scram-sha-256 or md5 for the connection type- Stale
.pgpass file or hardcoded credentials in application code
pg_authid stores password verifiers — mismatched after password change
Analysis Steps
-- Check the authentication method configured for the user
SELECT type, database, user_name, address, auth_method
FROM pg_hba_file_rules
WHERE 'app_user' = ANY(user_name) OR 'all' = ANY(user_name)
ORDER BY line_num;
-- auth_method = 'scram-sha-256' or 'md5' = password required
-- auth_method = 'trust' = no password needed (dangerous!)
-- auth_method = 'reject' = always rejected regardless of password
-- Check if the user exists and has a password set
SELECT rolname, rolcanlogin, rolpassword IS NOT NULL AS has_password
FROM pg_authid
WHERE rolname = 'app_user';
-- has_password=false = user has no password but pg_hba.conf requires one
-- Check when password was last changed (PostgreSQL doesn't track this natively)
-- Use PostgreSQL audit logging or application logs for timestamp
-- Check if password expiry is configured
SELECT rolname, rolvaliduntil
FROM pg_roles
WHERE rolname = 'app_user';
-- rolvaliduntil NOT NULL = password expires at that time
-- If rolvaliduntil < now() = password expired → authentication fails
-- Check for failed auth attempts in PostgreSQL log:
-- grep "password authentication failed\|authentication failed" $PGDATA/log/postgresql*.log | tail -20
Pitfalls
md5 authentication stores a static hash of the password. A password change updates pg_authid but old connections using cached md5 hashes may still work temporarily (they authenticated before the change). New connections fail.scram-sha-256 is more secure than md5 — but both require the client to send the correct password..pgpass files in the postgres user’s home directory (~/.pgpass) cache passwords and override the prompted value — stale .pgpass entries cause silent authentication failures.pg_hba.conf trust authentication bypasses all password checking. Never use trust for remote TCP connections.- Application connection pools cache credentials at startup — after a password rotation, the pool must be restarted or reconnected.
Resolution Approach
Verify the password is set correctly in pg_authid, verify the application has the new password, and check .pgpass files for stale entries. For credential rotations: use a two-phase approach (add new password alias, migrate, remove old) or use IAM authentication to avoid password management entirely.