DEV Community

Cover image for Fix: password authentication failed for user "postgres"
Mahdi BEN RHOUMA
Mahdi BEN RHOUMA

Posted on Originally published at iloveblogs.blog

Fix: password authentication failed for user "postgres"

#postgres #passwordauthentication #pghbaconf #scramsha256

You spin up PostgreSQL (locally, in Docker, or on a fresh VPS), type the password you set during install — and PostgreSQL says:

$ psql -U postgres -h localhost
Password for user postgres:
psql: error: connection to server on host "localhost" (127.0.0.1), port 5432 failed: FATAL:  password authentication failed for user "postgres"
Enter fullscreen mode Exit fullscreen mode

The Stack Overflow thread with ~1.6M views (Q19070362) lists three causes, in order of frequency:

  1. The role has no password set — common after a fresh install that only used peer auth.
  2. pg_hba.conf method mismatch — md5 vs scram-sha-256 disagreeing on which algorithm to validate the stored hash against.
  3. You typed the wrong password — common after restoring a dump, switching databases, or copy-pasting from a password manager that mangled a special character.

Here are the fixes for each.

Fix 1 — Set a password on the role

The most common case: the role exists but has no password. Peer auth let you in as the postgres OS user, but TCP connections require a password.

sudo -u postgres psql
Enter fullscreen mode Exit fullscreen mode 
ALTER USER postgres PASSWORD 'a-strong-dev-password';
Enter fullscreen mode Exit fullscreen mode

That works for the default postgres superuser. For your own role (say mahdi), connect as the role owner first:

sudo -u postgres psql -c "ALTER USER mahdi PASSWORD 'a-strong-dev-password';"
Enter fullscreen mode Exit fullscreen mode

ALTER USER ... PASSWORD rewrites the stored verifier using whichever algorithm password_encryption is set to in postgresql.conf (default scram-sha-256 on PG 14+). The password is hashed on the server — it never leaves the cluster in plaintext after the auth handshake.

Fix 2 — Switch pg_hba.conf from md5 to scram-sha-256

If you set a password and still get the error, the method declared in pg_hba.conf does not match the algorithm used to hash the stored verifier. Check the line that applies to your connection.

sudo -u postgres psql -c 'SHOW hba_file;'
Enter fullscreen mode Exit fullscreen mode

Open the file. The relevant line depends on whether you connect over TCP (host) or local socket (local):

# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Local socket:
local   all             all                                     scram-sha-256
Enter fullscreen mode Exit fullscreen mode

If you see md5 instead of scram-sha-256, change it. SCRAM-SHA-256 (RFC 5802) is the secure default on PostgreSQL 10+ and is what password_encryption = scram-sha-256 (the default) writes. md5 is legacy and vulnerable to replay of stolen password hashes.

Reload — pg_hba.conf is re-read on reload, not on every connection:

sudo systemctl reload postgresql
# or, if you start pg manually:
pg_ctl reload -D /var/lib/postgresql/data
Enter fullscreen mode Exit fullscreen mode


Setting the method to trust makes the error go away by disabling authentication entirely — any local user can connect as any role, including the superuser. Fine on an isolated throwaway VM, dangerous on any shared or developer machine. Use scram-sha-256 and a real password.

Fix 3 — Verify the password you typed

Less common but easy to miss:

  • Trailing whitespace. psql reads the password via /dev/tty; some terminals strip or add whitespace depending on how the password is pasted. Type it manually to be sure.
  • Special characters in shell. If you pass the password via PGPASSWORD=foo\!bar psql ..., \! is interpreted by bash. Use single quotes (PGPASSWORD='foo!bar') or a .pgpass file with mode 0600.
  • Wrong user. psql -U postgres with a connection string -h localhost connecting as mahdi — the role name in the error message is the one PostgreSQL tried to authenticate. Re-read the error message.

Use a .pgpass file (avoids shell quoting and password prompts) 

# ~/.pgpass (chmod 0600, owned by your user)
localhost:5432:*:postgres:your-strong-password
Enter fullscreen mode Exit fullscreen mode

Format: hostname:port:database:username:password. With mode 0600 and matching ownership, psql uses it automatically without prompting. Useful in scripts and CI.

Supabase local dev

The Supabase CLI (supabase start) runs a Postgres container with peer auth disabled — every connection is over TCP with md5 or scram-sha-256. Common gotchas:

  • The postgres role password is set by POSTGRES_PASSWORD in supabase/.env (default postgres). Connect to postgresql://postgres:postgres@localhost:54322/postgres.
  • If you changed POSTGRES_PASSWORD and the data directory was already initialized, the password baked into the catalog is still the old one. Recreate the volume: supabase stop && supabase start (deletes local data — back up first).
  • For Studio, the connection string is postgresql://postgres:postgres@db:5432/postgres from inside the docker network, or postgresql://postgres:postgres@localhost:54322/postgres from the host.

Docker (vanilla postgres image)

The official postgres image bakes POSTGRES_PASSWORD into the initialized data directory. You cannot ALTER USER it inside the container and have it persist across restarts — the data volume is the source of truth.

# Reset: drop the volume so it re-initializes with POSTGRES_PASSWORD
docker compose down
docker volume rm <project>_postgres-data
docker compose up -d postgres
Enter fullscreen mode Exit fullscreen mode

For a quick override without resetting:

docker exec -it <container> psql -U postgres -c "ALTER USER postgres PASSWORD 'new-pw';"
# ...but this is lost on the next `docker compose down` if the volume persists.
Enter fullscreen mode Exit fullscreen mode

For real work, set POSTGRES_PASSWORD and POSTGRES_USER in compose env and let the volume initialize once.

Common mistakes

  • Typed postgres as the user but the role is named differently — the error message names the role PostgreSQL tried, not the one you think you used. Check -U and the connection string.
  • Edited the wrong pg_hba.conf — there can be several (Debian vs Homebrew vs Docker). Confirm with SHOW hba_file; before editing.
  • Forgetting to reload — pg_hba.conf is re-read on pg_ctl reload, not on every connection. Run systemctl reload postgresql or pg_ctl reload -D ....
  • password_encryption is md5 but you switched pg_hba.conf to scram-sha-256 — the new hash is md5, but pg_hba.conf validates as scram-sha-256. Check SHOW password_encryption; and align it with pg_hba.conf (both should be scram-sha-256).
  • Bypassing with trust — disables auth. Use scram-sha-256 and a real password.
  • Supabase local password mismatch — POSTGRES_PASSWORD only takes effect on data-dir init. Changing it after supabase start requires supabase stop && rm -rf supabase/.branches && supabase start.
  • .pgpass wrong permissions — psql ignores ~/.pgpass if mode is not 0600 (or 0640 with the right owner). chmod 600 ~/.pgpass.

Official references: PostgreSQL — Client Authentication (pg_hba.conf), PostgreSQL — Password Authentication, PostgreSQL — The Password File (.pgpass).

Related Articles

Originally published at https://www.iloveblogs.blog

Top comments (0)