Connect Aurora Serverless from EC2/Lambda using Data API

TOC

Enabling Aurora Serverless Data API and connecting from EC2 (AWS CLI) and Lambda (Boto3)

The following pages cover the basics of Aurora Serverless.

One of the features of Aurora Serverless v1 is the Data API.

By using the Data API for Aurora Serverless v1, you can work with a web-services interface to your Aurora Serverless v1 DB cluster. The Data API doesn’t require a persistent connection to the DB cluster. Instead, it provides a secure HTTP endpoint and integration with AWS SDKs.
Using the Data API for Aurora Serverless v1

This page uses the Data API to connect to Aurora Serverless from EC2 instances and Lambda functions.

Environment

Diagram of connect Aurora Serverless from EC2/Lambda using Data API.

Create three subnets within the VPC.
All of them are private subnets with no access to the Internet.

Create Aurora Serverless and associate it with two subnets with different AZs.
Specify the latest version of the MySQL type.
Enable Data API.

Create an EC2 instance.
This is the client that will connect to Aurora Serverless.
Specify the latest version of Amazon Linux 2 as the OS.

Create an endpoint for SSM.
This is for remote access to the EC2 instance using SSM Session Manager.

Also create an endpoint for RDS Data.
Use the Data API to connect to Aurora Serverless from your EC2 instance.

Create a Lambda function outside the VPC.
This is also a client that connects to Aurora Serverless.
Select Python 3.8 as the runtime environment and activate the Function URL.

CloudFormation template files

Build the above configuration with CloudFormation.
The CloudFormation templates are located at the following URL

https://github.com/awstut-an-r/awstut-fa/tree/main/059

Explanation of key points of the template files

This page covers how to enable the Data API of Aurora Serverless and connect to it from EC2/Lambda.
For basic information about Aurora Serverless, please refer to the page introduced at the beginning of this document.

Security Group

Resources:
  InstanceSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupName: !Sub "${Prefix}-InstanceSecurityGroup"
      GroupDescription: Deny All.
      VpcId: !Ref VPC

  DBSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupName: !Sub "${Prefix}-DBSecurityGroup"
      GroupDescription: DBSecurityGroup.
      VpcId: !Ref VPC
      #    SecurityGroupIngress:
      #      - IpProtocol: tcp
      #        FromPort: !Ref MySQLPort
      #        ToPort: !Ref MySQLPort
      #        SourceSecurityGroupId: !Ref InstanceSecurityGroup

  EndpointSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupName: !Sub "${Prefix}-EndpointSecurityGroup"
      GroupDescription: Allow HTTPS from InstanceSecurityGroup.
      VpcId: !Ref VPC
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: !Ref HTTPSPort
          ToPort: !Ref HTTPSPort
          SourceSecurityGroupId: !Ref InstanceSecurityGroup
Code language: YAML (yaml)

Define three security groups.

The first is for EC2 instances.
Similar to the page introduced at the beginning of this article, no inbound communication to the instance will occur in this configuration.
Therefore, nothing will allow inbound communication.

The second is for Aurora Serverless.
This one also does not allow any inbound communication.
In this case, since the Data API is used to connect to Aurora Serverless, there is no need to release the MySQL port.
Also, the Data API connection is made via HTTPS, which is an outbound communication from Aurora Serverless to the Data API endpoint, so there is no need to allow this in the security group either.

The third is for the VPC endpoint.
In this case, we will create a total of four endpoints, one for SSM and one for RDS Data.
All of them will have HTTPS communication from the instances, so the content will reflect that.

Aurora Serverless

Resources:
  DBCluster:
    Type: AWS::RDS::DBCluster
    Properties:
      DatabaseName: !Ref DBName
      DBClusterIdentifier: !Ref DBClusterIdentifier
      DBSubnetGroupName: !Ref DBSubnetGroup
      EnableHttpEndpoint: true
      Engine: !Ref DBEngine
      EngineMode: serverless
      EngineVersion: !Ref DBEngineVersion
      MasterUsername: !Ref DBMasterUsername
      MasterUserPassword: !Ref DBMasterUserPassword
      StorageEncrypted: true
      VpcSecurityGroupIds:
        - !Ref DBSecurityGroup
Code language: YAML (yaml)

The key point is the EnableHttpEndpoint property.
To enable Data API in Aurora Serverless, set this property to “true”.
This setting will provide an HTTP endpoint for the Data API.

No other special settings are required.

Secrets Manager

Resources:
  Secret:
    Type: AWS::SecretsManager::Secret
    Properties:
      Name: !Sub "${Prefix}-Secret"
      SecretString: !Sub '{"username":"${DBMasterUsername}","password":"${DBMasterUserPassword}"}'
Code language: YAML (yaml)

To connect to Aurora Serverless using the Data API, you must use Sercrets Manager.

Users don’t need to pass credentials with calls to the Data API, because the Data API uses database credentials stored in AWS Secrets Manager.
Using the Data API for Aurora Serverless v1

