Edouard Vanbelle
858139ef3b
this commit changes GET /api/<resources> to return resource caller has access to
this is not anymmore resources users is owner of
75 lines
3.6 KiB
PL/PgSQL
75 lines
3.6 KiB
PL/PgSQL
-- ════════════════════════════════════════════════════════════════════════════
|
||
-- PR-B — storage.caller_group_ids: recursive group-membership expansion in SQL
|
||
-- ════════════════════════════════════════════════════════════════════════════
|
||
-- Every listing surface that scopes by "drives the caller can Read" (Photos,
|
||
-- Places, GET /api/drives, Trash, Search, root-folder listing) needs the
|
||
-- caller's *effective subject set* = caller_id ∪ every group they belong to
|
||
-- transitively.
|
||
--
|
||
-- Pre-Option-A the Rust-side `PgAclEngine::expand_subject_for_listing` did
|
||
-- the walk once (via `WITH RECURSIVE` in `subject_group_pg_repository.rs::
|
||
-- groups_for_user`) and cached the result in a Moka table with 30-second
|
||
-- TTL; every listing handler then passed the two parallel arrays
|
||
-- `subject_types` + `subject_ids` into the SQL. That leaked the expansion
|
||
-- ceremony into every caller (7+ sites).
|
||
--
|
||
-- Option A pushes the walk into a `STABLE` SQL function so each listing
|
||
-- query embeds the expansion inline:
|
||
--
|
||
-- WHERE (g.subject_type = 'user' AND g.subject_id = $caller)
|
||
-- OR (g.subject_type = 'group' AND g.subject_id IN
|
||
-- (SELECT storage.caller_group_ids($caller)))
|
||
--
|
||
-- Callers pass a bare `caller_id: Uuid` — no more expand-then-bind
|
||
-- ceremony. Postgres re-runs the walk per listing (~1-3 ms against the
|
||
-- indexed `auth.subject_group_members` table); we lose the Moka cache
|
||
-- benefit but gain a single audit trail for "how does group access
|
||
-- cascade" (this function) and drop ~15 lines of Rust glue per listing.
|
||
--
|
||
-- Cycle safety: `subject_group_pg_repository.rs::add_member` enforces
|
||
-- an INSERT-time cycle check via `WITH RECURSIVE descendants`, so the
|
||
-- membership DAG is guaranteed acyclic. Depth is capped at
|
||
-- MAX_GROUP_DEPTH by the same INSERT path. The recursion below always
|
||
-- terminates.
|
||
--
|
||
-- `STABLE`: the function reads DB state but never modifies it, and the
|
||
-- result is deterministic within a transaction. Postgres can memoise
|
||
-- calls within a single query plan (e.g. multiple references in the
|
||
-- same SELECT) and inline the CTE into the surrounding query where
|
||
-- beneficial. Marking it `VOLATILE` would forbid both optimisations.
|
||
--
|
||
-- `LEAKPROOF` is deliberately NOT set: the function reads a private
|
||
-- auth table, so it must not be pushed below a security barrier.
|
||
--
|
||
-- `SECURITY INVOKER` (the default) — runs with the calling role's
|
||
-- permissions, so RLS on `auth.subject_group_members` (if ever added)
|
||
-- applies consistently.
|
||
|
||
CREATE OR REPLACE FUNCTION storage.caller_group_ids(caller UUID)
|
||
RETURNS SETOF UUID
|
||
LANGUAGE sql
|
||
STABLE
|
||
AS $$
|
||
WITH RECURSIVE user_groups AS (
|
||
-- Direct memberships: groups the caller is listed in as a user.
|
||
SELECT group_id
|
||
FROM auth.subject_group_members
|
||
WHERE member_user_id = caller
|
||
|
||
UNION
|
||
|
||
-- Transitive memberships: groups that contain a group the caller
|
||
-- already belongs to. Repeats until no new rows are produced.
|
||
SELECT m.group_id
|
||
FROM auth.subject_group_members m
|
||
JOIN user_groups ug ON m.member_group_id = ug.group_id
|
||
)
|
||
SELECT group_id FROM user_groups;
|
||
$$;
|
||
|
||
-- Backing indexes used by the recursion. Already present from
|
||
-- 20260307000000_initial_schema.sql on
|
||
-- `auth.subject_group_members (member_user_id)` and
|
||
-- `auth.subject_group_members (member_group_id)` — no additional
|
||
-- indexes needed here.
|