Cannot connect to AWS RDS PostgreSQL database in Java - SocketTimeoutException-CodePudding

I have a Spring app I'm trying to get hosted on AWS, and I've been struggling for a few days with the configuration.

I have an EC2 instance and have been able to connect to it with an SSH. I also have a Postgres RDS set up in AWS, but I haven't been able to connect to it with my code in my IDE, so I haven't yet uploaded the app to the EC2 instance.

I believe the issue is with my security groups, as my application.properties appears the way the AWS docs and other guides suggest.

I've been trying to understand the ins and outs of the security group stuff, but it's been a struggle. I assume that it's extremely important to be precise with my SGs in an actual production environment, but I'm trying to use this app only as a demo.

Any loose configuration that allows connectivity to the RDS DB will work for me right now.

application.properties

spring.datasource.url=jdbc:postgresql://myEndpoint:myPort/myAppName
spring.datasource.username=myDbUsername
spring.datasource.password=myDbPassword
spring.datasource.driver-class-name=org.postgresql.Driver
spring.jpa.hibernate.ddl-auto=update
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgresPlusDialect
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.show-sql=true
spring.session.jdbc.initialize-schema=always
spring.session.store-type=jdbc
spring.session.jdbc.table-name=spring_session
server.servlet.session.timeout=30m

I've tried multiple SG configs, here are 2 examples:

RDS config 1:

Public accessibility: Yes
VPC Security Groups: launch-wizard-1 (sg-id-1) / Active

sg-id-1

Inbound rules:
rule 1 -- IP version: IPv4, Type: SSH, Protocol: TCP, Port range: MySSHPort, Source: 0.0.0.0/0
rule 2 -- IP version: IPv4, Type: Custom TCP, Protocol: TCP, Port range: MyLocalPort, Source: 0.0.0.0/0

Outbound rules:
rule 1 -- IP version: IPv4, Type: All traffic, Protocol: All, Port range: All, Destination: 0.0.0.0/0

RDS config 2:

Public accessibility: Yes
VPC Security Groups: default (sg-id-2) / Active

sg-id-2

Inbound rules:
rule 1 -- Type: PostgreSQL, Protocol: TCP, Port range: 5432, Source: sg-id
rule 2 -- Type: All traffic, Protocol: All, Port range: All, Source: sg-id

Outbound rules:
rule 1 -- IP version: IPv4, Type: All traffic, Protocol: All, Port range: All, Destination: 0.0.0.0/0

When I try to connect with either security group set on the RDS, the connection is timing out with a SocketTimeoutException.

What's causing this?

EDIT: It finally connected. The security group configuration that finally allowed access was:

Inbound rules:
rule 1 -- Type: PostgreSQL, Protocol: TCP, Port range: 5432, Source: 0.0.0.0/0

Outbound rules:
N/A

I also added a subnet route table to the internet gateway, which I believe was necessary, although it initially did not allow access because the source on the SG inbound rule was still incorrect.

CodePudding user response：

A SocketTimeoutException means that your application wasn't able to get a response back from the endpoint, which in this case is the database.

Something must be wrong with your RDS configuration, here are a few things you can check.

1. Security Group (SG) inbound & outbound rules

In terms of your SG configuration, security groups are stateful.

This means that if you create an inbound rule allowing in traffic, that traffic is automatically allowed back out regardless of any outbound rules.

As such, the optimal config for a public PostgreSQL RDS DB would be:

Inbound rules:
rule 1 -- Type: PostgreSQL, Protocol: TCP, Port range: 5432, Source: 0.0.0.0/0

Outbound rules:
N/A

If your inbound rules do not allow PostgreSQL traffic to flow through, your request will never be hitting the database - security groups are applied at the instance level.

2. "Public Accessibility" RDS attribute

If the security group is set up correctly, double-check that the RDS instance's "Public Accessibility" attribute in "Network & Security" is set to "Yes".

This will assign the RDS instance a public IP address for external access & a public IP address is a requirement for a public database.

3. Subnet type

If your SGS are fine & your database has a public IP but you don't have an

A public subnet is another requirement for a public database, as this will ensure communication can flow between your VPC & the internet.

A private subnet without an internet gateway, will not allow external access.

4. DNS hostnames & DNS resolution VPC attributes

If you still cannot access your database publically, make sure that the DNS hostnames & DNS resolution VPC attributes are enabled for the VPC the RDS instance is in.

5. Network ACLs

Check that you haven't created any network ACLs in a default state or NACLs with blocking rules, which are associated with the subnet.

Default created NACLs block all inbound and outbound traffic like a firewall regardless of security group settings unless you add rules explicitly allowing them.

Your VPC will come with a default NACL which allows all inbound and outbound IPv4 traffic - use that one.

6. Authentication

Ensure your username & password / IAM authentication token / Kerberos credentials are 100% correct.

The above is a pretty comprehensive list so hopefully, one of the suggestions should allow you to connect to the database externally & rule out your Java code from being incorrect.

You can then make the RDS instance private, only accepting traffic from your EC2 instance(s) security group as well as removing the internet gateway from your subnet to make it private, applying the security at all layers principle of the security pillar in AWS's Well-Architected Framework.