Zero-Downtime RDS Postgres Password Rotation with Secrets Manager

1. What you'll build

A Secrets Manager rotation Lambda that rotates an RDS for PostgreSQL application password every week using the alternating-users strategy, so a valid credential exists at every moment and running apps never see an auth failure. Everything ships from the CLI plus one CloudFormation template; you never package a Lambda.

2. Prerequisites

Verified against AWS CLI 2.36.32, RDS for PostgreSQL 17.11, psql 17, the AWS::SecretsManager-2024-09-16 transform, and (for the app step) Python 3.12 with aws-secretsmanager-caching 1.1.3 and psycopg 3.3.4. Commands are bash on macOS or Linux and need jq.

You need an RDS for PostgreSQL instance (14 or newer, called app-db below) in a VPC with at least two private subnets in different AZs, its master password, psql access to it (bastion, SSM port forward, whatever you use), and IAM rights to create secrets, security groups, a VPC endpoint, CloudFormation stacks, and IAM roles (the stack creates the Lambda execution role).

One limit up front: RDS Proxy does not support alternating users. If the app connects through a proxy, use the single-user strategy instead.

3. Create the app role and two secrets

Alternating users needs two secrets: the one you rotate, and an admin secret the Lambda uses to create and re-password the clone.

Create the app role with psql as the master user:

CREATE ROLE app_user WITH LOGIN PASSWORD 'change-me-now';
GRANT CONNECT ON DATABASE appdb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

Keep privileges on app_user. On the first rotation the Lambda runs GRANT app_user TO app_user_clone, so the clone inherits through role membership, and grants you add to app_user later flow to the clone too.

Pull the instance facts into shell variables:

export AWS_REGION=us-east-1
DB_ID=app-db
read -r DB_HOST DB_PORT VPC_ID DB_SG <<< "$(aws rds describe-db-instances \
  --db-instance-identifier "$DB_ID" \
  --query 'DBInstances[0].[Endpoint.Address,Endpoint.Port,DBSubnetGroup.VpcId,VpcSecurityGroups[0].VpcSecurityGroupId]' \
  --output text)"

aws rds describe-db-instances --db-instance-identifier "$DB_ID" \
  --query 'DBInstances[0].DBSubnetGroup.Subnets[*].[SubnetIdentifier,SubnetAvailabilityZone.Name]' \
  --output table
SUBNET_A=subnet-0aaaaaaaaaaaaaaaa   # pick one subnet per AZ from that table
SUBNET_B=subnet-0bbbbbbbbbbbbbbbb

Create the admin secret, then the app secret that points at it through masterarn:

read -rs MASTER_PASSWORD   # paste the RDS master password, no echo

ADMIN_SECRET_ARN=$(aws secretsmanager create-secret --name app-db/admin \
  --secret-string "$(jq -n --arg h "$DB_HOST" --arg p "$MASTER_PASSWORD" \
    '{engine:"postgres",host:$h,username:"postgres",password:$p,dbname:"appdb",port:5432}')" \
  --query ARN --output text)

APP_SECRET_ARN=$(aws secretsmanager create-secret --name app-db/app \
  --secret-string "$(jq -n --arg h "$DB_HOST" --arg m "$ADMIN_SECRET_ARN" \
    '{engine:"postgres",host:$h,username:"app_user",password:"change-me-now",dbname:"appdb",port:5432,masterarn:$m}')" \
  --query ARN --output text)

engine must be exactly postgres and host must be identical in both secrets; the function checks both and refuses to rotate otherwise.

4. Open the network path

The function runs inside the database VPC and needs port 5432 to the instance and port 443 to Secrets Manager. Private subnets have no route to the public API, so add an interface endpoint (skip that part if the subnets already have a NAT gateway).

LAMBDA_SG=$(aws ec2 create-security-group --group-name app-db-rotation-lambda \
  --description "Secrets Manager rotation function" --vpc-id "$VPC_ID" \
  --query GroupId --output text)

aws ec2 authorize-security-group-ingress --group-id "$DB_SG" \
  --protocol tcp --port 5432 --source-group "$LAMBDA_SG"

ENDPOINT_SG=$(aws ec2 create-security-group --group-name secretsmanager-endpoint \
  --description "Secrets Manager interface endpoint" --vpc-id "$VPC_ID" \
  --query GroupId --output text)

