How to Securely Connect to RDS from a Local Environment Using AWS Session Manager
Tags: webdev, beginners, aws, devops
Published: 2023-10-21 08:45:31

Until now, we used SSH tunnels through a bastion EC2 to connect to our database (RDS), facilitating connections from DB client tools (like MySQL Workbench) and managing migrations from our local environments.

We maintained secure external connections by restricting IP addresses in the inbound rules of the bastion EC2's security group. However, the hassle of IP address changes due to new team members and changing work locations, coupled with a preference to keep resources within a private subnet, led us to transition to a database connection method using AWS Systems Manager Session Manager.

By adopting Session Manager, we've reaped several benefits:
- No need for creating or managing SSH keys,
- More secure connections to our database,
- Logging and auditing of session actions through CloudTrail.

## Connection Process
The DB connection process using Session Manager consists mainly of the following steps:

1. Initiating the Port Forwarding Session: Start a port forwarding session through Session Manager using the AWS CLI or AWS Management Console.
2. Configuring the Local Machine: Once the session is established, the local machine acts as an endpoint for communication between the DB instance, based on the details provided by Session Manager.
3. Database Connection: Connect to the database through the established port forwarding session. Appropriate authentication credentials and connection parameters are required for this phase.

## Implementation Steps

### Preliminary Checks
Ensure the AWS Systems Manager Agent (SSM Agent) is operating on the targeted EC2 instance and is up-to-date. The SSM Agent facilitates secure communication between the instance and Systems Manager.

### Creation of IAM User
Create a custom IAM policy named AssumeSSMRolePermissionPolicy and attach it to specific IAM users (or IAM groups they belong to). This enables the IAM users to assume certain SSM-related IAM roles, granting them necessary permissions.

```json
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "sts:AssumeRole",
            "Resource": "arn:aws:iam::<Account ID>:role/AmazonSSMRoleForInstancesQuickSetup"
        }
    ]
}
```
`AmazonSSMRoleForInstancesQuickSetup` is an IAM role that you will attach to your EC2 instances (you will create this shortly). This role is used to grant the necessary permissions to the SSM agent on the EC2 instances to function correctly.

By assuming this role, IAM users can obtain temporary credentials (access keys, secret access keys, session tokens) for managing EC2 instances through the Session Manager. Consequently, there is no need to issue access keys and secret access keys to the IAM users themselves.

### Installing AWS CLI
You need to install the AWS CLI in advance to initiate port-forwarding sessions.

https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-getting-started.html

### Creating Security Group (SG)
Unlike when using the EC2 as a bastion server, inbound rules for the SG are no longer necessary. On the other hand, outbound rules should be set to allow connections to RDS and the SSM agent.

Below is the Terraform code for this setup.

```tf
resource "aws_security_group" "rds_ssm_connector_sg" {
  name        = "rds-ssm-connector-sg"
  description = "Security group for RDS SSM Connector"
  vpc_id      = data.aws_ssm_parameter.vpc_id.value
}

resource "aws_security_group_rule" "rds_ssm_connector_sg_egress" {
  type              = "egress"
  from_port         = 3306
  to_port           = 3306
  protocol          = "tcp"
  security_group_id = aws_security_group.rds_ssm_connector_sg.id
  cidr_blocks       = ["IP range of the RDS instance"]
}

resource "aws_security_group_rule" "rds_ssm_connector_sg_egress_ssm" {
  type              = "egress"
  from_port         = 443
  to_port           = 443
  protocol          = "tcp"
  security_group_id = aws_security_group.rds_ssm_connector_sg.id
  cidr_blocks       = ["0.0.0.0/0"]
}
```

### Creation of IAM Role
Create the IAM role `AmazonSSMRoleForInstancesQuickSetup` to be applied to your EC2 instances. Apply the following managed policies to this role for the creation and management of SSM sessions:

- `AmazonSSMManagedInstanceCore`: Provides the basic permissions for an instance to communicate with the SSM service.
- `AmazonSSMPatchAssociation`: Grants permissions to automate the application of patch baselines.

Additionally, under the "Trust relationships" tab, specify the following as the "Trusted entities." This action enables both EC2 and IAM users to assume this IAM role.

