ADR-0099 — raptor_app Least-Privilege Postgres Role (Option A)

Status: Accepted
Date: 2026-05-19 UTC
Deciders: Kristerpher (operator), software-architect
Refs: #1455, ADR-0058 §Phase 0, ADR-0022, ADR-0093, customer-audit-unified/design.md §11, feedback_heroku_pg_rds_password_gotcha.md, feedback_heroku_config_set_echoes_secrets.md

Context

The unified customer audit architecture (ADR-0058, design.md) issues REVOKE UPDATE, DELETE ON customer_audit_events FROM raptor_app to enforce append-only access at the DDL level. This REVOKE is meaningful only when the application connects as the raptor_app role.

Heroku Postgres exports DATABASE_URL as the provisioned owner credential, which has superuser-equivalent DML access. When Raptor connects using DATABASE_URL, it connects as the owner and the REVOKE statement has no runtime effect — an attacker who obtains DATABASE_URL can execute DELETE FROM customer_audit_events regardless of the schema-level REVOKE.

This is threat T-PEN-2 (composite score 6) in the unified audit threat model. It was flagged by the security scan on 2026-05-09 and treated as a pre-launch-blocker on issue #1455.

Three remediation options were presented to the operator on 2026-05-19:

The operator locked Option A on 2026-05-19.

Decision

Provision a raptor_app restricted Postgres role using heroku pg:credentials:create. Raptor's application runtime connects exclusively using RAPTOR_APP_DATABASE_URL, sourced from Infisical. DATABASE_URL is the owner credential, used only by Alembic at migration time.

D1 — Role provisioning mechanism: heroku pg:credentials:create

CREATE ROLE ... WITH LOGIN PASSWORD is rejected on Heroku Postgres (RDS-backed). RDS reserves the rds_password role for password operations; the DATABASE_URL owner is not a member. Any attempt to use CREATE ROLE WITH PASSWORD produces a permission error. The only supported path for creating named credentials on Heroku Postgres Standard-0+ is heroku pg:credentials:create. This is a hard constraint, not a preference.

D2 — Connection string storage: Infisical (not Heroku config vars)

The raptor_app connection string (returned by heroku pg:credentials:url --name raptor_app) contains a generated password. Storing it as a Heroku config var risks stdout leakage (see feedback_heroku_config_set_echoes_secrets.md; the leaked SECRET_KEY on 2026-05-01 followed this exact pattern). Infisical is the canonical secret store for all Raptor secrets per feedback_secrets_in_vault_sop.md.

Infisical path: /MooseQuest/raxx/RAPTOR_APP_DATABASE_URL (both /prod and /staging environments).

The Heroku config var RAPTOR_APP_DATABASE_URL on raxx-api-prod and raxx-api-staging is set as a reference to satisfy Heroku dyno env-var visibility, with stdout silenced: heroku config:set RAPTOR_APP_DATABASE_URL=... -a raxx-api-prod >/dev/null 2>&1. The canonical copy lives in Infisical; the Heroku config var is a bootstrap convenience for the dyno startup environment, not the authoritative copy. Rotation targets Infisical first, then re-syncs to the Heroku config var.

D3 — Connection lifecycle: two distinct roles, two distinct paths

DATABASE_URL is never read by the Flask application process. The application engine is initialised from RAPTOR_APP_DATABASE_URL. If RAPTOR_APP_DATABASE_URL is absent (local dev without Postgres, CI unit tests), the fallback is DATABASE_URL if it resolves to an sqlite:// URL, otherwise the process fails fast with a configuration error.

The Alembic env.py reads DATABASE_URL directly. Alembic is never configured to use RAPTOR_APP_DATABASE_URL. This prevents accidental DDL execution as the restricted role.

D4 — Fail-fast on misconfiguration, not silent fallback to DATABASE_URL in Postgres mode

If RAPTOR_APP_DATABASE_URL is unset or invalid and the deployment target is Postgres, the application must fail at startup with a ConfigurationError, not fall back to DATABASE_URL. Silent fallback would re-introduce the owner-credential risk and make the role separation invisible.

Concretely:

# Pseudocode — feature-developer implements the real engine init
def _get_runtime_db_url() -> str:
    url = os.environ.get("RAPTOR_APP_DATABASE_URL") or os.environ.get("DATABASE_URL", "")
    if url.startswith("postgresql") and not os.environ.get("RAPTOR_APP_DATABASE_URL"):
        raise ConfigurationError(
            "RAPTOR_APP_DATABASE_URL is required when DATABASE_URL is postgresql://. "
            "Set it in Infisical at /MooseQuest/raxx/RAPTOR_APP_DATABASE_URL."
        )
    return url

The or DATABASE_URL fallback is intentionally kept for the SQLite local-dev / CI unit-test case, where DATABASE_URL resolves to sqlite:///./dev.db.

D5 — Test and CI story

The CI workflow file adds:

env:
  DATABASE_URL: postgresql://postgres:postgres@localhost/raptor_test
  RAPTOR_APP_DATABASE_URL: postgresql://postgres:postgres@localhost/raptor_test

This keeps the integration test matrix simple without requiring a dedicated raptor_app Postgres user in the CI ephemeral database.

