PgAdmin access to AWS Postgres instance in private subnet

Question

I'm trying to create a realistic network setup for a multi-tiered web application. I've created a new VPC within AWS with 1 x public subnet & 2 x private subnet. I then created a Postgres instance within the private subnet and set it to not publicly accessible. This adds an extra layer of security around the database, but how do I then access the database from my local IP?

I created a security group & assigned my IP to the inbound rules & assigned that to the DB instance during creation:

But I still have no way of connecting to it? Do I need to create a VPN and connect to my VPC via the VPN and then connect to the DB instance? Within the proposed architecture, how do you connect to the DB?

What I'm trying to achieve is an architecture which will allow me to create Lambda functions which communicate with the DB via the API Gateway and serve data to a web frontend. So I want the DB protected via the private subnet. But I also want to be able to connect directly to the DB from my local laptop.

At the moment - the RDS instance is running in the VPC, but I don't know how to connect to it. DoI need to set up an Internet Gateway / VPN / EC2 instance and jump to the DB?

Answer 1

You have implemented excellent security by placing the Amazon RDS database into a private subnet. This means it is not accessible from the Internet, which blocks off the majority of potential security threats.

However, it also means that you cannot connect to it from the Internet.

The most common method to achieve your goals is to launch an Amazon EC2 instance in the public subnet and use it as a Bastion or Jump Box:

• You SSH into the Bastion
• The Bastion can then connect you to other resources within the VPC

Since you merely wish to connect to a database (as opposed to logging into another server), the best method is to use SSH with port forwarding.

In Windows, this can be done using your SSH client -- for example, if you are using PuTTY, you can configure Tunnelling. See: How to Configure an SSH Tunnel on PuTTY

For Mac/Linux, use this command:

ssh -i YOUR-KEYPAIR.pem -L 5555:RDS-ENDPOINT:5432 ec2-user@YOUR-BASTION-SERVER

You then point the SQL client on your laptop to: localhost:5555

• The 5555 can be any number you wish. It is merely the "local port" on your own computer that will be used to forward traffic to the remote computer.
• The RDS-ENDPOINT is the Endpoint of your RDS database as supplied in the RDS console. It will be similar to: db.cnrffgvaxtw8.us-west-2.rds.amazonaws.com
• BASTION-SERVER is the IP address or DNS name of the Jump Box you will use to connect

Then, any traffic sent to localhost:5555 from your SQL client will be automatically sent over the SSH connection to the Bastion/Jump Box, which will then forward it to port 5432 on the RDS database. The traffic will be encrypted across the SSH connection, and establishment of the connection requires an SSH keypair.

Answer 2

I referred a lot of articles and videos to find this answer.

yes, you can connect to rds instances in private subnets

we have two ways to connect

1. With server: By using ec2 in the public subnet and using it as a bastion host. we can connect to pg admin by ssh tunneling

2. Serverless: By using client VPN endpoint. create a client VPN endpoint and associate the subnets and allow the internet to the private subnets. and then download the configuration file and install open VPN GUI and import the configuration file and add the keys and then connect the open VPN. Now try to connect to pgadmin, it will connect.

for steps: https://docs.google.com/document/d/1rSpA_kCGtwXOTIP2wwHSELf7j9KbXyQ3pVFveNBihv4/edit )