Skip to content
CommonLayer
Documentation menu
On this page

ADR-0036: The processes connect as a least-privilege runtime database role

Status: Accepted (WP-70a; threat model O1/O2, in part). Implemented in crates/commonlayer-database/src/roles.rs, migrations/0014_runtime_role.sql, apps/cli/src/ops.rs (clctl migrate, clctl db check-role) and crates/commonlayer-app/src/db_role.rs; applied in Compose and the Helm chart (WP-70g).

Context

The ledger and audit tables are protected by triggers (append-only, balance guard). A role that owns the tables, or a superuser, can disable those triggers. Until WP-70a the API, worker and scheduler connected with the same role that ran the migrations, so a compromised process, or anyone holding its connection string, could switch the protections off.

Decision

  • Two roles. An owner role owns the database and runs migrations (database.migration_url); it is used only by clctl migrate. A runtime role (database.url, default name commonlayer_app) owns nothing, is not a member of the owner and has no attributes.
  • clctl migrate applies the migrations as the owner and then grants the runtime role exactly what it needs, derived from the catalogue: DML on ordinary tables; INSERT only on append-only tables; domain_events INSERT and UPDATE (dispatched_at); no DELETE on artifact_digests; ledger_balances and artifact_tag_history written only by SECURITY DEFINER trigger functions with a pinned search_path; never TRUNCATE, TRIGGER, REFERENCES, CREATE or ownership. New tables are covered without code changes. Re-running converges the privileges.
  • clctl db check-role fails unless the runtime role is least privilege (it tries the forbidden operations inside a rolled-back transaction). Deployments run it after clctl migrate and fail the rollout on a non-zero exit.
  • A process that connects with a privileged role starts anyway, logs a warning outside development, sets commonlayer_db_privileged_connection to 1 and reports database_role as degraded in /readyz; the alert DbPrivilegedConnection fires. Refusing to start was rejected so that existing deployments keep running until they are migrated.

Consequences

  • The application's own credentials can no longer disable the ledger or audit protections. Tests: crates/commonlayer-database/tests/it/roles.rs; the acceptance workflows and the seed run as the runtime role.
  • Verified locally: the Compose stack (stack-db-init, migrate), the restore drill (migrate as owner, check-role, ledger and audit verification as runtime) and scripts/dev/run-backend.sh. The Helm chart renders the split (unit tests); a kind install with the roles has not gone green yet, and the CloudNativePG wiring (bootstrap.initdb.owner, managed.roles) is documented but not exercised.
  • Still open: a superuser or whoever holds the owner credentials can disable the triggers; there is no hash chain over the ledger and audit checkpoints are not exported outside the database (threat model O1, O2).