Credential Provisioning

This chapter turns the naming standard into an executable procedure: create the read-only and read-write MySQL users, store their credentials in AWS Secrets Manager, and create the Hyperdrive configs that carry them. It follows the existing infra/cloudflare-hyperdrive/ Terraform module rather than provisioning by hand, so the standard stays reproducible and stateful.

Scope and guardrails

This procedure is written to be executed against development. Staging and production are governed by the platform hard rules:

• Staging and production changes require an explicit override. Extending this to stg or prd modifies pre-existing infrastructure and must follow the override protocol (named directive with the literal ALERT banner, second confirmation) and be logged in OVERRIDES.md before anything is applied.
• Production has an additional gate. No --env prd apply proceeds until the AWS Secrets Manager IdP-recovery production gate is complete and verified. See platform/aws/secrets-manager/01-idp-recovery.md.
• These are Hyperdrive origin credentials, not Worker secrets. They are configuration on the Hyperdrive resource and are never set with wrangler secret put. Their canonical home is AWS Secrets Manager.

Order of operations

The password for each role is generated once, then flows to three places: the MySQL user, the Secrets Manager secret, and the Hyperdrive config. Because the module validates the database connection when it creates a Hyperdrive config, the MySQL user must exist first.

1. Generate the four passwords for the environment (one per user).
2. Create the four MySQL users with those passwords, on each instance or cluster that hosts the database.
3. Feed the passwords and endpoint structure into the Terraform module.
4. Apply the module: it writes the Secrets Manager secrets and creates the Hyperdrive configs.
5. Rebind the Worker to the new configs, deploy, and retire the legacy single-user configs and users.

Database credential management (and why none of this needs downtime)

The instances today use self-managed master passwords, a master username and a static password set by hand. Two credential concerns live on top of that, and they are independent; keeping them separate is what makes the whole change online.

The master credential

RDS and Aurora can manage the master user password in AWS Secrets Manager with automatic rotation, instead of a self-managed static value. This is an AWS-native feature and it covers the master user only.

Enabling it is a modify with --apply-immediately; it does not reboot the instance or take the database offline. It does immediately reset the master password into a new AWS-managed secret, so move any tooling that still logs in as master over to reading that secret. Application traffic runs as the dedicated read-only and read-write users and is unaffected.

For the RDS MySQL instances (dev, staging):

cd ~/Repositories/GitHub/Adventive/adventive-platform-infra && aws rds modify-db-instance --db-instance-identifier <instance-id> --manage-master-user-password --apply-immediately

For the Aurora clusters (production) the same flag is set at the cluster level:

cd ~/Repositories/GitHub/Adventive/adventive-platform-infra && aws rds modify-db-cluster --db-cluster-identifier <cluster-id> --manage-master-user-password --apply-immediately

From the console: RDS → Databases → the instance or cluster → Modify → Settings → Manage master credentials in AWS Secrets Manager → choose the KMS key → Continue → Apply immediately. AWS then rotates the managed master secret automatically on a seven-day default schedule.

The feature is not supported for Aurora Serverless v1, Aurora global databases, cross-Region read replicas, or Aurora MySQL clusters with the validate_password plugin enabled, confirm the plugin state on the production clusters before enabling. To revert, modify with --no-manage-master-user-password --master-user-password <new-value>.

Two different secrets. The AWS-managed master secret is separate from the application credentials this standard provisions. The hd_console_ro / hd_console_rw / hd_aggregate_ro / hd_aggregate_rw users live in our own adventive-db-<db>-<mode>-<env> secrets, which the mechanism below creates and the Rotation section rotates. AWS-managed rotation applies only to the master user; it never touches the application users.

Adding the RO/RW users

Creating the application users is a pure CREATE USER / GRANT operation. It modifies only the internal grant tables, takes no locks on application tables, and causes no downtime. Create the users on the writer (the cluster endpoint); on Aurora they replicate to the readers within the cluster, so the reader endpoint authenticates the same users immediately.