```json
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": "ec2.amazonaws.com",
                "AWS": "arn:aws:iam::<Account ID>:user/<Specific IAM User>"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}
```

### Setting up Host Management
1. Navigate to AWS Systems Manager > Quick Setup.
2. Select Host Management.
3. Choose the relevant EC2 instances from your targets.
4. Initiate the creation of Host Management.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uraf3cv4vx33hnv4mgqu.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7lpy537zy1ece9xswd0v.png)

## Connection Process
### Obtaining Temporary Credentials
By utilizing the IAM role and AWS Security Token Service (STS), you can manipulate AWS CLI resources without the need to use access keys.

First, acquire temporary credentials by running the following command:

```bash
$ aws sts assume-role --role-arn "arn:aws:iam::<Account ID>:role/AmazonSSMRoleForInstancesQuickSetup" --role-session-name "RDSPortForwardingSession"
```
Copy the outputted `AccessKeyId`, `SecretAccessKey`, and `SessionToken`.

```yaml
{
    "Credentials": {
        "AccessKeyId": "XXXXXXXXXXXXX",
        "SecretAccessKey": "XXXXXXXXXXXXX",
        "SessionToken": "XXXXXXXXXXXXX",
        "Expiration": "2023-10-19T05:55:50+00:00"
    },
    "AssumedRoleUser": {
        "AssumedRoleId": "XXXXXXXXXXXXX:RDSPortForwardingSession",
        "Arn": "arn:aws:sts::XXXXXXXXXXXXX:assumed-role/AmazonSSMRoleForInstancesQuickSetup/RDSPortForwardingSession"
    }
}
```
Next, set the credentials (`AccessKeyId`, `SecretAccessKey`, `SessionToken`) as environment variables. This can be done using the following commands in your command-line interface:

```bash
export AWS_ACCESS_KEY_ID=<AccessKeyId>
export AWS_SECRET_ACCESS_KEY=<SecretAccessKey>
export AWS_SESSION_TOKEN=<SessionToken>
```

### Starting a Port Forwarding Session
Execute the following command:

```bash
$ aws ssm start-session --target [instance-id] --document-name AWS-StartPortForwardingSessionToRemoteHost --parameters host=[DB-endpoint],portNumber="3306",localPortNumber=[arbitrary-port]
```

For the arbitrary port, choose an unused port (such as `8888`).

Success is indicated by a message similar to the following:

```bash
Starting session with SessionId: session-id
Port 8888 opened for sessionId session-id.
Waiting for connections...
```
Additionally, upon a successful connection, the session will be displayed on the Session Manager console screen. This allows you to monitor and manage active sessions, enhancing your oversight and security measures for database connections through AWS.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uthcw3smf42q1xrgjrk0.png)

### Setting Up DB Client Tools (such as MySQL Workbench)
Proceed with the following settings:

- Connection Method: `Standard (TCP/IP)`
- Hostname: `127.0.0.1` (Be cautious as using `localhost` may result in a connection failure)
- Port: The port number you forwarded
- Username: Your DB username

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mzw8q2rxwh8cqaufrdq3.png)

Click on ‘Test Connection,’ and if prompted, enter the password for your DB user. A successful connection is confirmed if the following dialog appears.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vn82qzdatxqeq82xkwh4.png)

## Conclusion
Transitioning to a more secure method of database connection by utilizing AWS Session Manager marks a significant step in bolstering the security and efficiency of managing your resources. This approach not only streamlines the process by removing the need for bastion hosts and the management of SSH keys but also enhances security through fine-grained access control and auditing capabilities provided by AWS.

With the setup detailed in this guide, you've eliminated several layers of potential vulnerability and ensured a robust connection method. You can now connect to your database securely from your local environment, leveraging AWS's infrastructure without the overhead of managing intermediate servers. Furthermore, this method provides peace of mind with AWS CloudTrail, keeping a vigilant eye on your sessions' activities.

Always remember to review and follow best practices for security, ensuring that all software is up-to-date, and adhere to the principle of least privilege when assigning permissions. The landscape of technology and threats evolves continuously, and thus, staying informed and prepared is crucial for maintaining a secure and efficient environment.