Specifically, the secret is created from information about the master user and password that were set when the Aurora DB cluster was defined.
Define a secret string in JSON format in the SecretString property.
The two pieces of information are set as the value of ussername and password respectively.

VPC Endpoints

Resources:
  RDSDataEndpoint:
    Type: AWS::EC2::VPCEndpoint
    Properties:
      PrivateDnsEnabled: true
      SecurityGroupIds:
        - !Ref EndpointSecurityGroup
      ServiceName: !Sub "com.amazonaws.${AWS::Region}.rds-data"
      SubnetIds:
        - !Ref PrivateSubnet1
      VpcEndpointType: Interface
      VpcId: !Ref VPC

  #S3Endpoint:
  #  Type: AWS::EC2::VPCEndpoint
  #  Properties:
  #    RouteTableIds:
  #      - !Ref PrivateRouteTable
  #    ServiceName: !Sub "com.amazonaws.${AWS::Region}.s3"
  #    VpcId: !Ref VPC
Code language: YAML (yaml)

To connect to Aurora Serverless using the Data API, communication is made to the HTTP endpoint for the Data API.
EC2 instances in private subnets must create a VPC endpoint for RDS Data in order to communicate with the same endpoint.

For more information, please refer to the following official AWS page.

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/data-api.html#data-api.vpc-endpoint

Unlike the page introduced at the beginning of this article, the VPC endpoint for S3 is not created.
This endpoint was used by an instance on a private subnet to access the yum repository built on S3 and install the MySQL client package.
This time, however, it will communicate with Aurora Serverless via HTTPS instead of MySQL communication.
Therefore, the client package is no longer needed, and this endpoint is also unnecessary.

EC2 Instance

Resources:
  Instance:
    Type: AWS::EC2::Instance
    Properties:
      IamInstanceProfile: !Ref InstanceProfile
      ImageId: !Ref ImageId
      InstanceType: !Ref InstanceType
      NetworkInterfaces:
        - DeviceIndex: 0
          SubnetId: !Ref PrivateSubnet
          GroupSet:
            - !Ref InstanceSecurityGroup
      #UserData: !Base64 |
      #  #!/bin/bash -xe
      #  yum update -y
      #  yum install -y mariadb
Code language: YAML (yaml)

This is an EC2 instance as a client connecting to Aurora Serverless using the Data API.

As mentioned earlier, we do not need a MySQL client, so we will not perform package installation using user data.

Lambda Function

Resources:
  Function:
    Type: AWS::Lambda::Function
    Properties:
      Environment:
        Variables:
          DBCLUSTER_ARN: !Ref DBClusterArn
          DBNAME: !Ref DBName
          DBTABLE: !Ref DBTableName
          REGION: !Ref AWS::Region
          SECRET_ARN: !Ref SecretArn
      Code:
        ZipFile: |
          import boto3
          import json
          import os

          dbcluster_arn = os.environ['DBCLUSTER_ARN']
          dbname = os.environ['DBNAME']
          dbtable = os.environ['DBTABLE']
          region = os.environ['REGION']
          secret_arn = os.environ['SECRET_ARN']

          sql = 'select * from {table};'.format(table=dbtable)
          client = boto3.client('rds-data', region_name=region)

          def lambda_handler(event, context):
            response = client.execute_statement(
              database=dbname,
              resourceArn=dbcluster_arn,
              schema='mysql',
              secretArn=secret_arn,
              sql=sql
            )

            return {
              'statusCode': 200,
              'body': json.dumps(response, indent=2)
            }
      FunctionName: !Sub "${Prefix}-function"
      Handler: !Ref Handler
      Runtime: !Ref Runtime
      Role: !GetAtt FunctionRole.Arn
Code language: YAML (yaml)

A Lambda function as a client connecting to Aurora Serverless using the Data API.

Define the code to be executed in inline notation.
For more information, please check the following page

In the Environment property, set parameters such as DB cluster, Secrets Manager secret, etc. as environment variables.
In the code, access the environment variables in os.envrion.

Create a client object for RDS Data using Boto3.
Connect to Aurora Serverless by executing execute_statement.
Pass the aforementioned parameters as arguments.
The SQL to be executed is to retrieve a table in a specific table (Pets).
The result of SQL execution is returned as the result of function execution.

Enable Function URL.
This function allows Lambda functions to be executed from a specific URL.
For more information on Function URL, please refer to the following page

IAM Role

