Unable to connect to Amazon RDS database with Inbound rule to allow all connection and publicly accessible turned on

Question

I created a postgresql RDS database instance and connected to an EC2 instance. I am able to ssh into the EC2 instance and access the database from there, but I am unable to connect to the database using psql or using prisma to connect to the db.

The error I get in prisma-

Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "postgres", schema "public" at "greengainsdb.cfyqeu2ws1b6.us-west-2.rds.amazonaws.com"

Error: P1001: Can't reach database server at `greengainsdb.cfyqeu2ws1b6.us-west-2.rds.amazonaws.com`:`5432`

Please make sure your database server is running at `greengainsdb.cfyqeu2ws1b6.us-west-2.rds.amazonaws.com`:`5432`.

and the error I get using psql

psql --host=greengainsdb.cfyqeu2ws1b6.us-west-2.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=postgres
Password:
psql: error: connection to server at "greengainsdb.cfyqeu2ws1b6.us-west-2.rds.amazonaws.com" (35.155.218.201), port 5432 failed: Operation timed out
    Is the server running on that host and accepting TCP/IP connections?

And this the security group I have on the database.

I tried changing the security group to allow all connections on TCP on anywhere IP to test it out to see if I am able to connect to the database but I still can't connect to the database. SSH into EC2 works perfectly fine. Am i missing something? I have a feeling that I am on a private subnet but I don't know how to change the subnet settings to make it public.

Answer 1

Databases are normally launched in private subnets to add another layer of protection. Anything in a private subnet is, by definition, not directly reachable from the Internet.

It would appear that you have:

• An Amazon EC2 instance in a public subnet
• An Amazon RDS database in a private subnet

You can SSH into the EC2 instance because it is accessible to the Internet and from there you can connect to the database because all resources within a VPC can communicate with each other as long as the Security Group permits it.

There is another setting on Amazon RDS databases that is relevant. Your database might be set to Publicly available = No, which means that the DNS name (URL) of the database will only resolve to a private IP address. This address is only reachable from within the VPC.

So, you have two options:

Option 1: Use port forwarding

When establishing the SSH connection to the EC2 instance you can 'forward' a port from your own computer to the EC2 instance. For example, this is a normal SSH command:

ssh -i key.pem ec2-user@1.2.3.4

but you can add port forwarding with:

ssh -i key.pem -L 5432:greengainsdb.cfyqeu2ws1b6.us-west-2.rds.amazonaws.com:5432 ec2-user@1.2.3.4

This is saying: "Forward local port 5432 across the SSH connection. The computer at the other end of the connection should forward the traffic to greengainsdb.cfyqeu2ws1b6.us-west-2.rds.amazonaws.com:5432."

You can then connect to the database by referring to localhost:5432.

This will allow you to connect to the private database without compromising security and is the recommended method. In fact, many SQL clients allow you to configure SSH Port Forwarding as part of the database connection, so they will do the above command for you.

Option 2: Expose your database

If you really want to directly connect to your database, you will need this configuration:

• The Amazon RDS database needs to be in a public subnet
• It needs to be configured as Publicly Available = Yes
• The Security Group associated with the database needs to permit access from your computer's IP address

This will permit direct access to the database. However, it also makes it more vulnerable to attack from the Internet. A correctly configured Security Group will only permit access from your IP address, but putting a database in a public subnet is often regarded as poor security practice.