Setting up an RDS Aurora Serverless cluster with Secrets Manager and Lambda rotation in Terraform

Question

I'm having trouble trying to set this infrastructure: I need an Aurora serverless cluster running PostgreSQL and access it using Secrets Manager. I also want to rotate the secret using a Lambda function every X amount of days.

However, I can't get the Lambda function to connect to the RDS cluster even with the original credentials. What am I doing wrong? Is it not possible to do this?

This is my Terraform code:

# --- LOCALS
# ---
locals {
  db_role_name         = "MYAPP-app-backuprestore"
  db_name              = "MYAPP-rds-${var.region}"
  option_group_name    = "MYAPP-rds-optiongroup"
  security_group_name  = "MYAPP-vpc-scg"
  db_subnet_group_name = "MYAPP-vpc-sng"

  rotation_lambda_function_name = "MYAPP-secretsmanager-rotationlambda-${var.region}"
  rotation_lambda_role_name     = "MYAPP-app-rotationlambda"
  dbi_credentials_secret_name   = "MYAPP/rds/master-credentials"
  dbi_name                      = "MYAPP-rds-${var.region}"
  backup_bucket_name   = var.backup_bucket_name != "" ? var.backup_bucket_name : "MYAPP-data-${var.region}-${var.target_account_id}"
  backup_location      = var.backup_object_prefix == "" ? local.backup_bucket_name : "${local.backup_bucket_name}/${var.backup_object_prefix}"

  common_tags = {
    "owner:technical" = var.technical_owner
    "owner:business"  = var.business_owner
    migrated          = "False"
    environment       = var.environment
  }

  db_tags = merge(
    local.common_tags,
    {
      c7n_start         = 1
      confidentiality   = "restricted"
      Name              = local.db_name
    }
  )

  role_tags = merge(
    local.common_tags,
    {
      Name = local.db_role_name
    }
  )

  option_group_tags = merge(
    local.common_tags,
    {
      Name = local.option_group_name
    }
  )

  security_group_tags = merge(
    local.common_tags,
    {
      Name = local.security_group_name
    }
  )

  db_subnet_group_tags = merge(
    local.common_tags,
    {
      Name = local.db_subnet_group_name
    }
  )

  rotation_lambda_tags = merge(
    local.common_tags,
    {
      Name = local.rotation_lambda_function_name
    }
  )

  rotation_lambda_role_tags = merge(
    local.common_tags,
    {
      Name = local.rotation_lambda_role_name
    }
  )

  dbi_credentials_secret_tags = merge(
    local.common_tags,
    {
      Name = local.dbi_credentials_secret_name
    }
  )
}

# --- OPTION GROUP
# ---

resource "aws_iam_role" "rds_restore_role" {
  name               = local.db_role_name
  tags               = local.role_tags
  assume_role_policy = <<-POLICY
  {
    "Version": "2012-10-17",
    "Statement": [
      {
        "Action": "sts:AssumeRole",
        "Principal": {
          "Service": "rds.amazonaws.com"
        },
        "Effect": "Allow",
        "Sid": ""
      }
    ]
  }
  POLICY
}

resource "aws_iam_role_policy" "rds_backup_policy" {
  role   = aws_iam_role.rds_restore_role.id
  policy = <<-EOF
  {
    "Version": "2012-10-17",
    "Statement": [
      {
        "Sid": "ListContentInBackupBucket",
        "Effect": "Allow",
        "Action": "s3:ListBucket",
        "Resource": "arn:aws:s3:::${local.backup_bucket_name}",
        "Condition": {
          "StringLike": {
            "s3:prefix": [
              "${var.backup_object_prefix}",
              "${var.backup_object_prefix}/*"
            ]
          }
        }
      },
      {
        "Sid": "GetBucketLocation",
        "Effect": "Allow",
        "Action": "s3:GetBucketLocation",
        "Resource": "arn:aws:s3:::${local.backup_bucket_name}"
      },
      {
        "Sid": "ReadWriteObjects",
        "Effect": "Allow",
        "Action": [
          "s3:PutObject",
          "s3:GetObject",
          "s3:AbortMultipartUpload",
          "s3:ListMultipartUploadParts"
        ],
        "Resource": "arn:aws:s3:::${local.backup_location}/*"
      },
      {
        "Sid": "CheckAccessToBucketAndObjects",
        "Effect": "Allow",
        "Action": "s3:HeadBucket",
        "Resource": "*"
      }
    ]
  }
  EOF
}

