AWS RDS Aurora - How to connect using PgAdmin?

Question

Yesterday AWS launched Aurora serverless for PostgreSQL, but it doesn't seem to have the same configuration options as other RDS databases, I can't set it to public facing for example, it forces me to have a VPC.

Now, I have no clue how to apply these VPC things to PgAdmin, I've tried setting the inbound for the security group to all ports and ips but it still won't connect (no server response).

How can I connect to a RDS Database inside a VPC using PgAdmin? Opening the security group didn't work.

Answer 1

Officially, you can't... Per the docs:

You can't give an Aurora Serverless DB cluster a public IP address. You can access an Aurora Serverless DB cluster only from within a virtual private cloud (VPC) based on the Amazon VPC service.

However, connecting to a serverless DB from a non-Amazon product is just officially discouraged, it is not impossible.

The best solution I have found so far is to create an autoscaling cluster of bastion boxes within the same VPC. Then use them to tunnel through. The great part about this strategy is that it exposes a standard postgre format URL, so it can be used with pgAdmin, Navicat, ActiveRecord or any other ORM that uses typical connection urls.

...The bad part is that (so far) it seems to enforce a 30 sec timeout on connections. So you better get all your transactions wrapped up quick like.

If anyone can do better, I'd love to hear how as well.

Answer 2

I realize this question is old, but I kept coming back to it as I worked this out.

This solution is similar to @genkilabs solution but simpler.

Steps:

1. Spin up an ec2 micro instance in the same vpc as the database. You will tunnel through this.

2. Add the security group for your ec2 to the inbound rules of the database's security group.

3. ssh into the ec2 instance and install psql (and postgress...) with:

sudo amazon-linux-extras install postgresql10

4. Verify that you can connect to your database with psql:

psql -h {server} -p 5432 -U {database username} -d {database name} -p

5. In PGAdmin create a new server connection

• Enter the database host, username, and password as usual.
• Go to the SSH Tunnel tab
  • turn on ssh tunneling
  • enter your ec2 hostname for the tunnel host
  • enter your ssh username
  • select the identity file and find the .pem or .cer file for your ec2 instance.

Save and done. You should now be able to connect to the serverless Aurora database from your local PGAdmin.

If you have trouble connecting to the database form the ec2, this guide may be helpful. The same steps apply connecting from ec2 as from cloud9.