Securely Connect to Your Private RDS Using BitBucket Pipeline and SSH Tunnel

This is a practical guide how to connect to private RDS instance using custom deployments in BitBucket Pipelines or any other CI/CD tools as it's built using generic bash command for Ubuntu Server 22.04, this way you can deploy changes to database using specific query files or doing migration with your code.

There's two ways to do it, in this blog we will use the tunneling using EC2 Bastion Host solution to establish the connection to a private RDS instance, it could have other potentials uses for your pipeline or any other automation you want to establish in your solution

First of all we need to create the EC2 instance and making sure we have access to AWS

Use aws configure to authenticate.

Create your SSH key:

ssh-keygen -l -f YourSSHKeyPair
chmod 600 YourSSHKeyPair

First create the security group:

aws ec2 create-security-group --group-name YourSecurityGroupName --description "Your Security Group Description" --vpc-id YourVpcId
aws ec2 authorize-security-group-ingress --group-name YourSecurityGroupName --protocol tcp --port YourPortNumber --cidr YourIPAddress/32

This is just an example, follow the IP Address list to be set, remember to use only necessary access from ports and IP addresess.

As additional security parameter when you assign the security group to the EC2 instance, add only the public IP Addresses from BitBucket Pipeline to assure security to your private instance.

List: https://support.atlassian.com/bitbucket-cloud/docs/what-are-the-bitbucket-cloud-ip-addresses-i-should-use-to-configure-my-corporate-firewall/

The following command can help you create the instance real quick:

aws ec2 run-instances \
--image-id ami-xxxxxxxxxxxxxxxxx \  # Replace with your desired Amazon Machine Image (AMI) ID
--instance-type t2.micro \           # Replace with your desired instance type
--key-name YourSSHKeyPair \          # Replace with the name 
of your SSH key pair
--security-group sg-xxxxxxxxxxxxxxxx \ # Assign the previous Security Group ID to the instance
--subnet-id subnet-xxxxxxxxxxxxxxxxx \ # Replace with the ID of your desired subnet
--associate-public-ip-address \      # Optional: Assign a public IP address
--count 1 \                          # Number of instances to launch
--tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=YourInstanceName}]'

Short note on this step, is that keep in mind if you are going to send heavy workloads to any database or large amount of data, increasing the EC2 Instance Type is important, as network bandwidht is related to the instance type assigned.

Usually the following command is all you need to enable that connection:

export BASTION_IP=<YourBastionIPAddress>
export RDS_ENDPOINT=<YourRDSEndpoint>
ssh -o StrictHostKeyChecking=no -o ExitOnForwardFailure=yes -fNL 1433:$RDS_ENDPOINT:1433 ec2-user@$BASTION_IP -i key.pem

This skips some issues that you can find, such as keeping alive the SSH tunnel in the background and SSH Key permissions assigned to the file or environment variable, usually for BitBucket Pipelines you use a secured environment variable for this.

Happy coding!