[Security/Product Gap] Replace runtime secret transport with an auditable credential-provider contract

[seonghobae]

Buyer-visible problem

Protected main@8dc746920c12988f082e914879d95e13c9693535 still constructs Settings directly from environment variables and .env. APP_SECRET_FILE is already a useful fail-closed Docker/Podman secret seam, but database credentials, OIDC settings, LLM provider credentials, Clearfolio HMAC material, metrics tokens, and Valkey credentials remain runtime configuration values without one auditable lifecycle or rotation contract.

For a commercial ERD product that stores encrypted target DSNs and may connect to customer databases, deployment-time environment transport alone is insufficient evidence for least privilege, rotation, revocation, access attribution, or key migration. PII masking is not an acceptable substitute: authorized users must retain usable schema metadata while secret access is controlled and audited.

Product contract

Introduce a provider-neutral CredentialProvider / SecretReference boundary that preserves standalone Compose operation while allowing Keyverse or the organization credential registry to become the production authority.

Required behavior:

1. Treat environment variables and mounted files as bootstrap transport only; runtime product modules consume typed secret handles or resolved immutable secret values through one provider interface.
2. Keep plaintext secrets out of ORM rows, logs, exceptions, traces, metrics, task payloads, PR artifacts, repr/str, and frontend responses.
3. Support at least:
   • local mounted-secret provider for standalone/self-hosted deployments;
   • organization credential-registry provider;
   • deterministic test provider.
4. Record non-secret metadata: credential purpose, provider, version, created/rotated/revoked timestamps, intended consumer, and retrieval audit reference.
5. Support dual-read/single-write key rotation for APP_SECRET so existing encrypted DSNs can be re-encrypted without an outage or silent data loss.
6. Fail closed on missing, revoked, expired, ambiguous, wrong-purpose, symlinked, oversized, or malformed secret material.
7. Use short-lived workload identity or dynamic credentials where supported; do not copy provider secrets into every service.
8. Preserve local development and Compose through an explicit local_secret_file deployment profile rather than hidden fallback behavior.
9. Replace direct LLM provider credentials with the contextual-orchestrator contract; pg-erd-cloud should not become a general-purpose provider-key vault.
10. Document recovery when the active encryption key is unavailable and prove that recovery cannot expose DSN plaintext.

Required tests

• bootstrap file under /run/secrets succeeds; symlink/path escape/empty/oversized file fails;
• registry timeout, permission denial, revoked version, and stale cache fail closed;
• secret values never appear in logs, exception strings, traces, metrics, audit payloads, or serialized settings;
• dual-key DSN decryption and bounded re-encryption migration;
• rotation during concurrent snapshot jobs does not corrupt stored connections;
• provider cache invalidation and revocation;
• standalone profile works without Keyverse or network access;
• production statement and branch coverage 100%; public API docstring coverage 100%.

Acceptance evidence

• ADR defining credential authority, bootstrap transport, rotation, recovery, and standalone/module boundaries;
• typed provider contract and adapters;
• migration/runbook for existing encrypted DSNs;
• exact-current-head backend tests, migration rehearsal, security checks, and independent review;
• docs/product-technical-gap-baseline.md, security doctoring, architecture, and CHANGELOG.md updated;
• no claim that environment variables are universally forbidden: the explicit claim is that they are no longer the unaudited runtime authority.

References — APA 7th

Barker, E. (2020). Recommendation for key management: Part 1—General (NIST Special Publication 800-57 Part 1 Revision 5). National Institute of Standards and Technology. https://doi.org/10.6028/NIST.SP.800-57pt1r5

Open Worldwide Application Security Project. (n.d.). Secrets management cheat sheet. OWASP Cheat Sheet Series. https://cheatsheetseries.owasp.org/cheatsheets/Secrets_Management_Cheat_Sheet.html

National Institute of Standards and Technology. (2022). Secure software development framework (SSDF) version 1.1 (NIST Special Publication 800-218). https://doi.org/10.6028/NIST.SP.800-218