DEV Community

Goh Chun Lin
Goh Chun Lin

Posted on • Originally published at cuteprogramming.blog on

Setup and Access Private RDS Database via a Bastion Host

#amazonwebservices #cloudcomputingamazon #cloudformation #experience

There is always a common scenario that requires cloud engineers to configure infrastructure which allows developers to safely and securely connect to the RDS or Aurora database that is in a private subnet.

For development purpose, some developers tend to create a public IP address to access the databases on AWS as part of setup. This makes it easy for the developers to gain access to their database, but it is undoubtedly not a recommended method because it has huge security vulnerability that can compromise sensitive data.

Architecture Design

In order to make our database secure, the recommended approach by AWS is to place our database in a private subnet. Since a private subnet has no ability to communicate with the public Internet directly, we are able to isolate our data from the outside world.

Then in order to enable the developers to connect remotely to our database instance, we will setup a bastion host that allows them to connect to the database via SSH tunnelling.

The following diagram describes the overall architecture that we will be setting up for this scenario.

We will be configuring with CloudFormation template. The reason why we use CloudFormation is because it provides us with a simple way to create and manage a collection of AWS resources by provisioning and updating them in a predictable way.

Step 1: Specify Parameters

In the CloudFormation template, we will be using the following parameters.

Parameters: ProjectName: Type: String Default: my-project EC2InstanceType: Type: String Default: t2.micro EC2AMI: Type: String Default: ami-020283e959651b381 # Amazon Linux 2023 AMI 2023.3.20240219.0 x86_64 HVM kernel-6.1 EC2KeyPairName: Type: String Default: my-project-ap-northeast-1-keypair MasterUsername: Type: String Default: admin MasterUserPassword: Type: String AllowedPattern: "[a-zA-Z0-9]+" NoEcho: true EngineVersion: Type: String Default: 8.0 MinCapacity: Type: String Default: 0.5 MaxCapacity: Type: String Default: 1
Enter fullscreen mode Exit fullscreen mode

As you have noticed in the parameters for EC2, we choose to use the Amazon Linux 2023 AMI, which is shown in the following screenshot.


We can easily retrieve the AMI ID of an image in the AWS Console.

We are also using a keypair that we have already created. It is a keypair called “my-project-ap-northeast-1-keypair”.


We can locate existing key pairs in the EC2 instances page.

Step 2: Setup VPC

Amazon Virtual Private Cloud (VPC) is a foundational service for networking and compute categories. It lets us provision a logically isolated section of the AWS cloud to launch our AWS resources. VPC allows resources within a VPC to access AWS services without needing to go over the Internet.

When we use a VPC, we have control over our virtual networking environment. We can choose our own IP address range, create subnets, and configure routing and access control lists.

VPC: Type: AWS::EC2::VPC Properties: CidrBlock: 38.0.0.0/16 Tags: - Key: Name Value: !Sub '${AWS::StackName}-vpc' - Key: Project Value: !Ref ProjectName
Enter fullscreen mode Exit fullscreen mode

Step 3: Setup Public Subnet, IGW, and Bastion Host

A bastion host is a dedicated server that lets authorised users access a private network from an external network such as the Internet.

A bastion host, also known as a jump server, is used as a bridge between the public Internet and a private subnet in a network architecture. It acts as a gateway that allows secure access from external networks to internal resources without directly exposing those resources to the public.

This setup enhances security by providing a single point of entry that can be closely monitored and controlled, reducing the attack surface of the internal network.

In this step, we will be launching an EC2 instance which is also our bastion host into our public subnet which is defined as follows.

PublicSubnet: Type: AWS::EC2::Subnet Properties: AvailabilityZone: !Select [0, !GetAZs ''] VpcId: !Ref VPC CidrBlock: 38.0.0.0/20 MapPublicIpOnLaunch: true Tags: - Key: Name Value: !Sub '${AWS::StackName}-vpc-public-subnet1' - Key: AZ Value: !Select [0, !GetAZs ''] - Key: Project Value: !Ref ProjectName
Enter fullscreen mode Exit fullscreen mode

This public subnet will be able to receive public connection requests from the Internet. However, we should make sure that our bastion host to only be accessible via SSH at port 22.

