Incident Response

PostgreSQL "password authentication failed for user": How to Find the Real Cause

The client always gets the same one-line error, whether the password is wrong, the role does not exist, the password has expired or the hash is in the wrong format. The real reason is in the server log, two lines below.

· 13 min read · AG Data Team

"password authentication failed for user" means the client reached PostgreSQL, a pg_hba.conf rule matched and asked for a password, and the check failed. The documentation describes it exactly this way: the server is willing to talk to you, but not until you pass the authentication method from pg_hba.conf (authentication problems). The message is deliberately vague. The reason is in the server log.

psql: error: connection to server at "db1" (10.0.0.5), port 5432 failed:
FATAL:  password authentication failed for user "app"

Read the server log first

The documentation tip is short and worth following: the server log may contain more information about an authentication failure than is reported to the client. The client never sees why; the log has a DETAIL line, and in current versions also the pg_hba.conf line that matched:

FATAL:  password authentication failed for user "app"
DETAIL:  Password does not match for user "app".
        Connection matched file "/etc/postgresql/18/main/pg_hba.conf" line 98: "host all all 10.0.0.0/8 scram-sha-256"

The detail texts come from the PostgreSQL source (crypt.c, auth.c), and each one points to a different fix:

The Connection matched line matters too. pg_hba.conf uses the first record that matches connection type, address, database and user, and there is no fall-through: if that record's authentication fails, later records are not tried (pg_hba.conf). If the matched line is not the one you expected, the fix is the order of the rules, not the password. A missing rule gives a different error, covered in no pg_hba.conf entry for host.

Check the role

SELECT rolname, rolcanlogin, rolvaliduntil,
       left(rolpassword, 14) AS hash_type   -- needs superuser
FROM pg_authid
WHERE rolname = 'app';

-- which pg_hba.conf line will match, and did the file load cleanly?
SELECT line_number, type, database, user_name, address, auth_method, error
FROM pg_hba_file_rules;

rolcanlogin must be true, rolvaliduntil must be empty or in the future, and the start of rolpassword shows the hash type: SCRAM-SHA-256$ or md5. pg_hba_file_rules shows the rules as loaded and fills the error column for lines with problems; remember that edits to pg_hba.conf only take effect after a reload (pg_ctl reload, SELECT pg_reload_conf() or SIGHUP), except on Windows (pg_hba.conf).

To set a new password, prefer \password in psql over typing ALTER ROLE ... PASSWORD '...': psql prompts for the password, encrypts it and sends it as an ALTER ROLE command, so the new password does not appear in cleartext in the command history or the server log (psql).

-- in psql: prompts for the password and sends it encrypted,
-- so it does not appear in cleartext in history or server logs
\password app

-- expired password
ALTER ROLE app VALID UNTIL 'infinity';

-- role exists but cannot log in
ALTER ROLE app LOGIN;

MD5 and SCRAM

Since PostgreSQL 14 the default password_encryption is scram-sha-256 (connection settings). The interaction with pg_hba.conf is asymmetric:

The documented migration: make sure all client libraries support SCRAM, set password_encryption = 'scram-sha-256', have every user set a new password, then change pg_hba.conf to scram-sha-256. MD5 is deprecated; since PostgreSQL 18 CREATE ROLE and ALTER ROLE warn when an MD5 password is set, controlled by md5_password_warnings (PostgreSQL 18 release notes).

Is the client sending the password you think?

In our experience, when the log says "Password does not match", the password is usually fine and the client is sending something else:

  1. .pgpass. The format is hostname:port:database:username:password, the first matching line wins, and : or \ inside a value must be escaped with \. On Unix the file is ignored if group or others have any access (.pgpass). A stale wildcard line above the right one is a common surprise.
  2. Connection URIs. Characters with special meaning in a URI (@, :, /, % and others) must be percent-encoded (libpq connection strings). A password with @ in a postgresql:// URL is a frequent cause of "it works in psql but not in the app".
  3. Environment and secrets. A trailing newline in a secret file, an old PGPASSWORD in a shell profile, or a config map that was not reloaded.
  4. Docker. The official image applies POSTGRES_PASSWORD only when the container starts with an empty data directory; an existing database is left untouched (postgres Docker image). Changing the variable on a container with a persistent volume does not change the password.

Fix it on the right side. If the log says the role does not exist or the password expired, changing the application config does nothing. If it says the password does not match and you just reset it, look at what the client sends before resetting it again.

PgBouncer in the middle

Behind PgBouncer there are two logins: client to PgBouncer, and PgBouncer to PostgreSQL. The error can come from either, so check both logs. PgBouncer reads users from auth_file (plain text, MD5 or SCRAM secrets) or looks them up with auth_query. It can log in to PostgreSQL with a stored SCRAM secret only if the client also uses SCRAM, the database entry sets no fixed user, and the secret is identical to the server's, with the same salt and iterations, not just the same password (PgBouncer configuration). After a password change on the server, an auth_file with the old secret breaks every new server connection. Pool setup is covered in PgBouncer for PostgreSQL connection pooling.

What changes with many applications and big fleets

On a laptop, one wrong password is one error. On a fleet with dozens of services and hundreds of pods:

Key takeaways

PostgreSQL Errors Authentication Security
Work with us

Applications locked out of the database?

We find why logins fail, migrate MD5 to SCRAM without downtime, set up PgBouncer authentication and plan password rotation that does not take your applications down.

Get in touch More from the blog