postgresql 从远程机器连接到 RDS Postgres

Connecting to RDS Postgres from remote machine

提问by Chris Hinshaw

I have configured an RDS Postgres micro instance for testing but am having problem connecting. I have created a security group and allow all hosts (0.0.0.0/0) by default to port 5432 but for some reason I am not able to connect. I tried connecting to the port 5432 on the machine but it appears to be closed. Any ideas? I have verified that the security group is using the correct security group.

我已经配置了一个 RDS Postgres 微型实例进行测试，但连接出现问题。我创建了一个安全组并默认允许所有主机 (0.0.0.0/0) 使用端口 5432，但由于某种原因我无法连接。我尝试连接到机器上的端口 5432，但它似乎已关闭。有任何想法吗？我已经验证安全组使用的是正确的安全组。

This is my configuration

这是我的配置

TCP
Port (Service)  Source  Action
5432    0.0.0.0/0   Delete

Here is how I am trying to connect to the rds service

这是我尝试连接到 rds 服务的方式

psql -p 5432 -h example.cs945smhrv09.us-west-2.rds.amazonaws.com -U example example

UPDATE

更新

OK feel a little silly after figuring out that rds starts up on a private 172 subnet. I was able to connect from my ec2 instance.

确定 rds 在私有 172 子网上启动后感觉有点傻。我能够从我的 ec2 实例连接。

Question now is there a way to put that on a public address that I can connect to? I would like to configure it with a security group that limits the source ip but then be able to connect to it with psql from my desktop.

现在的问题是有没有办法把它放在我可以连接的公共地址上？我想用一个安全组来配置它，限制源 ip 但然后能够从我的桌面使用 psql 连接到它。

Update 2

更新 2

I don't remember all the details when I originally posted this question but it is no longer an issue. We now have a management vpc that we connect to using a vpn server. The management vpc network is trusted by the prod and devel security groups for the rds instances. We have no problem communicating with them without adding any public ips.

我不记得我最初发布这个问题时的所有细节，但这不再是一个问题。我们现在有一个管理 vpc，我们使用 vpn 服务器连接到它。管理 vpc 网络受到 rds 实例的 prod 和 devel 安全组的信任。我们在不添加任何公共 ip 的情况下与他们通信没有问题。

采纳答案by Gord Stephen

Step 4 of the RDS setup wizard ("Additional Config") lets you select whether the DB is Publicly Accessible or not (if it's not, access is limited to within your VPC, regardless of your security group settings). I'd imagine that determines whether the hostname resolves to 54.* vs 172.* .

RDS 设置向导的第 4 步（“附加配置”）让您选择数据库是否可公开访问（如果不是，则访问仅限于您的 VPC 内，无论您的安全组设置如何）。我想这决定了主机名是否解析为 54.* 与 172.* 。

Your first instance may have been set to private - as far as I can tell this can't be changed after initialization. I had a similar issue and creating a new instance set to be publicly accessible solved it for me.

您的第一个实例可能已设置为私有 - 据我所知，初始化后无法更改。我有一个类似的问题，创建一个可公开访问的新实例为我解决了这个问题。

回答by J Dawkins

My company's firewall had blocked outgoing traffic to the default postgres port, 5432. I didn't realise this until I tested the port using netcat in a terminal window, as follows:

我公司的防火墙阻止了到默认 postgres 端口 5432 的传出流量。直到我在终端窗口中使用 netcat 测试端口时我才意识到这一点，如下所示：

nc -zv portquiz.net 5432

Hopefully this saves someone half a day of messing around with AWS security groups.

希望这可以为某人节省半天的时间来处理 AWS 安全组。

回答by Joe Love

Absolutely there is. I have pgadmin loaded on my local machine connected to my RDS instance. |

绝对有。我在连接到我的 RDS 实例的本地机器上加载了 pgadmin。|

I just confirmed that I can connect using the same syntax you used for psql, although the -U was not necessary for me (I still included both "example" parameters though)

我刚刚确认我可以使用与 psql 相同的语法进行连接，尽管 -U 对我来说不是必需的（尽管我仍然包含两个“示例”参数）

I did edit the "default" security group and added my IP (Ip of the machine I'm connecting from)/32 to be allowed, although I'm sure you could get less restrictive than that, but I'd start there and see if it helps.

我确实编辑了“默认”安全组并添加了我的 IP（我从中连接的机器的 IP）/32 被允许，虽然我确定你可以得到比这更少的限制，但我会从那里开始看看它是否有帮助。

回答by Neil

This is likely caused by your outbound firewall rules. If you're behind a corporate firewall, they will often block outbound ports above a certain number. You need to setup an SSH tunnel of some kind to avoid this. http://conoroneill.net/accessing-amazon-rds-instances-from-your-desktop/

这可能是由您的出站防火墙规则引起的。如果您在公司防火墙后面，他们通常会阻止超过一定数量的出站端口。您需要设置某种类型的 SSH 隧道来避免这种情况。 http://conoroneill.net/accessing-amazon-rds-instances-from-your-desktop/

回答by Tim

I had this problem. This isn't a direct solution but might work for some. I set the Postgres security group to accept connections coming from the server that will be using it (same VPC). I then set up my Postgres client to use ssh tunneling. If I need to use the command line, I simply SSH into that server and then psqlfrom there.

我有这个问题。这不是一个直接的解决方案，但可能对某些人有用。我将 Postgres 安全组设置为接受来自将使用它的服务器（同一 VPC）的连接。然后我设置我的 Postgres 客户端以使用 ssh 隧道。如果我需要使用命令行，我只需通过 SSH 连接到该服务器，然后psql从那里开始。

回答by sanjeet pal

You can simply use workbench recommended by aws but make sure your all postgres availibily is set to everywhere..while adding PostgrSQL 5432

您可以简单地使用 aws 推荐的工作台，但请确保您的所有 postgres 可用设置为无处不在..同时添加 PostgrSQL 5432

https://aws.amazon.com/getting-started/tutorials/create-connect-postgresql-db/

https://aws.amazon.com/getting-started/tutorials/create-connect-postgresql-db/