How to Fix PostgreSQL Error Code 28000 (Invalid Authorization Specification)

POSTGRESQL ERROR 28000: INVALID AUTHORIZATION SPECIFICATION

1. Error details

SQLSTATE: 28000 Error: Invalid Authorization Specification

PostgreSQL error code 28000 indicates an authorization-related connection failure, typically caused by an invalid authentication configuration, an unsupported connection identity, or an access-control rule that rejects the connection. In cloud environments such as AWS RDS and enterprise database servers, the root cause is commonly an incorrect pg_hba.conf rule, a missing or misconfigured database role, or a mismatch between the requested connection identity and the server's authentication configuration.

Note: SQLSTATE 28000 is a general authorization exception; the exact cause depends on the accompanying error message.

2. Immediate fix

Step 1: Verify that the database role exists and can log in.

Connect using an administrative PostgreSQL account and execute:

SELECT rolname, rolcanlogin, rolinherit
FROM pg_roles
WHERE rolname = 'app_user';

If the role does not exist, create it:

CREATE ROLE app_user
WITH LOGIN
PASSWORD 'REPLACE_WITH_STRONG_PASSWORD';

If the role exists but login is disabled:

ALTER ROLE app_user WITH LOGIN;

If the password is incorrect, reset it:

ALTER ROLE app_user
WITH PASSWORD 'REPLACE_WITH_STRONG_PASSWORD';

Replace app_user and the password with the actual application credentials. Use a secret manager for production credentials.

Step 2: Grant database connection privileges.

Execute as a database owner or administrator:

GRANT CONNECT ON DATABASE app_db TO app_user;

Connect to app_db and grant schema access if required:

GRANT USAGE ON SCHEMA public TO app_user;

For an application that must read and write existing tables:

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public
TO app_user;

These grants address database and object privileges. They do not override failed authentication or an invalid host-based authentication rule.

Step 3: Correct host-based authentication configuration, if applicable.

For self-managed PostgreSQL, inspect pg_hba.conf and add an appropriate rule before any conflicting rule:

# TYPE   DATABASE   USER       ADDRESS          METHOD
host     app_db     app_user   10.20.30.0/24    scram-sha-256

Replace 10.20.30.0/24 with the application's actual trusted source CIDR. Do not use 0.0.0.0/0 as a production shortcut.

Reload the configuration:

SELECT pg_reload_conf();

On AWS RDS for PostgreSQL, pg_hba.conf is managed by AWS and cannot be edited directly. Instead, verify the DB security group inbound rules, VPC routing, database endpoint, SSL requirements, and role configuration. A security group or network block usually causes a timeout rather than SQLSTATE 28000.

Step 4: Verify the connection parameters.

Use the correct database, role, endpoint, port, and SSL mode:

psql "host=<DB_ENDPOINT> port=5432 dbname=app_db user=app_user sslmode=require" -W

Enter the password when prompted. For self-managed servers, use the SSL mode required by the server's policy; for AWS RDS, validate the certificate chain when enforcing server identity verification.

3. Causes

Incorrect pg_hba.conf rules

• The client IP does not match an allowed CIDR.
• The rule specifies the wrong database or role.
• An earlier matching rule uses an incompatible authentication method.
• The configured authentication method does not match the server's password configuration.

Incorrect role or authorization configuration

• The role does not exist in the target PostgreSQL instance.
• The role lacks the LOGIN attribute.
• The role lacks CONNECT privileges on the requested database.
• The application connects to the wrong database or uses a different role than expected.

Invalid credentials or connection security settings

• The password is stale, incorrect, or inconsistent with the configured authentication method.
• The application retrieves credentials from an outdated environment variable or secret.
• The server requires SSL, but the client uses an incompatible connection configuration.
• The client connects to the wrong endpoint, cluster, or database instance.

4. Verification

Run this terminal command to verify that PostgreSQL accepts the connection and executes a query:

psql "host=<DB_ENDPOINT> port=5432 dbname=app_db user=app_user sslmode=require" -W -c "SELECT current_user, current_database(), inet_client_addr();"

Expected result:

• current_user returns app_user.
• current_database returns app_db.
• The command exits with status 0.

For an automated check:

psql "host=<DB_ENDPOINT> port=5432 dbname=app_db user=app_user sslmode=require" -W -c "SELECT 1;"
echo $?

Expected output:

 ?column?
----------
        1

0

If the connection still fails, capture the complete PostgreSQL error message and SQLSTATE from the client and inspect the server logs. For AWS RDS, review the PostgreSQL logs in the RDS console or CloudWatch. Do not assume that SQLSTATE 28000 alone identifies the exact configuration defect.