PostgreSQL FATAL password authentication failed fix

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"

The connection is rejected immediately after entering the password or without prompting if the password is missing. This can occur locally or remotely.

Root Causes

Incorrect password: The most common cause – the password provided does not match the stored password for the user.
Password not set: The user may not have a password defined (e.g., created without PASSWORD clause).
Authentication method mismatch: The pg_hba.conf file specifies an authentication method (e.g., scram-sha-256) that requires a password, but the user’s password is stored using a different method (e.g., md5).
Missing or incorrect connection parameters: Wrong host, port, or database name can lead to hitting a different rule in pg_hba.conf that requires authentication.
User does not exist: The specified user may not be present in the database cluster.

Step-by-Step Fix

1. Verify the user exists and password is set

Connect as a superuser (e.g., postgres) using local trust authentication or OS authentication:

sudo -u postgres psql

Then list users:

\du

Check if the user has a password. If not, set it:

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/). 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 trust or reject). Example:

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

If using scram-sha-256, ensure the user’s password is stored with that method. You can re-encrypt by setting the password again.

3. Reload PostgreSQL configuration

After editing pg_hba.conf, reload the configuration:

sudo systemctl reload postgresql

Or from psql:

SELECT pg_reload_conf();

4. Test the connection

Try connecting again:

psql -h localhost -U username -d database

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

Alternative Fixes

Reset password using command line: As superuser, run psql -c "ALTER USER username WITH PASSWORD 'newpass';".
Change authentication method to trust temporarily: For troubleshooting, set method to trust for a specific IP range, then change back after testing.
Use .pgpass file: Create a ~/.pgpass file with the format host:port:database:user:password to avoid interactive prompts.
Drop and recreate the user: If the user is corrupted, drop and recreate with a password.

Prevention

Always set a strong password when creating users: CREATE USER username WITH PASSWORD 'strong_password';.
Keep pg_hba.conf consistent with your authentication requirements. Use scram-sha-256 for modern PostgreSQL 14+.
Regularly audit user accounts and passwords.
Use environment variables or connection strings with password to avoid typos.
Document connection parameters for each environment.

By following these steps, you can quickly resolve the PostgreSQL password authentication failure and secure your database connections.