Rotating RDS secrets in AWS with open connections

If you rotate the password for a user account, users will be cut off from the database until they fetch the new password.

A common strategy is to have two user accounts (user1 and user2) and rotate their passwords on a staggered schedule. The credentials for user1 will still work while the clients detect user2 and start using it. Note for this to be effective the clients will have to check for updated credentials periodically.

https://docs.aws.amazon.com/secretsmanager/latest/userguide/rotating-secrets-two-users.html

Most databases, including all the DBs in RDS, will not close out sessions/connections when you change a password (e.g. see this answer for oracle). Terminating sessions requires explicit terminate commands.

If you are using Java and a connection pool manager you can use the AWS provided JDBC wrapper to automatically pickup the latest password when your connections need to be re-established.

I can test this by:

• Spinning up a MySQL RDS instance
• Storing the master password in Secrets Manager
• Setting up single user rotation via the console
• Connect to the DB with the MySQL CLI
• Verify the connection with a query
• Keep the connection open by starting a subshell from the CLI
• Dump the current password
• Kick off an async rotation and wait a bit
• Verify rotation by dummping the new password
• Go back to the existing MySQL connection in the CLI by exiting the subshell
• Run another query

    $ mysql -h testdb -Dmysql -u root -p$(aws --region us-east-2 secretsmanager get-secret-value --secret-id testdb-root --query SecretString --output text | jq -r '.password')
       ...
    mysql> select user from user;
    +-----------+
    | user      |
    +-----------+
    | root      |
    | mysql.sys |
    | rdsadmin  |
    +-----------+
    3 rows in set (0.05 sec)

    mysql> \! bash
    $ # Show current password
    $ aws --region us-east-2 secretsmanager get-secret-value --secret-id testdb-root --query SecretString --output text | jq -r '.password'
    3%c70'-e9s<Dy5ecX-(0mV%&E6Y[<jnJ
    $ aws --region us-east-2 secretsmanager rotate-secret --secret-id testdb-root
       ...
    $ sleep 60 # Give rotation time to complete
    $ aws --region us-east-2 secretsmanager get-secret-value --secret-id testdb-root --query SecretString --output text | jq -r '.password'
    .z,B{,P]jE~pr3?0mZ5H,6rJi;aXrQVO
    $ exit
    mysql> select user from user;
    +-----------+
    | user      |
    +-----------+
    | root      |
    | mysql.sys |
    | rdsadmin  |
    +-----------+
    3 rows in set (0.05 sec)

From the Secret Manager documentation:

Secrets Manager can automatically rotate your secret for you on a specified schedule. You can rotate credentials without interrupting the service if you choose to store a complete set of credentials for a user or account, instead of only the password. If you change or rotate only the password, then the old password immediately becomes obsolete, and clients must immediately start using the new password or fail. If you can instead create a new user with a new password, or at least alternate between two users, then the old user and password can continue to operate side by side with the new one, until you choose to deprecate the old one. This gives you a window of time when all of your clients can continue to work while you test and validate the new credentials. After your new credentials pass testing, you commit all of your clients to using the new credentials and remove the old credentials.