Can't connect to a postgres server

Question

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.

Answer 1

PostgreSQL manual says about listen_addresses this:

listen_addresses (string)

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.

Answer 2

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.