Fix PostgreSQL FATAL: password authentication failed

Symptoms

When attempting to connect to a PostgreSQL database using psql or an application, you receive the error message: FATAL: password authentication failed for user "username". Connection attempts fail even when you believe the password is correct.

Root Causes

• Incorrect password: The most common cause – the password provided does not match the stored password for the user.
• Misconfigured pg_hba.conf: The host-based authentication file may not allow password authentication for the user/database/connection type.
• Password expiration: The user's password may have expired (if password expiration is enabled).
• Authentication method mismatch: The pg_hba.conf specifies md5 but the client uses scram-sha-256 or vice versa.
• User does not exist: The specified user may not exist in the PostgreSQL cluster.

Step-by-Step Fix

1. Verify the User Exists

Connect as a superuser (e.g., postgres) to check if the user exists:

sudo -u postgres psql -c "\du"

If the user is missing, create it:

sudo -u postgres psql -c "CREATE USER username WITH PASSWORD 'newpassword';"

2. Reset the Password

If the user exists, reset the password:

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

3. Check pg_hba.conf

Locate the pg_hba.conf file (usually in /etc/postgresql/<version>/main/pg_hba.conf or /var/lib/pgsql/data/pg_hba.conf). Look for the line matching your connection type (local, host, hostssl) and database/user. Ensure the method is set to md5 or scram-sha-256 (not peer or trust for password connections). Example for local connections:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     md5

After editing, reload PostgreSQL:

sudo systemctl reload postgresql

4. Test the Connection

Use psql to test:

psql -h localhost -U username -d databasename

Enter the password when prompted. If successful, the issue is resolved.

Alternative Fixes

• Use trust authentication temporarily: Change the method to trust in pg_hba.conf for testing, then revert to md5 after fixing. Not recommended for production.
• Check for password expiration: If using password expiration, reset the password and set no expiry: ALTER USER username WITH PASSWORD 'newpassword' VALID UNTIL 'infinity';
• Check SSL/TLS settings: If using hostssl, ensure the client is connecting with SSL and the pg_hba.conf method is appropriate.
• Review logs: Check PostgreSQL logs (usually in /var/log/postgresql/) for more detailed error messages.

Prevention

• Use strong passwords: Enforce password complexity and rotation policies.
• Document pg_hba.conf changes: Keep a backup of the original configuration and document any modifications.
• Regularly test backups: Ensure that password resets are part of disaster recovery drills.
• Monitor logs: Set up log monitoring for authentication failures to detect brute-force attempts early.
• Use connection pooling: Tools like PgBouncer can reduce authentication errors by reusing connections.