Fix: PostgreSQL 'password authentication failed for user' Error

Problem

You try to connect to PostgreSQL and authentication fails:

FATAL: password authentication failed for user "myuser"
FATAL: no pg_hba.conf entry for host "172.17.0.1"

Or in an application:

psycopg2.OperationalError: connection to server failed: FATAL: password authentication failed

Symptoms

  • Connection works with psql -U postgres but not with your application user
  • Works locally but not from Docker or another host
  • Password is definitely correct (you just reset it)
  • Error mentions pg_hba.conf or peer authentication

Root Cause

PostgreSQL’s authentication is governed by pg_hba.conf (Host-Based Authentication). This file controls who can connect from where and how they must authenticate. The common issues:

  1. Wrong password — actually wrong, or expired
  2. pg_hba.conf doesn’t allow the connection method (e.g., password from localhost, but only peer is configured)
  3. Docker/remote connections — the default pg_hba.conf only allows local connections
  4. Peer authentication — Unix socket connections require matching OS username

Solution

Step 1: Locate pg_hba.conf

# In psql
SHOW hba_file;

# Or find it
sudo -u postgres psql -c "SHOW hba_file;"

Typically at /etc/postgresql/16/main/pg_hba.conf or /var/lib/postgresql/data/pg_hba.conf in Docker.

Step 2: Understand the pg_hba.conf format

Each line has: TYPE DATABASE USER ADDRESS METHOD

# TYPE   DATABASE  USER    ADDRESS        METHOD
local    all       all                    peer
host     all       all     127.0.0.1/32   md5
host     all       all     ::1/128        md5
  • local = Unix socket connections
  • host = TCP/IP connections
  • peer = OS user must match DB user (local only)
  • md5 = password hashed with MD5
  • scram-sha-256 = modern password auth
  • trust = no password (development only!)

Step 3: Fix for local connections

If you’re connecting via Unix socket (psql -U myuser):

# Change peer to md5 or scram-sha-256 for password auth
# Edit pg_hba.conf:
local   all   all   scram-sha-256

# Reload
sudo systemctl reload postgresql
# Or: pg_ctl reload

Step 4: Fix for Docker connections

In Docker, add an explicit rule for the Docker network:

# In pg_hba.conf inside the container
host    all    all    172.0.0.0/8    scram-sha-256

Or override via environment variable:

docker run -d \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_HOST_AUTH_METHOD=scram-sha-256 \
  postgres:16

Step 5: Reset a forgotten password

# Connect as postgres superuser
sudo -u postgres psql

# Reset the password
ALTER USER myuser WITH PASSWORD 'new_password';

Verification

# Test local connection
psql -U myuser -h localhost -d mydb -c "SELECT 1;"

# Test Docker connection
docker exec my-postgres psql -U myuser -c "SELECT 1;"

Prevention

  • Never use trust in production — it allows passwordless access
  • Use scram-sha-256 over md5 for stronger password hashing
  • Grant minimal privileges — application users shouldn’t be superusers
  • Set POSTGRES_HOST_AUTH_METHOD=scram-sha-256 in Docker deployments
  • Keep pg_hba.conf version-controlled alongside your infrastructure config

References

Advertisement