无法使用 PgAdmin 在 Amazon EC2 实例上远程连接到 PostgreSQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17770329/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Cannot connect to PostgreSQL Remotely on Amazon EC2 instance using PgAdmin
提问by saladinxu
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
我有一个微型免费层 RHEL 6 实例正在运行，并使用此处的 yum 说明安装了 postgresql 9.2：http: //yum.pgrpms.org/howtoyum.php
And I am able connect to the PG server locally using this on server:
我可以在服务器上使用它在本地连接到 PG 服务器：
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
I've tried to make the above address to 0.0.0.0/0 but id didn't work.
我试图将上述地址设为 0.0.0.0/0 但 id 不起作用。
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:
在此 EC2 实例的安全组中，我已经可以看到此规则：
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:
netstat 命令显示端口已经打开：
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:
回答bma的问题：
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:
如果我在本地服务器上运行 nmap 命令，它似乎表明通过内部 DNS 它将转到另一台打开 5432 的主机：
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
iptables 命令提供以下输出
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]
[根据bma的建议添加后编辑]
iptables looks like this after the new addition:
新添加后的 iptables 看起来像这样：
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?
但我仍然无法连接（同样的错误）。这里丢失的部分可能是什么？
回答by javacreed
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 md5Editing 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;.
使用文本编辑器修改 pg_hba.conf。找到该行：
host all all 127.0.0.1/0 md5.在它的正下方，添加以下新行：
host all all 0.0.0.0/0 md5编辑 PostgreSQL postgresql.conf 文件：
使用文本编辑器修改 postgresql.conf。
找到以 开头的行
#listen_addresses = 'localhost'。通过删除 取消注释该行
#，然后更改'localhost'为'*'。该行现在应如下所示：
listen_addresses = '*' # what IP address(es) to listen on;.
Now Just restart your postgres service and it will be able to connect
现在只需重新启动您的 postgres 服务，它将能够连接
回答by bma
Do you have a firewall blocking port 5432? A quick nmap shows that it is being filtered.
您是否有阻止端口 5432 的防火墙？一个快速的 nmap 显示它正在被过滤。
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?
EC2 上的 iptables 为端口 5432 显示了什么？
iptables -nvL
[after OP added more details]
[OP添加更多细节后]
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.
Netstat 显示它正在侦听，但防火墙输出看起来不像 5432 端口是打开的（我承认我不是一个网络专家）。参考我之前安装的一些笔记，您可能需要向您的 IP 开放 EC2 端口 5432。
To allow input firewall access, replace YOUR-REMOTE-IP with the IP you are connecting from:
要允许输入防火墙访问，请将 YOUR-REMOTE-IP 替换为您正在连接的 IP：
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 -nvLlist after that. Can you connect?
iptables -nvL之后列出什么。你能连接吗？
回答by CloudStax
Looks your pg_hba.conf misses the "+" after the group name. try
看起来您的 pg_hba.conf 缺少组名后的“+”。尝试
# TYPE DATABASE USER ADDRESS METHOD
host all pgadmin+ 0.0.0.0/24 trust
host all all [my ip]/24 md5
# TYPE DATABASE USER ADDRESS METHOD
host all pgadmin+ 0.0.0.0/24 trust
host all all [my ip]/24 md5
The pg_hba.confexplains about user:
在pg_hba.conf中解释了有关用户：
The value all specifies that it matches all users. Otherwise, this is either the name of a specific database user, or a group name preceded by +. (Recall that there is no real distinction between users and groups in PostgreSQL; a + mark really means "match any of the roles that are directly or indirectly members of this role", while a name without a + mark matches only that specific role.)
值 all 指定它匹配所有用户。否则，这要么是特定数据库用户的名称，要么是前面带有 + 的组名。（回想一下，PostgreSQL 中的用户和组之间没有真正的区别；+ 标记的真正含义是“匹配任何直接或间接属于该角色的角色”，而没有 + 标记的名称仅匹配该特定角色。 )
