无法使用PgAdmin在Amazon EC2实例上远程连接到PostgreSQL [英] Cannot connect to PostgreSQL Remotely on Amazon EC2 instance using PgAdmin
问题描述
我正在运行一个微型免费层RHEL 6实例，并使用yum说明在此处安装了postgresql 9.2: http://yum.pgrpms.org/howtoyum.php
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
并且我可以使用服务器上的此本地连接到PG服务器:
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:
/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:
/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
我尝试将上述地址设置为0.0.0.0/0，但id无效.
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
在此EC2实例的安全组中，我已经可以看到此规则:
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
netstat命令显示端口已打开:
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
回答bma的问题:
如果我在本地服务器上运行nmap命令，则似乎建议通过内部DNS将其连接到另一台打开5432的主机:
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
并且iptables命令给出以下输出
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
[根据bma的建议添加后编辑]
iptables看起来像这样:
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?
推荐答案
我找到了解决此问题的方法. 需要两件事.
I Found the resolution to this problem. Two things are required.
-
使用文本编辑器修改pg_hba.conf.找到这行:
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
编辑PostgreSQL postgresql.conf文件:
Editing the PostgreSQL postgresql.conf file:
使用文本编辑器修改postgresql.conf.
Use a text editor to modify postgresql.conf.
找到以#listen_addresses = 'localhost'开头的行.
通过删除#取消注释该行，然后将'localhost'更改为'*'.
Uncomment the line by deleting the #, and change 'localhost' to '*'.
该行现在应如下所示:
listen_addresses = '*' # what IP address(es) to listen on;.
现在，只需重新启动postgres服务，它便可以连接
Now Just restart your postgres service and it will be able to connect
这篇关于无法使用PgAdmin在Amazon EC2实例上远程连接到PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
