0

I am struggling to connect to a remote Postgres 9.5 instance running on Ubuntu 16.10.
The usual:

psql: could not connect to server: Connection timed out
Is the server running on host "xx.xx.xx.xx" and accepting TCP/IP connections on port 5432?

My postgresql.conf includes

listen_addresses='*'

confirmed by output from

netstat -nlp | grep 5432
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      22981/postgres      
tcp6       0      0 :::5432                 :::*                    LISTEN      22981/postgres      
unix  2      [ ACC ]     STREAM     LISTENING     3320886  22981/postgres       /var/run/postgresql/.s.PGSQL.5432

Apart from my specific IP address & range, I have also tried the following entries in pg_hba.conf:

host    all             all             0.0.0.0/0               md5                                                                                                                                                                                       
host    all             all             ::/0                    md5
host    all             all             all                     md5
host    all             all             *                       md5 

I have disabled ufw and flushed all iptables rules:

iptables -L -n
Chain INPUT (policy ACCEPT)
target     prot opt source               destination         

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination         

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination 

Scanning with nmap from my workstation:

nmap xx.xx.xx.xx -p5432

Starting Nmap 7.01 ( https://nmap.org ) at 2017-02-13 09:17 SAST
Nmap scan report for xx.xx.xx.xx
Host is up (0.0074s latency).
PORT     STATE    SERVICE
5432/tcp filtered postgresql

telnet to xx.xx.xx.xx 5432 times out.

Almost all similar questions on SE seem to be solved with

listen_addresses='*'

which is not an issue here.

Changing the port that Postgres listens on has no effect.

This is a dedicated server with public facing network interface.
The server has SSH & Nginx running, both of which are remotely accessible on the interface.

Really confused by this, help would be greatly appreciated.
Thanks

EDIT: tcpdump output while attempting to psql & telnet into port 5432 using tcpdump switches suggested in comment:

tcpdump -i enp2s0 -s0 -XX -vv -n "tcp port 5432"
tcpdump: listening on enp2s0, link-type EN10MB (Ethernet), capture size 262144 bytes
^C
0 packets captured
2 packets received by filter
0 packets dropped by kernel

Ctrl-C after psql has timed out

  • The port is shown as filtered in nmap output. Can you run tcpdump on your DB server to check whether you are receiving anything or not? – Khaled Feb 14 '17 at 11:17
  • ran tcpdump - results above. Does this mean that packets to port 5432 are filtered out before even getting to the network interface? (Please excuse, I'm a networking novice) – Nel Feb 14 '17 at 14:56
  • This isn't clear. Use tcpdump -i enp2s0 -s0 -XX -vv -n "tcp port 5432". This will show packets with headers. – Khaled Feb 14 '17 at 14:57
  • what about firewall & selinux? – alexus Feb 14 '17 at 15:29
  • selinux not installed, firewall explained above – Nel Feb 14 '17 at 15:41
1

Turns out my hosting provider moved the server behind a managed firewall without notice.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.