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.
PostgreSQL manual says about listen_addresses this:
Specifies the TCP/IP address(es) on which the server is to listen for connections from client applications. The value takes the form of a comma-separated list of host names and/or numeric IP addresses. The special entry * corresponds to all available IP interfaces. The entry 0.0.0.0 allows listening for all IPv4 addresses and :: allows listening for all IPv6 addresses. If the list is empty, the server does not listen on any IP interface at all, in which case only Unix-domain sockets can be used to connect to it. The default value is localhost, which allows only local TCP/IP "loopback" connections to be made. While client authentication (Chapter 20) allows fine-grained control over who can access the server, listen_addresses controls which interfaces accept connection attempts, which can help prevent repeated malicious connection requests on insecure network interfaces. This parameter can only be set at server start.
May be there is some bug with * as listening parameter. Try to set it to 0.0.0.0, it force postgres to listen on all IP-addresses and could fix your issue.
After noticing all these random problems with no apparent cause, I assumed something more the one configuration file was off, I un-installed postgres, cleaned the /var/db/postgres and /usr/etc/postgres folders and then re-installed.
Everything now works as expected, so I am assuming the installation originally silently failed.
User contributions licensed under CC BY-SA 3.0