BastionSecurityGroup: Type: AWS::EC2::SecurityGroup Properties: GroupName: !Sub '${AWS::StackName}-bastion-sg' GroupDescription: !Sub 'Security group for ${AWS::StackName} bastion host' VpcId: !Ref VPC BastionAllowInboundSSHFromInternet: Type: AWS::EC2::SecurityGroupIngress Properties: GroupId: !Ref BastionSecurityGroup IpProtocol: tcp FromPort: 22 ToPort: 22 CidrIp: 0.0.0.0/0
Enter fullscreen mode Exit fullscreen mode

CidrIp defines the IP address range that is permitted to send inbound traffic through the security group. 0.0.0.0/0 means from the whole Internet. Thus, we can also make sure that the connections are from certain IP addresses such as our home or workplace networks. Doing so will reduce the risk of exposing our bastion host to unintended outside audiences.

In order to enable resources in our public subnets, which is our bastion host in this case, to connect to the Internet, we also need to add Internet Gateway (IGW). IGW is a VPC component that allows communication between the VPC and the Internet.

InternetGateway: Type: AWS::EC2::InternetGateway Properties: Tags: - Key: Name Value: !Sub '${AWS::StackName}-igw' - Key: Project Value: !Ref ProjectName VPCGatewayAttachment: Type: AWS::EC2::VPCGatewayAttachment Properties: InternetGatewayId: !Ref InternetGateway VpcId: !Ref VPC
Enter fullscreen mode Exit fullscreen mode

For outbound traffic, a route table for the IGW is necessary. When resources within a subnet need to communicate with resources outside of the VPC, such as accessing the public Internet or other AWS services, they need a route to the IGW.

PublicRouteTable: Type: AWS::EC2::RouteTable Properties: VpcId: !Ref VPC Tags: - Key: Name Value: !Sub '${AWS::StackName}-route-table' - Key: Project Value: !Ref ProjectName InternetRoute: Type: AWS::EC2::Route DependsOn: VPCGatewayAttachment Properties: RouteTableId: !Ref PublicRouteTable DestinationCidrBlock: 0.0.0.0/0 GatewayId: !Ref InternetGateway SubnetRouteTableAssociationAZ1: Type: AWS::EC2::SubnetRouteTableAssociation Properties: RouteTableId: !Ref PublicRouteTable SubnetId: !Ref PublicSubnet
Enter fullscreen mode Exit fullscreen mode

A destination of 0.0.0.0/0 in the DestinationCidrBlock means that all traffic that is trying to access the Internet needs to flow through the target, i.e. the IGW.

Finally, we can define our bastion host EC2 instance with the following template.

BastionInstance: Type: AWS::EC2::Instance Properties: ImageId: !Ref EC2AMI InstanceType: !Ref EC2InstanceType KeyName: !Ref EC2KeyPairName SubnetId: !Ref PublicSubnet SecurityGroupIds: - !Ref BastionSecurityGroup Tags: - Key: Name Value: !Sub '${AWS::StackName}-bastion' - Key: Project Value: !Ref ProjectName
Enter fullscreen mode Exit fullscreen mode

Step 4: Configure Private Subnets and Subnet Group

The database instance, as shown in the diagram above, is hosted in a private subnet so that it is securely protected from direct public Internet access.

When we are creating a database instance, we need to provide something called a Subnet Group. Subnet group helps deploy our instances across multiple Availability Zones (AZs), providing high availability and fault tolerance. Hence, we need to create two private subnets in order to successfully setup our database cluster.

PrivateSubnet1: Type: AWS::EC2::Subnet Properties: VpcId: !Ref VPC AvailabilityZone: !Select [0, !GetAZs ''] CidrBlock: 38.0.128.0/20 Tags: - Key: Name Value: !Sub '${AWS::StackName}-vpc-private-subnet1' - Key: AZ Value: !Select [0, !GetAZs ''] - Key: Project Value: !Ref ProjectName PrivateSubnet2: Type: AWS::EC2::Subnet Properties: VpcId: !Ref VPC AvailabilityZone: !Select [1, !GetAZs ''] CidrBlock: 38.0.144.0/20 Tags: - Key: Name Value: !Sub '${AWS::StackName}-vpc-private-subnet2' - Key: AZ Value: !Select [1, !GetAZs ''] - Key: Project Value: !Ref ProjectName
Enter fullscreen mode Exit fullscreen mode

