~/revparpro at commit bddf3fb (281 migrations, 39 edge functions, src/) plus vault spec specs/internal-launch/page-specs/22-user-access-controls.mdMigration 258 enabled RLS on 12 exposed tables, flipped 7 leaking SECURITY DEFINER views to security_invoker, revoked anon EXECUTE and all anon writes. Anon now gets 42501 on every locked surface. 258_anon_lockdown.sql, commit 8ded083
Migration 259 gated P&L and deal tables behind finance/deal roles. But balance sheet, deal P&L periods, and roughly 30 operational tables are still readable by any logged-in user. 259_finance_deal_role_scoping.sql, commit bdbff42
12 of 39 edge functions run with the service-role key (RLS bypassed) and check no caller identity at all: anyone holding the public anon key can call them. supabase/functions/ inventory, Section 2
The design of record is the April 2026 page spec (vault, spec 22). The live system follows its shape: Supabase Auth for identity, a custom access token hook that stamps role and property claims into the JWT, Postgres RLS as the enforcer, and cosmetic role filtering in the React nav. The browser talks straight to PostgREST with the anon key, so RLS is the only layer that actually enforces anything for normal app reads.
| Layer | Mechanism | Where | Enforcing? |
|---|---|---|---|
| Identity | Supabase Auth, email + password only (no OAuth/magic link in-app) | src/lib/auth.tsx:165 | YES |
| Claims | custom_access_token_hook injects app_role, tenant_id, assigned_properties (capped at 20, overflow sets is_super_portfolio), blocks inactive users from minting tokens | 002_auth_permissions.sql:48-99 | YES |
| Row filtering | RLS policies via helpers: app_role(), tenant_id(), can_access_hotel(), is_finance_role(), is_deal_role() | 002 / 259 migrations | YES |
| Nav scoping | ROLE_VISIBLE_GROUPS hides sidebar groups per role | src/lib/nav-config.ts:124-137 | COSMETIC |
| Route guards | None: every /app/* route mounts for any authenticated user; only the unrouted Admin page self-guards | src/App.tsx:72-107 | ABSENT |
Role model. 12 roles in the frontend union (owner, managing_partner, revenue_manager, director_of_sales, general_manager, chief_engineer, front_desk, front_office_supervisor, comptroller, bookkeeper, analyst, staff; source src/lib/nav-config.ts:20-32), plus an admin role used by RLS and isAdmin checks that is missing from that union. Roles are global (company-wide); property scoping rides separately on assigned_properties. profiles.role is free text in the DB, no enum or CHECK constraint (002_auth_permissions.sql:32-33).
Signup and approval (shipped 2026-07-16, commit 5e54941). Two paths: admin invite (via the invite-user edge function, role-gated to owner/managing_partner/admin) and per-property QR self-signup at /join/:code. Self-signups land inactive with the requested role clamped server-side to a frontline whitelist; approval RPCs let admins approve anyone and let a GM approve only for their own properties, with GM-mintable roles clamped so a GM cannot create elevated accounts. This flow is the best-guarded part of the system: enforcement lives in SECURITY DEFINER RPCs, not the UI. 260_staff_onboarding.sql:18-216
The 2026-07-16 audit (commits 8ded083, bdbff42) fixed the worst of it: pre-login leakage is closed and company P&L / acquisition deals are role-gated with verified zero rows for front_desk and staff. What follows is what is still open, ranked by exposure once frontline staff start self-signing up via QR.
| Rank | Finding | Detail | Evidence |
|---|---|---|---|
| P0 | 12 edge functions callable with zero caller auth, running service-role | The gateway's verify_jwt=true is satisfied by the public anon key, so these are effectively open to the internet, with RLS bypassed. The set: dfs-webhook (unauthenticated DB write, no callback secret), qbo-dump-coa-raw (dumps a property's full QBO chart of accounts), qbo-coa-migrate-mhd (writes COA mappings), deal-compset (acquisition-deal analytics), apollo-enrich and prospect-discovery (trigger paid API calls: billing DoS), str-parser (verify_jwt=false and no check, writes STR rows), dual-brand-refresh, alert-narrative,
| |
| P0 | Balance sheet and deal-period financials missed by migration 259 | bs_imports / bs_snapshots (full balance sheet), deal_pnl_periods / deal_str_periods (underwriting financials), and qbo_sync_log still carry tenant-only policies. Since every user is tenant 'default', any authenticated front-desk clerk can read them. Same sensitivity class as the tables 259 just locked. |
090_bs_financial_tables.sql:71,124; 140_deal_pipeline_intel.sql:58,94; 043:74 |
| P1 | ~30 operational tables readable by all staff (known, deferred) | Night audit entries, booking pace, all budget_* tables, sales plans, STR daily/monthly, labor timecards/shifts. All carry property IDs but no can_access_hotel gate; 259's header explicitly defers these pending per-role visibility decisions. This is the declared phase 2. |
259_finance_deal_role_scoping.sql:16-18; 013:85; 218:45; 243:78-80 |
| P1 | Financial edge functions gated by login only, no role check | qbo-sync-pl/-bs/-coa/-pl-summary, close-narrative, qbo-update-class, cc-import verify a JWT exists but not the role. Any logged-in user can drive P&L/BS syncs and month-close narratives. Contrast qbo-invoice / qbo-journal-entry, which do it right. Same theme on RPCs: refresh_pl_summary / refresh_bva are executable by any authenticated user. |
qbo-sync-pl/index.ts:595; close-narrative/index.ts:123; vs qbo-invoice/index.ts:172; 258:151-162 |
| P1 | Property scoping barely exists at the data layer | can_access_hotel() is applied to only 3 tables (prospects, account_production, account_guests). Everywhere else, per-property filtering happens in React (night-audit dropdown, compset list) and is bypassable by any user with devtools. A single-property GM can read every property's numbers. |
002:170-205; 073:85; 223:48; 227:35; NightAuditTab.tsx:441-445 |
| P2 | No route-level guards in the SPA | Sidebar hiding is the only page gating: a front_desk user can browse directly to /app/budget, /app/deals, /app/tax, /app/hotel-intel. Data exposure depends entirely on each table's RLS, but pages should fail closed too (defense in depth, and avoids blank-page confusion). | src/App.tsx:72-107; nav-config.ts:124-137 |
| P2 | Role vocabulary drift, no single source of truth | profiles.role is unconstrained text; admin sets are hardcoded string arrays repeated across policies, edge functions, and the frontend; admin exists in RLS and auth.tsx but not in the UserRole type (an admin user would get an empty nav); the informational AccessMatrix component can drift from real RLS. |
002:32-33; auth.tsx:179; nav-config.ts:20-32; AccessMatrix.tsx:27-149 |
| P2 | JWT staleness on revocation | Role and property changes only bite on the next token refresh; a disabled user keeps access until token expiry. Acknowledged in the migration comments. Standard claims tradeoff, worth a forced-refresh or short TTL before external customers. | 002_auth_permissions.sql:168 |
Surveyed 2026-07-17 (sources at the foot of the page). The honest framing: because RPP's browser queries Postgres directly through PostgREST, an external authorization engine cannot filter those rows. External engines only become usable if data access moves behind an API layer. That makes the realistic menu:
| Option | What it is | Fit for RPP |
|---|---|---|
| A. Status quo, hardened (recommended) | Keep Supabase Auth + JWT claims + RLS. Replace hardcoded role arrays with Supabase's documented RBAC pattern: a role_permissions table plus one authorize(permission) helper used by every policy, so permissions are data, not string literals scattered across 112 migrations. Add a shared auth middleware for edge functions. |
BEST FIT Works with the existing client-direct architecture, no new infra, migration is incremental. This is Supabase's own recommended shape for custom-claims RBAC. |
| B. Port the Mission Control model | MC already has a richer homegrown model in Convex: requirePermission(feature, level) and requireHotelAccess() with per-feature read/write levels and per-hotel overrides, checked server-side on every query. The concept (feature x level x hotel) ports cleanly into option A's permissions table. |
GOOD PATTERN Not a dependency to adopt, but the permission granularity RPP's AccessMatrix pretends to have, actually enforced. ~/mission-control/convex/authHelpers.ts:43-109, convex/team.ts |
| C. Policy engine sidecar: Cerbos, OPA, Casbin | Policy-as-code service evaluated from your backend. Cerbos publishes a Supabase integration explicitly positioned as complementary to RLS: RLS keeps guarding direct client queries, Cerbos governs API/edge-function logic. | LATER Only pays off once RPP has a meaningful API layer and multi-tenant customers with per-customer policy differences. Adds a service to run and a second policy language to keep in sync with RLS. |
| D. Zanzibar-style ReBAC: OpenFGA, SpiceDB, Permify | Relationship-based authorization (Google Zanzibar lineage) for fine-grained, graph-shaped permissions. Supabase has publicly explored OpenFGA integration for cases beyond RLS. | OVERKILL RPP's model is roles x properties, two flat dimensions. ReBAC earns its complexity with deep sharing graphs (docs, folders, teams), which RPP does not have. |
| E. Managed authz / auth platforms: Permit.io, Oso Cloud, Clerk, WorkOS, Auth0 | Hosted permission management UIs and APIs (Permit.io, Oso Cloud) or full identity platforms with org/RBAC features (Clerk, WorkOS, Auth0). | NOT NOW The identity platforms would mean leaving Supabase Auth (large migration, loses the token-hook + RLS integration). Hosted authz adds an external dependency in the read path for a 15-hotel internal rollout. Revisit if RPP sells externally at scale. |
| Where | What it holds | Status |
|---|---|---|
~/revparpro | The live implementation. Key files: supabase/migrations/002 (roles, token hook), 007 (RLS hardening), 258 (anon lockdown), 259 (finance/deal roles), 260 (self-signup + approvals); src/lib/auth.tsx, src/lib/nav-config.ts, src/hooks/useOnboarding.ts; edge fn auth in supabase/functions/*/index.ts | CANONICAL |
Vault: AceHQ/20-business/RevParPro/specs/internal-launch/page-specs/22-user-access-controls.md | April 2026 design of record: 8-role table, admin panel, invite flow, access checker (Woz tool), enforcement matrix, THM seed users. Now diverged: no admin panel at /app/admin (folded into Settings > Team), role list grew to 12+admin, access checker not built. | STALE, UPDATE |
~/mission-control | Sibling implementation of the same problem on Convex: feature x level x per-hotel permission templates, enforced per-query. The best internal prior art for option B. | PRIOR ART |
~/stickymetrics | Checked: data/engine repo, no user-facing auth layer. Not relevant. | N/A |
| External OSS (if ever needed) | cerbos/cerbos, openfga/openfga, authzed/spicedb, casbin/casbin, open-policy-agent/opa, Permify/permify | WATCH LIST |
requireRole() helper for all 39 functions; secret-gate dfs-webhook like the other webhooks; kill or gate qbo-dump-coa-raw, qbo-coa-migrate-mhd, gym-health-sync; add role checks to the qbo-sync family; tighten CORS off *.is_finance_role() / is_deal_role() gates to bs_imports, bs_snapshots, deal_pnl_periods, deal_str_periods, qbo_sync_log.can_access_hotel() to property-keyed data (night audit, budget, labor, STR) so single-property staff see only their property. This is the prerequisite for the QR rollout to 100+ frontline accounts.profiles.role; a role_permissions table + authorize() helper replacing hardcoded arrays (option A); route guards in App.tsx; drop legacy user_role() and the 001-era hook; update spec 22 to match reality and build the access checker it specifies, which is exactly the verification tool these audits keep needing.