How can I connect to my Amazon RDS DB instance using a bastion host from my Linux/macOS machine?

Last updated: 2020-09-23

I have an Amazon Relational Database Service (Amazon RDS) DB instance that is not publicly accessible. I would like to connect to it from my Linux/macOS machine. How can I connect to my RDS DB instance using a bastion host?

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 cannot use either a VPN or AWS Direct Connect, then the preferred option is to use a bastion host. You can also use this method to connect to Aurora Serverless and RDS Proxy from outside the VPC. This example shows you how to set up a bastion host to connect to your RDS DB instance from a Linux/macOS machine, even though the RDS DB instance is private.

Resolution

1.  Set your Amazon RDS DB instance to private by modifying the DB instance. Set the publicly accessible parameter to no, with private subnets (i.e., no Internet gateway - igw in route tables). Set the security group to allow the DB to port (5432, 3306) from all IPs.

2.  Launch the smallest available EC2 instance in the same VPC as your DB instance. Set your Amazon Elastic Compute Cloud (Amazon EC2) instance to be accessible from internet, with public subnets (i.e., has Internet gateway - igw in route tables). Set the security group to allow the IP of the Linux/macOS machine you are trying to connect from.

3.  Run the following command from your Linux/macOS machine to create a tunnel for connectivity from your machine:

Syntax 1:
ssh -i <identity_file> -f -l <bastion-host-username> -L 
<local-port-you-connect-to>:<rds-endpoint>:<rds:listening-port>
 <bastion-host-public-ip> -v

Example Command:
ssh -i "private_key.pem" -f -l ec2-user -L 5432:172.31.39.62:5432 3.133.141.189 -v

When you run the command above (SSH tunneling), you configure the following settings:

  • debug1: Local connections to LOCALHOST: 5432 forwarded to remote address 172.31.39.62: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.
Syntax 2:
ssh -i "Private_key.pem" -f -N -L 5433:RDS_Instance_Endpoint:5432 ec2-user@EC2-Instance_Endpoint -v

Example Command:
ssh -i "private.pem" -f -N -L
5433:pg115.xxxx.us-east-2.rds.amazonaws.com:5432
ec2-user@ec2-xxxx-xxx9.us-east-2.compute.amazonaws.com -v
4. Now that SSH tunneling is in place, you can connect to your DB instance from your local Linux/macOS machine. The following example connects to PostgreSQL, but you can also use this method to connect to MySQL, or any other engine you want to connect to.
Syntax -

psql -hlocalhost -Upostgres -p -d postgres


-h = localhost

-U = the username present in the DB for connectivity

-p = 'local-port-you-connect-to' from the SSH Tunneling command

-d = Any DB, user wish to connect.

Example command -
a483e73d651f:.ssh rahul_saha$ psql -hlocalhost -Upostgres -p5432 -d postgres
Password for user postgres: 
psql (12.1, server 11.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 rdsadmin  | rdsadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin
 template0 | rdsadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin          +
           |          |          |             |             | rdsadmin=CTc/rdsadmin
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)
$ psql -hlocalhost -Upostgres -p -d postgres
$ mysql -h127.0.0.1 -uroot -p  

Note: MySQL tries to connect using the socket if you use the keyword localhost when connecting to DB instance. Make sure to use the hostname 127.0.0.1 when accessing a MySQL DB instance. For more information, see the MySQL documentation for Can't connect to [local] MySQL server.