Even thought resources in private subnets should not be directly accessible from the internet, they still need to communicate with other resources within the VPC. Hence, route table is neccessary to define routes that enable this internal communication.

PrivateRouteTable1: Type: AWS::EC2::RouteTable Properties: VpcId: !Ref VPC Tags: - Key: Name Value: !Sub '${AWS::StackName}-route-table-private-1' - Key: Project Value: !Ref ProjectName PrivateSubnetRouteTableAssociationAZ1: Type: AWS::EC2::SubnetRouteTableAssociation Properties: RouteTableId: !Ref PrivateRouteTable1 SubnetId: !Ref PrivateSubnet1 PrivateRouteTable2: Type: AWS::EC2::RouteTable Properties: VpcId: !Ref VPC Tags: - Key: Name Value: !Sub '${AWS::StackName}-route-table-private-2' - Key: Project Value: !Ref ProjectName PrivateSubnetRouteTableAssociationAZ2: Type: AWS::EC2::SubnetRouteTableAssociation Properties: RouteTableId: !Ref PrivateRouteTable2 SubnetId: !Ref PrivateSubnet2
Enter fullscreen mode Exit fullscreen mode

In this article, as shown in the diagram above, one of the private subnets is not used. The additional subnet makes it easier for us to switch to a Multi-AZ database instance deployment in the future.

After we have defined the two private subnets, we can thus proceed to configure the subnet group as follows.

DBSubnetGroup: Type: 'AWS::RDS::DBSubnetGroup' Properties: DBSubnetGroupDescription: !Sub 'Subnet group for ${AWS::StackName}-core-db DB Cluster' SubnetIds: - !Ref PrivateSubnet1 - !Ref PrivateSubnet2 Tags: - Key: Project Value: !Ref ProjectName
Enter fullscreen mode Exit fullscreen mode

Step 5: Define Database Cluster and Instance

As mentioned earlier, we will be using Amazon Aurora. So what is Aurora?

In 2014, Aurora was introduced to the public. Aurora is a fully-managed MySQL and PostgreSQL-compatible RDBMS. Aurora has 5x the throughput of MySQL and 3x of PostgreSQL, at 1/10th the cost of commercial databases. Aurora.

Five years after that, in 2019, Aurora Serverless was generally available in several regions such as US, EU, and Japan. Aurora Serverless is a flexible and cost-effective RDBMS option on AWS for apps with variable or unpredictable workloads because it offers an on-demand and auto-scaling way to run Aurora database clusters.

In 2022, Aurora Serverless v2 is generally available and supports CloudFormation.

RDSDBCluster: Type: 'AWS::RDS::DBCluster' Properties: Engine: aurora-mysql DBClusterIdentifier: !Sub '${AWS::StackName}-core-db' DBSubnetGroupName: !Ref DBSubnetGroup NetworkType: IPV4 VpcSecurityGroupIds: - !Ref DatabaseSecurityGroup AvailabilityZones: - !Select [0, !GetAZs ''] EngineVersion: !Ref EngineVersion MasterUsername: !Ref MasterUsername MasterUserPassword: !Ref MasterUserPassword ServerlessV2ScalingConfiguration: MinCapacity: !Ref MinCapacity MaxCapacity: !Ref MaxCapacity RDSDBInstance: Type: 'AWS::RDS::DBInstance' Properties: Engine: aurora-mysql DBInstanceClass: db.serverless DBClusterIdentifier: !Ref RDSDBCluster
Enter fullscreen mode Exit fullscreen mode

The ServerlessV2ScalingConfiguration property is specially designed for Aurora Serverless v2 only. Here, we configure the minimum and maximum capacities for our database cluster to be 0.5 and 1 ACUs, respectively.

Choose 0.5 for the minimum because that allows our database instance to scale down the most when it is completely idle. For the maximum, we choose the lowest possible value, i.e. 1 ACU, to avoid the possibility of unexpected charges.

Step 6: Allow Connection from Bastion Host to the Database Instance

Finally, we need to allow the traffic from our bastion host to the database. Hence, our database security group template should be defined in the following manner.