# --- SECURITY GROUP
# ---

data "aws_vpcs" "vpc_ids" {}

resource "aws_security_group" "vpc_security_group" {
  name        = local.security_group_name
  description = ""
  tags        = local.security_group_tags
  vpc_id      = tolist(data.aws_vpcs.vpc_ids.ids)[0]

  ingress {
    description = "Allow incoming connections from network"
    from_port   = 3306
    to_port     = 3306
    protocol    = "tcp"
    cidr_blocks = [var.dbi_secgroup]    
    self        = true
  }

  # Allows rotation Lambda to reach Secrets Manager API
  egress {
    description = "Allow outgoing connections"
    from_port   = 0
    to_port     = 0
    protocol    = "-1"
    cidr_blocks = ["0.0.0.0/0"]
  }
}

# --- SUBNET
# ---

data "aws_subnet_ids" "private_subnets" {
  vpc_id  = tolist(data.aws_vpcs.vpc_ids.ids)[0]
  
  filter {
    name   = "tag:aws:cloudformation:logical-id"
    values = ["PrivateSubnet1", "PrivateSubnet2"]
  }
}

resource "aws_db_subnet_group" "db_subnet_group" {
  name       = local.db_subnet_group_name
  subnet_ids = data.aws_subnet_ids.private_subnets.ids
  tags       = local.db_subnet_group_tags
}

# --- AURORA SERVERLESS

resource "aws_rds_cluster" "default" {

  cluster_identifier      = local.db_name
  vpc_security_group_ids    = [ aws_security_group.vpc_security_group.id ]
  db_subnet_group_name     = aws_db_subnet_group.db_subnet_group.id
  engine_mode             = "serverless"
  engine                  = "aurora-postgresql"
  engine_version          = "10.7"
  master_username         = var.dbi_user_name
  master_password         = var.dbi_password
  backup_retention_period = 30
  storage_encrypted       = true
  apply_immediately       = true
  database_name           = "foobar"

  scaling_configuration {
    auto_pause               = true
    max_capacity             = 2
    min_capacity             = 2
    seconds_until_auto_pause = 500
  }
  skip_final_snapshot = true


  lifecycle {
    ignore_changes = [
      "engine_version",
    ]
  }
}

# --- SECRET MANAGER

resource "aws_secretsmanager_secret" "db_instance_credentials_secret" {
  name        = local.dbi_credentials_secret_name
  description = ""
  tags        = local.dbi_credentials_secret_tags
}

resource "aws_secretsmanager_secret_version" "db_instance_credentials_secret_values" {
  secret_id     = aws_secretsmanager_secret.db_instance_credentials_secret.id
  secret_string = jsonencode({
    username: var.dbi_user_name,
    password: var.dbi_password,
    engine: "postgres",
    host: aws_rds_cluster.default.endpoint,
    port: 5432,
    dbInstanceIdentifier: aws_rds_cluster.default.id
  })
}


resource "aws_ssm_parameter" "db_instance_credentials_secret_name" {
  name  = "MYAPP/dbi_credentials_secret_arn"
  type  = "String"
  value = aws_secretsmanager_secret.db_instance_credentials_secret.arn
}

# -- Rotation
resource "aws_secretsmanager_secret_rotation" "db_instance_credentials_rotation" {
  secret_id           = aws_secretsmanager_secret.db_instance_credentials_secret.id
  rotation_lambda_arn = aws_lambda_function.secret_rotation_lambda.arn

  rotation_rules {
    automatically_after_days = var.lambda_rotation_days
  }
}

# --- LAMBDA
# --- 

resource "aws_lambda_function" "secret_rotation_lambda" {
  filename           = "lambda/${var.rotation_lambda_filename}.zip"
  function_name      = local.rotation_lambda_function_name
  role               = aws_iam_role.lambda_rotation_role.arn
  handler            = "lambda_function.lambda_handler"
  source_code_hash   = filebase64sha256("lambda/${var.rotation_lambda_filename}.zip")
  runtime            = "python3.7"
  vpc_config {
    subnet_ids         = data.aws_subnet_ids.private_subnets.ids
    security_group_ids = [aws_security_group.vpc_security_group.id]
  }
  timeout            = 300
  description        = ""
  environment {
    variables = {
      SECRETS_MANAGER_ENDPOINT = "https://secretsmanager.${var.region}.amazonaws.com"
    }
  }
  tags               = local.rotation_lambda_tags
}

