Initialize RDS DB with CFN Custom Resource

Initialize RDS DB with CFN Custom Resource AWS_EN

Performing RDS DB initialization with CloudFormation Custom Resource

When creating an RDS resource with CloudFormation, we also want to initialize the DB (create DBs and tables, add test records, etc.).
In this case, we will initialize the DB using a CloudFormation custom resource.


Diagram of initialize RDS DB with CFN Custom Resource.

We will create four main resources.

The first is an RDS DB instance.
In this case, we will create a MySQL type DB instance.

The second is an EC2 instance.
This will be used as a client to connect to the DB instance.
The instance is the latest version of Amazon Linux 2.

The third is a Lambda function.
We associate a function with a CloudFormation custom resource so that this function will be automatically executed when the CloudFormation stack is created.
The function’s function is to initialize a DB instance.
The runtime environment for the function is Python 3.8.

The fourth is the SSM parameter store.
The SQL statement to be executed by the Lambda function is stored as a string in the parameter store.
The following AWS official page was used as a reference for the contents to be executed in the initialization process.

Module 3: Populate your MySQL database

CloudFormation Template Files

The above configuration is built using CloudFormation.
The CloudFormation templates are located at the following URL

Explanation of key points of the template files

SSM Parameter Store

    Type: AWS::SSM::Parameter
      Name: !Ref Prefix
      Type: String
      Value: |
        CREATE database tutorial;
        USE tutorial;
        INSERT INTO planet (name) VALUES ("Mercury");
        INSERT INTO planet (name) VALUES ("Venus");
        INSERT INTO planet (name) VALUES ("Earth");
        INSERT INTO planet (name) VALUES ("Mars");
        INSERT INTO planet (name) VALUES ("Jupiter");
        INSERT INTO planet (name) VALUES ("Saturn");
        INSERT INTO planet (name) VALUES ("Uranus");
        INSERT INTO planet (name) VALUES ("Neptune");
Code language: YAML (yaml)

Register the SQL statements to be executed by the Lambda function in the SSM parameter store.
After creating the DB and tables, save the test records.

CloudFormation Custom Resource