DatabaseSecurityGroup: Type: AWS::EC2::SecurityGroup Properties: GroupName: !Sub '${AWS::StackName}-core-database-sg' GroupDescription: !Sub 'Security group for ${AWS::StackName} core database' VpcId: !Ref VPC DatabaseAllowInboundFromBastion: Type: AWS::EC2::SecurityGroupIngress Properties: GroupId: !Ref DatabaseSecurityGroup IpProtocol: tcp FromPort: 3306 ToPort: 3306 SourceSecurityGroupId: Fn::GetAtt: - BastionSecurityGroup - GroupId GroupId: Fn::GetAtt: - DatabaseSecurityGroup - GroupId
Enter fullscreen mode Exit fullscreen mode

To connect to the database instance from the bastion host, we need to navigate to the folder containing the private key and perform the following.

ssh -i <private-key.pem> -f -N -L 3306:<db-instance-endpoint>:3306 ec2-user@<bastion-host-ip-address> -vvv
Enter fullscreen mode Exit fullscreen mode

The -L option in the format of port:host:hostport in the command above basically specifies that connections to the given TCP port on the local host are to be forwarded to the given host and port on the remote side.


We can get the endpoint and port of our DB instance from the AWS Console.

With the command above, we should be able to connect to our database instance via our bastion host, as shown in the screenshot below.


We can proceed to connect to our database instance after reaching this step.

Now, we are able to connect to our Aurora database on MySQL Workbench.


Connecting to our Aurora Serverless database on AWS!

WRAP-UP

That’s all for how we have to configure the infrastructure described in the following diagram so that we can connect to our RDS databases in private subnets through a bastion host.

I have also attached the complete CloudFormation template below for your reference.

