Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

PostgreSQL "no pg_hba.conf entry for host": write the rule that matches, then reload

Technical article
Troubleshooting

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

When a client sees no pg_hba.conf entry for host "192.0.2.10", user "appuser", database "appdb", no encryption, the server was reached and then refused the connection because no record in pg_hba.conf matches it. The message gives the four values to match: client address, user, database, and whether the connection used encryption. Write a record that matches them, put it in the right place in the file, check it, and reload.

What the error means

In the PostgreSQL source (branches REL_17_STABLE and REL_19_STABLE), the message is built as no pg_hba.conf entry for host "%s", user "%s", database "%s", %s. The host is printed as a numeric address. The final part is one of no encryption, SSL encryption or, on builds with GSSAPI support, GSS encryption. A walsender (replication) connection gets the variant no pg_hba.conf entry for replication connection from host "%s", user "%s", %s.

The server also writes extra lines to its log about how the client address resolved to a host name, such as Client IP address resolved to "%s", forward lookup matches. These are log-only detail lines in the source, so the client does not see them. If you cannot read the server log, you will not see them.

The PostgreSQL documentation describes the matching rule: "The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no “fall-through” or “backup”: if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied." So this error means that no record matched at all. If a record matched and the password was wrong, the error is different.

A separate message, pg_hba.conf rejects connection for host ..., appears when a record with the reject method matched. That is a deliberate rule, not a missing one.

The file is "read on start-up and when the main server process receives a SIGHUP signal", so edits do nothing until you reload. The documentation names three ways: pg_ctl reload, the SQL function pg_reload_conf(), or kill -HUP. On Microsoft Windows the file is applied by new connections immediately.

Likely causes

Cause What you see How to confirm
No record for the client's address The host in the message is not covered by any host line Compare the host with the address column of each record
Wrong connection type (hostssl against a non-SSL client, hostnossl against SSL) The last part of the message says no encryption while only hostssl records exist, or the reverse Compare the encryption state with the record type
Wrong user or database in the record (including replication) The user or database in the message is not named in any record Read the user and database columns of the rules
IPv4 record for an IPv6 client, or the reverse The host in the message is in the other address family The documentation says an IPv4 entry matches only IPv4 connections and an IPv6 entry only IPv6
Host name record that does not resolve both ways Record uses a name, but the log detail line says the lookup does not match The documentation says the name is checked by reverse then forward lookup
Edit made but not reloaded, or the rule has an error A new line exists in the file but is not in effect Reload is the fix. pg_hba_file_rules reads the file, not the rules the server last loaded, so it cannot show a missing reload; it shows an error value for a line that cannot be applied
listen_addresses or firewall A different error: a refused connection, not this message The message reads "Connection refused", not no pg_hba.conf entry

Prove the cause before changing anything

All of these are read-only.

1. Note the four values from the error. Write down the host, user, database and encryption state exactly as printed.

2. Find the file the server uses. Connect as a superuser over the local socket and ask:

SHOW hba_file;

The documentation says hba_file "Specifies the configuration file for host-based authentication (customarily called pg_hba.conf)", and that it can only be set at server start.

3. List the rules the file currently holds.

SELECT rule_number, file_name, line_number, type, database, user_name, address, auth_method, error FROM pg_hba_file_rules;

The pg_hba_file_rules view has a row for each non-empty, non-comment line, and its rule_number column indicates "the order in which each rule is considered until a match is found during authentication". The view "reports on the current contents of the file, not on what was last loaded by the server", and by default can be read only by superusers. A row with a non-null error marks a line that could not be applied.

4. Compare. Walk the rules in order against your four values. The record must match the connection type, the address range, the database name, and the user. Remember that all matches any database or user, but the replication keyword in the database field matches only physical replication connections, not an ordinary database with that name unless it is quoted.

Fix

Keep a second route to the server, such as the local console or an SSH session, before you start. If a change rejects new connections, you will need it to put the file back.

Save first

Copy the file under a dated name, using the path from SHOW hba_file;:

cp /srv/pgdata/pg_hba.conf /srv/pgdata/pg_hba.conf.saved-20261120

Add a matching record

Disruptive step (Windows): on Windows the edit itself is already live for new connections, so treat the edit as the disruptive step there; the saved copy above is your back-out. On every system, edit the file and add a record in the format the documentation gives: host database user address auth-method. For one application server:

host appdb appuser 192.0.2.10/32 scram-sha-256

The documentation says an address range is written with a CIDR mask length, and that for a single host you use 32 for IPv4 or 128 for IPv6. Place the line before any broader record that would match the same connection first, and before a reject record that covers it. The documentation notes that "the order of the records is significant". The Password Authentication page says of the scram-sha-256 method: "This is the most secure of the currently provided methods, but it is not supported by older client libraries." So confirm that the client library supports it. Do not use trust on a network address; the documentation says it lets anyone who can connect log in "as any PostgreSQL user they wish, without the need for a password".

If the message said no encryption and your records are hostssl, either make the client use SSL (the libpq sslmode option, whose default is prefer, sets whether it tries SSL) or add a record of the type that matches.

Check, then reload

Before reload, query pg_hba_file_rules again. The documentation for pg_reload_conf() says you can use the pg_hba_file_rules view to check the files for possible errors before reloading. Confirm the new rule appears, with a null error, and in the position you intended.

Disruptive step: reloading applies the file to all new connections. A mistaken record can block new connections or admit too many. Reload with the SQL function or the command:

SELECT pg_reload_conf();

or pg_ctl reload -D datadir. The pg_ctl page says reload "simply sends the postgres server process a SIGHUP signal, causing it to reread its configuration files". To back out, run cp /srv/pgdata/pg_hba.conf.saved-20261120 /srv/pgdata/pg_hba.conf, check it with pg_hba_file_rules, and reload again. If new connections are being refused, do the copy and the pg_ctl reload from the second route.

Example (illustrative): db01 has a record host appdb appuser 198.51.100.0/24 scram-sha-256. After a move, the application server app01 at 192.0.2.10 reports no pg_hba.conf entry for host "192.0.2.10", user "appuser", database "appdb", no encryption. pg_hba_file_rules shows no rule covering 192.0.2.10. The administrator saves the file, adds host appdb appuser 192.0.2.10/32 scram-sha-256, confirms the new rule in the view, reloads, and the application connects.

Confirm the fix

  1. pg_hba_file_rules shows the new rule with a null error.
  2. The client connects from the named host with the named user and database.
  3. The server log has no new no pg_hba.conf entry lines for that client.
  4. No other client changed: test one connection that worked before.

What to watch afterwards

  • Keep pg_hba.conf in configuration management, with a record of who changed it.
  • Re-check the file when application servers are added or readdressed.
  • Prefer specific address ranges over 0.0.0.0/0 and avoid trust on network records.
  • Watch the log for repeated no pg_hba.conf entry lines from addresses you do not know.

Risks and limits

  • These commands have not been run or lab-tested. They come from the PostgreSQL documentation and source.
  • Message text is from REL_17_STABLE and REL_19_STABLE; other releases can differ.
  • The way libpq adds its own connection prefix to the server's message is not covered, so only the server's message text is quoted.
  • LDAP, GSSAPI, certificate and PAM authentication, and include directives in the file, are not covered.
  • Whether already-connected sessions are affected by a reload is not stated in the PostgreSQL documentation on this subject.

Sources