Connect to AWS Aurora PostgreSQL/Amazon Redshift Database from AWS Lambda

Image by Peggy und Marco Lachmann-Anke from Pixabay

In this blog post I will discuss following scenarios to connect to databases from AWS Lambda function:

• Connecting to Amazon Aurora PostgreSQL database in private subnet with public accessibility set to No in same AWS account.
• Connecting to cross account Amazon Redshift database in public subnet with public accessibility set to Yes.

Connect to Amazon Aurora PostgreSQL database in Private subnet with Public accessibility set to No in the same AWS account

In this setup, Amazon Aurora PostgreSQL database is running in private subnet with public accessibility set to No. The connectivity and security detail are as follows:

To connect to the Aurora PostgreSQL database in a Private subnet you need to configure Lambda function in a Virtual Private Cloud (VPC). Lets go ahead and create the Lambda function -

AWS Service > Lambda > Functions > Create Function > Author from scratch

Under Basic Information

• Enter the function name
• Choose the language of your preference to use. Here I selected Python 3.8.
• In the Permissions section you can keep the default - Create a new role with basic Lambda permissions

Under Advanced settings

• Enter the details in Network
  • Choose the VPC name in which the Aurora PostgreSQL database is running
  • Select at least 2 Private subnets. To access private Amazon VPC resources, such DB instance you need to associate your Lambda function with one or more private subnets. In case you select public subnets instead of Private subnet, the Lambda function will time out as they cannot have public IP addresses.
  • Choose the default security group

Once you have completed the details, click on Create function. The function creation can take some time as Lambda creates ENI (elastic network interface) in each subnet of the VPC configuration. An ENI represents a virtual network cards and you can read more here.

In this case the Lambda function is launched with an execution role (IAM role) having 2 managed policies attached by default:

• AWSLambdaBasicExecutionRole
• AWSLambdaVPCAccessExecutionRole

In case you choose to use an existing role while creating the Lambda function make sure to attach AWSLambdaVPCAccessExecutionRole policy. This managed policy has the following permissions which Lambda uses to create and manage network interfaces:

• ec2:CreateNetworkInterface
• ec2:DescribeNetworkInterfaces
• ec2:DeleteNetworkInterface

Below is the VPC configuration for my Lambda function attached with 2 Private subnets:

After creating the lambda function, I used the below code to connect and execute SQL against the Amazon Aurora PostgreSQL database. The code installs the PostgreSQL interface library pg8000 to interact with the database. The handler creates a connection to the PostgreSQL database, executes a SELECT sql to fetch the current timestamp from the database into results variable and returns the results as string.

import sys
import boto3
import logging
import urllib.parse
from pip._internal import main

# install pg8000
main(['install', '-I', '-q', 'pg8000', '--target', '/tmp/', '--no-cache-dir', '--disable-pip-version-check'])
sys.path.insert(0,'/tmp/')
    
import pg8000

def lambda_handler(event, context):
    
    sql = """SELECT current_timestamp"""
    
    conn = pg8000.connect(
        database='demodb',
        user='admin',
        password='xxxxxxx',
        host='cluster-demodb.cluster-cijke9kklkrh.us-east-1.rds.amazonaws.com',
        port=8192,
        ssl_context=True
        )
        
    dbcur = conn.cursor()
    dbcur.execute(sql)
    results = dbcur.fetchall()
    dbcur.close()
    
    return str(results)

Output:

Connect to cross account Amazon Redshift database in Public subnet with Publicly accessible set to Yes

Below is Amazon Redshift connection details running in Account A. The database is running in public subnet and is publicly accessible. The security groups acts as virtual firewall for the cluster to control inbound and outbound traffic.

In Account B I have created a new VPC "Cross-Account-Lambda-VPC" to test this use-case. To create the Lambda function I followed the same steps as mentioned in the previous section except that the VPC selected in this case was "Cross-Account-Lambda-VPC" (VPC in Account B). The screenshot below is of Lambda function from Account B which has 2 private subnets added.

The private subnets are attached to a route table. The route table needs a NAT Gateway attached.

The NAT gateway resides in a public subnet and has an Elastic IP (EIP) associated with it which acts as a public IP address and can connect to the internet through the VPC's internet gateway.

This EIP address needs to be added to the inbound rules of security group attached with the Amazon Redshift database in Account A as shown below. With this configuration settings the lambda function will be able to connect to the database in cross account.

For now to conclude, in this blog post we reviewed step by step how we can setup the Lambda function to connect to database running in private subnet in the same AWS account and to connect to cross account database running on public subnet. In the next blog post I will show few libraries which I played around with to connect and query the cross account Redshift database from Lambda function.