# This is the complete template for our scenario discussed in this article. --- AWSTemplateFormatVersion: '2010-09-09' Description: 'Setup and Access Private RDS Database via a Bastion Host' Parameters: ProjectName: Type: String Default: my-project EC2InstanceType: Type: String Default: t2.micro EC2AMI: Type: String Default: ami-020283e959651b381 # Amazon Linux 2023 AMI 2023.3.20240219.0 x86_64 HVM kernel-6.1 EC2KeyPairName: Type: String Default: my-project-ap-northeast-1-keypair MasterUsername: Type: String Default: admin MasterUserPassword: Type: String AllowedPattern: "[a-zA-Z0-9]+" NoEcho: true EngineVersion: Type: String Default: 8.0 MinCapacity: Type: String Default: 0.5 MaxCapacity: Type: String Default: 1 Resources: VPC: Type: AWS::EC2::VPC Properties: CidrBlock: 38.0.0.0/16 Tags: - Key: Name Value: !Sub '${AWS::StackName}-vpc' - Key: Project Value: !Ref ProjectName PublicSubnet: Type: AWS::EC2::Subnet Properties: AvailabilityZone: !Select [0, !GetAZs ''] VpcId: !Ref VPC CidrBlock: 38.0.0.0/20 MapPublicIpOnLaunch: true Tags: - Key: Name Value: !Sub '${AWS::StackName}-vpc-public-subnet1' - Key: AZ Value: !Select [0, !GetAZs ''] - Key: Project Value: !Ref ProjectName PrivateSubnet1: Type: AWS::EC2::Subnet Properties: VpcId: !Ref VPC AvailabilityZone: !Select [0, !GetAZs ''] CidrBlock: 38.0.128.0/20 Tags: - Key: Name Value: !Sub '${AWS::StackName}-vpc-private-subnet1' - Key: AZ Value: !Select [0, !GetAZs ''] - Key: Project Value: !Ref ProjectName PrivateSubnet2: Type: AWS::EC2::Subnet Properties: VpcId: !Ref VPC AvailabilityZone: !Select [1, !GetAZs ''] CidrBlock: 38.0.144.0/20 Tags: - Key: Name Value: !Sub '${AWS::StackName}-vpc-private-subnet2' - Key: AZ Value: !Select [1, !GetAZs ''] - Key: Project Value: !Ref ProjectName InternetGateway: Type: AWS::EC2::InternetGateway Properties: Tags: - Key: Name Value: !Sub '${AWS::StackName}-igw' - Key: Project Value: !Ref ProjectName VPCGatewayAttachment: Type: AWS::EC2::VPCGatewayAttachment Properties: InternetGatewayId: !Ref InternetGateway VpcId: !Ref VPC PublicRouteTable: Type: AWS::EC2::RouteTable Properties: VpcId: !Ref VPC Tags: - Key: Name Value: !Sub '${AWS::StackName}-route-table' - Key: Project Value: !Ref ProjectName InternetRoute: Type: AWS::EC2::Route DependsOn: VPCGatewayAttachment Properties: RouteTableId: !Ref PublicRouteTable DestinationCidrBlock: 0.0.0.0/0 GatewayId: !Ref InternetGateway SubnetRouteTableAssociationAZ1: Type: AWS::EC2::SubnetRouteTableAssociation Properties: RouteTableId: !Ref PublicRouteTable SubnetId: !Ref PublicSubnet PrivateRouteTable1: Type: AWS::EC2::RouteTable Properties: VpcId: !Ref VPC Tags: - Key: Name Value: !Sub '${AWS::StackName}-route-table-private-1' - Key: Project Value: !Ref ProjectName PrivateSubnetRouteTableAssociationAZ1: Type: AWS::EC2::SubnetRouteTableAssociation Properties: RouteTableId: !Ref PrivateRouteTable1 SubnetId: !Ref PrivateSubnet1 PrivateRouteTable2: Type: AWS::EC2::RouteTable Properties: VpcId: !Ref VPC Tags: - Key: Name Value: !Sub '${AWS::StackName}-route-table-private-2' - Key: Project Value: !Ref ProjectName PrivateSubnetRouteTableAssociationAZ2: Type: AWS::EC2::SubnetRouteTableAssociation Properties: RouteTableId: !Ref PrivateRouteTable2 SubnetId: !Ref PrivateSubnet2 BastionSecurityGroup: Type: AWS::EC2::SecurityGroup Properties: GroupName: !Sub '${AWS::StackName}-bastion-sg' GroupDescription: !Sub 'Security group for ${AWS::StackName} bastion host' VpcId: !Ref VPC BastionAllowInboundSSHFromInternet: Type: AWS::EC2::SecurityGroupIngress Properties: GroupId: !Ref BastionSecurityGroup IpProtocol: tcp FromPort: 22 ToPort: 22 CidrIp: 0.0.0.0/0 BastionInstance: Type: AWS::EC2::Instance Properties: ImageId: !Ref EC2AMI InstanceType: !Ref EC2InstanceType KeyName: !Ref EC2KeyPairName SubnetId: !Ref PublicSubnet SecurityGroupIds: - !Ref BastionSecurityGroup Tags: - Key: Name Value: !Sub '${AWS::StackName}-bastion' - Key: Project Value: !Ref ProjectName DatabaseSecurityGroup: Type: AWS::EC2::SecurityGroup Properties: GroupName: !Sub '${AWS::StackName}-core-database-sg' GroupDescription: !Sub 'Security group for ${AWS::StackName} core database' VpcId: !Ref VPC DatabaseAllowInboundFromBastion: Type: AWS::EC2::SecurityGroupIngress Properties: GroupId: !Ref DatabaseSecurityGroup IpProtocol: tcp FromPort: 3306 ToPort: 3306 SourceSecurityGroupId: Fn::GetAtt: - BastionSecurityGroup - GroupId GroupId: Fn::GetAtt: - DatabaseSecurityGroup - GroupId DBSubnetGroup: Type: 'AWS::RDS::DBSubnetGroup' Properties: DBSubnetGroupDescription: !Sub 'Subnet group for ${AWS::StackName}-core-db DB Cluster' SubnetIds: - !Ref PrivateSubnet1 - !Ref PrivateSubnet2 Tags: - Key: Project Value: !Ref ProjectName RDSDBCluster: Type: 'AWS::RDS::DBCluster' Properties: Engine: aurora-mysql DBClusterIdentifier: !Sub '${AWS::StackName}-core-db' DBSubnetGroupName: !Ref DBSubnetGroup NetworkType: IPV4 VpcSecurityGroupIds: - !Ref DatabaseSecurityGroup AvailabilityZones: - !Select [0, !GetAZs ''] EngineVersion: !Ref EngineVersion MasterUsername: !Ref MasterUsername MasterUserPassword: !Ref MasterUserPassword ServerlessV2ScalingConfiguration: MinCapacity: !Ref MinCapacity MaxCapacity: !Ref MaxCapacity RDSDBInstance: Type: 'AWS::RDS::DBInstance' Properties: Engine: aurora-mysql DBInstanceClass: db.serverless DBClusterIdentifier: !Ref RDSDBCluster
Enter fullscreen mode Exit fullscreen mode

Top comments (0)