Connecting to RDS DATABASE via SSH TUNNEL

Let's say you work for an E-commerce company and all your inventories are stored in a Postgres database hosted in AWS RDS server. Your business would mostly have one need and that is the need to query tables in the database from a management tool like PG Admin, Dbeaver, etc.

You might also have Data Analysts who need to query tables in the Database to get insight into some of the datasets and create reports. They need to be able to do this from their local machines.

This can be a pain to get them connected for few Security reasons

• Your database server lives in a private subnet
• The Database public Accessibility is blocked (Set to false)
• The Database is in a security group that only allows traffic on specific IP addresses

What do we do in this situation? Allow public access and change the security group? Hell to the nawwwww! This is the fastest way to fired from your job.

The is a way out! Use SSH Tunnel with few easy steps below

• Create Security Groups
• Create RDS Database (With Appropriate configuration)
• Create bastion host
• Install PG Admin on your local Machine
• Connect and Enjoy!

Create Security Groups

We will start by first creating two Security groups. One for Our Database server and the other for our Bastion Host

1. Log into your AWS Account and navigate to ec2 console. On the left panel, scroll down under Network & Security and select Security groups.
2. We are creating our bastion host Security Group first. Click Create Security Group and fill the same details like the below. Pay attention to the inbound rule. We are allowing SSH from anywhere (Not ideal for Prod. We can lock down by IPs too)

Click "create security Group" and we are done with the bastion host SG.

3. Navigate back to Security Group and Create another Security Group for our Database. Fill the information below. For our Database Security Group, we need to allow traffic on port 5432 (default port for Postgres), choosing the bastion host security group(created above) as our source. Click on "Add Rule" to do that. You will then have something similar to the below)

Notice that we are choosing myBastionSG (Created initially) as the source here (Important!)

Go ahead and click "Create Security Group" and we should be good.

Create RDS Database (With Appropriate configuration)

Navigate to RDS on the console and click on "Create Database". You should see something similar to the below image

Choose PostgreSQL Engine Type, and Choose Dev/Test as template. Set your Credentials under credentials Settings (Keep them somewhere to remember)

Leave DB Instance Class and Storage at their Default Values. Under Connectivity, point to default VPC (Not ideal for prod). Make sure public Access is set to "No". For the VPC Security Group, select the dropdown for existing and choose the database security group we create earlier (myDbSG). Everything else is fine as default.

You you will see an estimated monthly cost towards the end. Do not worry!. You wont get charged. It's only a projection if you keep the RDS in use continuously. Just Go ahead and Click "Create Database".

While your Database is being initialized, we will go ahead and create a bastion host.

Create Bastion Host

Our Bastion host is nothing but an ec2 instance created to be used as a channel into our private database instance.

1. Navigate to ec2 on your console under instances, click 'Launch Instances". Be sure to select free tier like the below

Select "Next Configure Instance Details" and you will see a display similar to the image below.

Under Network, choose your default VPC, under subnet, select any of the default subnet since they are in a public subnet and make sure Auto-assign Public IP is enabled. Leave every other thing as default.

Click "Next- Add Storage" leave this section as default. Click "Next-Add tags" and also leave this as default.

Click "Next - Configure Security Group" and in this section, choose "select an existing security group" and check the bastion host security group we created earlier named "myBastionSG". See below for reference

Click "review and launch" and then click "launch". A dialogue box should pop up like the image below. Choose "create a new key pair" from the dropdown and give your key-pair the name "bastion-kp". (This is very important for database connection). Go ahead and click "Download Key Pair". The key pair should be downloaded on your local machine. We will need it when connecting later.

Click Launch Instances and yikeees!! Our bastion host should be launching. Click "view instances" to see all instances. You should see the bastion host we created.

Click the instance ID and you will see more details. Grab the public IPv4 address and save on a notepad somewhere.

Install PG Admin (Management tool) to connect to your DB

Incase you have have PG Admin on your local machine, Visit the official website of PG Admin here and download the PG Admin software compatible with your OS.

While waiting for your downloads to complete, quickly visit RDS to get some credentials from the Postgres database we created earlier. Open RDS and and click databases.

You will see a list of databases. Click the very one we created above. You will get a display of the properties. Copy the endpoint similar what is highlighted below and save somewhere

Go to your downloads, click the downloaded PG admin software and follow all installation guidelines. Once installation is done, try to open PG Admin from your machine.

If you are opening PG Admin for the first time, it might require you to set a password. Go ahead and add a password you can remember

Right Click server on the left panel, select create server like the below

You will see a display like the below. Under the General Tab, give your connection a name. I called my mine "my-aws-db".

Next, Click Connection Tab to enter your Database credentials . See mine below.

Fill the Host name/address we copied earlier, leave maintenance database as postgres, same for username (except you set a different username upon creation). Enter the password you saved when creating the database in RDS

Click "SSH Tunnel" tab and fill the credentials to connect via our tunnel similar to the below. Turn on Use SSH tunneling button, Grab the IPv4 IP address of your bastion host kept earlier and enter that for Tunnel host, Tunnel port should be "22", username should be "ec2-user" except you are using a different linux distribution like ubuntu. Select "Identity File" as authentication and lastly, browse the location of your downloaded bastion host key pair to fill the identity file text box.

Leave password empty

Click Save. If you do not encounter any error, our connection was successful.

One last thing to do is lock down security. Remember when we created the bastion host Security Group, we had entered 0.0.0.0/0 as source. For better practice, go back to the bastion host security group and change the source to the IP address of your local computer. (You can find your IPv4 address here). If you have more Analysts on your team who need to connect to the Postgres DB, all you need is their IP addresses and continuously create more rules using their respective IPs as source.

The downside of this is that IP addresses are dynamic and this is where using VPN plays out and you can lock down rules by IP ranges within a VPN.

DO NOT FORGET TO TERMINATE ALL THE RESOURCES USED IN THIS DEMO

• EC2
• RDS
• SECURITY GROUPS

I hope this was helpful. Let me know if you have questions or have difficulties completing this Demo. Don't forget to share