Making PyGreSQL to work in AWS Lambda (Python 3.9) to connect to a PostgreSQL instance

Question

I struggled a bit to make PyGreSQL to work in AWS Lambda (Python 3.9) to connect to an Aurora PostgreSQL instance. Searching google and stack overflow didn't return any relevant results. Most of the hits were for making psycopg2 to work with AWS Lambda. So leaving the following out here for anybody else having the same issue and trying to figure a solution.

Here is my Lambda code.

import boto3
import cfnresponse
import logging
import os
import sys
# import DB-API 2.0 compliant module for PygreSQL 
from pgdb import connect
from botocore.exceptions import ClientError
import json

logger = logging.getLogger()
logger.setLevel(logging.INFO)

DBHOST = os.environ['DBHost']
DBPORT = os.environ['DBPort']
DBNAME = os.environ['DBName']
DBUSER = os.environ['DBUser']
SECRET_ARN = os.environ['Secret_ARN']
REGION_NAME = os.environ['Region_Name']

def handler(event, context):
    try:
        responseData = {}
      
        try:
            DBPASS = get_secret(SECRET_ARN,REGION_NAME)
            # Connection to SSL enabled Aurora PG database using RDS root certificate
            HOSTPORT=DBHOST + ':' + str(DBPORT)
            my_connection = connect(database=DBNAME, host=HOSTPORT, user=DBUSER, password=DBPASS, sslmode='require', sslrootcert = 'rds-combined-ca-bundle.pem')
            logger.info("SUCCESS: Connection to RDS PG instance succeeded")
      
        except Exception as e:
            logger.error('Exception: ' + str(e))
            logger.error("ERROR: Unexpected error: Couldn't connect to Aurora PostgreSQL instance.")
            responseData['Data'] = "ERROR: Unexpected error: Couldn't connect to Aurora PostgreSQL instance."
            cfnresponse.send(event, context, cfnresponse.SUCCESS, responseData, "None")
            sys.exit()
     
        if event['RequestType'] == 'Create':
            try:
                with my_connection.cursor() as cur:
                    #Execute bootstrap SQLs
                    cur.execute("create extension if not exists pg_stat_statements")
                    cur.execute("create extension if not exists pgaudit")
                    my_connection.commit()
                    cur.close()
                    my_connection.close()
                    responseData['Data'] = "SUCCESS: Executed SQL statements successfully."
                    cfnresponse.send(event, context, cfnresponse.SUCCESS, responseData, "None")
            except Exception as e:
                logger.error('Exception: ' + str(e))
                responseData['Data'] = "ERROR: Exception encountered!"
                cfnresponse.send(event, context, cfnresponse.SUCCESS, responseData, "None")
        else:
            responseData['Data'] = "{} is unsupported stack operation for this lambda function.".format(event['RequestType'])
            cfnresponse.send(event, context, cfnresponse.SUCCESS, responseData, "None")
          
    except Exception as e:
        logger.error('Exception: ' + str(e))
        responseData['Data'] = str(e)
        cfnresponse.send(event, context, cfnresponse.SUCCESS, responseData, "None")
        
def get_secret(secret_arn,region_name):

    # Create a Secrets Manager client
    session = boto3.session.Session()
    client = session.client(
        service_name='secretsmanager',
        region_name=region_name
    )

    try:
        get_secret_value_response = client.get_secret_value(
            SecretId=secret_arn
        )
    except ClientError as e:
        if e.response['Error']['Code'] == 'DecryptionFailureException':
            logger.error("Secrets Manager can't decrypt the protected secret text using the provided KMS key")
        elif e.response['Error']['Code'] == 'InternalServiceErrorException':
            logger.error("An error occurred on the server side")
        elif e.response['Error']['Code'] == 'InvalidParameterException':
            logger.error("You provided an invalid value for a parameter")
        elif e.response['Error']['Code'] == 'InvalidRequestException':
            logger.error("You provided a parameter value that is not valid for the current state of the resource")
        elif e.response['Error']['Code'] == 'ResourceNotFoundException':
            logger.error("We can't find the resource that you asked for")
    else:
        # Decrypts secret using the associated KMS CMK.
        secret = json.loads(get_secret_value_response['SecretString'])['password']
        return secret

I used my Cloud9 Amazon Linux 2 instance to create the lambda zip package. Installed Python 3.9 following https://computingforgeeks.com/how-to-install-python-on-amazon-linux/ and installed PyGreSQL using the following commands:

mkdir pygresql
pip3.9 install --target ./pygresql PyGreSQL

I included the contents of pygresql directory in the lambda package containing the lambda code.

Lambda was showing the following error during my test:

Cannot import shared library for PyGreSQL probably because no libpq.so is installed libldap_r-2.4.so.2: cannot open shared object file: No such file or directory

This is because AWS Lambda is missing the required PostgreSQL libraries in the AMI image. To fix this, I had to do the following:

Install PostgreSQL 14.3 on my cloud9. Its important to run the configure command with with-openssl option if you want to connect to an RDS/Aurora PostgreSQL instance where rds.force_ssl is set to 1.

