Can't connect remotely to Postgres database

Question

Running Postgresql 9.5 on Ubuntu 16.04.

My /etc/postgresql/9.5/main/pg_hba.conf:

local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5
#remote connection 
host    all             all            0.0.0.0/0              trust

My /etc/postgresql/9.5/main/postgresql.conf:

# - Connection Settings -

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             # (change requires restart)
max_connections = 100           # (change requires restart)
#superuser_reserved_connections = 3 # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
                    # (change requires restart)
#unix_socket_group = ''         # (change requires restart)
#unix_socket_permissions = 0777     # begin with 0 to use octal notation
                    # (change requires restart)
#bonjour = off              # advertise server via Bonjour
                    # (change requires restart)
#bonjour_name = ''          # defaults to the computer name
                    # (change requires restart)

# - Security and Authentication -

#authentication_timeout = 1min      # 1s-600s
#ssl = true             # (change requires restart)
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
                    # (change requires restart)

My netstat -nlp | grep 5432:

tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      22621/postgres  
tcp6       0      0 :::5432                 :::*                    LISTEN      22621/postgres  
unix  2      [ ACC ]     STREAM     LISTENING     10261951 22621/postgres      /var/run/postgresql/.s.PGSQL.5432

I'm able to connect locally, but can't connect remotely. When I try to connect from another computer, the command times out.

Are you sure there is no appliance, etc. blocking TCP 5432? What happens when you telnet to TCP 5432 from the remote machine? — Pancake
telnet <myprivateIPaddress> 5432 --> Trying XXX.XXX.XX.XXX........ connection times out — user2593562

Z4-tier Z4-tier · Accepted Answer · 2018-08-15T19:31:23

make sure you don't have a firewall running on the same host as your database that is blocking 5432. I'm not an Ubuntu wizard, but I think you can do

ufw disable

to turn it off. If that doesn't work, try

service iptables stop

or possibly

iptables -F

Note that these will (of course) turn off your firewall, so be aware of that. The last one will flush your firewall rules so you will need to re-read your ruleset if you want to turn it back on.

and as @Pancake suggested, see what happens if you simply telnet to 5432 from another host (or use netcat, etc...).

Can't connect remotely to Postgres database

1 Answers