DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 08004 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 08004: Server Rejected the SQL Connection

PostgreSQL error code 08004 (sqlserver_rejected_establishment_of_sqlconnection) occurs when the database server explicitly refuses a client's connection attempt. Unlike network-level failures, this error is actively thrown by the server itself due to policy restrictions, resource limits, or permission issues. Understanding the root cause quickly is essential because this error directly blocks all application access to the database.

Top 3 Causes and Fixes

1. Misconfigured pg_hba.conf

This is the most common culprit. If the client's IP, username, or target database doesn't match any allow rule in pg_hba.conf, PostgreSQL rejects the connection immediately.

Diagnose:

-- Check current HBA rules (PostgreSQL 10+)
SELECT type, database, user_name, address, auth_method
FROM pg_hba_file_rules;
Enter fullscreen mode Exit fullscreen mode

Fix: Add the appropriate rule to pg_hba.conf:

# Allow a specific user from a specific subnet using scram-sha-256
host    mydb    myuser    192.168.1.0/24    scram-sha-256

# Allow all local connections
local   all     all                         trust
Enter fullscreen mode Exit fullscreen mode

Then reload without a full restart:

SELECT pg_reload_conf();
Enter fullscreen mode Exit fullscreen mode

2. max_connections Limit Exceeded

PostgreSQL enforces a hard cap on simultaneous connections via max_connections. When this limit is reached, new connection attempts are rejected with error 08004.

Diagnose:

-- Check current vs. maximum connections
SELECT count(*) AS active,
       (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_conn,
       (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') - count(*) AS remaining
FROM pg_stat_activity;

-- Find connection hogs
SELECT usename, application_name, state, count(*) AS total
FROM pg_stat_activity
GROUP BY usename, application_name, state
ORDER BY total DESC;
Enter fullscreen mode Exit fullscreen mode

Fix: Terminate stale idle connections and consider using a connection pooler like PgBouncer:

-- Terminate idle connections older than 10 minutes
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND state_change < NOW() - INTERVAL '10 minutes'
  AND pid <> pg_backend_pid();
Enter fullscreen mode Exit fullscreen mode

3. User Account Missing LOGIN Privilege or CONNECT Permission

A role without LOGIN privilege or without CONNECT rights on the target database will be denied, triggering 08004.

Diagnose:

-- Check role attributes
SELECT rolname, rolcanlogin, rolconnlimit, rolvaliduntil
FROM pg_roles
WHERE rolname = 'myuser';

-- Check database-level connect privilege
SELECT has_database_privilege('myuser', 'mydb', 'CONNECT');
Enter fullscreen mode Exit fullscreen mode

Fix:

-- Grant LOGIN privilege
ALTER USER myuser LOGIN;

-- Remove connection limit restriction
ALTER USER myuser CONNECTION LIMIT -1;

-- Grant CONNECT on the database
GRANT CONNECT ON DATABASE mydb TO myuser;

-- Fix expired password validity
ALTER USER myuser VALID UNTIL 'infinity';
Enter fullscreen mode Exit fullscreen mode

Quick Prevention Tips

Monitor connections proactively — Set up alerts when usage exceeds 80% of max_connections:

SELECT round(count(*) * 100.0 /
       (SELECT setting::int FROM pg_settings WHERE name = 'max_connections'), 2)
       AS connection_usage_pct
FROM pg_stat_activity;
Enter fullscreen mode Exit fullscreen mode

Use a connection pooler — Deploy PgBouncer in transaction mode between your application and PostgreSQL. This single change can reduce active server connections by 10x and virtually eliminates 08004 errors caused by connection exhaustion.

Version-control pg_hba.conf — Treat your HBA file like application code. Store it in Git, require peer review for changes, and always run pg_reload_conf() after edits to validate the new rules are applied correctly.

Related Error Codes

Code Name Description
08000 connection_exception Generic connection error
08001 sqlclient_unable_to_establish_sqlconnection Client-side connection failure
08006 connection_failure Physical connection disruption
28000 invalid_authorization_specification Auth mismatch (often confused with 08004)
28P01 invalid_password Wrong password supplied

Key distinction: 08004 means the server actively rejected the connection, while 08001 means the client couldn't reach the server at all. Always check PostgreSQL server logs (pg_log) alongside the error code for the exact rejection reason.

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