aws ec2 authorize-security-group-ingress --group-id "$ENDPOINT_SG" \
  --protocol tcp --port 443 --source-group "$LAMBDA_SG"

aws ec2 create-vpc-endpoint --vpc-id "$VPC_ID" --vpc-endpoint-type Interface \
  --service-name "com.amazonaws.$AWS_REGION.secretsmanager" \
  --subnet-ids "$SUBNET_A" "$SUBNET_B" --security-group-ids "$ENDPOINT_SG" \
  --private-dns-enabled

Private DNS lets the function keep calling secretsmanager.$AWS_REGION.amazonaws.com and land on the endpoint; it requires DNS hostnames and DNS support enabled on the VPC, which is the default.

5. Deploy the rotation function and schedule

The AWS::SecretsManager-2024-09-16 transform builds the Lambda from AWS's SecretsManagerRDSPostgreSQLRotationMultiUser template, creates the execution role with the Secrets Manager permissions, grants GetSecretValue on the admin secret, and adds the invoke permission for secretsmanager.amazonaws.com. Don't set Runtime: the transform pins one that matches its bundled PyGreSQL binaries.

Save this as rotation.yaml:

AWSTemplateFormatVersion: '2010-09-09'
Transform: AWS::SecretsManager-2024-09-16
Parameters:
  AppSecretArn: { Type: String }
  AdminSecretArn: { Type: String }
  SubnetIds: { Type: String }
  LambdaSecurityGroupId: { Type: String }
Resources:
  AppSecretRotation:
    Type: AWS::SecretsManager::RotationSchedule
    Properties:
      SecretId: !Ref AppSecretArn
      HostedRotationLambda:
        RotationType: PostgreSQLMultiUser
        RotationLambdaName: SecretsManagerAppDbRotation
        SuperuserSecretArn: !Ref AdminSecretArn
        VpcSubnetIds: !Ref SubnetIds
        VpcSecurityGroupIds: !Ref LambdaSecurityGroupId
      RotationRules:
        ScheduleExpression: 'cron(0 2 ? * SUN *)'
        Duration: 2h
      RotateImmediatelyOnUpdate: true

That schedule is every Sunday at 02:00 UTC with a two-hour window. Secrets Manager cron needs 0 in the minutes field and * in the year field, and the shortest allowed interval is four hours. RotateImmediatelyOnUpdate runs the first rotation as soon as the stack finishes, which is how you'll test it.

aws cloudformation deploy --template-file rotation.yaml --stack-name app-db-rotation \
  --capabilities CAPABILITY_IAM CAPABILITY_AUTO_EXPAND \
  --parameter-overrides AppSecretArn="$APP_SECRET_ARN" AdminSecretArn="$ADMIN_SECRET_ARN" \
    SubnetIds="$SUBNET_A,$SUBNET_B" LambdaSecurityGroupId="$LAMBDA_SG"

CAPABILITY_AUTO_EXPAND acknowledges the macro; CAPABILITY_IAM covers the role. The command ends with:

Successfully created/updated stack - app-db-rotation

6. Make the app rotation-proof

This is zero downtime because after each rotation both app_user and app_user_clone have working passwords, and the one you just stopped using stays valid until the next rotation. Open connections are never dropped. Only new connections need the fresh secret, so the one rule for the app is: don't bake the password into an environment variable at deploy time. Read it when you open a connection, with a short cache.

import json
import botocore.session
import psycopg
from aws_secretsmanager_caching import SecretCache, SecretCacheConfig

client = botocore.session.get_session().create_client("secretsmanager")
cache = SecretCache(config=SecretCacheConfig(secret_refresh_interval=300), client=client)

def connect():
    s = json.loads(cache.get_secret_string("app-db/app"))
    return psycopg.connect(host=s["host"], port=s["port"], dbname=s["dbname"],
                           user=s["username"], password=s["password"], sslmode="require")

Install with pip install aws-secretsmanager-caching "psycopg[binary]". The app's IAM role needs secretsmanager:GetSecretValue and secretsmanager:DescribeSecret on the app secret. With a 300-second refresh and a weekly rotation, the worst case is an instance handing out the previous user for five minutes, and that user still works. If you use a pool, set a max connection lifetime so connections opened as the old user cycle out on their own.

