Connecting to a Database Within an Amazon VPC

Data Tutorial

In this tutorial, we will walk through setting up an Amazon RDS instance inside a private VPC subnet and connecting to it using an SSH tunnel.

A reverse SSH tunnel makes an outbound encrypted connection from within your VPC to Chartio’s servers. This allows you to connect Chartio to a database in your private VPC subnet without modifying its route table or security groups.

Overview

The following diagram depicts what our final architecture will look like. We will create a VPC with 2 subnets in the us-west-1 region; 1 public subnet in the us-west-1a availability zone and 1 private subnet in the us-west-1b availability zone. The VPC will have an Internet gateway attached, however the main route table will contain only a single local route that enables communication within the VPC. The public subnet will have a custom route table that includes the local route as well as a route directing all other traffic over the Internet gateway. A Postgres RDS instance will be provisioned in the private subnet with an attached security group that only allows inbound traffic on port 5432 from the public subnet. An EC2 instance will be provisioned in the public subnet with an attached security group that only allows inbound SSH traffic from your local IP and all outbound traffic. Finally, we will SSH into the EC2 instance, install the Postgres client psql, create a table on the RDS instance, and install and set up an SSH tunnel.

Create a VPC

Navigate to the VPC Dashboard in the AWS Management Console and create a new VPC.

Create and Attach an Internet Gateway

On the Internet Gateways tab of the VPC Dashboard, create a new Internet gateway.

Attach the gateway to the newly created VPC.

Create a Custom Route Table

On the Route Tables tab of the VPC Dashboard, create a new route table.

Add a route to the route table for the Internet gateway.

Create Public and Private Subnets

Create a subnet in the us-west-1a availability zone.

Change the route table for the previously created subnet from the main route table to the custom route table.

Create a subnet in the us-west-1b availability zone.

Create Security Groups

Create a security group for the EC2 instance to be provisioned in the public us-west-1a subnet.

Authorize inbound SSH traffic from your local IP address. The default outbound rules should be fine.

Create a security group for the Postgres RDS instance to be provisioned in the private us-west-1b subnet.

Authorize inbound traffic from the public subnet over port 5432.

Remove all outbound rules for the RDS security group.

Provision an EC2 Instance

Provision an EC2 instance in the public us-west-1a subnet. Ensure a public IP is assigned.

Assign the previously created security group.

Provision a Postgres RDS Instance

Provision a Postgres RDS instance in the private us-west-1b subnet. Ensure a public IP is NOT assigned.

Install and set up an SSH tunnel

SSH into the EC2 instance and run the following commands.

# Substitute 54.153.81.83 with your instance's public IP.
ssh ubuntu@54.153.81.83

# Create a table so Chartio has something to reflect.
# Substitute chartio.cacziwncd30i.us-west-1.rds.amazonaws.com with your instance's endpoint.
sudo apt-get update
sudo apt-get install postgresql-client
psql -h chartio.cacziwncd30i.us-west-1.rds.amazonaws.com -p 5432 -d chartio -U chartio -c "CREATE TABLE foo(id int);"

Install autossh (or SSH tunnel manager of your choice) and set up a tunnel connection using the instructions here.

Check the Chartio schema editor to ensure the RDS instance’s schema has been reflected. If it has not, try clicking the “Refresh Schema” button.