sudo yum -y group install "Development Tools"
sudo yum -y install readline-devel
sudo yum -y install openssl-devel
mkdir /home/ec2-user/postgresql
cd /home/ec2-user/postgresql
curl https://ftp.postgresql.org/pub/source/v14.3/postgresql-14.3.tar.gz -o postgresql-14.3.tar.gz >> /debug.log
tar -xvf postgresql-14.3.tar.gz
cd postgresql-14.3
sudo ./configure --with-openssl
sudo make -C src/bin install
sudo make -C src/include install
sudo make -C src/interfaces install
sudo make -C doc install
sudo /sbin/ldconfig /usr/local/pgsql/lib

Then I copied the following files from /usr/local/pgsql/lib/ directory and included them in the lib directory of the lambda package containing the lambda code.

-rw-r--r-- 1 ec2-user ec2-user 287982 Aug  2 06:15 libpq.a
-rwxr-xr-x 1 ec2-user ec2-user 332432 Aug  2 06:15 libpq.so
-rwxr-xr-x 1 ec2-user ec2-user 332432 Aug  2 06:15 libpq.so.5
-rwxr-xr-x 1 ec2-user ec2-user 332432 Aug  2 06:16 libpq.so.5.14

Here are the contents of my lambda package:

drwxr-xr-x 1  1049089       0 Aug  1 15:25 PyGreSQL-5.2.4-py3.9.egg-info/
drwxr-xr-x 1  1049089       0 Aug  1 15:25 __pycache__/
-rw-r--r-- 1  1049089  345184 Aug  2 05:16 _pg.cpython-39-x86_64-linux-gnu.so
drwxr-xr-x 1  1049089       0 Aug  1 15:20 certifi/
drwxr-xr-x 1  1049089       0 Aug  1 15:20 certifi-2019.11.28.dist-info/
-rw-r--r-- 1  1049089    1845 Mar 23  2020 cfnresponse.py
drwxr-xr-x 1  1049089       0 Aug  1 15:20 chardet/
drwxr-xr-x 1  1049089       0 Aug  1 15:22 chardet-3.0.4.dist-info/
-rw-r--r-- 1  1049089    4391 Mar 23  2020 dbbootstrap.py
-rw-r--r-- 1  1049089 2094165 Aug  1 23:20 dbbootstrap.zip
drwxr-xr-x 1  1049089       0 Aug  1 15:22 idna/
drwxr-xr-x 1  1049089       0 Aug  1 15:22 idna-2.8.dist-info/
drwxr-xr-x 1  1049089       0 Aug  1 15:23 lib/
-rwxr-xr-x 1  1049089  104780 Mar 26 17:20 pg.py*
-rwxr-xr-x 1  1049089   66051 Mar 26 17:20 pgdb.py*
-rw-r--r-- 1  1049089   65484 Mar 23  2020 rds-combined-ca-bundle.pem
drwxr-xr-x 1  1049089       0 Aug  1 15:23 requests/
drwxr-xr-x 1  1049089       0 Aug  1 15:23 requests-2.22.0.dist-info/
drwxr-xr-x 1  1049089       0 Aug  1 15:23 urllib3/
drwxr-xr-x 1  1049089       0 Aug  1 15:25 urllib3-1.25.8.dist-info/

AWS Lambda was happy after this and was able to connect to the PostgreSQL instance.

score 0 · Answer 1 · answered Nov 14 '22 at 15:13

Thanks @Arabinda for this, nothing else really worked for me while trying to use psycopg2 and I accidentally found your guide. I was able to import psycopg2 using this way but just using pip3.9 install --target ./pygresql PyGreSQL psycopg2 instead of pip3.9 install --target ./pygresql PyGreSQL

After that, I just zipped the contents under python\lib\python3.9\site-packages and uploaded it as a Layer.

score 0 · Answer 2 · answered Jan 09 '23 at 11:34

We stumbled across the same problem and didn't want to provision a whole new server. So we ended up with a dockerfile building the layer zip for us. In case somebody could use it.

# https://docs.aws.amazon.com/serverless-application-model/latest/developerguide/serverless-image-repositories.html
FROM public.ecr.aws/sam/build-python3.9:latest

# https://ftp.postgresql.org/pub/source/
ARG postgresql_version=14.6

RUN yum upgrade -y && \
    yum install -y openssl-devel openssl-static

WORKDIR /tmp
ENV PREFIX /tmp/local

RUN \
  curl -fsSL https://ftp.postgresql.org/pub/source/v${postgresql_version}/postgresql-${postgresql_version}.tar.bz2 \
    -o postgresql-${postgresql_version}.tar.bz2

RUN \
  mkdir ${PREFIX} && \
  tar jxf postgresql-${postgresql_version}.tar.bz2 && \
  cd postgresql-${postgresql_version} && \
  ./configure  \
    --prefix=${PREFIX} \
    --with-openssl \
    --without-readline \
  && \
  make install

RUN \
    export PATH="$PATH:${PREFIX}/bin/" && \
    mkdir /tmp/python && \
    mkdir /tmp/lib && \
    mkdir /tmp/lib64 && \
    pip3 install  --target /tmp/python PyGreSQL

RUN \
  cd ${PREFIX} && \
  cp lib/libpq.so* /tmp/lib && \
  cp /lib64/libssl.so.10 /tmp/lib64

RUN \
   cd /tmp && \
   zip -r /tmp/libs.zip ./python ./lib ./lib64

ENTRYPOINT ["cat", "/tmp/libs.zip"]

Run

docker run myBuild:latest > layer.zip

to extract the zip from the dockerimage.

Making PyGreSQL to work in AWS Lambda (Python 3.9) to connect to a PostgreSQL instance

2 Answers2