7. Verify it works

The first rotation completes within a minute or so of the stack finishing.

aws secretsmanager describe-secret --secret-id app-db/app \
  --query '{Rotation:RotationEnabled,Last:LastRotatedDate,Stages:VersionIdsToStages}'

{
    "Rotation": true,
    "Last": "2026-08-27T14:03:11.412000+00:00",
    "Stages": {
        "1c2d3e4f-...": ["AWSPREVIOUS"],
        "7e8f9a0b-...": ["AWSCURRENT", "AWSPENDING"]
    }
}

AWSPENDING sitting on the same version as AWSCURRENT is normal after a successful run. Now check who the current user is and read the four rotation steps in the logs:

aws secretsmanager get-secret-value --secret-id app-db/app \
  --query SecretString --output text | jq -r .username

aws logs tail /aws/lambda/SecretsManagerAppDbRotation --since 15m --format short

app_user_clone
... createSecret: Successfully put secret for ARN arn:aws:secretsmanager:... and version ....
... setSecret: Successfully set password for app_user_clone in PostgreSQL DB for secret arn arn:....
... testSecret: Successfully signed into PostgreSQL DB with AWSPENDING secret in arn:....
... finishSecret: Successfully set AWSCURRENT stage to version ... for secret arn:....

Prove the old credential still works, then force a second rotation and watch the username flip back:

PREV=$(aws secretsmanager get-secret-value --secret-id app-db/app \
  --version-stage AWSPREVIOUS --query SecretString --output text)
PGPASSWORD=$(jq -r .password <<<"$PREV") psql \
  "host=$DB_HOST dbname=appdb user=$(jq -r .username <<<"$PREV") sslmode=require" \
  -c 'SELECT current_user'

aws secretsmanager rotate-secret --secret-id app-db/app
sleep 30
aws secretsmanager get-secret-value --secret-id app-db/app \
  --query SecretString --output text | jq -r .username

 current_user
--------------
 app_user
(1 row)

{
    "ARN": "arn:aws:secretsmanager:us-east-1:123456789012:secret:app-db/app-AbCdEf",
    "Name": "app-db/app",
    "VersionId": "9f0a1b2c-3d4e-5f60-7182-93a4b5c6d7e8"
}
app_user

8. Troubleshooting

Log stops after Found credentials in environment variables, then Task timed out after 30.03 seconds. The function can't reach Secrets Manager: the endpoint's security group doesn't allow 443 from $LAMBDA_SG, private DNS is off, or the subnets have neither an endpoint nor a NAT route. Fix the network and run rotate-secret again.

setSecret: Unable to log into database using credentials in master secret arn:.... If the invocation ran 5 seconds or longer before failing, the DB security group isn't allowing 5432 from $LAMBDA_SG. If it failed fast, the password in app-db/admin is wrong; test it with psql. TLS is not the cause: the template connects with sslmode=verify-full on its own, so rds.force_ssl=1 is fine.

Current database host X is not the same host as/rds replica of master Y, masterarn key is missing from secret JSON, or Database engine must be set to 'postgres'. The secret JSON is off. Both secrets need the same host, the app secret needs masterarn, and engine must be postgres. After fixing, a failed attempt may leave AWSPENDING on an empty version, which blocks the next run. Clear it:

aws secretsmanager update-secret-version-stage --secret-id app-db/app \
  --version-stage AWSPENDING --remove-from-version-id <version-id-from-describe-secret>

An error occurred (InsufficientCapabilitiesException) when calling the CreateChangeSet operation: Requires capabilities : [CAPABILITY_AUTO_EXPAND]. You dropped the capability flag from deploy. The transform is a macro and requires it.

9. Next steps

If the instance uses an RDS-managed master password (--manage-master-user-password), point SuperuserSecretArn at that secret instead; the function then needs AmazonRDSReadOnlyAccess plus an RDS interface endpoint, because the managed secret carries no host. Encrypting either secret with a customer-managed KMS key means adding KmsKeyArn or SuperuserSecretKmsKeyArn to HostedRotationLambda. To rotate more secrets against the same database, reuse the function with RotationLambdaARN in a second RotationSchedule instead of creating another HostedRotationLambda. And alert on the CloudTrail RotationFailed event through EventBridge so a broken security group rule doesn't wait a week to be noticed.