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.