I have a strange situation, I have created a second postgres server that will eventually become a slave to my current master, anyway for testing purposes I have currently installed postgres, and testing connections from other hosts.
In my postgresql.conf I have a nice an easy:
listen_addresses = '*'# what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432
Then In my pg_hba.conf I have:
# allow all connections, testing only, comment in production host all all 0.0.0.0/0 trust
This postgres server is running in a freebsd jail, so has two IP addresses:
root@postgres ~# telnet 10.1.1.19 5432 Trying 10.1.1.19... telnet: connect to address 10.1.1.19: Connection refused telnet: Unable to connect to remote host root@postgres ~# telnet 127.0.1.19 5432 1 Trying 127.0.1.19... Connected to 127.0.1.19. Escape character is '^]'. ^CConnection closed by foreign host. root@postgres ~# ifconfig em1: flags=8843<UP,BROADCAST,RUNNING,SIMPLEX,MULTICAST> metric 0 mtu 1500 options=4219b<RXCSUM,TXCSUM,VLAN_MTU,VLAN_HWTAGGING,VLAN_HWCSUM,TSO4,WOL_MAGIC,VLAN_HWTSO> ether 00:25:90:27:d8:24 inet 10.1.1.19 netmask 0xffffffff broadcast 10.1.1.19 media: Ethernet autoselect (1000baseT <full-duplex>) status: active lo1: flags=8049<UP,LOOPBACK,RUNNING,MULTICAST> metric 0 mtu 16384 options=600003<RXCSUM,TXCSUM,RXCSUM_IPV6,TXCSUM_IPV6> inet 127.0.1.19 netmask 0xffffffff groups: lo
So as you can see, I can on port 5432 but only using the loopback address, so then I try a simple
root@postgres ~# psql -h 127.0.1.19 psql: FATAL: no pg_hba.conf entry for host "127.0.1.19", user "root", database "root", SSL off
Why is this basic connection getting blocked, as my hba.conf has ALL and in addition why can I not connect via the local address of 10.1.1.19?
netstat shows the following output:
Proto Recv-Q Send-Q Local Address Foreign Address (state) tcp4 0 0 127.0.1.19.postgresql *.* LISTEN
NOTE.. I have another jail setup on another server with seemingly the same setup which works, thats on version 9.3.5 and this new server (with the issue) is on 9.6.3
EDIT: When changing config to listen on 0.0.0.0 I get a netstat output:
Proto Recv-Q Send-Q Local Address Foreign Address (state) tcp4 0 0 127.0.1.19.postgresql *.* LISTEN tcp4 0 0 10.1.1.19.3100 *.* LISTEN
You can see that SSH is successfully able to listen on the LAN address of 10.1.1.19 (on port 3100) so it can't be a jail networking issue, it must be something postgres related.