The RDS and Aurora console does not create MySQL database users, provisioned MySQL and Aurora MySQL have no SQL editor in the console (the Query Editor exists only for Aurora Serverless / the Data API). So the console is the tool for the master-credential conversion above; the application users and their secrets are created with a SQL client reached over the tunnel. The mechanism in the next section does exactly that, and the equivalent by-hand SQL is in Steps 1 to 2 for audit or override situations.

Preconditions

Cloud commands run from the local sandbox. Source the sandbox environment and put the toolchain on the path:

cd ~/Repositories/GitHub/Adventive/adventive-platform-infra && . ~/Documents/Claude/.cowork-env && export PATH=~/.npm-global/bin:$PATH

wrangler, terraform, cloudflared, aws, mysql, and jq must be available. Admin access to each database is over the tunnel via a cloudflared access tcp listener; the RDS master credential and the environment’s Access service token come from the sandbox environment ($DB_ADMIN_USER, $DB_ADMIN_PW, $CF_ACCESS_CLIENT_ID, $CF_ACCESS_CLIENT_SECRET).

The mechanism

Steps 1 and 2 are packaged as a single idempotent script, scripts/provision-db-credentials.sh, which is the mechanism the team runs to add the users and passwords. For each (database, mode) it ensures the Secrets Manager secret exists (generating the password on first run, reusing it thereafter), then creates the MySQL user on the writer endpoint, sets its password to the Secrets Manager value, applies the schema-scoped grants, and verifies the read-only user cannot write. It runs dry-run by default and only changes state with --apply; non-dev environments are held behind the override gate described above.

cd ~/Repositories/GitHub/Adventive/adventive-platform-infra/scripts && . ~/Documents/Claude/.cowork-env && ./provision-db-credentials.sh dev

cd ~/Repositories/GitHub/Adventive/adventive-platform-infra/scripts && ./provision-db-credentials.sh dev --apply

The remainder of this section documents what the script does at each step, so the work can be audited or performed by hand, for example during an override on a protected environment. The scripts/README.md in the repo (also in the sidebar under this folder) carries the full contract.

Step 1

Generate one 32-character, punctuation-free password per user and hold them in shell variables for the rest of the session. Run this in a shell with history disabled (HISTCONTROL=ignorespace, leading space) so the values are not written to disk.

cd ~/Repositories/GitHub/Adventive/adventive-platform-infra && for U in hd_console_ro hd_console_rw hd_aggregate_ro hd_aggregate_rw hd_billing_ro hd_billing_rw; do export "PW_${U}=$(aws secretsmanager get-random-password --exclude-punctuation --require-each-included-type --password-length 32 --query RandomPassword --output text)"; done

Step 2

Open an authenticated TCP tunnel to the target database, then apply an idempotent user-and-grant script. The example below is the development instance, which hosts all three databases, so all six users are created against it. On the production Aurora clusters, run only the users for the databases that cluster holds, the production cluster holds console and billing, the aggregate cluster holds aggregate, connecting through that cluster’s writer hostname.

Open the listener:

cd ~/Repositories/GitHub/Adventive/adventive-platform-infra && cloudflared access tcp --hostname db-console-dev.adventive.dev --url 127.0.0.1:13306 --service-token-id "$CF_ACCESS_CLIENT_ID" --service-token-secret "$CF_ACCESS_CLIENT_SECRET" &

Apply the users and grants. The host pattern scopes each user to the tunnel’s private-subnet CIDR; substitute the real CIDR for the environment. DDL privileges are deliberately withheld.

