Cant write to Database with AWS Lambda

Question

I am trying to write files to a postgres database with AWS Lambda but I am facing an error:

Calling the invoke API action failed with this message: Network Error

My code looks like this:

from sqlalchemy import create_engine
import pandas as pd

def test(event=None, context=None):
    conn = create_engine('postgresql://user:password@url:5439/database')

    df = pd.DataFrame([{'A': 'foo', 'B': 'green', 'C': 11},{'A':'bar', 'B':'blue', 'C': 20}])

    df.to_sql('your_table', conn, index=False, if_exists='replace', schema='schema')

test()

Resources: Memory - 1280MB Timeout - 2 minutes

What is the problem here and how else could I write pandas Dataframe to a Database with AWS Lambda?

Is your RDS publicly available and has correctly set security groups? — Marcin, Apr 17 '20 at 08:46
Hi @Marcin, I am not sure, but running the script in my local machine it executes it fine. — Jonas Palačionis, Apr 17 '20 at 08:48
This is my first time trying to use AWS lambda, I am not sure. I simply went to Lambda - > create function and used the code above. I understand for each lambda function AWS creates a vpc or runs it as serverless? — Jonas Palačionis, Apr 17 '20 at 08:53
If you used default settings, than you are not in vpc (which is good). — Marcin, Apr 17 '20 at 08:54
`Calling the invoke API action failed with this message: Network Error` where and how do you get this error? Seems like problem `invoking` lambda function itself. — Marcin, Apr 17 '20 at 08:55
I am getting this error after I click test. My lambda handler is `testing.test` because I upload a zip file to s3 with pandas, psycopg2, sqlalchemy packages where the code is named `testing.py`. — Jonas Palačionis, Apr 17 '20 at 08:58
I am quering an API, creating a dataframe and trying to write it to a database, I've managed to print out the whole dataframe which means the code is working, I am getting an error when I am trying to write it to the database. — Jonas Palačionis, Apr 17 '20 at 09:00
If `def test` is your handler, than you don't need to call it yourself at the end `test()`. But not sure if this is the cause or not. — Marcin, Apr 17 '20 at 09:02
Still got the same error when I remove the `test()` at the last line. — Jonas Palačionis, Apr 17 '20 at 09:06

score 0 · Answer 1 · answered Apr 18 '20 at 21:44

I'm assuming the Postgres instance is in RDS.

Is your lambda in your VPC? You can check this on the function's page in admin console, in the VPC box. By default it's not and the VPC box says "None".

Case 1: Lambda is not in VPC

Then the issue might be that the security group associated with your RDS instance does not allow connections from outside the VPC. That's the default if you didn't touch the security group. Find the security group for your RDS instance from the RDS admin, then check out the "Inbound rules" for that security group. Lambdas don't have an IP so you'll need to add an inbound rule allowing at least postgres traffic for source "0.0.0.0/0", i.e. the entire internet.

This should be sufficient but note that this is not considered very good for security, since anyone can now in theory reach your DB (and worse if they can guess the password). But depending on your project that might not be a problem for you. If that is an issue for you, you could instead associate your lambda with the same VPC the RDS instance is in, in order to provide better networking security, and move to Case 2.

Case 2: Lambda is in a VPC

I'm assuming you put the lambda in the same VPC as the RDS instance for simplicity - if not you probably know what you're doing.

All you need to do now (providing you didn't touch other network configs) is ensure your RDS instance's security group allows access from your lambda's security group. So you could put both in the default security group, or put them in separate groups but make sure the RDS one has an inbound rule allowing the lambda one.

Note that if your lambda also needs to call external services (since you mention querying an API), in order to enable that, after linking it to your VPC you'll also need to create a NAT Gateway like I described here: https://stackoverflow.com/a/61273118/299754

Cant write to Database with AWS Lambda

1 Answers1