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 but fails due to an authorization configuration mismatch — not just a wrong password, but a fundamental issue with how the connection is being authorized. This error commonly stems from misconfigured pg_hba.conf rules, non-existent database roles, or SSL/TLS authentication conflicts. Unlike error 28P01 (wrong password), 28000 typically points to a structural or configuration-level problem.

Top 3 Causes and Fixes

1. Missing or Incorrect pg_hba.conf Rule

The pg_hba.conf file controls who can connect, from where, and by which authentication method. If no matching rule exists for a client's IP, username, or database combination, PostgreSQL rejects the connection with error 28000.

Diagnose the issue:

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

-- Inspect active rules (PostgreSQL 10+)
SELECT type, database, user_name, address, auth_method, error
FROM pg_hba_file_rules
WHERE error IS NOT NULL;
Enter fullscreen mode Exit fullscreen mode

Fix — add a proper rule to pg_hba.conf:

# TYPE   DATABASE   USER      ADDRESS           METHOD
host     mydb       myuser    192.168.1.0/24    scram-sha-256
host     mydb       myuser    0.0.0.0/0         scram-sha-256
Enter fullscreen mode Exit fullscreen mode

Then reload the configuration without restarting:

-- Reload pg_hba.conf without full restart
SELECT pg_reload_conf();
Enter fullscreen mode Exit fullscreen mode

2. The Database Role Does Not Exist

PostgreSQL manages users as "roles." If the specified username doesn't exist in the cluster, authentication cannot proceed and error 28000 is thrown — often caused by typos, case sensitivity, or roles that weren't created in a particular environment.

Diagnose the issue:

-- Check if a role exists
SELECT EXISTS (
    SELECT 1 FROM pg_roles WHERE rolname = 'myuser'
) AS role_exists;

-- List all roles with login capability
SELECT rolname, rolcanlogin, rolsuper
FROM pg_roles
WHERE rolcanlogin = true;
Enter fullscreen mode Exit fullscreen mode

Fix — create the role and grant access:

-- Create the missing role
CREATE ROLE myuser WITH LOGIN PASSWORD 'SecureP@ssw0rd!';

-- Grant database connection privilege
GRANT CONNECT ON DATABASE mydb TO myuser;

-- If role exists but login is disabled
ALTER ROLE myuser LOGIN;

-- Reset an expired password
ALTER ROLE myuser PASSWORD 'NewP@ssw0rd!' VALID UNTIL 'infinity';
Enter fullscreen mode Exit fullscreen mode

3. SSL/TLS Authentication Mismatch

When pg_hba.conf enforces hostssl (SSL-only connections) but the client connects without SSL — or vice versa — PostgreSQL will reject the connection with 28000. Certificate-based authentication failures also fall under this error.

Diagnose the issue:

-- Check if SSL is enabled on the server
SHOW ssl;

-- Review all SSL-related settings
SELECT name, setting
FROM pg_settings
WHERE name LIKE 'ssl%';
Enter fullscreen mode Exit fullscreen mode

Fix — align pg_hba.conf SSL rules with client capabilities:

# Require SSL connections only
hostssl    mydb   myuser   0.0.0.0/0    scram-sha-256

# Allow both SSL and non-SSL (development)
host       mydb   myuser   0.0.0.0/0    scram-sha-256
Enter fullscreen mode Exit fullscreen mode

Test connection with explicit SSL mode:

# Test without SSL
psql "host=myserver dbname=mydb user=myuser sslmode=disable"

# Test with SSL required
psql "host=myserver dbname=mydb user=myuser sslmode=require"
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist 

-- 1. Confirm the role exists and can log in
SELECT rolname, rolcanlogin FROM pg_roles WHERE rolname = 'myuser';

-- 2. Check for pg_hba.conf rule errors
SELECT line_number, type, database, user_name, address, auth_method, error
FROM pg_hba_file_rules;

-- 3. Reload config after any changes
SELECT pg_reload_conf();

-- 4. Verify SSL status
SHOW ssl;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Version-control pg_hba.conf
Store pg_hba.conf in a Git repository and require peer review for all changes. Integrate a validation step into your CI/CD pipeline that checks pg_hba_file_rules for errors immediately after any proposed change, preventing misconfigured rules from ever reaching production.

2. Standardize Role Management with IaC
Use infrastructure-as-code tools (Terraform, Ansible, or standardized SQL migration scripts) to manage all database roles consistently across environments. Manually created roles are a leading cause of environment-specific 28000 errors. Schedule regular audits of pg_roles to catch orphaned or misconfigured accounts early.

Related Errors

Error Code Name Brief Description
28P01 invalid_password Role exists and rule matches, but password is wrong
42501 insufficient_privilege Auth succeeded, but no permission on the object
08001 sqlclient_unable_to_establish_sqlconnection Network-level failure, check listen_addresses and firewall
3D000 invalid_catalog_name Target database does not exist

📖 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)