Ways to connect to postgress RDS instance from bastion host and from local host

Connecting_postgress_RDS_from_local_via_bastion.md

When it comes to databases and AWS VPC, best practice is to place your database in private subnet. By definition, private subnet in AWS is not reachable from the Internet because no Internet gateway is attached to private subnet. This is the way you protect your data. This kind of configuration is good for security but bad for data management.

How can you easily access and manage your secured data?

There are two basic ways to acees it.

1. Access postgres RDS from bastion host. There are following requirements for this.

• postgre RDS instance should open 5432 for ingress from bastion/jump server
• pgsql client should be installed on bastion host

Run following command to connect:

$psql -h <host> -p <port> -U <username> -W <password>

To install pgsql client you should following this page Install PostgreSQL Client

2. You can also access postgress sql from your local (Mac). For this you should create tunnel from you local to rds instance via bastion host.

Suppose: 
Bastion host IP is 132.5.10.11 and user is ec2-user and key_name is bastion_key.pem
Your postgress RDS instance fqdn is postgress.cpypigm0kth7.us-east-1.rds.amazonaws.com

Command to make tunnel:

ssh -i "bastion_key.pem" -NL 8886:postgress.cpypigm0kth7.us-east-1.rds.amazonaws.com:5432 ec2-user@132.5.10.11 -v

Here 8886 is port for you loacl host. 5432 is port of postgress on rds. NL — N will not open a session with the server. It will set up the tunnel. L will set up the port forwarding. -v : Is optional. With this you will print the ssh log on your terminal.

How to check if tunnel is established or not.

netstat -ntaP tcp | grep -i LISTEN | grep portnumber

It will give output like below.

shailendras-mbp:~ shaikuma$ netstat -ntaP tcp | grep -i LISTEN | grep 8886
tcp4       0      0  127.0.0.1.8886         *.*                    LISTEN               0          0
tcp6       0      0  ::1.8886               *.*                    LISTEN               0          0

Once tunnel is eatablished, You can either use pgsql command line client or you can download pgAdmin.

Using pgsql command line client on mac:

$psql -h localhost -p 8886 -U <username> 

Using pgAdmin: Steps are as follows:

1. Open pgAdmin
2. Make connection: Make sure while making connection you should use loaclhost as server address and port on which tunnel was made(in this case 8886)

Home screen will as below

Click on server and the click on add servers:

You should put localhost in hostname/address local port(8886) in port and put username and password accordingly. After this click on save.

very nice documentation! I am going to try to write a blog/video on setting this up in the near future. Will deff reference this!

Very helpful page, thank-you!