In this guide I'll explain how to connect to AWS RDS (Relational Database Service) postgres instance from your local machine when your instance is not publicly accessible. This guide uses the SSH tunnel method. Connecting to an RDS instance via an SSH tunnel is a secure method, especially if your RDS instance is not publicly accessible. The SSH tunnel will port forward your connection from an EC2 instance to the RDS instance.
I can confirm this solution works with:
- ElasticBeanstalk stack with attached rds instance.
- ec2 instance which is in the same vpc as the rds instance
I'll also explain how to setup a connection via terminal psql, PgAdmin4 and IntelliJ.
What's SSH tunneling
SSH Tunneling, also known as SSH port forwarding, lets you securely access remote services by making them available on your local computer as if they were running locally (other example: connect to phpmyadmin that's running on a remote server's localhost).
In this case we're going to setup SSH tunneling via Bastion host - ec2 server will act as an intermediary so you can access services from remote AWS RDS as if they were running locally on your machine. Why are we doing that? AWS RDS is not publicly accessible.
Maybe it would be easier if illustrated with animated process below:
When you open an SSH tunnel to a port 123, you can make a request to port 123.
- EC2 instance will acknowledge the request to 123 and forward it to AWS RDS
- Service on AWS RDS that's running on 123 will send a response
- EC2 instance will forward the response back to you
Prerequisites
Before I begin with instructions, ensure you have the following setup:
- EC2 Instance: Ensure you have an EC2 instance in the same VPC as your RDS instance. This EC2 instance will act as the jump server (bastion host). It's purpose in this case is to allow access to an private network in which the rds instance is accessible.
- SSH Access: Ensure you have SSH access to your EC2 instance using an SSH key pair. Verify that you can connect to your ec2 instance:
Optionally, configure passwordless login so you don't need to point to ec2.
ssh -v -N -L ec2-user@ip-addressIf connection doesn't go through, you might want to check my other post for connecting to ec2.
How to Connect to RDS Locally with psql
Setup an ssh tunnel with the following command (append -i path_to_your.pem if you haven't configured passwordless login):
And now you can connect to your RDS instance using localhost:
# setup a tunnel
ssh -N -L 5433:database-1.cd3ccciwf.us-east-1.rds.amazonaws.com:5432 [email protected]
# connect
psql -h 127.0.0.1 -p 5432 -U your-username -W
How to connect to RDS from PgAdmin4 locally via SSH tunnel
Open pgadmin4, add a new server. Give it a name, click on the Connection tab. Fill the fields with your connection details for rds (host, user, pass). Leave the maintenance database to be postgres.
Click on SSH Tunnel, and fill tunnel host, username and identity file.
Click on Save, you should be able to connect to your server and see ebdb database in list.
How to connect to RDS from IntelliJ IDEA locally via SSH tunnel
Add a new datasource – Click on Database tab -> + sign -> Datasource -> PostgreSQL. Like shown in the screenshot:
Fill the fields with your connection details for rds (host, user, pass). Database will probably be ebdb.Click on SSH/SSL, check Use SSH tunnel, add a new configuration by clicking three dots on the right. Fill tunnel host, username and identity file.
Click on Apply, update driver if you need to, and test the connection.
Troubleshooting
There might be cases where you can't connect to aws rds from local machine:
- Connection Refused: Check that security group for your EC2 instance allows inbound SSH traffic (22 port) from your local machine.
- Authentication Failed: Check the SSH key pair and verify that the correct
.pemfile is used. - Cannot reach RDS: Check the rds endpoint and ensure the ec2 instance can connect to the rds instance internally.