Learn how to connect a database with 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.

Diagram of the VPC architecture.

Create a VPC

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

Create a VPC.

Create and Attach an Internet Gateway

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

Create an Internet gateway.

Attach the gateway to the newly created VPC.

Attach the gateway.

Create a Custom Route Table

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

Create a custom route table.

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

Add routes to the route table.

Create Public and Private Subnets

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

Create the us-west-1a subnet.

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

Change the subnet's route table.

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

Create the us-west-1b subnet.

Create Security Groups

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

Create EC2 security group.

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

Authorize SSH traffic.

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

Create RDS security group.

Authorize inbound traffic from the public subnet over port 5432.

Authorize traffic from the public subnet over port 543.

Remove all outbound rules for the RDS security group.

Remove outbound rules.

Provision an EC2 Instance

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

Provision an EC2 instance. EC2 tag.

Assign the previously created security group.

Assign a 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.

Provision a Postgres RDS instance. RDS instance settings. Choose the private us-west-1b subnet and 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.

Chartio schema eitor.