title: Cannot connect to PostgreSQL Remotely on Amazon EC2 instance using PgAdmin
body: <p>I have a micro free tier RHEL 6 instance running and have postgresql 9.2 installed using the yum instructions here:
  <a href="http://yum.pgrpms.org/howtoyum.php">http://yum.pgrpms.org/howtoyum.php</a></p>
  
  <p>And I am able connect to the PG server locally using this on server:</p>
  
  <pre><code>03:46:20 root@xxx[~]$ psql -hlocalhost -p5432 -Upostgres
  </code></pre>
  
  <p>However i've never successfully connected to it outside of box. The error message looks like:</p>
  
  <pre><code>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?
  </code></pre>
  
  <p>I've tried a bunch of different ways. Here's how my configure files look now:</p>
  
  <p>/var/lib/pgsql/9.2/data/postgresql.conf:</p>
  
  <pre><code>...
  
  # - 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)
  ...
  </code></pre>
  
  <p>/var/lib/pgsql/9.2/data/pg_hba.conf:</p>
  
  <pre><code># 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
  </code></pre>
  
  <p>I've tried to make the above address to 0.0.0.0/0 but id didn't work.</p>
  
  <p>And every time i made a change i restarted by running this</p>
  
  <pre><code>service postgresql-9.2 restart
  </code></pre>
  
  <p>In the Security Group of this EC2 instance i can see this rule already:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The netstat command shows that the port is already open:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>To answer bma's question:</p>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>And the iptables command gives the following output</p>
  
  <pre><code>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  
  </code></pre>
  
  <p>[Edited after adding according to bma's suggestion]</p>
  
  <p>iptables looks like this after the new addition:</p>
  
  <pre><code>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 
  </code></pre>
  
  <p>But i'm still not able to connect (same error). What could be the missing piece here?</p>