Question

Postgresql remote connection not openning necessary ports

Posted July 9, 2014 5.8k views

I bought a VPS from DigitalOcean with applications Rails+Unicorn+Nginx. I installed Postgresql 9.1 and trying to accept remote connections from that. I read all of the solutions/problems about it (Googled much) and did exactly. The problem is the following:

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

I edited the postgresql.conf file with listenaddresses=’*’ I edited the pghba.conf file and added host all all 0.0.0.0/0 md5 I restarted postgresql service and even the VPS however still I cannot connected to the database. So I tried to check the server’s listening ports:

netstat -an | grep 5432
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN     
unix  2      [ ACC ]     STREAM     LISTENING     8356     /tmp/.s.PGSQL.5432

and then I nmap'ed the server from different machine:

Not shown: 996 closed ports
PORT    STATE SERVICE
21/tcp  open  ftp
22/tcp  open  ssh
80/tcp  open  http
554/tcp open  rtsp

But still I cannot understand why postgresql not serving at the port 5432 after the configurations. I thought it might be caused by iptables. So that I add the following rules:

iptables -A INPUT -p tcp -m tcp --dport 5432 -j ACCEPT
iptables -A OUTPUT -p tcp -m tcp --dport 5432 -j ACCEPT

Still no work.
Need advice.

Thanks.

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
1 answer

