Record-level permissions when the app server bypasses Postgres RLS: one SQL source for read filters and write guards

A web app talks to Postgres with a superuser-like role (BYPASSRLS), so row-level security policies never apply to its queries. Adding multi-user permissions (view/edit/delete scoped to own records, team records, or all, plus sharing) by sprinkling checks into each server action is fragile: one forgotten action, export, count, or search leaks or modifies data.

Keep the bypassing connection, but make SQL functions the single source of truth: owner_rights(owner) resolves the effective rights (admin = all; assigned permission set overrides individual rights; then clamp by license/seat), owner_scope(owner, level) returns the visible owner ids (own / members of own teams plus nested child teams / all), can_view_record / can_edit_record take the record. (1) Reads: compile the scope into one extra WHERE condition (owner_id = any(:ids) or (owner_id is null and :unassigned) or shared-with-me JSON containment) and inject it in the single query builder used by lists, boards, counts, exports and search, so every listing path gets it at once; add a partial index (object_type, owner_id) where not archived. (2) Writes: check in the server action for a friendly message AND add a BEFORE UPDATE/DELETE/INSERT trigger that reads the acting user from set_config('app.actor_id') (already set by the write helper for audit history) and refuses when the actor lacks the right. Exempt system sources (automations, form submissions, merges, migrations) the way the product spec does. Test both layers with pgTAP using fixtures of four owners in two nested teams.

RLS cannot help when the role bypasses it, and switching the server to a restricted role means rewriting every policy and breaks SECURITY DEFINER helpers. A DB trigger keyed on the actor setting catches every write path, including ones added later; a compiled scope condition in the shared query builder catches every read path without per-page code.