1

As background when connecting from remote (eg home mostly using JDBC) due to how the VPN works I always get a different IP address and hence I can't connect because the IP isn't listed in pg_hba.conf. Hence I tried with host name but that doesn't work either.

How can I allow access to PostgreSQL by hostname and not IP address?

EDIT:

I found the following which seems more suitable to place here than the comments:

https://www.postgresql.org/message-id/[email protected]

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

So while ping with host name works, the reverse dns lookup doesn't work and hence according to above text explains why connection then fails. So it for sure seems the way the VPN works is to blame for this as nslookup indeed fails to find a name.

3
  • Please add that quote as an answer, then you can accept that answer so that your question is marked as resolved.
    – user1822
    Commented Feb 21, 2020 at 14:16
  • If you are connecting through a VPN, that is almost surely acting as a firewall as well, denying any incoming connections that aren't through the VPN. So why do you need a restrictive IP address in pg_hba.conf on top of the VPN already keeping the bad guys out?
    – jjanes
    Commented Feb 22, 2020 at 23:27
  • @jjanes It's also for keeping anyone internal out that shouldn't have access. Following the principle of white-listing. Only allow those that need it.
    – beginner_
    Commented Feb 24, 2020 at 5:08

2 Answers 2

1

Quote from the manual

address

Specifies the client machine address(es) that this record matches. This field can contain either a host name, an IP address range, or ...

(emphasis mine)

So putting a hostname into pg_hba.conf is supported.

During the initial connection request, Postgres only sees the IP address (as part of the TCP protocol) and needs to do a lookup to resolve the IP address to a hostname. This only works if the client and the Postgres server are in the same network and are using the same DNS server(s).

My assumption is, that your VPN solution is preventing that hostname lookup to work properly.

If you always get your IP addresses from the same range, you could configure that range in pg_hba.conf rather than a single address (or hostname)

4
  • I'm not a network specialist so here my additions: From the server the database is running on I can ping my laptop using host name. So shouldn't this be working correctly? (ping in windows cmd, all machines are windows). The range seems to be the same but I assume that would theoretically allow someone else to connect from the same range...which I want to avoid.
    – beginner_
    Commented Feb 21, 2020 at 13:46
  • If you can ping the hostname of your computer from the Postgres server, then this should work.
    – user1822
    Commented Feb 21, 2020 at 13:55
  • @beginner_: you might need to specify the hostname including the domain.
    – user1822
    Commented Feb 21, 2020 at 14:07
  • Indeed I'm doing that
    – beginner_
    Commented Feb 21, 2020 at 14:10
1

Possible Explanation for my issue:

https://www.postgresql.org/message-id/[email protected]

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.