Securely connecting external tools to your RDS database (via SSH)

You often want to connect external tools, like Postico, MySQLWorkbench and Trevor.io (disclaimer: I work here), to your Amazon RDS database.

There are two common methods for doing this:

1. Connecting directly to the database (via TCP/IP)
2. Connecting indirectly via a bastion host (TCP/IP over SSH), which is explained below.

Connecting to your RDS database via a bastion host (TCP/IP over SSH)

Connecting via a bastion host means that you will:

• not expose direct connections to your RDS database from the outside world,
• but will instead set up a bastion host (an EC2 server instance) inside the same VPC as your RDS database,
• and provide secure SSH access to that bastion host.

This allows you to securely connect external tools to your database, without allowing direct connections to your database from the outside world.

Making this work involves 3 steps (with 1 optional bonus step):

1. Create an EC2 instance in the same VPC as your database instance.
2. Give your EC2 instance access to your RDS database.
3. Permit SSH access to your EC2 instance.
4. (Optional) Create a new EC2 SSH user

We’ll go through each of these steps below.

1. Create an EC2 instance in the same VPC as your database instance

Go to EC2 on your AWS console and launch a new instance (making sure it is in the same VPC as your RDS database instance).

Just as you launch it, it will prompt you to choose (or create a new) key pair for connecting to your instance (see image below).

Download the key pair to your local machine, and set its permissions to read-only:

$ chmod 400 my-ec2-keypair.pem

This is needed in order to use it for connecting to your new EC2 instance later.

Once your instance is up and running, make a note of its Public DNS address:

You’ll need this for later.

2. Give your EC2 instance access to your RDS database.

The EC2 instance we have just created is in the same VPC as your RDS database instance, which is a great first step, but we also need to explicitly allow TCP/IP access to the database port of your RDS database instance from your EC2 instance.

We achieve this by going to the Security Group for our RDS database instance (sg-0425d2ffa12dec41a in the image below), in the AWS console, and adding an Inbound Rule that says that our EC2 instance’s security group (sg-0b24f82792e885a2b in the image below) has TCP access to the database port (e.g. Postgres port 5432):

Great. Your new EC2 instance now has access to your RDS database. Now we just need to permit SSH access to the EC2 instance itself.

3. Permit SSH access to your EC2 instance

To do this, you will need to update the Security Group for your new EC2 instance to allow SSH connections from your IP address:

In the image above I have added an Inbound rule that permits SSH access (uses port 22) from my current IP address (213.82.31.110). This will allow me to connect using tools running on my own computer.

I could also have specified 0.0.0.0/0 as the source, which would allow access from any IP address.

Or if I’m using a cloud service (like Trevor.io) I could whitelist its specific static IP addresses (Trevor.io’s are 34.192.31.89 and 34.192.37.108, so I would add rules like those shown below).

Test that you are now able to connect (as “ec2-user”) to your EC2 instance via SSH (using the Public DNS address from step 1):

$ ssh -i <keypair> ec2-user@<ec2-host-public-dns>

e.g:

$ ssh -i ./my-ec2-keypair.pem ec2-user@ec2–12-345-678-1.us-west-2.compute.amazonaws.com

Here we have used:

• The built-in ssh client for Linux/Mac.
• The keypair you downloaded in step 1 (my-ec2-keypair.pem).
• The default user assigned to EC2 instances (ec2-user).
• And the Public DNS URL for the new EC2 instance that you created in Step 1.

You should now be able to connect an external tool of your choice to your database, using the following details:

• SSH Host: the EC2 host (Public DNS) from step 1
• SSH Port: 22
• SSH User: ec2-user (in step 4 below we show how to create a custom user to use instead)
• SSH Private Key: my-ec2-keypair.pem
• Database Host: the “Endpoint” for your RDS database
• Database Port: the “Port” for your RDS database
• User: the username for your database user
• Password: the password for your database user
• Database: the name of the database to connect to (MySQL calls this your schema).

4. (Optional) Create a new EC2 SSH user

We’re going to create an EC2 user called trevor for use with Trevor.io so that we don’t have to share the ec2-user credentials with a 3rd party.

Let’s start by generating a new key pair specifically for this user:

• In the AWS console, go to EC2, and go to “Key Pairs”
• Click “Create key pair” and name the key pair something like “trevors-key-pair”

Once you create it, it will download the private key to your local machine.

Now we need to extract the public key from it.

To do this, we need to change the file’s permissions to be read-only:

$ chmod 400 trevors-key-pair.pem

and then we can use the ssh-keygen tool to extract the public key:

$ ssh-keygen -y -f trevors-key-pair.pem

The public key content will be output to the Terminal. Hold onto this output. We will need it in a second.

Now let’s create the new “trevor” user.

Start by connecting to your EC2 instance again (like you did in Step 3):

$ ssh -i ./my-ec2-keypair.pem ec2-user@ec2–12-345-678-1.us-west-2.compute.amazonaws.com

Then let’s create a new user, called “trevor”:

$ sudo adduser trevor

Once created, we then login as this user:

$ sudo su — trevor

Create a hidden folder called “ssh” and update its permissions so that only “trevor” can access it:

$ mkdir .ssh
$ chmod 700 .ssh

Now create a file inside this folder called “authorized_keys” and set its permissions:

$ cd .ssh
$ touch authorized_keys
$ chmod 600 authorized_keys

At this point we need the public key content from earlier.

Copy that content and paste it into the authorized_keys file, and save it.

That’s it. Now open a new Terminal on your local machine and confirm that you can login as the new user:

$ ssh -i trevors-key-pair.pem trevor@ec2–12-345-678-1.us-west-2.compute.amazonaws.com

Success

And that is it. You have now set up SSH access to your RDS database instance.

Why not try it out by connecting from one of these wonderful database tools:

• Postico
• MySQLWorkbench
• Trevor.io (disclaimer: I work here)