Psycopg2 on AWS Lambda not connecting to RDS database

Question

When I try to connect to my RDS Postgresql DB I get the following output

{
   "errorMessage": "2022-01-07T13:28:35.428Z 975a92cd-936c-4d1c-8c23-6318cd609bff Task timed out after 10.01 seconds"
}

The DB is set to public access

Lambda psycopg2 connection code

connection = psycopg2.connect(user=user,
                              password=password,
                              host=host,
                              port=port,
                              database=database)

print(connection)

<connection object at 0x7ff7eb854b90; dsn: 'user=db_user password=db_password dbname=db_name host=rds_host port=5432', closed: 0>

RDS_LAMBDA_SECURITY_GROUP Inbound

VPC Route Table Routes

Also all the subnets are associated to the route table

Lambda_Role permissions

VPC Logs
Lots of REJECTED connections, not sure if it is safe to post a print here. Sometimes the connection to the DB is status ACCEPTED but there are a few other with REJECTED status

Any idea on why I still can't connect to my DB?

Are you running the Lambda in a VPC? Does it try to connect to the database using a local IP address or a public IP? Do you have a NAT? What are the egress rules for your Lambda's security group? Do you have a NACL that gets in the way? — Parsifal, Jan 07 '22 at 14:26
@Parsifal Yes Im running in the same VPC as the database. Im using the DB endpoint provided by the RDS. I dont have a NAT, the outbound rules for the Lambda's security group is 0.0.0.0/0 for all ports. I do have a NACL that allows 0.0.0.0/0 rule number 100 and Deny 0.0.0.0/0 * — Bruno Pigatto, Jan 07 '22 at 14:49
Verify that your Lambda is using a private IP to access the database. — Parsifal, Jan 07 '22 at 14:52
@Parsifal Do you mean the DB's IP? I only have access to the endpoint provided by the RDS console, how can I check de IP? — Bruno Pigatto, Jan 07 '22 at 15:04
@Parsifal Found the private IP at Network interfaces, using it as host now but still getting the same result — Bruno Pigatto, Jan 07 '22 at 15:48
You seem to have eliminated all of the possibilities, so I'm going to throw out a really basic question: is your Lambda running in the same VPC as your database? — Parsifal, Jan 07 '22 at 16:50
I think it's still worth using `socket.gethostbyname()` to verify that you're getting the private IP address associated with your database. If not, it indicates that _something_ is messed up. — Parsifal, Jan 07 '22 at 16:51
And outside of that, the best answer that I can give you is to enable VPC Flow Logs and look at the network interfaces associated with both the Lambda and the database. That's a painful process, but if you follow the chain you should be able to see where the packets stop flowing. — Parsifal, Jan 07 '22 at 16:52
Oh, and one other basic question: do the routing tables attached to the various subnets allow traffic to go between each other? — Parsifal, Jan 07 '22 at 16:53
@Parsifal Yes Im using the only VPC avaiable. Where exactly should I run the command `socket.gethostbyname()`? How can I check if the routing tables allow traffic between each other? — Bruno Pigatto, Jan 07 '22 at 17:09
@Parsifal The Route Table routes propagation status are negative, does it have some influence? — Bruno Pigatto, Jan 07 '22 at 17:25
@Parsifal The VPC log returned `2 405140832464 eni-0021758612320477d - - - - - - - 1641576644 1641576675 - NODATA` — Bruno Pigatto, Jan 07 '22 at 17:32
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/240869/discussion-between-bruno-pigatto-and-parsifal). — Bruno Pigatto, Jan 08 '22 at 15:57

score 0 · Answer 1 · answered Jan 11 '22 at 10:22

0

Whitelist Lambda security group in RDS security group to allow inbound access from lambda. (Add Lambda SG as source SG in RDS Inbound rules with RDS port)

This is required besides having lambda running in the same VPC or in different VPCs with peering

answered Jan 11 '22 at 10:22

omuthu

5,948
1
27
37

The RDS and Lambda's SG are the same, is that a problem? – Bruno Pigatto Jan 11 '22 at 18:24
I've updated the RDS_LAMBDA_SECURITY_GROUP Inbound section on the question with the new configurations. Still getting connection closed on lambda psycopg2 – Bruno Pigatto Jan 11 '22 at 18:47
Please whitelist same SG in inbound for RDS port If RDS and Lambda security groups are same. That will allow all request who is using this SG. – omuthu Jan 12 '22 at 04:26
The security group now has a 5432 (Postgres PORT) permission, but still cant connect using lambda – Bruno Pigatto Jan 12 '22 at 14:56

score 0 · Answer 2 · edited Jan 17 '22 at 13:54

0

I had this issue the other week. Put the lambda function and the RDS in the same VPC, with same security groups and subnets. Go on to the RDS Connectivity & security tab. Image showing the endpoint address referenced

The endpoint is your host you put into psycopg2.

This worked for me I used sqlalchemy with psycopg2 engine.

edited Jan 17 '22 at 13:54

S.B

13,077
10
22
49

answered Jan 17 '22 at 13:51

Milyan Brewer

1

score 0 · Accepted Answer · answered Jan 18 '22 at 23:22

0

I changed the lamba to nodejs, and then followed the exact same steps as this question and then I was able to make it work. I believe the error was related to a public subnet without a NAT

answered Jan 18 '22 at 23:22

Bruno Pigatto

67
2
13

Psycopg2 on AWS Lambda not connecting to RDS database

3 Answers3