CODEWITHSUNDEEP
pythonpostgresqlamazon-web-servicesaws-lambdaamazon-rds
Allen Yang
Allen Yang

Reputation: 53

AWS Lambda to RDS PostgreSQL

Hello fellow AWS contributors, I’m currently working on a project to set up an example of connecting a Lambda function to our PostgreSQL database hosted on RDS. I tested my Python + SQL code locally (in VS code and DBeaver) and it works perfectly fine with including only basic credentials(host, dbname, username password). However, when I paste the code in Lambda function, it gave me all sorts of errors. I followed this template and modified my code to retrieve the credentials from secret manager instead.

I’m currently using boto3, psycopg2, and secret manager to get credentials and connect to the database.

List of errors I’m getting-

  1. server closed the connection unexpectedly. This probably means the server terminated abnormally before or while processing the request
  2. could not connect to server: Connection timed out. Is the server running on host “db endpoint” and accepting TCP/IP connections on port 5432?
  3. FATAL: no pg_hba.conf entry for host “ip:xxx”, user "userXXX", database "dbXXX", SSL off

Things I tried -

  1. RDS and Lambda are in the same VPC, same subnet, same security group.
  2. IP address is included in the inbound rule
  3. Lambda function is set to run up to 15 min, and it always stops before it even hits 15 min
  4. I tried both database endpoint and database proxy endpoint, none of it works.

It doesn’t really make sense to me that when I run the code locally, I only need to provide the host, dbname, username, and password, that’s it, and I’m able to write all the queries and function I want. But when I throw the code in lambda function, it’s requiring all these secret manager, VPC security group, SSL, proxy, TCP/IP rules etc. Can someone explain why there is a requirement difference between running it locally and on lambda?

Finally, does anyone know what could be wrong in my setup? I'm happy to provide any information in related to this, any general direction to look into would be really helpful. Thanks!

Upvotes: 5

Views: 14650

Answers (2)

Josh
Josh

Reputation: 3345

It seems like you've already solved your issue, but I had trouble getting Psycopg2 to work, so I thought I'd share an alternative I found in another post...

Pg8000 was very easy to get working: https://github.com/tlocke/pg8000

No need to build binaries or push enormous packages to lambda layers. Maybe someone else will benefit as well.

Cheers!

Upvotes: 0

envoy
envoy

Reputation: 36

Following the directions at the link below to build a specific psycopg2 package and also verifying the VPC subnets and security groups were configured correctly solved this issue for me.

I built a package for PostgreSQL 10.20 using psycopg2 v2.9.3 for Python 3.7.10 running on an Amazon Linux 2 AMI instance. The only change to the directions I had to make was to put the psycopg2 directory inside a python directory (i.e. "python/psycopg2/") before zipping it -- the import psycopg2 statement in the Lambda function failed until I did that.

This the VPC scenario I'm using. The Lambda function is executing inside the Public Subnet and associated Security Group. Inbound rules for the Private Subnet Security Group only allow TCP connections to 5432 for the Public Subnet Security Group.

Upvotes: 2

Related Questions