Cannot connect to PostgreSQL Remotely on Amazon EC2 instance using PgAdmin

Question

I have a micro free tier RHEL 6 instance running and have postgresql 9.2 installed using the yum instructions here: http://yum.pgrpms.org/howtoyum.php

And I am able connect to the PG server locally using this on server:

03:46:20 root@xxx[~]$ psql -hlocalhost -p5432 -Upostgres

However i've never successfully connected to it outside of box. The error message looks like:

12:11:56 saladinxu@GoodOldMBP[~]$ psql -h ec2-xxx.ap-southeast-1.compute.amazonaws.com -p5432 -Upostgres
    psql: could not connect to server: Connection refused
    Is the server running on host "ec2-54-251-188-3.ap-southeast-1.compute.amazonaws.com" (54.251.188.3) and accepting TCP/IP connections on port 5432?

I've tried a bunch of different ways. Here's how my configure files look now:

/var/lib/pgsql/9.2/data/postgresql.conf:

...

# - Connection Settings -

listen_addresses = '*'      # what IP address(es) to listen on;
                # comma-separated list of addresses;
                # defaults to 'localhost'; use '*' for all
port = 5432             # (change requires restart)
max_connections = 100           # (change requires restart)
...

/var/lib/pgsql/9.2/data/pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             pgadmin         0.0.0.0/24              trust
host    all             all             [my ip]/24         md5
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident

I've tried to make the above address to 0.0.0.0/0 but id didn't work.

And every time i made a change i restarted by running this

service postgresql-9.2 restart

In the Security Group of this EC2 instance i can see this rule already:

TCP
Port (Service)  Source  Action
22 (SSH)    0.0.0.0/0   Delete
80 (HTTP)   0.0.0.0/0   Delete
5432    0.0.0.0/0   Delete

The netstat command shows that the port is already open:

04:07:46 root@ip-172-31-26-139[~]$ netstat -na|grep 5432
tcp        0      0 0.0.0.0:5432                0.0.0.0:*                   LISTEN      
tcp        0      0 :::5432                     :::*                        LISTEN      
unix  2      [ ACC ]     STREAM     LISTENING     14365  /tmp/.s.PGSQL.5432

To answer bma's question:

If I run the nmap command on the server locally it seems to suggest that thru internal DNS it's going to another host where 5432 is open:

10:16:05 root@ip-172-31-26-139[~]$ nmap -Pnv -p 5432 ec2-54-251-188-3.ap-southeast-1.compute.amazonaws.com

