Running Setup SQL scripts on an RDS instance within a VPC, via Terraform

README.markdown

This Terraform configuration allows running a set of SQL commands on a new AWS RDS database instance that's operating within an AWS VPC.

The commands are executed via AWS Lambda functions - the first (rds_creation) operates outside the VPC and connects to the AWS API to determine credential information for the new database (endpoint, port, username, database). It then sends these details via SNS to another function operating within the VPC (rds_setup), which connects to the PostgreSQL database and executes the SQL commands.

The initial notification comes via SNS from the RDS events (and there is the configuration within the Terraform file here to set up that subscription).

Please note:

• There are variables defined at the top of the Terraform file - everything else should be pretty self-contained (though you should definitely read through all of the code to ensure you understand it before using it).
• The internal Lambda function is expecting a PostgreSQL database. This will need changing if you're going to use MySQL/Aurora/etc.
• The internal Lambda function connects via SSL, and so requires a local copy of the AWS SSL Certificate file (which I have saved as rds-cert.pem within the rds_setup directory).

Each lambda function is stored within their respective folders (rds_creation, rds_setup), and also have package.json files to go within them as well.

If you think there's a better way of doing this, shoot me a message on Twitter.

LICENSE.txt

The MIT License (MIT)

Copyright (c) 2018 Pat Allan

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.

package.json

// For rds_creation
{
  "dependencies": {
    "aws-sdk": "^2.302.0"
  }
}

// For rds_setup
{
  "dependencies": {
    "pg": "~> 7.4"
  }
}

rds_creation_index.js

// For rds_creation/index.js
const rds = require('aws-sdk/clients/rds'),
      sns = require('aws-sdk/clients/sns');

const awsClient = new rds();
const snsClient = new sns();

exports.handler = async (event, context) => {
  const record = JSON.parse(event.Records[0].Sns.Message);

  if (record["Event Source"] != "db-instance")          { return; }
  if (record["Event Message"] != "DB instance created") { return; }

  console.log("Requesting instance information for " + record["Source ID"]);
  var data = await awsClient.describeDBInstances(
    {DBInstanceIdentifier: record["Source ID"]}
  ).promise();

  if (data.DBInstances.length == 0) { return; }

  var instance = data.DBInstances[0];
  var credentials = {
    user:     instance.MasterUsername,
    database: instance.DBName,
    host:     instance.Endpoint.Address,
    port:     instance.Endpoint.Port
  };

  await snsClient.publish({
    Message: JSON.stringify(credentials),
    Subject: "Database Created",
    TopicArn: process.env.SNS_TOPIC_ARN
  }).promise();
};

rds_setup_index.js

// For rds_setup/index.js
const fs = require('fs'),
      pg = require('pg');

exports.handler = async (event, context) => {
  const record = JSON.parse(event.Records[0].Sns.Message);
  console.log(record);

  var credentials = {
    user:     record.user,
    database: record.database,
    host:     record.host,
    port:     record.port,
    ssl:      {
      rejectUnauthorized: false,
      cert:               fs.readFileSync("./rds-cert.pem").toString()
    }
  };

  const pgClient = new pg.Client(credentials);

  await pgClient.connect();
  var results = await pgClient.query(process.env.SQL_SCRIPT);
  await pgClient.end();

  console.log(results.map(function(result) { return result.rowCount; }));
};

terraform.tf

variable "database_password" {
  type = "string"
}
variable "subnet_ids" {
  description = "Comma-delimited string of subnet ids"
  type        = "string"
}
variable "security_group_id" {
  type = "string"
}

provider "archive" {
  version = "~> 1.1"
}

provider "aws" {
  version = "~> 1.23"
}

locals {
  # Solution from this comment to open issue on non-relative paths
  # https://github.com/hashicorp/terraform/issues/8204#issuecomment-332239294

  # +1 for removing the "/"
  creation_filename = "${substr(data.archive_file.rds_creation_zip.output_path, length(path.cwd) + 1, -1)}"
  setup_filename = "${substr(data.archive_file.rds_setup_zip.output_path, length(path.cwd) + 1, -1)}"
}

data "archive_file" "rds_creation_zip" {
  type        = "zip"
  output_path = "${path.module}/rds_creation.zip"
  source_dir  = "${path.module}/rds_creation/"
}

data "archive_file" "rds_setup_zip" {
  type        = "zip"
  output_path = "${path.module}/rds_setup.zip"
  source_dir  = "${path.module}/rds_setup/"
}

# The SQL script that gets evaluated on new database instances.
# Clearly, you will want to change this!
data "template_file" "sql_script" {
  template = <<SQL
SELECT 1;
SQL
}

# Subscribe to all new database creation notifications 
resource "aws_db_event_subscription" "creation" {
  name             = "rds-creation"
  sns_topic        = "${aws_sns_topic.rds.arn}"
  source_type      = "db-instance"
  event_categories = ["creation"]
}

