Connect to AWS Aurora PostgreSQL/Amazon Redshift Database from AWS Lambda
Tags: aws, database, datascience
Published: 2021-03-05 20:58:45

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rv23ciie5kl5awezfw7e.jpg)
Image by <a href="https://pixabay.com/users/peggy_marco-1553824/?utm_source=link-attribution&amp;utm_medium=referral&amp;utm_campaign=image&amp;utm_content=1019769">Peggy und Marco Lachmann-Anke</a> from <a href="https://pixabay.com/?utm_source=link-attribution&amp;utm_medium=referral&amp;utm_campaign=image&amp;utm_content=1019769">Pixabay</a>

<!-- wp:paragraph -->
<p>In this blog post I will discuss following scenarios to connect to databases from AWS Lambda function:</p>
<!-- /wp:paragraph -->

<!-- wp:list -->
<ul><li>Connecting to Amazon Aurora PostgreSQL database <strong><em>in private subnet</em></strong> with public accessibility set to No <strong><em>in same AWS account</em></strong>.</li><li>Connecting to <strong><em>cross account</em></strong> Amazon Redshift database <strong><em>in public subnet</em></strong> with public accessibility set to Yes.</li></ul>
<!-- /wp:list -->

<!-- wp:heading {"level":4} -->
<h4><span style="text-decoration:underline;">Connect to Amazon Aurora PostgreSQL</span> <span style="text-decoration:underline;">database in Private subnet with Public accessibility set to No in the same AWS account</span></h4>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>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:</p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":4726,"sizeSlug":"large","linkDestination":"media","className":"is-style-default"} -->
<figure class="wp-block-image size-large is-style-default"><a href="https://aprakash.files.wordpress.com/2021/02/rds_db_security.png"><img src="https://aprakash.files.wordpress.com/2021/02/rds_db_security.png?w=1024" alt="" class="wp-image-4726"/></a></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>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 - </p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><em>AWS Service &gt; Lambda &gt; Functions &gt; Create Function &gt; Author from scratch</em></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Under Basic Information </p>
<!-- /wp:paragraph -->

<!-- wp:list -->
<ul><li>Enter the function name</li><li>Choose the language of your preference to use. Here I selected Python 3.8.</li><li>In the Permissions section you can keep the default - Create a new role with basic Lambda permissions</li></ul>
<!-- /wp:list -->

<!-- wp:paragraph -->
<p>Under Advanced settings</p>
<!-- /wp:paragraph -->

<!-- wp:list -->
<ul><li>Enter the details in Network<ul><li>Choose the VPC name in which the Aurora PostgreSQL database is running</li><li>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.</li><li>Choose the default security group</li></ul></li></ul>
<!-- /wp:list -->

<!-- wp:paragraph -->
<p>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 <a href="https://docs.aws.amazon.com/vpc/latest/userguide/VPC_ElasticNetworkInterfaces.html" target="_blank" rel="noreferrer noopener">here</a>.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>In this case the Lambda function is launched with an execution role (IAM role) having 2 managed policies attached by default:</p>
<!-- /wp:paragraph -->

<!-- wp:list -->
<ul><li>AWSLambdaBasicExecutionRole</li><li>AWSLambdaVPCAccessExecutionRole</li></ul>
<!-- /wp:list -->

<!-- wp:paragraph -->
<p>In case you choose to use an existing role while creating the Lambda function make sure to attach <em>AWSLambdaVPCAccessExecutionRole</em> policy. This managed policy has the following permissions which Lambda uses to create and manage network interfaces:</p>
<!-- /wp:paragraph -->

<!-- wp:list -->
<ul><li>ec2:CreateNetworkInterface</li><li>ec2:DescribeNetworkInterfaces</li><li>ec2:DeleteNetworkInterface</li></ul>
<!-- /wp:list -->

