Accessing to RDS posgtgreSQL within VPC?

Question

I have a RDS postgreSQL intsnce in my AWS. In my RDS PostgreSQL instance public accessibilty is 'NO' and i have my own VPC and private subnet. I choose that private subnet and VPC for my instance. I created a lambda function to connect the same RDS instance using the endpoint and checked. It was successfully connected.

Now, I want to access the RDS instance and create some table on this RDS postgreSQL instance. How could I access this RDS Instance and how to create tables on the same?

I installed AWS CLI in my system. I used below command to connect with AWS CLI.

psql -h endpoint -p 5432 -U username Databasename

But i am getting an error like "connection time out". But opened the port 5432 for the same security group.

Is there any way to connect using AWS CLI? I have an EC2 instance also.

Is there anyone to help me? This would be great if you can help me in this. Waiting for your favorable replay..

Answer 1

There are two ways you can connect to a Database in Private subnet -

• Connect to Nat Instance(EC2 in public subnet) and then connect to RDS from there.
• From any machine, you can create a tunnel from Nat to RDS with valid ssh keys.

Using Nat

1. First ssh into NAT using public IP or elastic IP.

    ssh -i key(pem) key_user@xx.xx.xx.xx(Public IP)
   

Then you can Run the command directly. you don`t need any AWs CLI or boto for this

    psql -h endpoint -p 5432 -U username -p Databasename 

2. From your system, you can create a tunnel through NAT. for that you need to run this on a command line. you only Need psql to be installed on your system or NAT.

    ssh -i key.pem -N -q -o "StrictHostKeyChecking=no" -L 54320:ENDPOINT:5432 key_user@PublicIP &
   

& will ensure that process will run in the background

Ensure that your process is running in Background after this by ps -ef | grep ssh After that, you can connect to psql using

    psql -h 127.0.0.1 -p 54320 -U username -p 

Here 54320 is the local port which will be used for connecting over a tunnel.

For Security Group Configuration Make sure you have these inbound rules -

1. PostgreSQL opened 5432 from NAT. you can add Nat Ip in by Selecting a custom type

2. SSh 22 should be opened for NAT

Answer 2

Since you Mentioned you are new to AWS I will try to answer accordingly :

You can connect to your aws ec2 instance via Putty , Please follow this Documentation

Now this is assuming that your ec2 is public subnet and RDS is in Private Subnet of Same VPC

Now Assuming that both EC2 and RDS have different security groups firstly you will have to :

To create a rule in a VPC security group that allows connections from another security group, do the following:

• Navigate to Security Groups and Select or create a security group that you want to allow access to members of another security group. In the scenario above, this would be the security group you will use for your DB instances. Choose Add Rule.
• From Type, choose All ICMP. In the Source box, start typing the ID of the security group; this provides you with a list of security groups. Select the security group with members that you want to have access to the resources protected by this security group. In the scenario above, this would be the security group you will use for your EC2 instance.
• Repeat the steps for the TCP protocol by creating a rule with All TCP as the Type and your security group in the Source box. If you intend to use the UDP protocol, create a rule with All UDP as the Type and your security group in the Source box
• Create a custom TCP rule that permits access via the port you used when you created your DB instance, such as port 3306 for MySQL. Enter your security group or an IP address you will use in the Source box.

Now you can Connect using your normal sql connect command :

mysql -h myinstance.123456789012.us-east-1.rds.amazonaws.com -P 3306 -u mymasteruser -p

Also for More Detailed Guide you can refer to AWS Guide

Hope this Helps you!

Answer 3

Since your RDS instance is in Private Subnet and is configured not to have public access, you wont be abke to access it directly from your system outside thr VPC.

What you can do here is to do an ssh/rdp to your ec2 instance (assuming its having elastic ip/public dns) using putty or terminal from your system and then access the rds from your ec2 instance.

You should enable access to port 22 (ssh) or port 3389 (rdp) to access your ec2 instance from your system

Answer 4

You will need to first connect to an ec2 instance in a public subnet in same vpc from which you can then connect to RDS instance.