# 'External' Lambda function that gets the new database SNS notification
# and queries the AWS API to obtain further details about this.
#
# It then sends those details off to another SNS notification, which is
# picked up by the 'internal' Lambda function.
resource "aws_lambda_function" "rds_creation" {
  function_name     = "rds-creation"
  handler           = "index.handler"
  filename          = "${local.creation_filename}"
  source_code_hash  = "${base64sha256(file("${local.creation_filename}"))}"

  role              = "${aws_iam_role.rds_external_lambda.arn}"
  runtime           = "nodejs8.10"
  timeout           = 10

  environment {
    variables {
      SNS_TOPIC_ARN = "${aws_sns_topic.internal.arn}"
    }
  }
}

# 'Internal' Lambda function which receives database information from
# the external function (via SNS) and then connects to the database
# and evaluates the script against it.
# 
# This operates within the VPC, and hence does not have access to the
# internet or AWS APIs.
resource "aws_lambda_function" "rds_setup" {
  function_name     = "rds-setup"
  handler           = "index.handler"
  filename          = "${local.setup_filename}"
  source_code_hash  = "${base64sha256(file("${local.setup_filename}"))}"

  role              = "${aws_iam_role.rds_internal_lambda.arn}"
  runtime           = "nodejs8.10"
  timeout           = 10

  vpc_config {
    subnet_ids         = ["${split(",", var.subnet_ids)}"]
    security_group_ids = ["${var.security_group_id}"]
  }

  environment {
    variables = {
      PGPASSWORD = "${var.database_password}"
      SQL_SCRIPT = "${replace(trimspace(data.template_file.sql_script.rendered), "/\n/", " ")}"
    }
  }
}

resource "aws_lambda_permission" "rds_creation" {
  statement_id   = "AllowExecutionFromSNS"
  action         = "lambda:InvokeFunction"
  function_name  = "${aws_lambda_function.rds_creation.function_name}"
  principal      = "sns.amazonaws.com"
  source_arn     = "${aws_sns_topic.rds.arn}"
}

resource "aws_lambda_permission" "rds_setup" {
  statement_id   = "AllowExecutionFromSNS"
  action         = "lambda:InvokeFunction"
  function_name  = "${aws_lambda_function.rds_setup.function_name}"
  principal      = "sns.amazonaws.com"
  source_arn     = "${aws_sns_topic.internal.arn}"
}

# SNS Topic for new database creations (via RDS events)
resource "aws_sns_topic" "rds" {
  name = "rds-creation"
}

# SNS Topic for database credentials (via the external lambda)
resource "aws_sns_topic" "internal" {
  name = "rds-setup"
}

# Subscriptions connecting topics to lambdas
resource "aws_sns_topic_subscription" "rds" {
  topic_arn = "${aws_sns_topic.rds.arn}"
  protocol  = "lambda"
  endpoint  = "${aws_lambda_function.rds_creation.arn}"
}

resource "aws_sns_topic_subscription" "rds_internal" {
  topic_arn = "${aws_sns_topic.internal.arn}"
  protocol  = "lambda"
  endpoint  = "${aws_lambda_function.rds_setup.arn}"
}

# IAM Role for 'External' lambda which has access to
# CloudWatch, SNS, and RDS.
resource "aws_iam_role" "rds_external_lambda" {
  name = "RDSExternal"

  assume_role_policy = <<EOF
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Action": "sts:AssumeRole",
      "Principal": {
        "Service": "lambda.amazonaws.com"
      },
      "Effect": "Allow"
    }
  ]
}
EOF
}

# IAM Role for 'Internal' lambda which has access to
# CloudWatch and VPC behaviour.
resource "aws_iam_role" "rds_internal_lambda" {
  name = "RDSInternal"

  assume_role_policy = <<EOF
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Action": "sts:AssumeRole",
      "Principal": {
        "Service": "lambda.amazonaws.com"
      },
      "Effect": "Allow"
    }
  ]
}
EOF
}

resource "aws_iam_role_policy" "rds_internal" {
  name   = "RDSInternalNotifications"
  role   = "${aws_iam_role.rds_internal_lambda.id}"
  policy = <<EOF
{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Action": [
      "logs:CreateLogGroup",
      "logs:CreateLogStream",
      "logs:PutLogEvents"
    ],
    "Resource": "*"
  }]
}
EOF
}

resource "aws_iam_role_policy" "rds_external" {
  name   = "RDSExternalNotifications"
  role   = "${aws_iam_role.rds_external_lambda.id}"
  policy = <<EOF
{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Action": [
      "logs:CreateLogGroup",
      "logs:CreateLogStream",
      "logs:PutLogEvents",
      "rds:DescribeDBInstances",
      "sns:Publish"
    ],
    "Resource": "*"
  }]
}
EOF
}

resource "aws_iam_role_policy_attachment" "rds_lambda_vpc" {
  role       = "${aws_iam_role.rds_internal_lambda.id}"
  policy_arn = "arn:aws:iam::aws:policy/service-role/AWSLambdaVPCAccessExecutionRole"
}

Hello Pat! The code on this gist is brilliant but we can't use it because there is no license. Having no license does not allow people to use your open source code in any way (commercial, private, etc). For more information see here. Would you consider adding a license to the gist (e.g. MIT, Apache, etc)?

@marcandjulien I've just added an MIT License to this, so feel free to use it as you see fit.

It's worth noting that apparently it's now possible to talk to the RDS API from within a VPC, so you may only need one lambda instead of two. The project I wrote this for is no longer active, so I can't test it myself (but certainly, what I've written worked well for me).