Bind the request principal to the session and re-emit it per transaction

[bencap]

Bind the request principal to the database session so policies can read it.

Why a one-shot bind cannot work

Both auth paths query the same session get_db yielded, autobeginning the transaction before a principal exists:

• get_current_user_data_from_api_key runs db.query(AccessKey) at authentication.py:131.
• get_current_user runs db.query(User) at authentication.py:187.

Setting the GUCs in the auth dependency is also insufficient. SET LOCAL is transaction-scoped, and MaveDB commits mid-request on every write path — 34 db.commit() calls in routers/. score_sets.py:2374 commits, then db.refresh(item), then model_validate(item), which lazy-loads relationships. All of that runs in a fresh transaction with no principal set.

The failure is not a quiet degrade to public. After COMMIT, current_setting('app.user_id', true) returns '' — not NULL, because SET LOCAL reverts to the session-level value and a custom GUC that has been set once carries '' forever on that pooled connection. ''::integer raises invalid input syntax for type integer: "", and RESET does not restore the unset state. Verified on PG 15.17. Without both halves below, create_score_set and publish_score_set return 500.

Both halves are required:

1. The principal is stored on the Session (session.info) by the auth dependency, once.
2. A SessionEvents.after_begin listener re-emits set_config for every transaction on that session, including ones opened after a mid-request commit.

bind_principal additionally applies retroactively when auth has already opened the transaction, which is what makes it safe for authentication to keep running on the request session, and retires the JWT/API-key transaction asymmetry as a correctness concern.

Scope

Session scope is told, never inferred

class SessionScope(enum.Enum):
    REQUEST = "request"   # RLS predicates apply
    SYSTEM  = "system"    # worker, scripts, migrations

Set at construction — deps.get_db for the former, worker and scripts for the latter. The before_cursor_execute guard reads that, not starlette_context and not the HTTP method. Ambient inference fails open, so it is not an option here.

SessionScope.SYSTEM declared on the mavedb_api credential still sees only public rows. The declaration confers nothing — privilege comes from the credential, and the api's has none. There is no escape hatch in the api process to misuse.

The GUC set

app.user_id       integer    -- '' for anonymous
app.active_roles  text[]     -- '{}' for anonymous

active_roles rather than app.is_admin: a boolean drops UserRole.mapper, which reads private score sets, experiments, and experiment sets.

Emit with set_config('app.user_id', :uid, true), never an interpolated SET LOCAL — active_roles derives from the client-supplied X-Active-Roles header.

Policies read GUCs with missing_ok and nullif

nullif(current_setting('app.user_id', true), '')::integer. missing_ok alone leaves the '' failure above. With nullif, a connection carrying a stale '' degrades to public-only. Enforced by catalog test C9 in #824.

Auth tables

users stays carved out. access_keys does not — it carries a real policy, with the single pre-principal lookup served by app_resolve_access_key() (see #827).

Acceptance criteria

• The principal is stored on the session and re-emitted by after_begin for every transaction.
• Test: a caller reads its own private entity, db.commit(), then reads it again in the same request.
• Test: an authenticated API-key caller sees its own private entities, and so does a JWT caller. The paths differ in transaction handling, so one does not cover the other.
• Test: two sequential requests on one pooled connection do not leak a principal. Plain SET is verified to leak; this must use set_config(..., true).
• Test: a request-scoped query with an unresolved principal raises, and the guard reads session.info, not request state.
• Test: a SessionScope.SYSTEM session opened on the api credential sees public rows only.
• Test: a user holding only UserRole.mapper reads a private score set.
• Test: JWT login and API-key auth both succeed with all policies live.
• conftest_optional.py:284's get_current_user override moves down to the token/key resolution seam, so router tests exercise bind_principal rather than bypassing it. Without this, every test in the canary and differential matrix validates the override. See Test and local fixture parity with the production role topology #834.