Cannot Connect to PostgreSQL Remotely on an Amazon EC2 Instance Using PgAdmin

Question

I have an instance of RHEL 6 with a micro-free RHEL level, and after it is installed postgresql 9.2 using the yum instructions: http://yum.pgrpms.org/howtoyum.php

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

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

However, I never connected to it outside the window. The error message looks like this:

 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 tried a bunch of different ways. Here's what my configuration files look like:

/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 tried to make the above address 0.0.0.0/0, but id did not work.

And every time I made changes, I restarted by running this

 service postgresql-9.2 restart 

In the security group of this EC2 instance, I already see this rule:

 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 the bma question:

If I run the nmap command on the server locally, it looks like it is moving through the internal DNS 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 as suggested by bma]

iptables is as follows:

 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 still cannot connect (same error). What could be missing here?

Answer 1

I found a solution to this problem. Two things are required.

• Use a text editor to modify pg_hba.conf. Find the local host at just 127.0.0.1/0 md5. Immediately after that add this new line: host all all 0.0.0.0/0 md5

• Editing the postgresql.conf PostgreSQL file:

  Use a text editor to modify postgresql.conf. Find the line starting with C # listen_addresses = 'localhost'. Uncomment the line by removing # and change localhost to. Now the line should look like this: listen_addresses = '' # what is the IP address to listen to;

Now just restart the postgres service and it will be able to connect to

Answer 2

Do you have a 5432 firewall blocking port? 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 iptables show on your EC2 for port 5432?

 iptables -nvL 

[after the OP added the details]

Netstat shows that it is listening, but the firewall output does not look like port 5432 is open (I admit that I am not a big network guy). Referring to some of my notes from previous installations, you may need to open EC2 port 5432 to your IP.

To allow incoming access to the firewall, replace YOUR-REMOTE-IP with the IP address from which you are connecting:

 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 after iptables -nvL list. Can you connect?

Answer 3

Causes your pg_hba.conf to skip "+" after the group name. try

# TYPE DATABASE USER ADDRESS METHOD host all pgadmin+ 0.0.0.0/24 trust host all all [my ip]/24 md5

pg_hba.conf explains to the user:

A value of all indicates that it matches all users. Otherwise, it is either the name of a specific database user or the name of the group preceded by +. (Recall that there is no real difference between users and groups in PostgreSQL, the + sign does mean “match any of the roles that are directly or indirectly members of this role,” and the unsigned name matches only that particular role.)