Automated password Rotation for Aurora PostgreSQL using SecretManager & Custom Lambda

Sep 10

6 min read

Introduction

Managing database credentials is one of the most critical — and often neglected — aspects of cloud security. Static passwords that never change create a large attack surface: a single leaked credential can give an attacker persistent access to production data.

AWS Secrets Manager addresses this by managing the full lifecycle of database credentials: storing them encrypted, distributing them to applications, and rotating them automatically on a schedule. When combined with Amazon Aurora PostgreSQL and a Lambda function that implements the rotation protocol, the entire process becomes zero-touch.

This post shows how to implement automatic password rotation for Aurora PostgreSQL users using AWS Secrets Manager and a native Lambda function, fully provisioned with Terraform.

Why not use the SAR application?

AWS provides a pre-built rotation Lambda via the Serverless Application Repository (SecretsManagerPostgreSQLRotationMultiUser). However, deploying it requires serverlessrepo:GetApplication and serverlessrepo:CreateCloudFormationChangeSet on an AWS-owned resource — permissions that are often absent in SSO Permission Sets even for Administrator roles, since they apply to a resource in another AWS account.

Rather than requesting cross-account SAR permissions, deploying the rotation Lambda directly as a native resource is simpler and gives full control over the code.

Architecture

The rotation flow works as follows:

Secrets Manager triggers the Lambda on schedule (every 90 days in this example).
The Lambda executes the 4-step rotation protocol.
Each step reads or writes the secret version staged as AWSPENDING or AWSCURRENT.
On success, the new credentials become AWSCURRENT and are immediately available to applications.

Prerequisites

An existing Aurora PostgreSQL cluster (or RDS PostgreSQL — see note at the end).
The cluster must be accessible from within the VPC.
Terraform >= 1.0 and AWS provider >= 5.0.
Python 3.13 installed locally (required by terraform-aws-modules/lambda/aws to package dependencies).
The database users to be rotated must already exist in the DB.

Project structure

project/
├── aurora.tf
├── lambda.tf
└── lambdas/
    └── aurora-rotation/
        ├── index.py
        └── requirements.txt

Step 1 — Security Group for the Lambda

The rotation Lambda needs outbound access to Aurora on port 5432 and to Secrets Manager on port 443:

# aurora.tf
resource "aws_security_group" "rotation_lambda" {
  name        = "project-environment-rotation-lambda"
  description = "Security group for the Aurora password rotation Lambda"
  vpc_id      = vpc_id

  egress {
    from_port   = 5432
    to_port     = 5432
    protocol    = "tcp"
    cidr_blocks = ["10.0.0.0/8"] # adjust to your VPC CIDR
    description = "Allow outbound to Aurora"
  }

  egress {
    from_port   = 443
    to_port     = 443
    protocol    = "tcp"
    cidr_blocks = ["0.0.0.0/0"]
    description = "Allow outbound to Secrets Manager endpoint"
  }
}

# Allow inbound from the Lambda SG into Aurora
resource "aws_security_group_rule" "aurora_from_rotation_lambda" {
  type                     = "ingress"
  from_port                = 5432
  to_port                  = 5432
  protocol                 = "tcp"
  security_group_id        = aurora_security_group_id
  source_security_group_id = aws_security_group.rotation_lambda.id
  description              = "Allow PostgreSQL traffic from rotation Lambda"
}

Step 2 — Define the secrets

Each database user gets its own secret. The ignore_changes lifecycle rule prevents Terraform from overwriting the value after the Lambda rotates it:

resource "aws_secretsmanager_secret" "dev_user_secret" {
  name                    = "/${local.project}/development/DB_PASSWORD"
  description             = "Credentials for dev_user on Aurora PostgreSQL"
  recovery_window_in_days = 0
})
resource "aws_secretsmanager_secret_version" "dev_user_secret_val" {
  secret_id = aws_secretsmanager_secret.dev_user_secret.id
  secret_string = jsonencode({
    engine   = "postgres"
    host     = module.aurora.cluster_endpoint
    port     = 5432
    dbname   = "dev_db"
    username = "dev_user"
    password = var.DEV_DB_PASSWORD
  })

  lifecycle {
    # Prevent Terraform from overwriting after Lambda rotates
    ignore_changes = [secret_string]
  }
}

Step 3 — IAM Role for the Lambda

# lambda.tf

