How do I connect to my Amazon RDS DB instance with a bastion host from my Linux or macOS machine?

4 minute read

I want to connect to my private Amazon Relational Database Service (Amazon RDS) DB instance from my Linux or macOS machine.

Short description

To connect to a private Amazon RDS or Amazon Aurora DB instance, it's a best practice to use a VPN or AWS Direct Connect. If you can't use either a VPN or AWS Direct Connect, then you can use a bastion host instead.

Resolution

Set the Amazon RDS DB instance to private

Modify the DB instance with the following parameters:

For Publicly accessible, choose No and use private subnets.

For Route tables, don't use Internet gateway - igw.

For Security group, allow inbound traffic on ports 5432 and 3306 from the bastion host security group.

Launch an EC2 bastion host instance

Launch the smallest available EC2 instance in the same VPC as your DB instance with the following parameters:

For Route tables, use Internet gateway - igw. This value opens your Amazon Elastic Compute Cloud (Amazon EC2) instance to the internet on a public subnet.

For Security group, allow port 22 from the IP address of the Linux or macOS machine that you want to connect from.

Create an SSH tunnel

To create an SSH tunnel to your DB instance, run one of the following commands from your Linux or macOS machine.

Use the RDS private IP address

Run the following command:

ssh -i IDENTITY-FILE -f -l BASTION-HOST-USERNAME -L LOCAL-PORT:RDS-ENDPOINT:RDS-LISTENING-PORT BASTION-HOST-PUBLIC-IP -v

Note: Replace IDENTITY-FILE with your private key file, BASTION-HOST-USERNAME with the bastion host user, and LOCAL-PORT with the local port to forward. Also, replace RDS-ENDPOINT with the RDS private IP address, RDS-LISTENING-PORT with the database port, and BASTION-HOST-PUBLIC-IP with the bastion host public IP.

After a successful connection, you receive output similar to the following:

debug1: Local connections to LOCALHOST:5432 forwarded to remote address RDS-ENDPOINT:5432
debug1: Local forwarding listening on 127.0.0.1 port 5432
debug1: channel 0: new [port listener]
debug1: Local forwarding listening on ::1 port 5432

Use the RDS DNS endpoint

Run the following command:

ssh -i IDENTITY-FILE -f -N -L LOCAL-PORT:RDS-INSTANCE-ENDPOINT:RDS-LISTENING-PORT BASTION-HOST-USERNAME@EC2-INSTANCE-ENDPOINT -v

Note: Replace IDENTITY-FILE with your private key file, LOCAL-PORT with the local port to forward, and RDS-INSTANCE-ENDPOINT with the RDS DNS endpoint. Also, replace RDS-LISTENING-PORT with the database port, BASTION-HOST-USERNAME with the bastion host user, and EC2-INSTANCE-ENDPOINT with the EC2 public DNS name.

Connect to the DB instance

After the SSH tunnel is active, connect to your DB instance from your local machine. You can use this method to connect to MySQL or any other engine.

To connect to a PostgreSQL DB instance, run the following command:

psql -h localhost -U DB-USERNAME -p LOCAL-PORT -d DATABASE-NAME

Note: Replace DB-USERNAME with the database username, LOCAL-PORT with the local port from the SSH tunnel command, and DATABASE-NAME with the database to connect to.

To connect to a MySQL DB instance, run the following command:

mysql -h 127.0.0.1 -P LOCAL-PORT -u DB-USERNAME -p

Note: Replace LOCAL-PORT with the local port from the SSH tunnel command and DB-USERNAME with the database username.

If you use the keyword localhost when you connect to a MySQL DB instance, then MySQL tries to use the socket to connect. Use the hostname 127.0.0.1 to access a MySQL DB instance. For more information, see Can't connect to [local] MySQL server on the MySQL website.