AWS RDS for PostgreSQL cannot be connected after several hours

Question

I created several instances of RDS with PostgreSQL and get the same problems:

• I can connect to all of them right after creating the instances.
• After several hours (I stop working on it, turn off my laptop), I cannot connect to any of them again.
• I use DBeaver for the connections, the error show is "Connection attempt timed out."

I attached the information of the . Hope someone can help me with this problem. Thank you in advance.

Natika

There seems to be an option under properties to "Connect on query execute". Try ticking that option. Also I see an option to auto refresh (clock symbol), try it with a large value at first – Sydelle

Answer 1

Finally, I found the answer for my problem. For the error of "connection timeout", one of the reasons can be from the security settings. Although I set it as public when creating the RDS instance, the instance is attached with a private VPC security group which is not exposed publicly.

I can access to RDS from the same IP address that I'm on when I created it. When I go to other place (or switching VPN), I'm unable to connect it. That explains the intermittent connection.

We can attach the RDS instance with a public security group inside the VPC (I don't think it is a good setting, just for the beginner in AWS like me) as below:

• from Services, select EC2, select Security Groups in the left panel.
• click "Create Security Group" button.
• in the dialog, enter the name for the Group, e.g "postgres-public-access"
• if you have several VPC's, make sure in the last dropdown that you select the same VPC that your database is in
• in the dialog, click "Add Rule" button.
• In the "Type" column, select "PostgreSQL" or other types of RDS instances (or you can input the port of your RDS instance, usually it is 5432 for Postgres).
• In the "Source" column, enter "0.0.0.0/0".
• Click "Save" button.
• from Services, select RDS, select the RDS instance, click "Modify" button.
• In "Network & Security", "Security group", select the VPC Security Group you just created, in my case, it is "postgres-public-access".
• Click "Continue" button. Now you can go ahead and connect with your database everywhere.

Natika

Thanks a lot! For me just removing the default security group also worked after clicking "modify" for my database. Would not have figured it out without you! – Jenette

This is the most unintuitive process - what's the "public" option for if it doesn't make it public??? Thank you!!! – Nationality

Thank you. I edited the answer to make sure when you create the Security Group, that you ALSO select the SAME VPC as the db. – Predicate

Answer 2

I had to add/edit a rule to the VPC to allow connections from All sources.

Steps:

1. Go to DB > Connectivity & security > click on VPC(vpc-

2. Under Security > Security Groups > open sg-[something] for which VPC ID matches the DB VPC

3. Inbound Rules > Edit Rules > Change Source to anywhere

So it seems that even when creating the DB and selecting allow public access, it only includes the traffic from withing the VPC. By doing the above steps you can allow access to all sources.

Neve

fyi: you cannot change the source if it is used by a VPC. so you can first delete the inbound rule, and create new one with specified options above. – Case

Answer 3

I just followed the guide: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ConnectToPostgreSQLInstance.html

Run through the typical things:

• Make sure the Database is Public! Check in the AWS Website Console, if its Private make it public.

• Check you have the Firewall port open for the software and the port you're trying to connect through.

• When you create a dB in RDS a Security Group is created automatically with the Rule All, All:

• You can add a rule for TCP Port 5432, like I have above.

• Check Username/Password - sometimes incorrect ones get cached.

• Try to ping the dB to see if its a internet connection problem.

Booker

Can you please point out how to check if the Database is Public ... Thanks – Masseuse

@VivekPuurkayastha in AWS if the RDS DB isn't in a VPC it will be publicly accessible if you override the default Network Security Group (NSG). If you create an RDS in a VPC, typically you will put it in a Private subnet (with a NSG to the public subnet allowing the DB Port) and this way the DB won't be reachable, only things in the Public subnet like web servers are public accessiblbe, (unless you have a jump box). The difference between private and public subnets in a VPC is public ones are connected to an Internet Gateway and have route rule allowing WWW, 0.0.0.0. – Booker

Answer 4

Select DB -> Modify -> Connectivity-> Save

Duenna

Answer 5

In my case, I did not set up internet gate. Check your db's route table in VPC. And, add internet gateway

Donahue

This is what was happening for me as well. This guide shows you how to edit the routing in detail: medium.com/overlander/… – Bryantbryanty

Answer 6

I faced the same issue and it end up because of the VPN am using, when i disconnected the VPN i apply to connect.

Bondy