resource "aws_iam_role" "lambda_rotation_role" {
  name               = local.rotation_lambda_role_name
  tags               = local.rotation_lambda_role_tags
  assume_role_policy = <<-EOF
  {
    "Version": "2012-10-17",
    "Statement": [
      {
        "Action": "sts:AssumeRole",
        "Principal": {
          "Service": "lambda.amazonaws.com"
        },
        "Effect": "Allow",
        "Sid": ""
      }
    ]
  }
  EOF
}

resource "aws_iam_role_policy_attachment" "policy_AWSLambdaBasicExecutionRole" {
  role       = aws_iam_role.lambda_rotation_role.name
  policy_arn = "arn:aws:iam::aws:policy/service-role/AWSLambdaBasicExecutionRole"
}

resource "aws_iam_role_policy_attachment" "policy_AWSLambdaVPCAccessExecutionRole" {
  role       = aws_iam_role.lambda_rotation_role.name
  policy_arn = "arn:aws:iam::aws:policy/service-role/AWSLambdaVPCAccessExecutionRole"
}

data "aws_iam_policy_document" "SecretsManagerRDSAuroraServerlessRotationSingleUserRolePolicy" {
  statement {
    actions = [
      "ec2:CreateNetworkInterface",
      "ec2:DeleteNetworkInterface",
      "ec2:DescribeNetworkInterfaces",
      "ec2:DetachNetworkInterface",
    ]
    resources = ["*"]
  }
  statement {
    actions = [
      "secretsmanager:DescribeSecret",
      "secretsmanager:GetSecretValue",
      "secretsmanager:PutSecretValue",
      "secretsmanager:UpdateSecretVersionStage",
    ]
    resources = [
      "arn:aws:secretsmanager:${var.region}:${var.target_account_id}:secret:*",
    ]
    condition {
      test     = "StringEquals"
      variable = "secretsmanager:resource/AllowRotationLambdaArn"
      values = [aws_lambda_function.secret_rotation_lambda.arn]
    }
  }
  statement {
    actions   = ["secretsmanager:GetRandomPassword"]
    resources = ["*"]
  }
}

resource "aws_iam_policy" "SecretsManagerRDSAuroraRotationSingleUserRolePolicy" {
  path   = "/"
  policy = data.aws_iam_policy_document.SecretsManagerRDSAuroraRotationSingleUserRolePolicy.json
}

resource "aws_iam_role_policy_attachment" "SecretsManagerRDSAuroraRotationSingleUserRolePolicy" {
  role       = aws_iam_role.lambda_rotation_role.name
  policy_arn = aws_iam_policy.SecretsManagerRDSAuroraRotationSingleUserRolePolicy.arn
}

resource "aws_lambda_permission" "allow_secret_manager_call_roation_lambda" {
  function_name = aws_lambda_function.secret_rotation_lambda.function_name
  statement_id = "AllowExecutionSecretManager"
  action = "lambda:InvokeFunction"
  principal = "secretsmanager.amazonaws.com"
}

The lambda/ folder has the code I downloaded from a Lambda function I set up manually to do the rotation, which I later deleted. The lambda_function.py code fails at this point:

def set_secret(service_client, arn, token):
    
    # First try to login with the pending secret, if it succeeds, return
    pending_dict = get_secret_dict(service_client, arn, "AWSPENDING", token)
    conn = get_connection(pending_dict)
    if conn:
        conn.close()
        logger.info("setSecret: AWSPENDING secret is already set as password in PostgreSQL DB for secret arn %s." % arn)
        return

    logger.info("setSecret: unable to log with AWSPENDING credentials")
    curr_dict = get_secret_dict(service_client, arn, "AWSCURRENT")

    # Now try the current password
    conn = get_connection(curr_dict)
    if not conn:
        # If both current and pending do not work, try previous
        logger.info("setSecret: unable to log with AWSCURRENT credentials")
        try:
            conn = get_connection(get_secret_dict(service_client, arn, "AWSPREVIOUS"))
        except service_client.exceptions.ResourceNotFoundException:
            logger.info("setSecret: Unable to log with AWSPREVIOUS credentials")
            conn = None

It can't connect to the RDS cluster with any of the secrets, even though I can connect from the console using those credentials (username and password).