ECSに環境変数経由で渡したSecrets Manager管理のDB パスワードを、接続エラーなしでローテーションできるかやってみた

# シングルユーザー方式用
export SINGLE_SECRET_ARN=$(aws secretsmanager create-secret \
  --name rotation-test/app-single \
  --secret-string "$(jq -n --arg host "$DB_HOST" --arg pw "$SINGLE_PASSWORD" \
    '{engine: "mysql", host: $host, port: 3306, username: "app_single", password: $pw, dbname: "appdb"}')" \
  --query ARN --output text)

# 交代ユーザー方式用
export ALT_SECRET_ARN=$(aws secretsmanager create-secret \
  --name rotation-test/app-alt \
  --secret-string "$(jq -n --arg host "$DB_HOST" --arg pw "$ALT_PASSWORD" --arg master "$MASTER_SECRET_ARN" \
    '{engine: "mysql", host: $host, port: 3306, username: "app_alt", password: $pw, dbname: "appdb", masterarn: $master}')" \
  --query ARN --output text)

echo "export SINGLE_SECRET_ARN=$SINGLE_SECRET_ARN" >> ~/rotation-test.env
echo "export ALT_SECRET_ARN=$ALT_SECRET_ARN" >> ~/rotation-test.env

aws secretsmanager get-secret-value --secret-id rotation-test/app-alt --query SecretString --output text | jq 'del(.password)'

deploy_rotation_function() {
  local app=$1 fn=$2 master=${3:-}
  local params
  params=$(jq -n \
    --arg endpoint "https://secretsmanager.${AWS_REGION}.amazonaws.com" \
    --arg fn "$fn" \
    --arg subnets "${PRIVATE_SUBNET_1},${PRIVATE_SUBNET_2}" \
    --arg sg "$APP_SG" \
    --arg master "$master" \
    '[
      {Name: "endpoint", Value: $endpoint},
      {Name: "functionName", Value: $fn},
      {Name: "vpcSubnetIds", Value: $subnets},
      {Name: "vpcSecurityGroupIds", Value: $sg}
    ] + (if $master != "" then [{Name: "masterSecretArn", Value: $master}] else [] end)')

  local change_set
  change_set=$(aws serverlessrepo create-cloud-formation-change-set \
    --application-id arn:aws:serverlessrepo:us-east-1:297356227824:applications/${app} \
    --stack-name ${fn} \
    --capabilities CAPABILITY_IAM CAPABILITY_RESOURCE_POLICY \
    --parameter-overrides "$params" \
    --query ChangeSetId --output text)
  aws cloudformation wait change-set-create-complete --change-set-name $change_set
  aws cloudformation execute-change-set --change-set-name $change_set
  aws cloudformation wait stack-create-complete --stack-name serverlessrepo-${fn}
}

deploy_rotation_function SecretsManagerRDSMySQLRotationSingleUser rotation-test-single-rotation
deploy_rotation_function SecretsManagerRDSMySQLRotationMultiUser rotation-test-alt-rotation "$MASTER_SECRET_ARN"

export SINGLE_ROTATION_FN_ARN=$(aws lambda get-function --function-name rotation-test-single-rotation --query Configuration.FunctionArn --output text)
export ALT_ROTATION_FN_ARN=$(aws lambda get-function --function-name rotation-test-alt-rotation --query Configuration.FunctionArn --output text)

aws secretsmanager rotate-secret \
  --secret-id rotation-test/app-single \
  --rotation-lambda-arn $SINGLE_ROTATION_FN_ARN \
  --rotation-rules '{"ScheduleExpression": "rate(7 days)"}' \
  --no-rotate-immediately

aws secretsmanager rotate-secret \
  --secret-id rotation-test/app-alt \
  --rotation-lambda-arn $ALT_ROTATION_FN_ARN \
  --rotation-rules '{"ScheduleExpression": "rate(7 days)"}' \
  --no-rotate-immediately

for s in rotation-test/app-single rotation-test/app-alt; do
  aws secretsmanager describe-secret --secret-id $s --query '{name: Name, enabled: RotationEnabled, function: RotationLambdaARN, rules: RotationRules}'
done

import os
import socket

import pymysql
from http.server import BaseHTTPRequestHandler, ThreadingHTTPServer

HOSTNAME = socket.gethostname()