First, check the Lambda functions to be executed in the custom resource.

    Type: AWS::Lambda::Function
          DB_ENDPOINT_ADDRESS: !Ref DBInstanceEndpointAddress
          DB_ENDPOINT_PORT: !Ref MySQLPort
          DB_PASSWORD: !Ref DBMasterUserPassword
          DB_USER: !Ref DBMasterUsername
          REGION: !Ref AWS::Region
          SQL_PARAMETER: !Ref SQLParameter
        ZipFile: |
          import boto3
          import cfnresponse
          import mysql.connector
          import os
          db_endpoint_port = os.environ['DB_ENDPOINT_PORT']
          db_endpoint_address = os.environ['DB_ENDPOINT_ADDRESS']
          db_password = os.environ['DB_PASSWORD']
          db_user = os.environ['DB_USER']
          region = os.environ['REGION']
          sql_parameter = os.environ['SQL_PARAMETER']
          CREATE = 'Create'
          response_data = {}
          def lambda_handler(event, context):
              if event['RequestType'] == CREATE:
                client = boto3.client('ssm', region_name=region)
                response = client.get_parameter(Name=sql_parameter)
                sql_statements = response['Parameter']['Value']
                conn = mysql.connector.connect(
                cur = conn.cursor()
                for sql in sql_statements.splitlines():
              cfnresponse.send(event, context, cfnresponse.SUCCESS, response_data)
            except Exception as e:
              cfnresponse.send(event, context, cfnresponse.FAILED, response_data)
      FunctionName: !Sub "${Prefix}-function"
      Handler: index.lambda_handler
        - !Ref LambdaLayer
      Runtime: !Ref Runtime
      Role: !GetAtt FunctionRole.Arn
      Timeout: !Ref Timeout
          - !Ref FunctionSecurityGroup
          - !Ref PrivateSubnet
Code language: YAML (yaml)

Define the code to be executed by the Lambda function in inline notation.
For more information, please refer to the following page

Use the cfnresponse module to implement functions as Lambda-backed custom resource.
For more information, please refer to the following page

In this configuration, the Lambda function is placed in the VPC.
The VpcConfig property specifies the subnet where the function will be placed and the security group to be applied.

The code to be executed is as follows.

  1. retrieve the environment variables defined in the CloudFormation template by accessing os.environ.
  2. Create a client object for SSM with Boto3.
  3. Use the client object to access the SSM parameter store and retrieve the SQL statement described above.
  4. connect to the DB instance using MySQL client and execute SQL one statement at a time.

MySQL client for DB instance connection, but use MySQL Connector.

In this case, we will prepare the MySQL Connector package as a Lambda layer.
For more information on the Lambda layer, please refer to the following page

For your information, the IAM role for the functions is as follows

    Type: AWS::IAM::Role
        Version: 2012-10-17
          - Effect: Allow
            Action: sts:AssumeRole
        - arn:aws:iam::aws:policy/service-role/AWSLambdaVPCAccessExecutionRole
        - PolicyName: GetSSMParameterPolicy
            Version: 2012-10-17
              - Effect: Allow
                  - ssm:GetParameter
                  - !Sub "arn:aws:ssm:${AWS::Region}:${AWS::AccountId}:parameter/${SQLParameter}"
Code language: YAML (yaml)

In addition to the AWS management policy AWSLambdaVPCAccessExecutionRole, grant permission to retrieve parameters from the SSM parameter store.

Then check the CloudFormation custom resource body.

    Type: Custom::CustomResource
      ServiceToken: !Ref FunctionArn
Code language: YAML (yaml)

Specifies the Lambda function described above.

VPC Endpoint

In this configuration, multiple VPC endpoints will be created.
Here we focus on the VPC endpoint for SSM.

    Type: AWS::EC2::VPCEndpoint
      PrivateDnsEnabled: true
        - !Ref EndpointSecurityGroup
      ServiceName: !Sub "com.amazonaws.${AWS::Region}.ssm"
        - !Ref PrivateSubnet
      VpcEndpointType: Interface
      VpcId: !Ref VPC
Code language: YAML (yaml)

Use this VPC endpoint to connect to the SSM parameter store from a Lambda function deployed in the VPC.
By creating a VPC endpoint, you can connect to AWS resources outside the VPC without going through the Internet.

(Reference) RDS DB Instance

    Type: AWS::RDS::DBInstance
      AllocatedStorage: !Ref DBAllocatedStorage
      AvailabilityZone: !Sub "${AWS::Region}${AvailabilityZone}"
      DBInstanceClass: !Ref DBInstanceClass
      DBInstanceIdentifier: dbinstance
      DBSubnetGroupName: !Ref DBSubnetGroup
      Engine: !Ref DBEngine
      EngineVersion: !Ref DBEngineVersion
      MasterUsername: !Ref DBMasterUsername
      MasterUserPassword: !Ref DBMasterUserPassword
        - !Ref DBSecurityGroup
Code language: YAML (yaml)

DB instance.
No special configuration is required to initialize the DB using CloudFormation custom resources.

(Reference) EC2 Instance

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

Define the initialization process of the instance with user data.
Install the MySQL client package to connect to a MySQL type DB instance from an EC2 instance.
For more information, please refer to the following page


Use CloudFormation to build this environment and check the actual behavior.

Preliminary Preparation

As a preliminary preparation, prepare a deployment package for the Lambda layer.
Specifically, execute the following commands

$ mkdir python

$ pip3 install mysql-connector-python -t ./python

$ zip -r python
Code language: Bash (bash)

Upload the created deployment package to the S3 bucket.

Create CloudFormation stacks and check resources in stacks

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

After checking the resources in each stack, information on the main resources created this time is as follows

  • RDS DB instance: dbinstance
  • RDS DB instance endpoint:
  • EC2 instance: i-0b3d18375ca48f06b
  • Lambda function: fa-062-function
  • Parameters in SSM parameter store: fa-062

Check each resource from the AWS Management Console.
First, check the CloudFormation custom resource.

Result of Custom Resource 1.
Result of Custom Resource 2.

You can see that the Lambda function and the custom resource itself have been successfully created.

Next, check the values stored in the SSM parameter store.

SSM Parameter Store.

You can see that the SQL statement for DB initialization is saved.

Checking Action

Now that everything is ready, let’s check the Operation.

Execution Result of Lambda Function

First, check the execution result of the Lambda function in the CloudWatch Logs log group.

Result of Custom Resource 3.

From the log, we can see that the SQL stored in the SSM parameter store is executed one statement at a time.
This means that the DB initialization process was successfully executed.

We can also see that the function returns “SUCCESS” as a CloudFormation custom resource.
This means that the function has successfully acted as a custom resource.

EC2 Instance

Next, connect to the EC2 instance and check the initialization status of the DB.

Use SSM Session Manager to access the instance.

% aws ssm start-session --target i-0b3d18375ca48f06b

Starting session with SessionId: root-0b2e1f71f0d2aea98

Code language: Bash (bash)

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

First, check the execution status of the initialization process of the EC2 instance with user data.

sh-4.2$ yum list installed | grep mariadb
mariadb.aarch64                       1:5.5.68-1.amzn2               @amzn2-core
mariadb-libs.aarch64                  1:5.5.68-1.amzn2               installed  

sh-4.2$ mysql -V
mysql  Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (aarch64) using readline 5.1
Code language: Bash (bash)

You will see that the MySQL client package has been successfully installed.

Use this client package to connect to the DB instance.

sh-4.2$ mysql -h -P 3306 -u testuser -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.0.28 Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]>
Code language: Bash (bash)

You have successfully connected.

Select DB.

MySQL [(none)]> USE tutorial;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [tutorial]>
Code language: Bash (bash)

You could change the DB.
This means that the initialization process with the CloudFormation custom resource has been successfully executed.

Finally, we retrieve all records.

MySQL [tutorial]> select * from planet;
| id | name    |
|  1 | Mercury |
|  2 | Venus   |
|  3 | Earth   |
|  4 | Mars    |
|  5 | Jupiter |
|  6 | Saturn  |
|  7 | Uranus  |
|  8 | Neptune |
8 rows in set (0.01 sec)
Code language: Bash (bash)

A test record has been returned.
It means that the CREATE TABLE and INSERT statements were successfully executed.

As described above, we were able to initialize the DB instance with the CloudFormation custom resource.


We have shown how to initialize a DB using CloudFormation custom resources.