Starting Nmap 5.51 ( http://nmap.org ) at 2013-07-22 10:16 EDT
Nmap scan report for ec2-54-251-188-3.ap-southeast-1.compute.amazonaws.com (172.31.26.139)
Host is up (0.00012s latency).
rDNS record for 172.31.26.139: ip-172-31-26-139.ap-southeast-1.compute.internal
PORT     STATE SERVICE
5432/tcp open  postgresql

Nmap done: 1 IP address (1 host up) scanned in 0.07 seconds

And the iptables command gives the following output

10:16:14 root@ip-172-31-26-139[~]$ iptables -nvL
Chain INPUT (policy ACCEPT 0 packets, 0 bytes)
pkts bytes target     prot opt in     out     source               destination         
25776   14M ACCEPT     all  --  *      *       0.0.0.0/0            0.0.0.0/0           state RELATED,ESTABLISHED 
0     0 ACCEPT     icmp --  *      *       0.0.0.0/0            0.0.0.0/0           
45  1801 ACCEPT     all  --  lo     *       0.0.0.0/0            0.0.0.0/0           
251 15008 ACCEPT     tcp  --  *      *       0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:22 
35  2016 REJECT     all  --  *      *       0.0.0.0/0            0.0.0.0/0           reject-with icmp-host-prohibited 

Chain FORWARD (policy ACCEPT 0 packets, 0 bytes)
pkts bytes target     prot opt in     out     source               destination         
0     0 REJECT     all  --  *      *       0.0.0.0/0            0.0.0.0/0           reject-with icmp-host-prohibited 

Chain OUTPUT (policy ACCEPT 21695 packets, 5138K bytes)
pkts bytes target     prot opt in     out     source               destination  

[Edited after adding according to bma's suggestion]

iptables looks like this after the new addition:

11:57:20 root@ip-172-31-26-139[~]$ iptables -nvL
Chain INPUT (policy ACCEPT 0 packets, 0 bytes)
 pkts bytes target     prot opt in     out     source               destination         
26516   14M ACCEPT     all  --  *      *       0.0.0.0/0            0.0.0.0/0           state RELATED,ESTABLISHED 
0     0 ACCEPT     icmp --  *      *       0.0.0.0/0            0.0.0.0/0           
47  1885 ACCEPT     all  --  lo     *       0.0.0.0/0            0.0.0.0/0           
255 15236 ACCEPT     tcp  --  *      *       0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:22 
38  2208 REJECT     all  --  *      *       0.0.0.0/0            0.0.0.0/0           reject-with icmp-host-prohibited 
0     0 ACCEPT     tcp  --  *      *       [my ip]         54.251.188.3        tcp spts:1024:65535 dpt:5432 state NEW,ESTABLISHED 
0     0 ACCEPT     tcp  --  *      *       0.0.0.0/0            54.251.188.3        tcp spt:5432 dpts:1024:65535 state ESTABLISHED 

Chain FORWARD (policy ACCEPT 0 packets, 0 bytes)
pkts bytes target     prot opt in     out     source               destination         
0     0 REJECT     all  --  *      *       0.0.0.0/0            0.0.0.0/0           reject-with icmp-host-prohibited 

Chain OUTPUT (policy ACCEPT 5 packets, 1124 bytes)
pkts bytes target     prot opt in     out     source               destination         
0     0 ACCEPT     tcp  --  *      *       54.251.188.3         [my ip]        tcp spt:5432 dpts:1024:65535 state ESTABLISHED 
0     0 ACCEPT     tcp  --  *      *       54.251.188.3         0.0.0.0/0           tcp spts:1024:65535 dpt:5432 state NEW,ESTABLISHED 

But i'm still not able to connect (same error). What could be the missing piece here?

Answer 1

I Found the resolution to this problem. Two things are required.

1. Use a text editor to modify pg_hba.conf. Locate the line:

   host all all 127.0.0.1/0 md5.

   Immediately below it, add this new line:

   host all all 0.0.0.0/0 md5

2. Editing the PostgreSQL postgresql.conf file:

   Use a text editor to modify postgresql.conf.

   Locate the line that starts with #listen_addresses = 'localhost'.

   Uncomment the line by deleting the #, and change 'localhost' to '*'.

   The line should now look like this:

   listen_addresses = '*' # what IP address(es) to listen on;.

Now Just restart your postgres service and it will be able to connect

Answer 2

Do you have a firewall blocking port 5432? A quick nmap shows that it is being filtered.

nmap -Pnv -p 5432 ec2-54-251-188-3.ap-southeast-1.compute.amazonaws.com

Starting Nmap 6.00 ( http://nmap.org ) at 2013-07-21 11:05 PDT
Nmap scan report for ec2-54-251-188-3.ap-southeast-1.compute.amazonaws.com (54.251.188.3)
Host is up (0.19s latency).
PORT     STATE    SERVICE
5432/tcp filtered postgresql

What does the iptables on your EC2 show for port 5432?

iptables -nvL

[after OP added more details]

Netstat shows that it is listening, but the firewall output doesn't look like the 5432 port is open (I confess to not being much of a network guy). Referring to some of my notes from previous installs, you might need to open up EC2 port 5432 to your IP.

To allow input firewall access, replace YOUR-REMOTE-IP with the IP you are connecting from:

iptables -A INPUT -p tcp -s YOUR-REMOTE-IP --sport 1024:65535 -d 54.251.188.3 --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s 54.251.188.3 --sport 5432 -d YOUR-REMOTE-IP --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

--outbound access

iptables -A OUTPUT -p tcp -s 54.251.188.3 --sport 1024:65535 -d 0/0 --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A INPUT -p tcp -s 0/0 --sport 5432 -d 54.251.188.3 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

What does iptables -nvL list after that. Can you connect?