CertGrid CertGrid
Hands-on Lab·PostgreSQL

PostgreSQL Connection Failure Diagnosis

Six ways a connection fails and the six different messages they produce - including the two that look like authentication problems and are not.

Troubleshooting Guide 43 of 47 Intermediate

Written against the versions above. psql attempts SSL first and falls back, so a rejection is reported twice - once for each attempt. That is one failure, not two.

The 2 hosts these commands ran on: db-b01, db-util01.
Server NameIP AddressOSRolesCPURAMHDD
db-b01192.168.0.82Ubuntu 26.04 LTSStandby / Replica / Replica Set Member 22 Core4 GB50 GB
db-util01192.168.0.84Ubuntu 26.04 LTSClient, Backup Target and Monitoring2 Core4 GB50 GB

Before you start

  1. Ask whether anything is listening before anything else

    pg_isready opens a TCP connection and asks the server if it is accepting connections, without authenticating. accepting connections means the network path, the port and the postmaster are all fine - so whatever is wrong is above that layer.

    The connection then fails anyway:

    FATAL: no pg_hba.conf entry for host "192.168.0.84", user "appuser", database "appdb", no encryption

    This message is precise and worth reading in full. It names the four things that must match a rule: the client address, the user, the database, and whether the connection is encrypted. One of them has no rule.

    It is reported twice because psql tried SSL, was rejected, retried without, and was rejected again.

    bash Example session 
    pg_isready -h 192.168.0.82 -p 5432192.168.0.82:5432 - accepting connectionsenv PGCONNECT_TIMEOUT=5 psql -h 192.168.0.82 -U appuser -d appdb -w -c "SELECT 1"psql: error: connection to server at "192.168.0.82", port 5432 failed: FATAL:  no pg_hba.conf entry for host "192.168.0.84", user "appuser", database "appdb", SSL encryptionconnection to server at "192.168.0.82", port 5432 failed: FATAL:  no pg_hba.conf entry for host "192.168.0.84", user "appuser", database "appdb", no encryption[exit 2]

    Expected resultaccepting connections, then no pg_hba.conf entry.

    Success conditionYou can separate a network problem from an authorisation one in one command.

  2. Add the rule the message asked for

    Creating the role and granting CONNECT changes nothing - the same no pg_hba.conf entry. That is the point most people lose an hour to: pg_hba.conf is checked before the role and the database are consulted at all. A perfectly valid account with correct privileges is still refused.

    One appended line, matching host, database, user and address, then pg_reload_conf() - pg_hba.conf is reloadable, no restart.

    The connection succeeds, and inet_client_addr() confirms the server sees the client at the address the rule matched.

    bash Example session 
    sudo -u postgres psql -c "CREATE ROLE appuser LOGIN PASSWORD 'AppUser#2026'"CREATE ROLEsudo -u postgres psql -d appdb -c "GRANT CONNECT ON DATABASE appdb TO appuser"GRANTenv PGCONNECT_TIMEOUT=5 PGPASSWORD=AppUser#2026 psql -h 192.168.0.82 -U appuser -d appdb -w -c "SELECT 'still refused' AS result"psql: error: connection to server at "192.168.0.82", port 5432 failed: FATAL:  no pg_hba.conf entry for host "192.168.0.84", user "appuser", database "appdb", SSL encryptionconnection to server at "192.168.0.82", port 5432 failed: FATAL:  no pg_hba.conf entry for host "192.168.0.84", user "appuser", database "appdb", no encryption[exit 2]sudo bash -c 'echo "host    appdb    appuser    192.168.0.84/32    scram-sha-256" >> /etc/postgresql/18/main/pg_hba.conf'sudo -u postgres psql -c "SELECT pg_reload_conf()" pg_reload_conf---------------- t(1 row)env PGCONNECT_TIMEOUT=5 PGPASSWORD=AppUser#2026 psql -h 192.168.0.82 -U appuser -d appdb -w -c "SELECT current_user, inet_client_addr() AS from_host" current_user |  from_host--------------+-------------- appuser      | 192.168.0.84(1 row)

    Expected resultStill refused, then accepted after the rule and the reload.

    Success conditionYou can add a host rule and apply it without a restart.

  3. Three more rejections, and only one of them says what you expect

    A wrong password gives the message you would predict: FATAL: password authentication failed for user "appuser".

    A wrong database name does not. -d nosuchdb gives no pg_hba.conf entry for host ..., database "nosuchdb" - not database "nosuchdb" does not exist.

    A non-existent role does the same: -U nobody gives no pg_hba.conf entry ... user "nobody", not role "nobody" does not exist.

    Both are correct behaviour. The rule added above is scoped to appdb and appuser; anything else matches no rule and is refused there, before the catalogue is ever consulted. It is also a deliberate security property - the server does not confirm which database or role names exist to an unauthenticated client.

    So no pg_hba.conf entry does not mean the file is wrong. It can equally mean a typo in the database name or the username.

    bash Example session 
    env PGCONNECT_TIMEOUT=5 PGPASSWORD=wrong psql -h 192.168.0.82 -U appuser -d appdb -w -c "SELECT 1"psql: error: connection to server at "192.168.0.82", port 5432 failed: FATAL:  password authentication failed for user "appuser"connection to server at "192.168.0.82", port 5432 failed: FATAL:  password authentication failed for user "appuser"[exit 2]env PGCONNECT_TIMEOUT=5 PGPASSWORD=AppUser#2026 psql -h 192.168.0.82 -U appuser -d nosuchdb -w -c "SELECT 1"psql: error: connection to server at "192.168.0.82", port 5432 failed: FATAL:  no pg_hba.conf entry for host "192.168.0.84", user "appuser", database "nosuchdb", SSL encryptionconnection to server at "192.168.0.82", port 5432 failed: FATAL:  no pg_hba.conf entry for host "192.168.0.84", user "appuser", database "nosuchdb", no encryption[exit 2]env PGCONNECT_TIMEOUT=5 PGPASSWORD=AppUser#2026 psql -h 192.168.0.82 -U nobody -d appdb -w -c "SELECT 1"psql: error: connection to server at "192.168.0.82", port 5432 failed: FATAL:  no pg_hba.conf entry for host "192.168.0.84", user "nobody", database "appdb", SSL encryptionconnection to server at "192.168.0.82", port 5432 failed: FATAL:  no pg_hba.conf entry for host "192.168.0.84", user "nobody", database "appdb", no encryption[exit 2]

    Expected resultPassword failure once; no pg_hba.conf entry twice.

    Success conditionYou will check the database and user spelling before editing pg_hba.conf.

  4. Nothing on that port

    Port 5433, where no server is listening.

    pg_isready reports no response. psql reports Connection refused with Is the server running on that host and accepting TCP/IP connections?

    *Refused* is specific: the host is reachable and answered with a TCP reset. Something is on the network at that address, and nothing is bound to that port. It is the message you get from a wrong port, a stopped server, or a server bound to a different address - and never from a firewall that drops packets.

    bash Example session 
    pg_isready -h 192.168.0.82 -p 5433192.168.0.82:5433 - no response[exit 2]env PGCONNECT_TIMEOUT=5 PGPASSWORD=AppUser#2026 psql -h 192.168.0.82 -p 5433 -U appuser -d appdb -w -c "SELECT 1"psql: error: connection to server at "192.168.0.82", port 5433 failed: Connection refused	Is the server running on that host and accepting TCP/IP connections?[exit 2]

    Expected resultno response, then Connection refused.

    Success conditionYou can tell a refused connection from a dropped one.

  5. Listening on the wrong address

    ss -ltnp shows the server on 0.0.0.0:5432 and [::]:5432 - every interface.

    listen_addresses = 'localhost' and a restart changes it to 127.0.0.1:5432 only. This is a postmaster setting; a reload will not do it.

    Now the split that confuses people: the local socket still works perfectly - psql on the server itself is completely happy - while the remote client gets Connection refused. Every check you run while logged into the server passes.

    ss -ltnp is the check that would have found this in seconds, and it is worth running before reading pg_hba.conf at all.

    bash Example session 
    sudo ss -ltnp | grep 5432LISTEN 0      200          0.0.0.0:5432      0.0.0.0:*    users:(("postgres",pid=15069,fd=6))LISTEN 0      200             [::]:5432         [::]:*    users:(("postgres",pid=15069,fd=7))sudo -u postgres psql -c "ALTER SYSTEM SET listen_addresses = 'localhost'"ALTER SYSTEMsudo systemctl restart postgresql@18-mainsudo ss -ltnp | grep 5432LISTEN 0      200        127.0.0.1:5432      0.0.0.0:*    users:(("postgres",pid=15514,fd=6))sudo -u postgres psql -c "SELECT 'local socket still works' AS result"          result-------------------------- local socket still works(1 row)env PGCONNECT_TIMEOUT=5 PGPASSWORD=AppUser#2026 psql -h 192.168.0.82 -U appuser -d appdb -w -c "SELECT 1"psql: error: connection to server at "192.168.0.82", port 5432 failed: Connection refused	Is the server running on that host and accepting TCP/IP connections?[exit 2]

    Expected resultBound to 127.0.0.1 only; local works, remote refused.

    Success conditionYou check what the server is bound to before you suspect the network.

  6. ALTER SYSTEM RESET does not mean undo

    The obvious repair is ALTER SYSTEM RESET listen_addresses. After the restart, it is still bound to 127.0.0.1 only.

    RESET removes the setting from postgresql.auto.conf. It does not restore what was there before - it falls back to whatever postgresql.conf says, and grep -n "^listen_addresses" on that file finds nothing, so the value comes from PostgreSQL's built-in default, which is localhost.

    The * this cluster had been running with came from an earlier ALTER SYSTEM SET, and RESET discarded it.

    Treat ALTER SYSTEM RESET as "delete the override", never as "undo". Setting the value explicitly puts the server back, and pg_isready from the client confirms it.

    bash Example session 
    sudo -u postgres psql -c "ALTER SYSTEM RESET listen_addresses"ALTER SYSTEMsudo systemctl restart postgresql@18-mainsudo ss -ltnp | grep 5432LISTEN 0      200        127.0.0.1:5432      0.0.0.0:*    users:(("postgres",pid=15786,fd=6))sudo grep -n "^listen_addresses" /etc/postgresql/18/main/postgresql.conf [exit 1]sudo -u postgres psql -c "ALTER SYSTEM SET listen_addresses = '*'"ALTER SYSTEMsudo systemctl restart postgresql@18-mainpg_isready -h 192.168.0.82 -p 5432192.168.0.82:5432 - accepting connections

    Expected resultStill 127.0.0.1 after the RESET; correct after an explicit SET.

    Success conditionYou know what RESET actually does to a setting.

  7. Dropped rather than refused

    One iptables rule dropping port 5432, and the symptoms change shape.

    pg_isready says no response. psql says timeout expired - after the five seconds PGCONNECT_TIMEOUT allowed it, not immediately.

    That delay is the diagnosis. A refused connection comes back at once because something sent a reset; a dropped one hangs because nothing replied at all. Refused points at the server - wrong port, wrong bind address, not running. Timed out points at the path between you - a firewall, a security group, a routing problem.

    Without PGCONNECT_TIMEOUT this hangs for the operating system's TCP timeout, which is why the same failure is often described as "psql just hangs". Removing the rule restores the connection immediately.

    bash Example session 
    sudo iptables -I INPUT -p tcp --dport 5432 -j DROPenv PGCONNECT_TIMEOUT=5 pg_isready -h 192.168.0.82 -p 5432192.168.0.82:5432 - no response[exit 2]env PGCONNECT_TIMEOUT=5 PGPASSWORD=AppUser#2026 psql -h 192.168.0.82 -U appuser -d appdb -w -c "SELECT 1"psql: error: connection to server at "192.168.0.82", port 5432 failed: timeout expired[exit 2]sudo iptables -D INPUT -p tcp --dport 5432 -j DROPenv PGCONNECT_TIMEOUT=5 PGPASSWORD=AppUser#2026 psql -h 192.168.0.82 -U appuser -d appdb -w -c "SELECT 'back' AS result" result-------- back(1 row)

    Expected resulttimeout expired rather than Connection refused.

    Success conditionThe shape of the failure tells you which layer to look at.

  8. Put the server back

    The rule comes out of pg_hba.conf, the grant is revoked and the role is dropped - in that order, because a role with an outstanding privilege will not drop.

    Removing a pg_hba.conf line needs a reload to take effect, exactly as adding one did. A rule you deleted but did not reload is still enforcing.

    bash Example session 
    sudo sed -i '/appuser/d' /etc/postgresql/18/main/pg_hba.confsudo -u postgres psql -d appdb -c "REVOKE CONNECT ON DATABASE appdb FROM appuser"REVOKEsudo -u postgres psql -c "DROP ROLE appuser"DROP ROLE

    Expected resultThe rule and the role both gone.

    Success conditionThe lab is back to the access rules it started with.

Troubleshooting

Official sources

← All PostgreSQL guides