class Handler(BaseHTTPRequestHandler):
    def do_GET(self):
        if self.path == "/health":
            self.reply(200, "ok")
            return
        try:
            conn = pymysql.connect(
                host=os.environ["DB_HOST"],
                user=os.environ["DB_USER"],
                password=os.environ["DB_PASSWORD"],
                database="appdb",
                connect_timeout=3,
            )
            with conn.cursor() as cur:
                cur.execute("SELECT CURRENT_USER()")
                user = cur.fetchone()[0]
            conn.close()
            self.reply(200, f"OK {user} {HOSTNAME}")
        except Exception as e:
            print(f"DB_ERROR {e}", flush=True)
            self.reply(500, f"NG {e} {HOSTNAME}")

    def reply(self, code, body):
        data = body.encode()
        self.send_response(code)
        self.send_header("Content-Type", "text/plain")
        self.send_header("Content-Length", str(len(data)))
        self.end_headers()
        self.wfile.write(data)

    def log_message(self, format, *args):
        pass

ThreadingHTTPServer(("0.0.0.0", 8080), Handler).serve_forever()

export APP_B64=$(base64 -w0 app.py)

make_taskdef() {
  jq -n \
    --arg exec "$EXEC_ROLE_ARN" \
    --arg b64 "$APP_B64" \
    --arg arn "$1" \
    --arg region "$AWS_REGION" \
    '{
      family: "rotation-test-app",
      requiresCompatibilities: ["FARGATE"],
      networkMode: "awsvpc",
      cpu: "256",
      memory: "512",
      executionRoleArn: $exec,
      containerDefinitions: [{
        name: "app",
        image: "public.ecr.aws/docker/library/python:3.12-slim",
        essential: true,
        portMappings: [{containerPort: 8080}],
        entryPoint: ["sh", "-c"],
        command: ["pip install -q pymysql cryptography && echo \"$APP_B64\" | base64 -d > /app.py && exec python /app.py"],
        environment: [{name: "APP_B64", value: $b64}],
        secrets: [
          {name: "DB_HOST", valueFrom: ($arn + ":host::")},
          {name: "DB_USER", valueFrom: ($arn + ":username::")},
          {name: "DB_PASSWORD", valueFrom: ($arn + ":password::")}
        ],
        logConfiguration: {
          logDriver: "awslogs",
          options: {
            "awslogs-group": "/ecs/rotation-test",
            "awslogs-region": $region,
            "awslogs-stream-prefix": "app"
          }
        }
      }]
    }' > taskdef-app.json
}

make_taskdef "$SINGLE_SECRET_ARN"
aws ecs register-task-definition --cli-input-json file://taskdef-app.json --query 'taskDefinition.taskDefinitionArn' --output text

aws ecs create-service \
  --cluster rotation-test-cluster \
  --service-name rotation-test-app \
  --task-definition rotation-test-app \
  --desired-count 2 \
  --launch-type FARGATE \
  --network-configuration "awsvpcConfiguration={subnets=[${PUBLIC_SUBNETS_CSV}],securityGroups=[${APP_SG}],assignPublicIp=ENABLED}" \
  --load-balancers "targetGroupArn=${TG_ARN},containerName=app,containerPort=8080" \
  --deployment-configuration "minimumHealthyPercent=100,maximumPercent=200" \
  --health-check-grace-period-seconds 60 \
  --query 'service.serviceArn' --output text

aws ecs wait services-stable --cluster rotation-test-cluster --services rotation-test-app
curl -s http://$ALB_DNS/; echo

import json
import os

import boto3

ecs = boto3.client("ecs")

def lambda_handler(event, context):
    print(json.dumps(event))
    ecs.update_service(
        cluster=os.environ["CLUSTER"],
        service=os.environ["SERVICE"],
        forceNewDeployment=True,
    )
    print("forceNewDeployment requested")

zip redeploy.zip redeploy.py

cat > lambda-trust.json <<'EOF'
{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Principal": {"Service": "lambda.amazonaws.com"},
    "Action": "sts:AssumeRole"
  }]
}
EOF
aws iam create-role --role-name rotation-test-redeploy-role --assume-role-policy-document file://lambda-trust.json --query 'Role.Arn' --output text
aws iam attach-role-policy --role-name rotation-test-redeploy-role --policy-arn arn:aws:iam::aws:policy/service-role/AWSLambdaBasicExecutionRole

cat > redeploy-policy.json <<EOF
{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Action": "ecs:UpdateService",
    "Resource": "arn:aws:ecs:${AWS_REGION}:${ACCOUNT_ID}:service/rotation-test-cluster/rotation-test-app"
  }]
}
EOF
aws iam put-role-policy --role-name rotation-test-redeploy-role --policy-name rotation-test-update-service --policy-document file://redeploy-policy.json

sleep 15

