PostgreSQL 'no pg_hba.conf entry for host' and password authentication failures: a fix-it guide

#The errors and what they mean

#How pg_hba.conf is evaluated

Each line is: TYPE DATABASE USER ADDRESS METHOD [options]. PostgreSQL reads rules top to bottom and uses the first one that matches connection type, database, user and address. If that first match is reject or the password fails, it does not try the next line.

# TYPE    DATABASE  USER       ADDRESS          METHOD
local     all       postgres                    peer
hostssl   sales     app        10.0.0.0/24      scram-sha-256
host      all       all        127.0.0.1/32     scram-sha-256
hostnossl all       all        0.0.0.0/0        reject

• host matches SSL and non-SSL TCP; hostssl only SSL; hostnossl only non-SSL.
• local is Unix sockets only. Connecting with -h localhost uses TCP, so host lines apply.

#Step 1: confirm the server is listening

ss -ltnp | grep 5432
psql -U postgres -c "SHOW listen_addresses;" -c "SHOW port;"

listen_addresses = 'localhost' means remote clients get "connection refused", whatever pg_hba.conf says. Changing it needs a restart.

#Step 2: find and read the active rules

SHOW hba_file;

SELECT line_number, type, database, user_name, address, netmask, auth_method, error
FROM   pg_hba_file_rules
ORDER  BY line_number;

pg_hba_file_rules shows the file as PostgreSQL parses it now, including an error column for lines that won't load. Check it before reloading.

Walk through the rules with the values from the error message (client IP, user, database, SSL or not) and find the first line that matches. That line is what PostgreSQL applied.

#Step 3: add or fix the rule

Add a specific rule above any broader reject line:

hostssl   sales     app        10.0.0.0/24      scram-sha-256

Reload; no restart needed:

SELECT pg_reload_conf();

or pg_ctl reload -D $PGDATA / systemctl reload postgresql. Then check the log for "pg_hba.conf was not reloaded" errors.

Warning

If the edited file has a syntax error, PostgreSQL keeps the old rules on reload, but a restart with a broken file refuses all connections. Always check pg_hba_file_rules for errors before restarting.

#Step 4: fix password method mismatches

PostgreSQL 14+ defaults to password_encryption = scram-sha-256. Problems appear when the stored hash and the rule's method don't line up.

SHOW password_encryption;

SELECT rolname, left(rolpassword, 14) AS hash_type
FROM   pg_authid
WHERE  rolname = 'app';      -- needs superuser

Re-hash the password after setting password_encryption = 'scram-sha-256':

SET password_encryption = 'scram-sha-256';
\password app

Old drivers (very old JDBC, libpq < 10, some ODBC builds) can't do SCRAM. Upgrade the driver rather than downgrading the server to md5.

#Step 5: SSL mismatches

• Client uses sslmode=disable and only hostssl rules exist: "no pg_hba.conf entry ... no encryption". Set sslmode=require (or verify-full) on the client.
• Check whether a session is encrypted:

SELECT pid, usename, client_addr, ssl, version
FROM   pg_stat_ssl JOIN pg_stat_activity USING (pid)
WHERE  usename = 'app';

#Logging that helps

ALTER SYSTEM SET log_connections = on;
SELECT pg_reload_conf();

The server log then records connection attempts, including authentication failures with the user and host.

#Managed services

You can't edit pg_hba.conf directly on managed PostgreSQL:

"no pg_hba.conf entry" on these services usually means an SSL requirement or a firewall rule not covering the client.

#Prevent it next time

• Keep pg_hba.conf in configuration management with comments for every rule's owner.
• Use specific CIDRs and named databases/users, and end the file with an explicit reject.
• Move all roles to SCRAM and set password_encryption = scram-sha-256.
• Require SSL (hostssl) for every non-local connection.