DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 28P01 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 28P01: Invalid Password — Causes, Fixes & Prevention

PostgreSQL error code 28P01 is thrown during the authentication phase when the password provided by a client does not match the one stored for the given database role. For security reasons, PostgreSQL intentionally returns the same error whether the user doesn't exist or the password is simply wrong — making diagnosis require a closer look at server logs. This error can surface in any client tool or application driver including psql, pg_dump, JDBC, SQLAlchemy, and connection poolers like PgBouncer.

Top 3 Causes

1. Password Was Changed But Config Was Not Updated

The most common cause. Someone rotated the database password but forgot to update the application's environment variables or config files.

-- Reset the password for the application user
ALTER ROLE myapp_user WITH PASSWORD 'NewSecurePassword!2024';

-- Check password expiry for all login-capable roles
SELECT rolname, rolvaliduntil
FROM pg_roles
WHERE rolcanlogin = true
ORDER BY rolvaliduntil NULLS LAST;
Enter fullscreen mode Exit fullscreen mode

After running the SQL above, make sure your .env, database.yml, or secret store reflects the new password immediately.

2. Authentication Method Mismatch in pg_hba.conf

PostgreSQL supports multiple authentication methods (scram-sha-256, md5, password, peer). Since PostgreSQL 14, the default is scram-sha-256. Older client drivers that only support md5 will fail with 28P01 against a server expecting scram-sha-256.

-- Check the server's current password encryption setting
SHOW password_encryption;

-- View current pg_hba rules (PostgreSQL 10+)
SELECT line_number, type, database, user_name, address, auth_method
FROM pg_hba_file_rules
ORDER BY line_number;

-- Temporarily downgrade to md5 for legacy client compatibility
SET password_encryption = 'md5';
ALTER ROLE myapp_user WITH PASSWORD 'NewSecurePassword!2024';

-- Reload config after editing pg_hba.conf
SELECT pg_reload_conf();
Enter fullscreen mode Exit fullscreen mode

Example pg_hba.conf entry:

# TYPE  DATABASE    USER         ADDRESS          METHOD
host    mydb        myapp_user   192.168.1.0/24   scram-sha-256
host    mydb        myapp_user   10.0.0.0/8       md5
Enter fullscreen mode Exit fullscreen mode

3. Role Does Not Exist or Lacks LOGIN Privilege

If the role doesn't exist, PostgreSQL still returns 28P01 rather than exposing whether a username is valid — a deliberate security design. Similarly, a role without LOGIN privilege cannot authenticate.

-- Check if the role exists and has login capability
SELECT rolname, rolcanlogin, rolconnlimit, rolvaliduntil
FROM pg_roles
WHERE rolname = 'myapp_user';

-- Create the role if it doesn't exist
CREATE ROLE myapp_user WITH
  LOGIN
  PASSWORD 'SecurePassword!2024'
  CONNECTION LIMIT 50;

-- Grant login if missing
ALTER ROLE myapp_user WITH LOGIN;

-- Grant database access
GRANT CONNECT ON DATABASE mydb TO myapp_user;
GRANT USAGE ON SCHEMA public TO myapp_user;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist

  1. Check server logs first — the log will reveal whether it's a missing user, wrong password, or method mismatch.
  2. Verify the role exists using SELECT * FROM pg_roles WHERE rolname = 'your_user';
  3. Reset the password with ALTER ROLE ... WITH PASSWORD '...';
  4. Reload config with SELECT pg_reload_conf(); after any pg_hba.conf change. 
-- Enable connection logging to capture future failures
ALTER SYSTEM SET log_connections = 'on';
ALTER SYSTEM SET log_disconnections = 'on';
SELECT pg_reload_conf();
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Use a Secret Manager for Password Rotation

Integrate AWS Secrets Manager, HashiCorp Vault, or a similar tool to centrally manage and automatically rotate database credentials. This eliminates the human error of updating one service but forgetting another.

-- Enforce periodic rotation with expiry
ALTER ROLE myapp_user WITH PASSWORD 'RotatedPassword!'
  VALID UNTIL '2025-06-01';
Enter fullscreen mode Exit fullscreen mode

2. Monitor Authentication Failures

Set up alerting on repeated 28P01 errors. A spike in authentication failures from a single IP can indicate a brute-force attack. Use Prometheus with pg_stat_activity, Datadog, or CloudWatch to set thresholds and trigger alerts automatically.

-- Monitor active connections and their states
SELECT usename, client_addr, state, count(*)
FROM pg_stat_activity
GROUP BY usename, client_addr, state
ORDER BY count(*) DESC;
Enter fullscreen mode Exit fullscreen mode

Related Errors

Code Name Description
28000 invalid_authorization_specification Parent class of 28P01; host/user not permitted in pg_hba.conf
3D000 invalid_catalog_name Target database does not exist
08006 connection_failure Network-level failure reaching the server
42501 insufficient_privilege Auth succeeded but user lacks object-level permissions

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)