export REDEPLOY_FN_ARN=$(aws lambda create-function \
  --function-name rotation-test-redeploy \
  --runtime python3.12 \
  --handler redeploy.lambda_handler \
  --role arn:aws:iam::${ACCOUNT_ID}:role/rotation-test-redeploy-role \
  --zip-file fileb://redeploy.zip \
  --environment "Variables={CLUSTER=rotation-test-cluster,SERVICE=rotation-test-app}" \
  --query FunctionArn --output text)
echo "export REDEPLOY_FN_ARN=$REDEPLOY_FN_ARN" >> ~/rotation-test.env

cat > pattern.json <<'EOF'
{
  "source": ["aws.secretsmanager"],
  "detail-type": ["Secret Label Updated"],
  "detail": {
    "name": ["rotation-test/app-single", "rotation-test/app-alt"],
    "labelUpdated": ["AWSCURRENT"]
  }
}
EOF
export RULE_ARN=$(aws events put-rule --name rotation-test-secret-updated --event-pattern file://pattern.json --query RuleArn --output text)
aws lambda add-permission \
  --function-name rotation-test-redeploy \
  --statement-id eventbridge \
  --action lambda:InvokeFunction \
  --principal events.amazonaws.com \
  --source-arn $RULE_ARN
aws events put-targets --rule rotation-test-secret-updated --targets "Id=redeploy,Arn=${REDEPLOY_FN_ARN}"

aws events describe-rule --name rotation-test-secret-updated --query '{name: Name, state: State, pattern: EventPattern}'
aws events list-targets-by-rule --rule rotation-test-secret-updated --query 'Targets[].Arn'

cat > measure.sh <<'EOF'
#!/bin/bash
trap 'exit 0' INT TERM
while true; do
  body=$(curl -s --max-time 5 -w ' HTTP%{http_code}' "http://${ALB_DNS}/")
  echo "$(date '+%H:%M:%S') ${body}"
  sleep 0.5
done
EOF
chmod +x measure.sh

./measure.sh | tee single.log

aws secretsmanager rotate-secret --secret-id rotation-test/app-single

aws ecs describe-services --cluster rotation-test-cluster --services rotation-test-app \
  --query 'services[0].deployments[].{status: status, rollout: rolloutState, running: runningCount, taskDefinition: taskDefinition}' --output table

NG (1045, "Access denied for user 'app_single'@'10.0.0.XXX' (using password: YES)")

awk '{print $NF}' single.log | sort | uniq -c

aws logs tail /aws/lambda/rotation-test-single-rotation --since 30m
aws logs tail /aws/lambda/rotation-test-redeploy --since 30m

{
  "name": "rotation-test/app-single",
  "labelUpdated": "AWSCURRENT",
  "versionId": "ab8f8944-c7b9-4e53-aeb7-95c1c221d4ec"
}

make_taskdef "$ALT_SECRET_ARN"
aws ecs register-task-definition --cli-input-json file://taskdef-app.json --query 'taskDefinition.taskDefinitionArn' --output text
aws ecs update-service --cluster rotation-test-cluster --service rotation-test-app --task-definition rotation-test-app --query 'service.taskDefinition' --output text
aws ecs wait services-stable --cluster rotation-test-cluster --services rotation-test-app
curl -s http://$ALB_DNS/; echo

./measure.sh | tee alt-1.log

aws secretsmanager rotate-secret --secret-id rotation-test/app-alt

aws ecs describe-services --cluster rotation-test-cluster --services rotation-test-app \
  --query 'services[0].deployments[].{status: status, rollout: rolloutState, running: runningCount, taskDefinition: taskDefinition}' --output table

awk '{print $NF}' alt-1.log | sort | uniq -c

OVERRIDES=$(jq -n '{containerOverrides: [{name: "mysql", environment: [{name: "SQL", value: "SELECT user, host FROM mysql.user WHERE user LIKE '\''app%'\'';"}]}]}')

aws ecs run-task \
  --cluster rotation-test-cluster \
  --task-definition rotation-test-setup \
  --launch-type FARGATE \
  --network-configuration "awsvpcConfiguration={subnets=[${PUBLIC_SUBNET_1}],securityGroups=[${APP_SG}],assignPublicIp=ENABLED}" \
  --overrides "$OVERRIDES" \
  --query 'tasks[0].taskArn' --output text

# 1分ほど待ってから
aws logs tail /ecs/rotation-test --log-stream-name-prefix setup --since 5m

./measure.sh | tee alt-2.log

aws secretsmanager rotate-secret --secret-id rotation-test/app-alt

awk '{print $NF}' alt-2.log | sort | uniq -c