RV-S3: cluster-wide te_app role causes cross-environment credential invalidation #173

What happened

External repository review at commit c7bebd1 found that the runtime database role is cluster-wide, which breaks when the role-creation script is run for a second environment on the same PostgreSQL cluster. Static reading of scripts/create_app_role.py L31–67 and api/db.py L209–241; not reproduced live.

Reconstructed scenario:

• First run: creates te_app (cluster-wide), writes password to ~/.te-migration/app-db.env.
• Second run (same cluster, different env): rotates te_app password (same role), overwrites credentials file.
• The first environment's connections silently break on the next reconnect.

Password rotation is documented. The cross-environment invalidation consequence isn't.

Why this matters

Dev PCs commonly share one PostgreSQL cluster across environments. The product owner's dev setup runs PG 17 on 5432 and PG 18 on 5433; standing up Dev and Test on either shared cluster trips this.

Production is less affected (each env typically gets its own Azure PG), but the setup wizard runs on dev PCs where this is exactly the shape it fails on.

The te_app runtime role was introduced in M3d. There may be in-flight work assuming the current single-role model.

Decision taken 3 Nov 2026

Option (a) of 3 considered: per-environment role names and per-environment credential files.

• te_app_dev, te_app_test, te_app_staging, te_app_prod as role names.
• ~/.te-migration/app-db-dev.env, app-db-test.env, etc. as credential file names.
• Role-creation script takes a mandatory --env arg; refuses without it.

Option (b) rejected: per-database role via PostgreSQL's CREATE ROLE … IN DATABASE …. More idiomatic PostgreSQL but overkill for the scope and harder to reason about.

Option (c) rejected: documenting the limitation and warning users is a workaround that leaves the hazard in place. Correctness over simplicity.

Proposed

1. scripts/create_app_role.py — add mandatory --env arg; derive role name as te_app_<env>; write credential file as ~/.te-migration/app-db-<env>.env.
2. api/db.py — read role name and credential file based on current environment, not a hardcoded te_app.
3. Any scripts, docs or configs that currently reference ~/.te-migration/app-db.env as a bare name need updating. The PR should enumerate these in its description.
4. Migration path for existing dev PCs: if ~/.te-migration/app-db.env exists (from pre-fix usage), the new script offers to migrate it to app-db-dev.env on first run with --env dev.

Done when

• Reproduction test added: shell test that provisions Dev, then Test, then verifies Dev's connection still works byte-identically.
• The test fails on main at c7bebd1 without the fix.
• The test passes with the fix.
• All existing references to ~/.te-migration/app-db.env enumerated in the PR description and updated in the change.
• CI replica run quoted on the PR (counts in passed / failed / skipped form).
• Migration path exercised in a test (existing single-role dev PC upgraded cleanly).

Reference

Tracker row G36 (main tab). Repo review finding RV-S3. Related to M3d (which introduced te_app). Related: G34 (RV-S1 production DB config), G37 (RV-S4 image secrets) — all Standards P1 touching deployment and credential safety.