Resources:
  InstanceRole:
    Type: AWS::IAM::Role
    Properties:
      AssumeRolePolicyDocument:
        Version: 2012-10-17
        Statement:
          - Effect: Allow
            Action: sts:AssumeRole
            Principal:
              Service:
                - ec2.amazonaws.com
      ManagedPolicyArns:
        - arn:aws:iam::aws:policy/AmazonSSMManagedInstanceCore

  FunctionRole:
    Type: AWS::IAM::Role
    Properties:
      AssumeRolePolicyDocument:
        Version: 2012-10-17
        Statement:
          - Effect: Allow
            Action: sts:AssumeRole
            Principal:
              Service:
                - lambda.amazonaws.com
      ManagedPolicyArns:
        - arn:aws:iam::aws:policy/service-role/AWSLambdaBasicExecutionRole

  AuroraServerlessDataAPIPolicy:
    Type: AWS::IAM::Policy
    Properties:
      PolicyName: !Sub "${Prefix}-AuroraServerlessDataAPIPolicy"
      PolicyDocument:
        Version: 2012-10-17
        Statement:
          - Effect: Allow
            Action:
              - secretsmanager:GetSecretValue
            Resource:
              - !Ref SecretArn
          - Effect: Allow
            Action:
              - rds-data:BatchExecuteStatement
              - rds-data:BeginTransaction
              - rds-data:CommitTransaction
              - rds-data:ExecuteStatement
              - rds-data:RollbackTransaction
            Resource:
              - !Ref DBClusterArn
      Roles:
        - !Ref FunctionRole
        - !Ref InstanceRole
Code language: YAML (yaml)

Create IAM roles for the EC2 instance and the Lambda function.
The permissions common to both are defined as IAM policies.

There are two points.

The first is access to the Secrets Manager, which gives access to the aforementioned secrets.

The second point is the permission to use the Data API, which is the minimum permission to execute SQL against the Aurora Server DB cluster mentioned above

This time, I referred to the following page for setting up.

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/data-api.html#data-api.access

Architecting

Using CloudFormation, we will build this environment and check the actual behavior.

Create CloudFormation stacks and check resources in stacks

Create CloudFormation stacks.
For information on how to create stacks and check each stack, please refer to the following page

After checking the resources for each stack, the following is the information for the main resources created in this case

EC2 instance: i-02b21e07f33a5074c
Lambda function: fa-059-function
Function URL for Lambda function: https://7vgt4c3bmf7n3xp7htprqdhi5u0tfdms.lambda-url.ap-northeast-1.on.aws/
Database name to be created in Aurora Serverless: testdb
Table name to be created in Aurora Serverless database: Pets
ARN of Aurora Serverless DB cluster: arn:aws:rds:ap-northeast-1:[account-id]:cluster:fa-059-dbcluster
ARN for Secrets Manager secret: arn:aws:secretsmanager:ap-northeast-1:[account-id]:secret:fa-059-Secret-poaIGs

The creation status of Aurora Serverless is also checked from the AWS Management Console.

Aurora Serverless has been successfully created.
And we can also confirm that Data API has been activated.

Checking Action

EC2 Instance

Now that everything is ready, access the EC2 instance.
Use SSM Session Manager to access the instance.

% aws ssm start-session --target i-02b21e07f33a5074c

Starting session with SessionId: root-0815f8b32141bb9b4

sh-4.2$
Code language: Bash (bash)

For more information on SSM Session Manager, please refer to the following page

Connect to Aurora Serverless from the AWS CLI using the Data API.
First, create a Pets table in the Aurora Serverless database.

sh-4.2$ aws rds-data execute-statement \
--resource-arn "arn:aws:rds:ap-northeast-1:[account-id]:cluster:fa-059-dbcluster" \
 --schema "mysql" \
--secret-arn "arn:aws:secretsmanager:ap-northeast-1:[account-id]:secret:fa-059-Secret-poaIGs" \
 --region ap-northeast-1 \
 --sql "create table Pets(id varchar(200), type varchar(200), price float)" \
--database "testdb"
{
    "numberOfRecordsUpdated": 0,
    "generatedFields": []
}
Code language: Bash (bash)

Successfully executed.

Next, add records to the created table.

sh-4.2$ aws rds-data execute-statement \
--resource-arn "arn:aws:rds:ap-northeast-1:[account-id]:cluster:fa-059-dbcluster" \
 --schema "mysql" \
--secret-arn "arn:aws:secretsmanager:ap-northeast-1:[account-id]:secret:fa-059-Secret-poaIGs" \
 --region ap-northeast-1 \
 --sql "insert into Pets values (111, 'dog', 123)" \
--database "testdb"
{
    "numberOfRecordsUpdated": 1,
    "generatedFields": []
}
Code language: YAML (yaml)

This also executed successfully.

Check the added records.

sh-4.2$ aws rds-data execute-statement \
--resource-arn "arn:aws:rds:ap-northeast-1:[account-id]:cluster:fa-059-dbcluster" \
 --schema "mysql" \
--secret-arn "arn:aws:secretsmanager:ap-northeast-1:[account-id]:secret:fa-059-Secret-poaIGs" \
 --region ap-northeast-1 \
 --sql "select * from Pets" \
--database "testdb"
{
    "records": [
        [
            {
                "stringValue": "111"
            },
            {
                "stringValue": "dog"
            },
            {
                "doubleValue": 123.0
            }
        ]
    ],
    "numberOfRecordsUpdated": 0
}Code language: JavaScript (javascript)