PostgreSQL "FATAL: password authentication failed for user": find the real reason

1. Read the server log

On Debian and Ubuntu, the file name has the version and cluster name: /var/log/postgresql/postgresql-<version>-main.log. On RHEL-family systems, check sudo journalctl -u postgresql or the log/ folder inside the data directory (/var/lib/pgsql/data/log/ for the distribution’s package). Every failed login leaves two lines:

FATAL:  password authentication failed for user "app"
DETAIL:  Connection matched file "/etc/postgresql/16/main/pg_hba.conf" line 125: "host    all    all    127.0.0.1/32    scram-sha-256"

The DETAIL line is the answer. When it only names the pg_hba.conf line, as here, the password is wrong: with scram-sha-256, the default since PostgreSQL 14, the server gives no other reason. Any other cause adds a line before it. Older versions (before 16) write Connection matched pg_hba.conf line 125 without the file name.

2. “Peer authentication failed”

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

This is not about a password at all. Without -h, psql connects through a local Unix socket, and the default pg_hba.conf uses peer authentication there: the Linux user name must be the same as the database user name. You are guest on Linux, you asked for app → refused. That’s why sudo -u postgres psql works, and psql -U postgres doesn’t.

Two ways out:

• Connect over TCP, which uses passwords: psql -h localhost -U app -d shop. Applications usually connect this way anyway.
• Or change the local line in pg_hba.conf from peer to scram-sha-256 (see below), if you really need passwords on the socket.

3. Set a new password

As the postgres superuser:

sudo -u postgres psql

\password app

\password asks for the new password twice and sends it already hashed, so it doesn’t end up in your shell history or in the server log. ALTER ROLE app PASSWORD '…'; works too, but the password then appears in plain text in ~/.psql_history.

If you need a new value, the Password Generator has a shell-safe option, so it won’t need encoding in a connection URL.

Then test exactly the way the application connects — same host, same user, same database: psql -h localhost -U app -d shop.

If the password is right in psql but the app still fails, check how the app passes it:

• In a connection URL, special characters must be URL-encoded: @ becomes %40, : becomes %3A, / becomes %2F — the URL encoder does it for you.
• PGPASSWORD in the environment or a ~/.pgpass file can send an old password without you noticing.

4. MD5 vs. SCRAM

Since PostgreSQL 14, passwords are stored as SCRAM-SHA-256 by default, and new pg_hba.conf files ask for scram-sha-256. A role whose password was set long ago (or copied from an older server) may still have an MD5 hash, which a scram-sha-256 line can’t check:

SELECT rolname, left(rolpassword, 14) FROM pg_authid WHERE rolname = 'app';
-- md5…  = old MD5 hash;  SCRAM-SHA-256$… = new format

Set the password again (section 3). With password_encryption = 'scram-sha-256' (the default), it’s stored in the new format. Clients need libpq 10 or newer for SCRAM — anything from the last several years is fine.

5. Check pg_hba.conf

PostgreSQL reads pg_hba.conf from the top and uses the first line that matches the connection type, database, user and address. A broad line near the top can win over the one you just added at the bottom. Find the file, and see how PostgreSQL itself reads it:

SHOW hba_file;
SELECT line_number, type, database, user_name, address, auth_method, error FROM pg_hba_file_rules;

The error column shows lines PostgreSQL couldn’t parse. After editing the file, reload — no restart needed:

SELECT pg_reload_conf();

Connection types: local is the Unix socket (psql without -h), host is TCP (-h localhost, -h 10.0.0.5, every app over the network). A remote app needs a host line for its address and listen_addresses in postgresql.conf that isn’t only localhost — otherwise you get “Connection refused” instead.

6. Docker: the password only works on the first start

With the official postgres image, POSTGRES_PASSWORD (and POSTGRES_USER, POSTGRES_DB) are used only when the data directory is empty, the first time the container starts. If you change the password in docker-compose.yml later, the database in the volume still has the old one — and your app now sends the new one.

The database container’s log shows it, with the same lines as above:

docker compose logs db | grep -A1 FATAL

Either set the new password inside the container. Inside it, psql connects through the socket, which the image trusts, so it doesn’t ask for the old one. Use the POSTGRES_USER from your file (postgres if it doesn’t set one), or you’ll get role "postgres" does not exist:

docker compose exec db psql -U app -d shop -c "ALTER USER app PASSWORD 'new-secret'"

-c '\password app' does the same and asks for the password instead of putting it in your shell history. Then start the app again with docker compose up -d.

Or, if the data doesn’t matter (a fresh dev setup), delete the volume so the image initializes again: docker compose down -v — this deletes all the data in it. Both ways can be tried in the terminal, which starts with the password just changed in compose.yaml. The same thing happens with MySQL and MariaDB images — see MySQL ERROR 1045.