1

I'm trying to test a postgresql database connectivity using psql command line client. There are several network interfaces on the server and several network interfaces on the client. Each network interface corresponding to a network plane. In pg_hba.conf on server, all connections are denied by default and the clients (actually other servers) who are authorized to connect are granted access using "host" records. Those "host" records only correspond to one network plane.

When I try to connect from client using psql, I had the following error message:

psql: FATAL:  no pg_hba.conf entry for host "1.2.3.4", user "FOO", database "BAR", SSL off

As this is a test environment, I added new "host" records with the hostnames / ip addresses corresponding to the other network plane and it worked. The default route on client uses a network interface that is not the postgresql network plane and this is what I want from a system point of view.

That said, to ease testing, is possible to keep my current routes on client and only the right hosts in pg_hba.conf and bind postgresql client on a non-default network interface? I checked psql man, but found nothing.

I found a thread mentioning a "%interface_name" at the end of -h host parameter for ipv6. I tried with my hostname (resolved to an ipv4 address), but it didn't work:

psql: could not translate host name "...%eth0" to address: Name or service not known

Thank you

  • Since psql (and in fact libpq) doesn't have an option to bind() a socket to an address before connect(), it's the OS kernel that decides which network interface gets used based on the routing table. Whatever solution there is, it will be at the OS/networking level, not in PostgreSQL. – Daniel Vérité Oct 17 '17 at 12:02
  • that was my assumption as I'm not a postgresql expert, but at least it is confirmed. Thank you! – yohann.martineau Oct 17 '17 at 14:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.