cd ~/Repositories/GitHub/Adventive/adventive-platform-infra && MYSQL_PWD="$DB_ADMIN_PW" mysql -h 127.0.0.1 -P 13306 -u "$DB_ADMIN_USER" <<SQL
CREATE USER IF NOT EXISTS 'hd_console_ro'@'10.0.%' IDENTIFIED BY '${PW_console_ro}';
GRANT SELECT ON console.* TO 'hd_console_ro'@'10.0.%';
CREATE USER IF NOT EXISTS 'hd_console_rw'@'10.0.%' IDENTIFIED BY '${PW_console_rw}';
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON console.* TO 'hd_console_rw'@'10.0.%';
CREATE USER IF NOT EXISTS 'hd_aggregate_ro'@'10.0.%' IDENTIFIED BY '${PW_aggregate_ro}';
GRANT SELECT ON aggregate.* TO 'hd_aggregate_ro'@'10.0.%';
CREATE USER IF NOT EXISTS 'hd_aggregate_rw'@'10.0.%' IDENTIFIED BY '${PW_aggregate_rw}';
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON aggregate.* TO 'hd_aggregate_rw'@'10.0.%';
CREATE USER IF NOT EXISTS 'hd_billing_ro'@'10.0.%' IDENTIFIED BY '${PW_billing_ro}';
GRANT SELECT ON billing.* TO 'hd_billing_ro'@'10.0.%';
CREATE USER IF NOT EXISTS 'hd_billing_rw'@'10.0.%' IDENTIFIED BY '${PW_billing_rw}';
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON billing.* TO 'hd_billing_rw'@'10.0.%';
FLUSH PRIVILEGES;
SQL

Confirm the users exist before moving on, because a Hyperdrive config that names a non-existent MySQL user fails at creation with an AuthSwitchRequest error that reads like a Hyperdrive limitation rather than a missing user.

cd ~/Repositories/GitHub/Adventive/adventive-platform-infra && MYSQL_PWD="$DB_ADMIN_PW" mysql -h 127.0.0.1 -P 13306 -u "$DB_ADMIN_USER" -e "SELECT User, Host, plugin FROM mysql.user WHERE User LIKE 'console\_%' OR User LIKE 'aggregate\_%' OR User LIKE 'billing\_%';"

Close the listener when the users for the instance are created:

cd ~/Repositories/GitHub/Adventive/adventive-platform-infra && kill %1

Step 3

The module takes a non-sensitive structure describing each environment’s databases, modes, and endpoints, plus a sensitive password map keyed [env][db][mode] (kept separate because Terraform forbids sensitive values in for_each keys). Extend the maps to carry the mode dimension and, for production, the per-endpoint host.

The non-sensitive structure, for development, both modes share the single instance host; for production, rw carries the writer endpoint and ro the reader:

environments = {
  dev = {
    rds_sg_id = "sg-0dev…"
    databases = {
      console = {
        modes = {
          ro = { rds_host = "development.coi6rcntfbgg.us-east-1.rds.amazonaws.com", origin_host = "db-console-dev.adventive.dev" }
          rw = { rds_host = "development.coi6rcntfbgg.us-east-1.rds.amazonaws.com", origin_host = "db-console-dev.adventive.dev" }
        }
      }
      aggregate = {
        modes = {
          ro = { rds_host = "development.coi6rcntfbgg.us-east-1.rds.amazonaws.com", origin_host = "db-aggregate-dev.adventive.dev" }
          rw = { rds_host = "development.coi6rcntfbgg.us-east-1.rds.amazonaws.com", origin_host = "db-aggregate-dev.adventive.dev" }
        }
      }
      billing = {
        modes = {
          ro = { rds_host = "development.coi6rcntfbgg.us-east-1.rds.amazonaws.com", origin_host = "db-billing-dev.adventive.dev" }
          rw = { rds_host = "development.coi6rcntfbgg.us-east-1.rds.amazonaws.com", origin_host = "db-billing-dev.adventive.dev" }
        }
      }
    }
  }
}

In production, billing’s rds_host is the production cluster endpoint it shares with console, ro = production.cluster-ro-… and rw = production.cluster-…, while its origin_host stays db-billing-ro-prd / db-billing-rw-prd so the naming remains one hostname per database.

The sensitive password map, supplied from the Step 1 variables via a TF_VAR_database_passwords export or an untracked *.auto.tfvars file that is never committed:

database_passwords = {
  dev = {
    console   = { ro = "…", rw = "…" }
    aggregate = { ro = "…", rw = "…" }
    billing   = { ro = "…", rw = "…" }
  }
}

The module’s locals.tf flattens [env][db][mode] into tuples; secrets.tf, access.tf, and hyperdrive.tf iterate the flattened set. The derived names follow the standard exactly:

