How to Fix PostgreSQL Error: Password Authentication Failed for User

The "Password authentication failed for user" error occurs when PostgreSQL rejects a connection attempt due to incorrect credentials. This error means the authentication mechanism (typically password-based) failed to verify the user's identity, either because of an incorrect password, wrong username, or authentication method mismatch.

Impact

This error prevents users and applications from accessing the database, potentially causing application failures and service disruptions. It can also indicate security issues such as brute force attacks or misconfigured authentication settings.

Common Causes

1. Incorrect password provided
2. Wrong username specified
3. Password has been changed but application not updated
4. Authentication method mismatch in pg_hba.conf
5. Password encoding issues (MD5 vs SCRAM-SHA-256)
6. Special characters in password not properly escaped
7. Case sensitivity issues with username
8. Expired or locked user account

Troubleshooting and Resolution Steps

1. Verify the username exists and check case sensitivity:

   -- Connect as superuser
   sudo -u postgres psql
   
   -- List all users
   \du
   
   -- Check specific user
   SELECT usename FROM pg_user WHERE usename = 'your_username';
   

2. Reset the user password:

   -- As superuser
   ALTER USER your_username WITH PASSWORD 'new_secure_password';
   

3. Check pg_hba.conf authentication method:

   sudo nano /etc/postgresql/15/main/pg_hba.conf
   

   Ensure the authentication method matches your setup:

   # Recommended: SCRAM-SHA-256
   host    all    all    0.0.0.0/0    scram-sha-256
   
   # Or MD5 (less secure)
   host    all    all    0.0.0.0/0    md5
   
   # Or trust (development only - very insecure)
   host    all    all    127.0.0.1/32    trust
   

4. Reload PostgreSQL configuration:

   SELECT pg_reload_conf();
   

   Or restart the service:

   sudo systemctl reload postgresql
   

5. Verify password encryption method:

   -- Check current setting
   SHOW password_encryption;
   
   -- Set to scram-sha-256 (recommended)
   ALTER SYSTEM SET password_encryption = 'scram-sha-256';
   SELECT pg_reload_conf();
   

6. Test connection with psql:

   psql -h localhost -U your_username -d your_database
   

7. Check for special characters in password:

   # Python example - properly escape password in connection string
   import urllib.parse
   password = urllib.parse.quote_plus('p@ssw0rd!')
   conn_string = f"postgresql://user:{password}@host:5432/db"
   

8. Verify user has not been locked or expired:

   SELECT rolname, rolvaliduntil
   FROM pg_authid
   WHERE rolname = 'your_username';
   

9. Check PostgreSQL logs for detailed error messages:

   sudo tail -f /var/log/postgresql/postgresql-15-main.log
   

10. For applications, verify environment variables or config files:

    # Check environment variables
    echo $DATABASE_URL
    
    # Verify configuration file
    cat /etc/myapp/database.conf
    

Additional Information

• Always use SCRAM-SHA-256 instead of MD5 for password encryption
• Implement strong password policies (minimum length, complexity requirements)
• Use connection pooling with proper credential management
• Consider using certificate-based authentication for enhanced security
• Regularly rotate database passwords
• Monitor failed authentication attempts for security threats
• Use environment variables or secret management systems (HashiCorp Vault, AWS Secrets Manager) for password storage

Frequently Asked Questions

Q: What's the difference between MD5 and SCRAM-SHA-256 authentication?
A: SCRAM-SHA-256 is a more secure authentication method that prevents password sniffing and replay attacks. MD5 is considered cryptographically weak and should be avoided.

Q: How do I migrate from MD5 to SCRAM-SHA-256?
A: First, set password_encryption = 'scram-sha-256' in postgresql.conf, then reset all user passwords using ALTER USER ... WITH PASSWORD. Finally, update pg_hba.conf to use scram-sha-256 instead of md5.

Q: Can I reset the postgres superuser password if I forgot it?
A: Yes, temporarily set authentication to 'trust' in pg_hba.conf for local connections, restart PostgreSQL, connect without password, reset the password, then revert pg_hba.conf to secure authentication.

Q: Why does my password work in psql but not in my application?
A: Check for special characters that need URL encoding in connection strings, verify the application is using the correct connection parameters, and ensure the application user has the same authentication method configured in pg_hba.conf.

Q: How can I see failed login attempts?
A: Enable connection logging by setting log_connections = on and log_failed_connections = on in postgresql.conf, then check the PostgreSQL log files.

Q: Does PostgreSQL have a password expiration feature?
A: PostgreSQL supports the VALID UNTIL clause when creating or altering users: ALTER USER username VALID UNTIL '2024-12-31';