D6 — Rollback path

If RAPTOR_APP_DATABASE_URL is wrong or missing after a deploy:

1. Heroku release dyno (alembic upgrade head) runs as owner. Schema state is correct even if the subsequent web dyno fails.
2. Web dyno fails at startup (ConfigurationError). The dyno crash-loops. Heroku routes zero traffic to the crashed dyno until restart succeeds.
3. Recovery: operator sets correct RAPTOR_APP_DATABASE_URL via heroku config:set ... >/dev/null 2>&1 or fixes Infisical and re-triggers the release. No data is corrupted. No rollback of schema changes is required.

There is no partial-serving state: the dyno either initialises successfully with the correct restricted role, or it does not start.

D7 — REVOKE scope

Phase 0 GRANTs/REVOKEs (applied after schema deployment, using DATABASE_URL / owner):

-- runtime role gets restricted access
GRANT CONNECT ON DATABASE <db> TO raptor_app;
GRANT USAGE ON SCHEMA public TO raptor_app;
GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO raptor_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT ON TABLES TO raptor_app;

-- append-only enforcement on audit table
REVOKE UPDATE, DELETE ON customer_audit_events FROM raptor_app;

-- audit archiver: retention deletes only
GRANT SELECT, DELETE ON customer_audit_events TO audit_archiver;
REVOKE UPDATE ON customer_audit_events FROM audit_archiver;

-- migrations role: DDL but no INSERT on audit tables (prevent forged historical rows)
REVOKE INSERT ON customer_audit_events FROM raptor_migrations;

-- same pattern for all shadow tables
REVOKE UPDATE, DELETE ON users_history FROM raptor_app;
REVOKE UPDATE, DELETE ON subscriptions_history FROM raptor_app;
REVOKE UPDATE, DELETE ON positions_history FROM raptor_app;
REVOKE UPDATE, DELETE ON trades_history FROM raptor_app;
REVOKE UPDATE, DELETE ON sessions_history FROM raptor_app;
REVOKE UPDATE, DELETE ON passkeys_history FROM raptor_app;

These are run as a standalone Alembic migration (not inside the schema migration chain that creates the tables) so they can be applied or re-applied independently if the role is re-provisioned.

Consequences

Positive: - T-PEN-2 (owner-role bypass) is closed at runtime. DELETE FROM customer_audit_events as the application role produces ERROR: permission denied for table customer_audit_events. - Every REVOKE in design.md §3 and §11 now has actual teeth. - DATABASE_URL rotation (via heroku pg:credentials:rotate) does not require application restart. The application uses RAPTOR_APP_DATABASE_URL. - Role rotation via heroku pg:credentials:rotate --name raptor_app also does not require a schema migration or DDL change — only the Infisical secret and Heroku config var are updated. - raptor_app credential can be rotated on the standard Velvet cadence; Velvet's rotation manifest includes RAPTOR_APP_DATABASE_URL as a managed secret.

Negative / accepted debt: - Two connection strings must be managed and kept in sync across staging and prod. - Local dev requires documentation to prevent developer confusion about why DATABASE_URL alone works locally but not in Postgres-mode deployments. - CI integration test matrix uses the same credential for both roles, which means REVOKE enforcement is not tested in CI. This is accepted — the REVOKE verification test (SC-4) runs against a dedicated staging database with the real role split in place.

Risks accepted: - Infisical unavailability at dyno startup would block application launch. Mitigated by: Infisical SDK caches the last-known secret on the dyno filesystem (Heroku ephemeral disk); the Heroku config var RAPTOR_APP_DATABASE_URL also serves as a bootstrap fallback if the Infisical SDK returns an error, consistent with the layered secret-fetch pattern used by other Raptor services.

Alternatives Considered

Option B — Application-layer write guards

Rejected. Middleware that blocks UPDATE/DELETE on specific tables is a software control, not a DDL control. It is bypassable by any code path that does not go through the middleware (direct DB connections in tests, admin scripts, future services). It provides no protection against an attacker with direct database access. The audit invariant (ADR-0022) requires DDL-level enforcement.

Option C — Defer to post-v1

Rejected. customer_audit_events is a pre-launch-blocker table. Without Phase 0, the append-only invariant (I-2 in design.md) is aspirational. All Phase 1 schema creation work and the KMS hash chain (approved at ~$2/month) provide no tamper resistance if the application role can delete audit rows. T-4 days is a tight but achievable window given that the raptor_app credential was already provisioned on prod on 2026-05-15 (see issue #1455 comment).

Runbook

Operational procedures for credential rotation, incident response, and local development are in:

docs/ops/runbooks/raptor-db-credentials.md

That document covers: - The two-URL model (RAPTOR_APP_DATABASE_URL vs DATABASE_URL) in operational terms - Rotation procedure for RAPTOR_APP_DATABASE_URL via heroku pg:credentials:rotate --name raptor_app - Rotation procedure for DATABASE_URL (owner) via heroku pg:credentials:rotate - Incident response for crash-loop, role missing, permission denied, and config override scenarios - Velvet rotation manifest status - Fast rollback via FLAG_RAPTOR_APP_ROLE_SEPARATION=0