Initialize an RDS Instance on Creation with CDK

Often we need to initialize an RDS instance right after it is created to e.g. create multiple databases, users or install plugins.

This post will walk through the process of initializing a postgres RDS instance with CDK. We will create two databases in the same instance and install some plugins in each of them.
This will be done with help of AWS custom resources

Here is the code: https://github.com/felipeloha/aws-init-db

Preconditions

• A secret with root password “testPassword” of the RDS instance exists with the name “database-credentials-test”

Implementation

The next section will show how to:

• Create the database
• Create the lambda function that will initialize the databases
• Create the custom resource that will trigger the lambda

Create a CDK template with an RDS instance and a specific security group

const dbSecurityGroup = new ec2.SecurityGroup(this, 'dbSecurityGroup', {...});
...
const dbCredentials = secretsmanager.Secret.fromSecretNameV2(this, 'DBSecret', 'database-credentials-test');
const database = new rds.DatabaseInstance(this, 'Database', {
    ...
    securityGroups: [dbSecurityGroup],
    credentials: rds.Credentials.fromSecret(dbCredentials),
});

Create the lambda function that will execute the initialization and give it access to the database

const lambdaRole = new iam.Role(stack, 'initDBLambdaRole', {
    assumedBy: new iam.ServicePrincipal('lambda.amazonaws.com'),
});
lambdaRole.addManagedPolicy(
    iam.ManagedPolicy.fromManagedPolicyArn(
        stack,
        'lambdavpcaccesspolicy',
        'arn:aws:iam::aws:policy/service-role/AWSLambdaVPCAccessExecutionRole',
    ),
);
lambdaRole.addManagedPolicy(
iam.ManagedPolicy.fromAwsManagedPolicyName('AmazonS3FullAccess'));
lambdaRole.addManagedPolicy(
iam.ManagedPolicy.fromAwsManagedPolicyName('CloudWatchLogsFullAccess'));
const initDBSecurityGroup = new ec2.SecurityGroup(stack, 'initDBSecurityGroup', {...});
const onEvent = new lambda.Function(stack, 'InitDBHandler', {
    ...
    code: lambda.Code.fromAsset(path.join(__dirname, 'lambda')),
    handler: 'index.handler',
    role: lambdaRole,
    securityGroups: [initDBSecurityGroup],
});
dbSecurityGroup.connections.allowFrom(
    onEvent,
    ec2.Port.tcp(5432),
);

Create a custom resources configured to be triggered every time the database.dbInstanceEndpointAddress changes with permissions to execute lambda functions.

const role = new iam.Role(stack, 'initDBRole', {
    assumedBy: new iam.ServicePrincipal('lambda.amazonaws.com'),
});
role.addManagedPolicy(
iam.ManagedPolicy.fromAwsManagedPolicyName('AWSLambda_FullAccess'));
const provider = new cr.Provider(stack, 'dbInitProvider', {
    onEventHandler: onEvent,
    logRetention: logs.RetentionDays.ONE_DAY,
// must be assumable by the `lambda.amazonaws.com` service principal
    role, 
});
const dbInit = new cdk.CustomResource(stack, 'databaseInit', {
    serviceToken: provider.serviceToken,
    properties: {
        host: database.dbInstanceEndpointAddress,
        //help to trigger event every time
        //random: `${Math.random()}`, 
    },
});
dbInit.node.addDependency(database);

Finally implement the lambda code to create a second database and install the plugins

const { Client } = require('pg');
module.exports.handler = async function (event) {
    console.log('request:', JSON.stringify(event, undefined, 2));
    switch (event.RequestType) {
        case 'Create':
            console.log('create event');
        default:
            console.log('did not match event type');
    }
const connStr = `postgresql://my_stack_user:testPassword@${event.ResourceProperties.host}:5432`;
    await execute(connStr, 'CREATE DATABASE my_stack_database_next;');
    await execute(connStr + '/my_stack_database', 'CREATE EXTENSION pg_trgm;CREATE EXTENSION btree_gin;');
    await execute(connStr + '/my_stack_database_next', 'CREATE EXTENSION pg_trgm;CREATE EXTENSION btree_gin;');
    //remove admin rights from each user
//TODO fail when an error happens so that the system rolls back
    return {
        Status: 'SUCCESS',
        Reason: '',
        LogicalResourceId: event.LogicalResourceId,
        //PhysicalResourceId: directoryId + '+user-' + username,
        RequestId: event.RequestId,
        StackId: event.StackId,
    };
};
async function execute(connStr, command) {
    try {
        await executeQuery(connStr, command);
    } catch (e) {
        console.log('error executing query', command, e);
    }
}
async function executeQuery(connStr, command) {
    const dbconn = {
        connectionString: connStr,
        query_timeout: 5000,
        connectionTimeoutMillis: 5000,
    };
    const client = new Client(dbconn);
    await client.connect();
    try {
        const q = await client.query(command);
        console.log(q);
    } catch (e) {
        console.log('error executing ', command, e);
    } finally {
        await client.end();
    }
}

At this point the template will create an RDS instance with two databases “my_stack_database” and “my_stack_database_next” and will install in both of them the extensions “pg_trgm” and “btree_gin”

Beware that this code has very open permissions in some points and the lambda function is not well implemented and does not have a good error handling.

If you liked this post, please follow me. I would be very grateful ;)