resource "aws_iam_role" "aurora_rotation_lambda" {
  name = "${local.project}-${local.environment}-aurora-rotation"

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

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

resource "aws_iam_policy" "aurora_rotation_secrets" {
  name        = "${local.project}-${local.environment}-aurora-rotation-secrets"
  description = "Allow rotation Lambda to manage Secrets Manager secrets"

  policy = jsonencode({
    Version = "2012-10-17"
    Statement = [
      {
        Effect = "Allow"
        Action = [
          "secretsmanager:DescribeSecret",
          "secretsmanager:GetSecretValue",
          "secretsmanager:PutSecretValue",
          "secretsmanager:UpdateSecretVersionStage",
          "secretsmanager:GetRandomPassword",
        ]
        Resource = "*"
      }
    ]
  })
}

resource "aws_iam_role_policy_attachment" "aurora_rotation_secrets" {
  role       = aws_iam_role.aurora_rotation_lambda.name
  policy_arn = aws_iam_policy.aurora_rotation_secrets.arn
}

Step 4 — Deploy the rotation Lambda

The terraform-aws-modules/lambda/aws module handles packaging, including installing pg8000 (a pure-Python PostgreSQL driver — no native compilation required):

# lambda.tf (continued)

module "aurora_rotation_lambda" {
  source  = "terraform-aws-modules/lambda/aws"
  version = "8.8.0"

  function_name = "${local.project}-${local.environment}-aurora-rotation"
  handler       = "index.lambda_handler"
  runtime       = "python3.13"
  timeout       = 30

  create_role = false
  lambda_role = aws_iam_role.aurora_rotation_lambda.arn

  source_path = "../../../lambdas/aurora-rotation"

  # pg8000 is a pure-Python PostgreSQL driver — no native compilation needed
  layers = []

  environment_variables = {
    SECRETS_MANAGER_ENDPOINT = "https://secretsmanager.${data.aws_region.current.name}.amazonaws.com"
  }

  vpc_subnet_ids         = module.vpc.private_subnets
  vpc_security_group_ids = [aws_security_group.lambda_rotation_sg.id]

  allowed_triggers = {
    SecretsManagerDev = {
      principal  = "secretsmanager.amazonaws.com"
      source_arn = aws_secretsmanager_secret.dev_user_secret.arn
    }
    SecretsManagerQa = {
      principal  = "secretsmanager.amazonaws.com"
      source_arn = aws_secretsmanager_secret.qa_user_secret.arn
    }
  }

  # Avoid adding permissions to a specific version — use $LATEST alias
  create_current_version_allowed_triggers = false

  cloudwatch_logs_retention_in_days = local.cloudwatch_log_group_retention_in_days
}

Note: The runtime value must match the Python version installed locally. The module runs pip install using that interpreter to package dependencies. If you have python3.11, set runtime = "python3.11".

Step 5 — Configure the rotation schedule

resource "aws_secretsmanager_secret_rotation" "dev_user_rotation" {

  secret_id           = aws_secretsmanager_secret.dev_user_secret.id
  rotation_lambda_arn = module.aurora_rotation_lambda.lambda_function_arn

  rotation_rules {
    automatically_after_days = 90
  }
}

Step 6 — The rotation function

Create lambdas/aurora-rotation/requirements.txt:

pg8000==1.31.2

Create lambdas/aurora-rotation/index.py:

"""
Aurora PostgreSQL single-user secret rotation for AWS Secrets Manager.

The Lambda connects using the secret's own credentials and changes its
own password. No masterarn required.

Rotation steps
--------------
1. createSecret  – generate a new random password and stage it as AWSPENDING.
2. setSecret     – connect to Aurora as the user and ALTER its own password.
3. testSecret    – verify the pending credentials can open a connection.
4. finishSecret  – promote AWSPENDING → AWSCURRENT.
"""

import json
import logging
import os

import boto3
import pg8000.native

logger = logging.getLogger()
logger.setLevel(logging.INFO)


def lambda_handler(event, context):
    secret_arn = event["SecretId"]
    token      = event["ClientRequestToken"]
    step       = event["Step"]

    sm = boto3.client(
        "secretsmanager",
        endpoint_url=os.environ.get("SECRETS_MANAGER_ENDPOINT"),
    )

    metadata = sm.describe_secret(SecretId=secret_arn)
    if not metadata.get("RotationEnabled"):
        raise ValueError(f"Rotation is not enabled for secret {secret_arn}")

    versions = metadata.get("VersionIdsToStages", {})
    if token not in versions:
        raise ValueError(f"Token {token} not found in versions of {secret_arn}")
    if "AWSCURRENT" in versions[token]:
        logger.info("Token is already AWSCURRENT — nothing to do")
        return
    if "AWSPENDING" not in versions[token]:
        raise ValueError(f"Token {token} is not staged as AWSPENDING for {secret_arn}")

    if step == "createSecret":
        _create_secret(sm, secret_arn, token)
    elif step == "setSecret":
        _set_secret(sm, secret_arn, token)
    elif step == "testSecret":
        _test_secret(sm, secret_arn, token)
    elif step == "finishSecret":
        _finish_secret(sm, secret_arn, token)
    else:
        raise ValueError(f"Unknown rotation step: {step}")


def _create_secret(sm, arn, token):
    """Stage a new random password as AWSPENDING (idempotent)."""
    try:
        sm.get_secret_value(SecretId=arn, VersionId=token, VersionStage="AWSPENDING")
        logger.info("createSecret: AWSPENDING already exists — skipping")
        return
    except sm.exceptions.ResourceNotFoundException:
        pass

    current = _get_secret_dict(sm, arn, stage="AWSCURRENT")
    new_password = sm.get_random_password(
        PasswordLength=32,
        ExcludeCharacters="/@\"'\\",
    )["RandomPassword"]

    current["password"] = new_password
    sm.put_secret_value(
        SecretId=arn,
        ClientRequestToken=token,
        SecretString=json.dumps(current),
        VersionStages=["AWSPENDING"],
    )
    logger.info("createSecret: new AWSPENDING version staged")


def _set_secret(sm, arn, token):
    """Connect as the user itself and ALTER its own password."""
    pending = _get_secret_dict(sm, arn, version_id=token, stage="AWSPENDING")

    # Idempotency: if pending creds already work, password was already changed
    if _can_connect(pending):
        logger.info("setSecret: pending credentials already work — skipping ALTER")
        return

    # Connect using current credentials
    current = _get_secret_dict(sm, arn, stage="AWSCURRENT")
    conn = _connect(current)
    try:
        username     = pending["username"]
        new_password = pending["password"]
        # PostgreSQL does not support bind parameters in DDL statements.
        # Fetch a properly escaped literal from the server to prevent injection.
        escaped = conn.run("SELECT quote_literal(:pwd)", pwd=new_password)[0][0]
        conn.run(f'ALTER USER "{username}" WITH PASSWORD {escaped}')
        logger.info("setSecret: password updated successfully")
    finally:
        conn.close()


def _test_secret(sm, arn, token):
    """Verify the AWSPENDING credentials can connect to Aurora."""
    pending = _get_secret_dict(sm, arn, version_id=token, stage="AWSPENDING")
    conn = _connect(pending)
    conn.close()
    logger.info("testSecret: connection with AWSPENDING credentials succeeded")


def _finish_secret(sm, arn, token):
    """Promote AWSPENDING to AWSCURRENT."""
    metadata = sm.describe_secret(SecretId=arn)
    current_version = next(
        (v for v, stages in metadata["VersionIdsToStages"].items() if "AWSCURRENT" in stages),
        None,
    )
    if current_version == token:
        logger.info("finishSecret: token is already AWSCURRENT — nothing to do")
        return

    sm.update_secret_version_stage(
        SecretId=arn,
        VersionStage="AWSCURRENT",
        MoveToVersionId=token,
        RemoveFromVersionId=current_version,
    )
    logger.info("finishSecret: AWSCURRENT promoted to token %s", token)


def _get_secret_dict(sm, arn, *, stage, version_id=None):
    kwargs = {"SecretId": arn, "VersionStage": stage}
    if version_id:
        kwargs["VersionId"] = version_id
    raw = sm.get_secret_value(**kwargs)["SecretString"]
    return json.loads(raw)


def _connect(secret_dict):
    return pg8000.native.Connection(
        user=secret_dict["username"],
        password=secret_dict["password"],
        host=secret_dict["host"],
        port=int(secret_dict.get("port", 5432)),
        database=secret_dict.get("dbname", "postgres"),
        ssl_context=True,
        timeout=5,
    )


def _can_connect(secret_dict):
    try:
        conn = _connect(secret_dict)
        conn.close()
        return True
    except Exception:
        return False

Step 7 — Example tfvars

DEV_DB_PASSWORD = Example123

Security note: Never commit initial_password to source control. Pass it via environment variable (TF_VAR_db_users) or a secrets backend like AWS SSM Parameter Store.

Step 8 — Test the rotation

Force an immediate rotation to validate the setup:

aws secretsmanager rotate-secret \
  --secret-id "/myapp/staging/APP_DB_PASSWORD" \
  --region us-east-1

Follow the Lambda logs in real time:

aws logs tail /aws/lambda/myapp-staging-aurora-rotation \
  --region us-east-1 \
  --follow

A successful rotation produces four sequential log entries:

createSecret: new AWSPENDING version staged
setSecret: password updated successfully
testSecret: connection with AWSPENDING credentials succeeded
finishSecret: AWSCURRENT promoted to token <version-id>

Confirm the rotation completed:

aws secretsmanager describe-secret \
  --secret-id "/myapp/staging/APP_DB_PASSWORD" \
  --region us-east-1 \
  --query '{LastRotatedDate: LastRotatedDate, RotationEnabled: RotationEnabled}'

Conclusion

This setup removes static database passwords from the equation entirely. Once deployed, credentials rotate on schedule with no manual intervention. Applications always retrieve the current value from Secrets Manager at connection time, so the rotation is transparent to them.

The key advantages of the native Lambda approach over SAR:

No cross-account permissions required
Fits naturally into existing Terraform workflows
Full visibility into the rotation logic — easy to debug, audit, and extend

Note: While this implementation uses Python, AWS Lambda supports several other languages for rotation functions, including Rust (recommended by AWS), Node.js (JavaScript/TypeScript), Java, Go, Ruby, C#/.NET, and PowerShell.

References

Tomas Köhler

Cloud Engineer