I have an Amazon Relational Database Service (Amazon RDS) DB instance that isn't publicly accessible. I want to connect to it 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. You can also use this method from a Linux or macOS machine to connect to Amazon Aurora Serverless and Amazon RDS Proxy from outside the instance's virtual private cloud (VPC).
Resolution
To use a bastion host to connect to your Amazon RDS DB instance from a Linux or macOS machine, follow these steps:
-
Set your Amazon RDS DB instance to private. To do this, modify the DB instance with the following parameters:
Set the publicly accessible parameter to no and use private subnets.
Don't use Internet gateway - igw in your route tables.
Set the security group to allow the DB to ports 5432 and 3306 from all IP addresses.
-
Launch the smallest available EC2 instance in the same VPC as your DB instance:
Use Internet gateway - igw in your route tables. This value opens your Amazon Elastic Compute Cloud (Amazon EC2) instance to the internet when it's on a public subnet.
Set the security group to allow the IP address of the Linux or macOS machine that you want to connect from.
-
To create a tunnel for connectivity, run the following command from your Linux or macOS 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 this command for SSH tunneling, configure the following settings with these values:
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 5432:RDS_Instance_Endpoint:5432 ec2-user@EC2-Instance_Endpoint -v
Example command:
ssh -i "private.pem" -f -N -L
5432:pg115.xxxx.us-east-2.rds.amazonaws.com:5432
ec2-user@ec2-xxxx-xxx9.us-east-2.compute.amazonaws.com -v
-
You can now connect to your DB instance from your local machine. The following example command connects to PostgreSQL, but you can use this same method to connect to MySQL or to any other engine:
Syntax -
psql -hlocalhost -Upostgres -p<local-port-you-connect-to> -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 who wants 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: If you use the keyword localhost when you connect to the DB instance, then MySQL tries to use the socket to connect. Make sure that you 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.
Related information
Linux bastion hosts on AWS
How can I connect to a private Amazon RDS DB instance from a local machine using an Amazon EC2 instance as a bastion host?