• Secret name adventive-db-${db}-${mode}-${env}, value { username = "${db}_${mode}", password, host = mode.rds_host, port = 3306, database = db }.
• Hyperdrive config name adv-svc-public-api-${db}-${mode}-${env}, origin { host = mode.origin_host, database = db, user = "${db}_${mode}", access_client_id, access_client_secret } with port omitted (the cloudflared ingress determines the port under Access service-token auth).

For production, the tunnel module must first publish the per-mode hostnames (db-<db>-ro-prd, db-<db>-rw-prd) with ingress rules pointing at the reader and writer endpoints, and the runtime instance must be refreshed to pick them up, before this module is applied.

Step 4

Apply from the sandbox. In development the tunnel hostnames already exist, so only the secrets and Hyperdrive configs are new:

cd ~/Repositories/GitHub/Adventive/adventive-platform-infra/infra/cloudflare-hyperdrive && terraform init && terraform plan -out=tfplan && terraform apply tfplan

The Hyperdrive create validates connectivity through the live cloudflared instance, so a clean apply is also proof that the user, password, tunnel ingress, and security-group path all work end to end. Capture the new Hyperdrive IDs from the module outputs for the next step.

For staging or production, the full sequence, tunnels, then cflared-asg, then an Auto Scaling instance refresh, then this module, applies exactly as in the Public API migration runbook, and only after the override and production gates above are cleared.

Step 5

Point the Worker’s bindings at the new configs, one per mode, in each [env.*] block of wrangler.toml:

[[env.dev.hyperdrive]]
binding = "DB_CONSOLE_RO"
id = "…console-ro-dev id…"

[[env.dev.hyperdrive]]
binding = "DB_CONSOLE_RW"
id = "…console-rw-dev id…"

[[env.dev.hyperdrive]]
binding = "DB_AGGREGATE_RO"
id = "…aggregate-ro-dev id…"

[[env.dev.hyperdrive]]
binding = "DB_AGGREGATE_RW"
id = "…aggregate-rw-dev id…"

[[env.dev.hyperdrive]]
binding = "DB_BILLING_RO"
id = "…billing-ro-dev id…"

[[env.dev.hyperdrive]]
binding = "DB_BILLING_RW"
id = "…billing-rw-dev id…"

Deploy from the sandbox:

cd ~/Repositories/GitHub/Adventive/adventive-public-api-worker && wrangler deploy --env dev

Once the Worker is verified on the new bindings, retire the superseded single-user Hyperdrive configs (adv-svc-public-api-console-dev, adv-svc-public-api-aggregate-dev, adv-svc-public-api-billing-dev) and drop the legacy console, aggregate, and billing MySQL users. Retiring before the Worker is confirmed on the new bindings would break the running service.

Verification

Confirm each binding resolves and carries only its intended privilege. A read-only binding must fail a write; a read-write binding must succeed. Run a smoke query through the deployed Worker (or a local wrangler dev session) for each binding, and poll rather than manually re-issuing, retry every one to two seconds, stop on the expected result, and time out at thirty seconds.

cd ~/Repositories/GitHub/Adventive/adventive-public-api-worker && for i in $(seq 1 20); do curl -fsS "https://<worker-dev-host>/__smoke/db-console-ro" && break || sleep 2; done

Confirm at the database that the read-only user cannot write:

cd ~/Repositories/GitHub/Adventive/adventive-platform-infra && MYSQL_PWD="$PW_console_ro" mysql -h 127.0.0.1 -P 13306 -u hd_console_ro -e "INSERT INTO console.__probe VALUES (1);" 2>&1 | grep -q "command denied" && echo "hd_console_ro correctly denied write"

Rotation

Rotating a credential updates the value in three places: the MySQL user, the Secrets Manager secret, and the Hyperdrive config. Generate a new password (Step 1 form), ALTER USER '<db>_<mode>'@'<cidr>' IDENTIFIED BY '<new>' on the database, update the Secrets Manager value and the database_passwords map, and terraform apply so the module pushes the new value into the Hyperdrive config’s baked-in copy. Rotate the read-write role first, verify, then the read-only role, so a single rotation never takes both roles offline at once.

---

See also: Connection & credential naming standard · Public API migration as-built runbook (projects/public-api-cf-migration/07-as-built-runbook.md, §10)