It definitely seems like you can’t access it due to firewalling. What the full output of iptables -L ?

  • @asb I’m having a similar issue

    I’ve put my iptables output below

    Chain INPUT (policy DROP)
    target     prot opt source               destination         
    ufw-before-logging-input  all  --  anywhere             anywhere            
    ufw-before-input  all  --  anywhere             anywhere            
    ufw-after-input  all  --  anywhere             anywhere            
    ufw-after-logging-input  all  --  anywhere             anywhere            
    ufw-reject-input  all  --  anywhere             anywhere            
    ufw-track-input  all  --  anywhere             anywhere            
    ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:postgresql
    ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:postgresql
    
    Chain FORWARD (policy DROP)
    target     prot opt source               destination         
    ufw-before-logging-forward  all  --  anywhere             anywhere            
    ufw-before-forward  all  --  anywhere             anywhere            
    ufw-after-forward  all  --  anywhere             anywhere            
    ufw-after-logging-forward  all  --  anywhere             anywhere            
    ufw-reject-forward  all  --  anywhere             anywhere            
    ufw-track-forward  all  --  anywhere             anywhere            
    
    Chain OUTPUT (policy ACCEPT)
    target     prot opt source               destination         
    ufw-before-logging-output  all  --  anywhere             anywhere            
    ufw-before-output  all  --  anywhere             anywhere            
    ufw-after-output  all  --  anywhere             anywhere            
    ufw-after-logging-output  all  --  anywhere             anywhere            
    ufw-reject-output  all  --  anywhere             anywhere            
    ufw-track-output  all  --  anywhere             anywhere            
    ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:postgresql
    
    Chain ufw-after-forward (1 references)
    target     prot opt source               destination         
    
    Chain ufw-after-input (1 references)
    target     prot opt source               destination         
    ufw-skip-to-policy-input  udp  --  anywhere             anywhere             udp dpt:netbios-ns
    ufw-skip-to-policy-input  udp  --  anywhere             anywhere             udp dpt:netbios-dgm
    ufw-skip-to-policy-input  tcp  --  anywhere             anywhere             tcp dpt:netbios-ssn
    ufw-skip-to-policy-input  tcp  --  anywhere             anywhere             tcp dpt:microsoft-ds
    ufw-skip-to-policy-input  udp  --  anywhere             anywhere             udp dpt:bootps
    ufw-skip-to-policy-input  udp  --  anywhere             anywhere             udp dpt:bootpc
    ufw-skip-to-policy-input  all  --  anywhere             anywhere             ADDRTYPE match dst-type BROADCAST
    
    Chain ufw-after-logging-forward (1 references)
    target     prot opt source               destination         
    LOG        all  --  anywhere             anywhere             limit: avg 3/min burst 10 LOG level warning prefix "[UFW BLOCK] "
    
    Chain ufw-after-logging-input (1 references)
    target     prot opt source               destination         
    LOG        all  --  anywhere             anywhere             limit: avg 3/min burst 10 LOG level warning prefix "[UFW BLOCK] "
    
    Chain ufw-after-logging-output (1 references)
    target     prot opt source               destination         
    
    Chain ufw-after-output (1 references)
    target     prot opt source               destination         
    
    Chain ufw-before-forward (1 references)
    target     prot opt source               destination         
    ACCEPT     all  --  anywhere             anywhere             ctstate RELATED,ESTABLISHED
    ACCEPT     icmp --  anywhere             anywhere             icmp destination-unreachable
    ACCEPT     icmp --  anywhere             anywhere             icmp source-quench
    ACCEPT     icmp --  anywhere             anywhere             icmp time-exceeded
    ACCEPT     icmp --  anywhere             anywhere             icmp parameter-problem
    ACCEPT     icmp --  anywhere             anywhere             icmp echo-request
    ufw-user-forward  all  --  anywhere             anywhere            
    
    Chain ufw-before-input (1 references)
    target     prot opt source               destination         
    ACCEPT     all  --  anywhere             anywhere            
    ACCEPT     all  --  anywhere             anywhere             ctstate RELATED,ESTABLISHED
    ufw-logging-deny  all  --  anywhere             anywhere             ctstate INVALID
    DROP       all  --  anywhere             anywhere             ctstate INVALID
    ACCEPT     icmp --  anywhere             anywhere             icmp destination-unreachable
    ACCEPT     icmp --  anywhere             anywhere             icmp source-quench
    ACCEPT     icmp --  anywhere             anywhere             icmp time-exceeded
    ACCEPT     icmp --  anywhere             anywhere             icmp parameter-problem
    ACCEPT     icmp --  anywhere             anywhere             icmp echo-request
    ACCEPT     udp  --  anywhere             anywhere             udp spt:bootps dpt:bootpc
    ufw-not-local  all  --  anywhere             anywhere            
    ACCEPT     udp  --  anywhere             224.0.0.251          udp dpt:mdns
    ACCEPT     udp  --  anywhere             239.255.255.250      udp dpt:1900
    ufw-user-input  all  --  anywhere             anywhere            
    
    Chain ufw-before-logging-forward (1 references)
    target     prot opt source               destination         
    
    Chain ufw-before-logging-input (1 references)
    target     prot opt source               destination         
    
    Chain ufw-before-logging-output (1 references)
    target     prot opt source               destination         
    
    Chain ufw-before-output (1 references)
    target     prot opt source               destination         
    ACCEPT     all  --  anywhere             anywhere            
    ACCEPT     all  --  anywhere             anywhere             ctstate RELATED,ESTABLISHED
    ufw-user-output  all  --  anywhere             anywhere            
    
    Chain ufw-logging-allow (0 references)
    target     prot opt source               destination         
    LOG        all  --  anywhere             anywhere             limit: avg 3/min burst 10 LOG level warning prefix "[UFW ALLOW] "
    
    Chain ufw-logging-deny (2 references)
    target     prot opt source               destination         
    RETURN     all  --  anywhere             anywhere             ctstate INVALID limit: avg 3/min burst 10
    LOG        all  --  anywhere             anywhere             limit: avg 3/min burst 10 LOG level warning prefix "[UFW BLOCK] "
    
    Chain ufw-not-local (1 references)
    target     prot opt source               destination         
    RETURN     all  --  anywhere             anywhere             ADDRTYPE match dst-type LOCAL
    RETURN     all  --  anywhere             anywhere             ADDRTYPE match dst-type MULTICAST
    RETURN     all  --  anywhere             anywhere             ADDRTYPE match dst-type BROADCAST
    ufw-logging-deny  all  --  anywhere             anywhere             limit: avg 3/min burst 10
    DROP       all  --  anywhere             anywhere            
    
    Chain ufw-reject-forward (1 references)
    target     prot opt source               destination         
    
    Chain ufw-reject-input (1 references)
    target     prot opt source               destination         
    
    Chain ufw-reject-output (1 references)
    target     prot opt source               destination         
    
    Chain ufw-skip-to-policy-forward (0 references)
    target     prot opt source               destination         
    DROP       all  --  anywhere             anywhere            
    
    Chain ufw-skip-to-policy-input (7 references)
    target     prot opt source               destination         
    DROP       all  --  anywhere             anywhere            
    
    Chain ufw-skip-to-policy-output (0 references)
    target     prot opt source               destination         
    ACCEPT     all  --  anywhere             anywhere            
    
    Chain ufw-track-forward (1 references)
    target     prot opt source               destination         
    
    Chain ufw-track-input (1 references)
    target     prot opt source               destination         
    
    Chain ufw-track-output (1 references)
    target     prot opt source               destination         
    ACCEPT     tcp  --  anywhere             anywhere             ctstate NEW
    ACCEPT     udp  --  anywhere             anywhere             ctstate NEW
    
    Chain ufw-user-forward (1 references)
    target     prot opt source               destination         
    
    Chain ufw-user-input (1 references)
    target     prot opt source               destination         
    ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:ssh
    ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:http
    ACCEPT     udp  --  anywhere             anywhere             udp dpt:80
    ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:https
    ACCEPT     udp  --  anywhere             anywhere             udp dpt:443
    ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:postgresql
    ACCEPT     udp  --  anywhere             anywhere             udp dpt:postgresql
    ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:ssh
    ACCEPT     udp  --  anywhere             anywhere             udp dpt:22
    ACCEPT     tcp  --  anywhere             anywhere             multiport dports http,https /* 'dapp_Nginx%20Full' */
    ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:http /* 'dapp_Nginx%20HTTP' */
    ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:https /* 'dapp_Nginx%20HTTPS' */
    ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:postgresql
    ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:ssh
    
    Chain ufw-user-limit (0 references)
    target     prot opt source               destination         
    LOG        all  --  anywhere             anywhere             limit: avg 3/min burst 5 LOG level warning prefix "[UFW LIMIT BLOCK] "
    REJECT     all  --  anywhere             anywhere             reject-with icmp-port-unreachable
    
    Chain ufw-user-limit-accept (0 references)
    target     prot opt source               destination         
    ACCEPT     all  --  anywhere             anywhere            
    
    Chain ufw-user-logging-forward (0 references)
    target     prot opt source               destination         
    
    Chain ufw-user-logging-input (0 references)
    target     prot opt source               destination         
    
    Chain ufw-user-logging-output (0 references)
    target     prot opt source               destination         
    
    Chain ufw-user-output (1 references)
    target     prot opt source               destination         
    
    
Submit an Answer