DEV Community

umzzil nng
umzzil nng

Posted on Originally published at oraerror.com

PostgreSQL 28000 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 28000: Invalid Authorization Specification

PostgreSQL error code 28000 (invalid_authorization_specification) occurs when a client attempts to connect to the database server but the connection is rejected due to an authorization rule mismatch — not simply a wrong password (that's 28P01). This error typically means the server's pg_hba.conf rules don't match the incoming connection parameters, the role doesn't exist, or the authentication method is incompatible.

Top 3 Causes

1. Missing or Mismatched pg_hba.conf Rule

The pg_hba.conf file controls who can connect, from where, and how. If no rule matches the incoming connection (user, database, IP address, auth method), PostgreSQL immediately rejects it with error 28000.

-- Check the location of pg_hba.conf
SHOW hba_file;

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

Fix: Add the correct rule to pg_hba.conf and reload.

# Allow a specific user from a specific subnet using scram-sha-256
host    mydb    myuser    192.168.1.0/24    scram-sha-256
Enter fullscreen mode Exit fullscreen mode 
-- Reload configuration without restarting the server
SELECT pg_reload_conf();
Enter fullscreen mode Exit fullscreen mode

2. Role or Database Does Not Exist

If the username or database specified in the connection string doesn't exist in PostgreSQL, the authentication step fails with error 28000. This commonly happens when deployment scripts skip the role or database creation step.

-- Check existing roles
SELECT rolname, rolcanlogin FROM pg_roles ORDER BY rolname;

-- Check existing databases
SELECT datname FROM pg_database;

-- Create a missing role with login privilege
CREATE ROLE myuser WITH LOGIN PASSWORD 'SecureP@ssw0rd!';

-- Create a missing database
CREATE DATABASE mydb OWNER myuser;

-- Grant basic privileges
GRANT CONNECT ON DATABASE mydb TO myuser;
GRANT USAGE ON SCHEMA public TO myuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser;
Enter fullscreen mode Exit fullscreen mode

3. peer Authentication Mismatch (Linux/Unix)

The peer authentication method requires the OS username to match the PostgreSQL role name exactly. Running psql -U postgres while logged in as the OS user ubuntu will trigger error 28000 because the names don't match.

# Fix option 1: Switch to the matching OS user
sudo -i -u postgres
psql
Enter fullscreen mode Exit fullscreen mode 
-- Fix option 2: Set a password and change pg_hba.conf to scram-sha-256
ALTER ROLE postgres WITH PASSWORD 'NewSecurePassword!';
Enter fullscreen mode Exit fullscreen mode 
# pg_hba.conf — change peer to scram-sha-256 for local connections
# Before:
local   all   postgres   peer

# After:
local   all   postgres   scram-sha-256
Enter fullscreen mode Exit fullscreen mode 
-- After editing pg_hba.conf, reload without restart
SELECT pg_reload_conf();
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist

  1. Verify the rule exists — Run SELECT * FROM pg_hba_file_rules; to confirm a matching entry exists for your user, database, and IP.
  2. Confirm the role exists — Run SELECT rolname FROM pg_roles WHERE rolname = 'youruser';.
  3. Confirm the database exists — Run SELECT datname FROM pg_database WHERE datname = 'yourdb';.
  4. Reload after every pg_hba.conf change — Always run SELECT pg_reload_conf(); or sudo systemctl reload postgresql.
  5. Check PostgreSQL logs — The server log provides the exact reason for rejection: tail -f /var/log/postgresql/postgresql-*.log.

Prevention Tips

Manage pg_hba.conf as code. Store the file in version control (Git), require peer review for changes, and include pg_hba.conf updates in your deployment scripts whenever new roles or databases are added.

Follow the Least Privilege principle. Never use the superuser postgres account for application connections. Create dedicated roles per application with only the permissions they need, and use strong passwords with scram-sha-256 authentication.

-- Example: application-specific role with limited privileges
CREATE ROLE app_user WITH LOGIN PASSWORD 'AppSecurePass!' NOCREATEDB NOCREATEROLE;
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • 28P01 — invalid_password: Role exists but password is wrong. Distinct from 28000.
  • 3D000 — invalid_catalog_name: Target database does not exist.
  • 42501 — insufficient_privilege: Connection succeeded but the role 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)