You can use AWS Secrets Manager to automatically rotate of passwords using a custom lambda function. AWS provides lambda function templates for many AWS services (RDS, DocumentDB, etc.) and a template you can use to develop your own functions.

Secrets Manager uses a labeling scheme during password rotation.

1. Ensure there is a new secret, labeled AWSPENDING. Create if needed.

2. Connect using the current secret, labeled AWSCURRENT, and change the password

3. Test connection using the AWSPENDING secret

   1. if successful, AWSCURRENT -> AWSPREVIOUS, AWSPENDING -> AWSCURRENT

   2. if failed, leave everything as is

The idea is to try and ensure that there's no way a failure that can result in your credentials getting lost, and that the process should automatically recover.

Modifying the MySQL RDS Procedure

I started with the AWS-provided RDS MySQL function. Then I made the changes necessary to get it working with Snowflake.

High-Level Overview of Steps:

• Replaced pymsql with snowflake.connector

• Restructured secret format to match Snowflake connection parameters

• Removed MySQL SSL-handling code

• Updated password change code to Snowflake syntax

• Updated connection test code to Snowflake syntax

The exact changes are easily visible in the commit history.

Permissions

The Lambda needs some permissions to rotate the secret:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "logs:CreateLogGroup",
            "Resource": "arn:aws:logs:us-east-1:121769289400:*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "logs:CreateLogStream",
                "logs:PutLogEvents"
            ],
            "Resource": [
                "arn:aws:logs:us-east-1:121769289400:log-group:/aws/lambda/SecretsManagerSnowflakeRotationSingleUser:*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "secretsmanager:DescribeSecret",
                "secretsmanager:GetSecretValue",
                "secretsmanager:PutSecretValue",
                "secretsmanager:UpdateSecretVersionStage"
            ],
            "Resource": "arn:aws:secretsmanager:us-east-1:121769289400:secret:snowflake-ofxx6S"
        },
        {
            "Effect": "Allow",
            "Action": [
                "secretsmanager:GetRandomPassword"
            ],
            "Resource": "*"
        },
        {
            "Action": [
                "ec2:CreateNetworkInterface",
                "ec2:DeleteNetworkInterface",
                "ec2:DescribeNetworkInterfaces",
                "ec2:DetachNetworkInterface"
            ],
            "Resource": "*",
            "Effect": "Allow"
        }
    ]
}

The lambda needs to be able to:

• Write logs to CloudWatch

• Create new random secrets, and administer the secret

• Access the KMS key the secret is encrypted with (by default, the service key has access)

• (optional) Connect to a VPC

Secrets Manager needs to have access to invoke the lambda, as well:

Testing Rotation

You can see it running the four stages in CloudWatch during the scheduled rotation: