Is Your DB Safe?: How to Securely Access RDS

I accidentally deleted the production DB on my first day and was told to resign. On top of that, the CTO said legal review might be necessary - how serious is this?

This post on Reddit from 2017 went viral, and a tech news site held a vote on who should be fired. 45% of people voted that the CTO should be fired. (source)

In other words, new hires or existing employees can cause system failures, and preventing this is the role of the CTO or tech lead. After reading this post, I reflected on how we access the DB at CodeTree, where I serve as CTO.

At CodeTree, we use AWS RDS to manage our Database. Back when there weren't many people accessing the DB, we used a method of (1) allowing public access to RDS and (2) connecting with the master username and password, but I thought this needed security improvements. After considering how to access it safely, we decided to (1) place the RDS instance in a private subnet and (2) use IAM-based access.

With AWS VPC, you can distinguish between public subnet and private subnet. A public subnet is accessible from any VPC, while a private subnet is only accessible from within the same VPC. So when configuring a VPC, you typically place resources that need external access (ALB, EC2, etc.) in the public subnet, and resources that don't need external access (RDS, Elasticache, etc.) in the private subnet.

When creating an RDS instance, you can decide whether to allow Public access as shown above. If you don't allow public access and place the RDS instance in a private subnet, you can create a secure environment where RDS cannot be accessed from outside the VPC at all.

When you place an RDS instance in a private subnet, it cannot be accessed from outside the VPC. However, for development or operations, you need to be able to access the RDS from the user's local environment. So you need to consider methods for accessing RDS, and there are mainly two approaches:

1. Access through Bastion host
2. Access through AWS Session Manager

Let's look at each method.

A Bastion host is an EC2 instance used to access resources inside a VPC from outside the VPC. The architecture can be illustrated as follows:

You place the Bastion host in the Public subnet as shown above, and access the RDS in the private subnet through SSH port forwarding (tunneling).

I'll skip the details on how to access via Bastion host as this post covers it in detail. :)

The pros and cons of the Bastion host approach are as follows:

1. The method of accessing RDS is simple.
2. Since most MySQL GUI tools support SSH tunneling, you can conveniently access RDS using a MySQL GUI tool.
3. You can control access by IP through the Bastion host's security group.

1. Since SSH keys are used to access the Bastion host, sharing SSH keys for convenience can create security vulnerabilities, and managing separate SSH keys can be cumbersome.
2. When controlling access through security groups, it's difficult to handle cases where IPs change dynamically.

Since we placed RDS in a private subnet for security, we needed to address these disadvantages. So we decided to use the AWS Session Manager access method below.

AWS Session Manager is a service that allows you to access private instances without using SSH or RDP. (AWS Session Manager also uses a bastion host.)

I'll skip the details on how to access RDS through Session Manager as this post covers it in detail. :)

The pros and cons of the Session Manager approach are as follows:

1. Since SSH keys aren't used, SSH key management isn't necessary.
2. You can check currently active sessions in the Systems Manager Session Manager console.
3. Since access is controlled via IAM, you can handle cases where IPs change dynamically.

1. Since MySQL GUI tools don't natively support connections through Session Manager, you need to connect using AWS CLI first and then access via localhost.
2. Sessions opened through Session Manager are automatically terminated when there's no activity.

Although it's somewhat more complex than Bastion host, we decided to use Session Manager because it's more secure.

Typically, when accessing a DB, you create an account to access the DB and use a password to connect.

Creating users directly on the DB server and adjusting their permissions becomes difficult to manage as the team grows or when there are multiple DB servers.

Since RDS supports IAM Authentication, you can grant each IAM user permission to access RDS, and each IAM user can obtain a token to access RDS.

Here's how to use IAM Authentication:

In the RDS console, select the RDS instance and click the Modify button.

In the Database authentication section, check Password and IAM database authentication and click the Continue button.

Access the RDS instance and create a DB user.

MySQL or MariaDB

CREATE USER jane_doe IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS';

PostgreSQL

CREATE USER jane_doe; 
GRANT rds_iam TO jane_doe;

In the IAM console, select the IAM user or group you want to grant permissions to and click the Add inline policy button.

Click JSON and enter the following permissions with your desired variable names:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "rds-db:connect"
            ],
            "Resource": [
                "arn:aws:rds-db:<AWS_REGION>:<AWS_ACCOUNT_ID>:dbuser:<dbiRESOURCE_ID>/<DB_USERNAME>"
            ]
        }
    ]
}

<AWS_REGION> is the region where the RDS instance was created. <AWS_ACCOUNT_ID> is your AWS account id. <dbiRESOURCE_ID> is the resource id of the RDS instance. <DB_USERNAME> is the username of the DB user created above. (In the example above, jane_doe)

In the state where you're authenticated as the IAM user from step 3 (you can authenticate using aws configure), generate a token with the following command:

TOKEN="$(aws rds generate-db-auth-token --hostname <RDS hostname> --port <port> --region <region> --username <db-user-name>)"

<RDS hostname> is the hostname of the RDS instance. <region> is the region where the RDS instance was created. <db-user-name> is the username of the DB user created above. (In the example above, jane_doe) <port> is the port of the RDS instance. (3306 for MySQL, 5432 for PostgreSQL)

If you're connected through Session Manager, you can access RDS with the following command: (For MySQL)

mysql -h 127.0.0.1 --enable-cleartext-plugin -u <DB_USERNAME> -p $TOKEN

For MySQL, you need to add the --enable-cleartext-plugin option. (For MariaDB, you don't need to add the --enable-cleartext-plugin option.)

We've covered methods for securely accessing RDS. We've enabled access from local environments for development while being able to access securely without sharing master user credentials. (This also reduced the risk of getting fired.)

Also, by unifying the authentication method with IAM, we can easily manage permissions by simply adding or removing IAM accounts when employees join or leave.

I hope this helps CTOs and tech leads who have similar concerns.

Thank you. :)

1. Hiding a DB instance in a VPC from the internet
2. IAM authentication with Amazon RDS for MariaDB
3. A DB instance in a VPC accessed by an EC2 instance in the same VPC
4. Securely connect to an Amazon RDS or Amazon EC2 database instance remotely with your preferred GUI
5. IAM database authentication for MariaDB, MySQL, and PostgreSQL