<!-- wp:paragraph -->
<p>Below is the VPC configuration for my Lambda function attached with 2 Private subnets:</p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":4667,"sizeSlug":"large","linkDestination":"media","className":"is-style-default"} -->
<figure class="wp-block-image size-large is-style-default"><a href="https://aprakash.files.wordpress.com/2021/01/screen-shot-2021-01-31-at-4.39.48-pm.png"><img src="https://aprakash.files.wordpress.com/2021/01/screen-shot-2021-01-31-at-4.39.48-pm.png?w=1024" alt="" class="wp-image-4667"/></a></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>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 <a href="https://pypi.org/project/pg8000/" target="_blank" rel="noreferrer noopener">pg8000</a> 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.</p>
<!-- /wp:paragraph -->

<!-- wp:syntaxhighlighter/code -->
<pre class="wp-block-syntaxhighlighter-code">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)</pre>
<!-- /wp:syntaxhighlighter/code -->

<!-- wp:paragraph -->
<p>  Output: </p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":4676,"sizeSlug":"large","linkDestination":"media","className":"is-style-default"} -->
<figure class="wp-block-image size-large is-style-default"><a href="https://aprakash.files.wordpress.com/2021/01/screen-shot-2021-01-31-at-4.54.42-pm.png"><img src="https://aprakash.files.wordpress.com/2021/01/screen-shot-2021-01-31-at-4.54.42-pm.png?w=1024" alt="" class="wp-image-4676"/></a></figure>
<!-- /wp:image -->

<!-- wp:heading {"level":4} -->
<h4><span style="text-decoration:underline;">Connect to cross account Amazon Redshift database in Public subnet with Publicly accessible set to Yes </span></h4>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>Below is Amazon Redshift connection details running in <strong><em>Account A</em></strong>. 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.</p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":4716,"sizeSlug":"large","linkDestination":"media","className":"is-style-default"} -->
<figure class="wp-block-image size-large is-style-default"><a href="https://aprakash.files.wordpress.com/2021/02/screen-shot-2021-01-31-at-9.31.30-pm.png"><img src="https://aprakash.files.wordpress.com/2021/02/screen-shot-2021-01-31-at-9.31.30-pm.png?w=1024" alt="" class="wp-image-4716"/></a></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>In <strong><em>Account B</em></strong> 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.</p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":4680,"sizeSlug":"large","linkDestination":"media","className":"is-style-default"} -->
<figure class="wp-block-image size-large is-style-default"><a href="https://aprakash.files.wordpress.com/2021/01/screen-shot-2021-01-31-at-9.40.14-pm-1.png"><img src="https://aprakash.files.wordpress.com/2021/01/screen-shot-2021-01-31-at-9.40.14-pm-1.png?w=1024" alt="" class="wp-image-4680"/></a></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>The private subnets are attached to a route table. The route table needs a NAT Gateway attached. </p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":4719,"sizeSlug":"large","linkDestination":"media","className":"is-style-default"} -->
<figure class="wp-block-image size-large is-style-default"><a href="https://aprakash.files.wordpress.com/2021/02/screen-shot-2021-02-28-at-6.33.16-pm-1.png"><img src="https://aprakash.files.wordpress.com/2021/02/screen-shot-2021-02-28-at-6.33.16-pm-1.png?w=964" alt="" class="wp-image-4719"/></a></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>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. </p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":4720,"sizeSlug":"large","linkDestination":"media","className":"is-style-default"} -->
<figure class="wp-block-image size-large is-style-default"><a href="https://aprakash.files.wordpress.com/2021/02/screen-shot-2021-02-28-at-6.44.39-pm.png"><img src="https://aprakash.files.wordpress.com/2021/02/screen-shot-2021-02-28-at-6.44.39-pm.png?w=1024" alt="" class="wp-image-4720"/></a></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>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.</p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":4728,"sizeSlug":"large","linkDestination":"media","className":"is-style-default"} -->
<figure class="wp-block-image size-large is-style-default"><a href="https://aprakash.files.wordpress.com/2021/02/screen-shot-2021-02-28-at-7.19.20-pm.png"><img src="https://aprakash.files.wordpress.com/2021/02/screen-shot-2021-02-28-at-7.19.20-pm.png?w=1024" alt="" class="wp-image-4728"/></a></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>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.</p>
<!-- /wp:paragraph -->