Package tests run every query as the cluster's superuser, so a privilege the runtime role lacks shows only in the running console #259

New Issue

2026-10-24T06:51:53Z

cgalo5758 commented

2026-10-24 06:51:53 +00:00

What a contributor runs into

internal/testkit/dbtest opens TEST_DATABASE_URL, and on the test stack that address logs in as member_console, the user the Postgres image creates as the cluster's superuser (test/compose.yaml, test/bootstrap-stack.sh). A superuser passes every privilege check, so a query or workflow test proves nothing about whether console_runtime, the group role the running console's login holds, may run the query.

A query that needs a privilege console_runtime does not hold therefore passes make test and fails with SQLSTATE 42501 only in a console that serves as its runtime user. Such a query can be a locking read (FOR UPDATE) of a table the runtime role may only read, a write to such a table, or a call to a sequence or function a migration did not grant.

docs/testing.md ("Privileges.") tells a test author to drop to console_runtime with SET LOCAL ROLE inside the test's transaction, and a few tests do: TestLockPlanActObligationRunsAsTheRuntimeRole in internal/entitlements/plan_act_queries_db_test.go, and the tests that use asRole in internal/db. Nothing makes it the default. About 200 test files open the test database through dbtest, and 9 of them switch role; every other test runs its queries as the superuser.

#224 is about which database the test kit opens; this is about which role its queries run as.

Why it costs

A missing privilege is found by reading the queries, by a walkthrough or screen capture that happens to reach the path, or by a deployment. LockPlanActObligation (internal/entitlements/queries/plan_acts.sql) is the worked example. It locks one row of the work a plan change owes a resource pool, and console_runtime holds no UPDATE on those rows, so a bare FOR UPDATE of the row fails as the runtime role and passes as the superuser. The query now takes the lock through the function core.lock_plan_act_obligation, and only the one test that switches role pins that. TestRuntimeRolePrivileges (internal/db/runtime_role_privileges_test.go) reads the catalog and checks the role's privileges table by table, which catches a forgotten table grant but not a query that needs a privilege the table's intended grants withhold.

Where

internal/testkit/dbtest/open.go

Done when

The query and workflow tests of every package run their queries as console_runtime by default, for example because the handles dbtest returns have already switched role.
A fixture that needs the superuser (seeding a locked table, creating a role) steps out of the runtime role explicitly, where a reader of the test can see it.
A query that needs a privilege console_runtime lacks fails make test.

### What a contributor runs into `internal/testkit/dbtest` opens `TEST_DATABASE_URL`, and on the test stack that address logs in as `member_console`, the user the Postgres image creates as the cluster's superuser (`test/compose.yaml`, `test/bootstrap-stack.sh`). A superuser passes every privilege check, so a query or workflow test proves nothing about whether `console_runtime`, the group role the running console's login holds, may run the query. A query that needs a privilege `console_runtime` does not hold therefore passes `make test` and fails with SQLSTATE 42501 only in a console that serves as its runtime user. Such a query can be a locking read (`FOR UPDATE`) of a table the runtime role may only read, a write to such a table, or a call to a sequence or function a migration did not grant. `docs/testing.md` ("Privileges.") tells a test author to drop to `console_runtime` with `SET LOCAL ROLE` inside the test's transaction, and a few tests do: `TestLockPlanActObligationRunsAsTheRuntimeRole` in `internal/entitlements/plan_act_queries_db_test.go`, and the tests that use `asRole` in `internal/db`. Nothing makes it the default. About 200 test files open the test database through `dbtest`, and 9 of them switch role; every other test runs its queries as the superuser. #224 is about which database the test kit opens; this is about which role its queries run as. ### Why it costs A missing privilege is found by reading the queries, by a walkthrough or screen capture that happens to reach the path, or by a deployment. `LockPlanActObligation` (`internal/entitlements/queries/plan_acts.sql`) is the worked example. It locks one row of the work a plan change owes a resource pool, and `console_runtime` holds no UPDATE on those rows, so a bare `FOR UPDATE` of the row fails as the runtime role and passes as the superuser. The query now takes the lock through the function `core.lock_plan_act_obligation`, and only the one test that switches role pins that. `TestRuntimeRolePrivileges` (`internal/db/runtime_role_privileges_test.go`) reads the catalog and checks the role's privileges table by table, which catches a forgotten table grant but not a query that needs a privilege the table's intended grants withhold. ### Where `internal/testkit/dbtest/open.go` ### Done when - The query and workflow tests of every package run their queries as `console_runtime` by default, for example because the handles `dbtest` returns have already switched role. - A fixture that needs the superuser (seeding a locked table, creating a role) steps out of the runtime role explicitly, where a reader of the test can see it. - A query that needs a privilege `console_runtime` lacks fails `make test`.

cgalo5758 added the

kind

debt

area/testing labels 2026-10-24 06:51:53 +00:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: wiki-cafe/member-console#259