Menu
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?
815
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/ . Open the RDS console and then choose Databases to display a list of your DB instances. Choose the PostgreSQL DB instance name to display its details. On the Connectivity & security tab, copy the endpoint.
Specify the “Port” as “5432”. Enter the name of the database in the “Database Maintenance” field. Enter your username as postgres and password (use the same password you used when previously configuring the server to accept remote connections) for the database. Click “Save” to apply the configuration.
I Found the resolution to this problem. Two things are required.
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
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
51
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?
43
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
