PostgreSQL "Could not resolve client IP address to a host name" error
Using hostnames in pg_hba.conf
Since PostgreSQL 9.1 it's possible to use host names in pg_hba.conf.
I'm using it in a server and I got this error from a Java's client application trying to connect to it:
Caused by: org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host "192.168.1.2", user "my_user", database "my_database", SSL off
In PostgreSQL's log I found:
2016-05-26 17:31:52 CEST - 32745 - FATAL: no pg_hba.conf entry for host "192.168.1.2", user "my_user", database "my_database", SSL off
2016-05-26 17:31:52 CEST - 32745 - DETAIL: Could not resolve client IP address to a host name: Name or service not known.
The reason is that the reverse DNS lookup wasn't configured for this client:
[user@server /]# host 192.168.1.2 Host 18.104.22.168.in-addr.arpa. not found: 3(NXDOMAIN)
While the server is reached:
[root@eon pg_log]# ping myserver.mydomain.com PING myserver.mydomain.com (192.168.1.2) 56(84) bytes of data. 64 bytes from 192.168.1.2: icmp_seq=1 ttl=64 time=1.67 ms
PostgreSQL documentation is awesome and it explains it:
If a host name is specified (anything that is not an IP address range or a special key word is treated as a host name), that name is compared with the result of a reverse name resolution of the client's IP address (e.g., reverse DNS lookup, if DNS is used). Host name comparisons are case insensitive. If there is a match, then a forward name resolution (e.g., forward DNS lookup) is performed on the host name to check whether any of the addresses it resolves to are equal to the client's IP address. If both directions match, then the entry is considered to match. (The host name that is used in pg_hba.conf should be the one that address-to-name resolution of the client's IP address returns, otherwise the line won't be matched. Some host name databases allow associating an IP address with multiple host names, but the operating system will only return one host name when asked to resolve an IP address.)
I like to use host names in pg_hba because it's easier to read, but there're some side effects that you should be aware of. That's why I highly recommend to read PostgreSQL documentation, where there're some good suggestions, like:
When host names are specified in pg_hba.conf, you should make sure that name resolution is reasonably fast. It can be of advantage to set up a local name resolution cache such as nscd. Also, you may wish to enable the configuration parameter log_hostname to see the client's host name instead of the IP address in the log.