Resolve PostgreSQL FATAL password authentication failed

Symptoms

When attempting to connect to a PostgreSQL database, you receive an error message similar to: FATAL: password authentication failed for user "username". This can occur with tools like psql, pgAdmin, or application connectors. The connection attempt is rejected immediately after providing the password.

Root Causes

This error is typically caused by one of the following:

Incorrect password for the specified user.
Misconfiguration in the pg_hba.conf file, such as incorrect authentication method (e.g., trust vs md5).
User account is locked or password has expired.
Using the wrong database or user name.
PostgreSQL service not running or not accepting TCP/IP connections.

Step-by-step Fix

1. Verify Credentials

Double-check the username and password. Use psql -U username -d database -h host -p port and ensure you are entering the correct password. If unsure, reset the password:

sudo -u postgres psql -c "ALTER USER username WITH PASSWORD 'new_password';"

2. Check pg_hba.conf

Locate the pg_hba.conf file (usually in /etc/postgresql/<version>/main/ or /var/lib/pgsql/data/). Ensure the entry for your connection type (local, host) uses a password-based method like md5 or scram-sha-256:

# TYPE  DATABASE  USER  ADDRESS  METHOD
local  all       all            md5
host   all       all  127.0.0.1/32  scram-sha-256

If it says trust, change it to md5 or scram-sha-256. Save the file and reload PostgreSQL:

sudo systemctl reload postgresql

3. Test Connection

Try connecting again:

psql -U username -d database -h localhost -W

If successful, the issue is resolved.

Alternative Fixes

Reset PostgreSQL admin password: If you cannot log in as any user, edit pg_hba.conf to set local all all trust, restart PostgreSQL, connect without password, reset the user password, then revert the config.
Check user existence: Run sudo -u postgres psql -c "\du" to list users. If the user is missing, create it with CREATE USER username WITH PASSWORD 'password';
Enable TCP/IP: Ensure listen_addresses = 'localhost' or '*' in postgresql.conf and restart.

Prevention

Use strong, unique passwords and store them securely.
Regularly audit pg_hba.conf for correct authentication methods.
Set password expiration policies if needed, or use VALID UNTIL 'infinity'.
Monitor PostgreSQL logs for authentication failures.

By following these steps, you can quickly resolve the password authentication failed error and prevent future occurrences.