Best practice to codify the provisioning of new user with multi-user rotation securely

[jackie-linz]

Context

We are doing as much of configuration-as-code as possible for the DB setup, so that it can be reliably reproduced across all our environments.

The setup includes:

• CDK to provision any AWS resource (DB, secrets, roles, etc.)
• DB migration scripts (using liquibase) to provision DB schema, users, roles & grants

Goal

We would like to use the combination of CDK and DB migration scripts to codify the provisioning of DB users securely, i.e. without having to commit any initial password in our repo.

Our initial design is to have DB migration script to create the users without password - which means the users cannot yet be used to login.

CREATE USER test1 WITH LOGIN;
CREATE USER test1_clone WITH LOGIN;

CREATE ROLE test1_role;
-- GRANT various permissions to test1_role;

GRANT test1_role TO test1;
GRANT test1_role TO test1_clone;

And then create the secret and multi-user rotation with CDK (for user test1) and with rotateImmediatelyOnUpdate: true.

We were thinking that upon the first execution, the rotation function would set password for test1_clone and from that point the credential becomes valid and client can login to the database using the credential from the secret.

Problem

The multi-user rotation lambda have a security feature that verifies that the current credential can connect to the database successfully, before proceeding to set the new password, as per the following code snippets

• Postgres:

  aws-secrets-manager-rotation-lambdas/SecretsManagerRDSPostgreSQLRotationMultiUser/lambda_function.py

  Lines 176 to 182 in b4b8eca

  	# Before we do anything with the secret, make sure the AWSCURRENT secret is valid by logging in to the db
  	# This ensures that the credential we are rotating is valid to protect against a confused deputy attack
  	conn = get_connection(current_dict)
  	if not conn:
  	logger.error("setSecret: Unable to log into database using current credentials for secret %s" % arn)
  	raise ValueError("Unable to log into database using current credentials for secret %s" % arn)
  	conn.close()

• MySQL:

  aws-secrets-manager-rotation-lambdas/SecretsManagerRDSMySQLRotationMultiUser/lambda_function.py

  Lines 179 to 185 in b4b8eca

  	# Before we do anything with the secret, make sure the AWSCURRENT secret is valid by logging in to the db
  	# This ensures that the credential we are rotating is valid to protect against a confused deputy attack
  	conn = get_connection(current_dict)
  	if not conn:
  	logger.error("setSecret: Unable to log into database using current credentials for secret %s" % arn)
  	raise ValueError("Unable to log into database using current credentials for secret %s" % arn)
  	conn.close()

Which means the very first rotation would fail, leaving the users in the initial state where they cannot be used to login.

Potential solution considered

We thought of seeding the test1 user with an initial password, so that the password rotation can work. However, with this approach the initial password would still be valid between the first and the second rotation (since first rotation actually only updates the password for test1_clone). This would leave the DB compromised.

Question

What's the best practice to codify the provision of username/password credentials?

[jackie-linz]

This is what I came up with at the end:

1. in liquibase scripts, just create the non-login role CREATE ROLE test1_role;
2. use CDK to provision
   1. secret with username test1 and a random password
   2. multi-user password rotation with rotateImmediatelyOnUpdate: false
   3. custom resource to manage the users, which
      1. on Create event
         • create user <username> with the password in the secret and grant it <username>_role, if it doesn't already exist
         • create user <username>_clone without password and grant it <username>_role, if it doesn't already exist
         • note - the if it doesn't already exist check should prevent confused deputy attack
      2. on Delete event
         • delete users <username> and <username>_clone