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 Three

CloudFormation Template Files

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

awstut-fa/062 at main · awstut-an-r/awstut-fa
Contribute to awstut-an-r/awstut-fa development by creating an account on GitHub.

Explanation of key points of the template files

SSM Parameter Store

Resources: SQLParameter: Type: AWS::SSM::Parameter Properties: Name: !Ref Prefix Type: String Value: | CREATE database tutorial; USE tutorial; CREATE TABLE planet (id INT UNSIGNED AUTO_INCREMENT, name VARCHAR(30), PRIMARY KEY(id)); 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.

Resources: Function: Type: AWS::Lambda::Function Properties: Environment: Variables: 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 Code: 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): try: 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( host=db_endpoint_address, port=db_endpoint_port, user=db_user, password=db_password ) cur = conn.cursor() for sql in sql_statements.splitlines(): print(sql) cur.execute(sql) cur.close() conn.commit() cfnresponse.send(event, context, cfnresponse.SUCCESS, response_data) except Exception as e: print(e) cfnresponse.send(event, context, cfnresponse.FAILED, response_data) FunctionName: !Sub "${Prefix}-function" Handler: index.lambda_handler Layers: - !Ref LambdaLayer Runtime: !Ref Runtime Role: !GetAtt FunctionRole.Arn Timeout: !Ref Timeout VpcConfig: SecurityGroupIds: - !Ref FunctionSecurityGroup SubnetIds: - !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.

MySQL :: MySQL Connector/Python Developer Guide

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

Resources: FunctionRole: Type: AWS::IAM::Role Properties: AssumeRolePolicyDocument: Version: 2012-10-17 Statement: - Effect: Allow Action: sts:AssumeRole Principal: Service: - ManagedPolicyArns: - arn:aws:iam::aws:policy/service-role/AWSLambdaVPCAccessExecutionRole Policies: - PolicyName: GetSSMParameterPolicy PolicyDocument: Version: 2012-10-17 Statement: - Effect: Allow Action: - ssm:GetParameter Resource: - !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.

Resources: CustomResource: Type: Custom::CustomResource Properties: 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.

Resources: SSMEndpoint: Type: AWS::EC2::VPCEndpoint Properties: PrivateDnsEnabled: true SecurityGroupIds: - !Ref EndpointSecurityGroup ServiceName: !Sub "com.amazonaws.${AWS::Region}.ssm" SubnetIds: - !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

Resources: DBInstance: Type: AWS::RDS::DBInstance Properties: 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 VPCSecurityGroups: - !Ref DBSecurityGroup
Code language: YAML (yaml)

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

(Reference) 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)

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 sh-4.2$
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.