protected-resources

name

protected-resources

description

Single-path-of-resistance pattern for tables/operations that must NOT be modifiable by anyone except Super Admins — even contributors with full codebase access. Mandatory reading whenever a task touches `public.admins`, `public.admin_audit_log`, the `is_super_admin()` / `requireSuperAdmin()` / `selectIsSuperAdmin` gates, anything in `app/api/admin/admins/**`, `app/(authenticated)/(admin-auth)/administration/admins/**`, the SECURITY DEFINER admin RPCs (`admin_promote`, `admin_update`, `admin_revoke`, `admin_list`, `admin_list_audit`, `admin_find_user_by_email`), or when adding a new table/feature you intend to lock down to Super Admin only (billing, feature flags, secrets, audit data, anything sensitive). Use this skill before writing any new RLS policy, SECURITY DEFINER RPC, `createAdminClient()` call, or admin-gated API route.

Protected Resources — Single Path of Resistance

This skill is the canonical pattern for protecting tables/operations from contributors who have the codebase but should NOT be able to modify the data. It assumes the threat model: regular admins have commit access, can read .env files, can deploy from branches, and can call the API with their own session token.

Rule of thumb: if the wrong person controlling the codebase could cause real damage, the protection has to be in the database, not in TypeScript.

---

Threat model — what you're defending against

A developer or senior_admin admin who has the codebase can:

• Edit requireSuperAdmin() to remove the check, push to a branch, deploy from their machine.
• Call any API route directly via curl with their own auth token, skipping the UI.
• Read .env.local for any leaked keys.
• Use createAdminClient() (service role) if that key is reachable from their environment.

The naive answers — "guard the route", "check the selector", "hide the button" — all fail. The codebase is hostile territory.

---

The five-layer defense (load-bearing → soft)

#	Layer	Where it lives	Bypassable by a regular admin?
1	Service-role key isolation	Only in Vercel production env. Never in any committed .env*. Never in dev tooling.	Only if leaked. This is the foundation; without it, layers 2–3 fold.
2	RLS deny-writes on the protected table	Postgres policy: FOR ALL USING (false) WITH CHECK (false) for authenticated. Lets service_role through (Supabase default).	No — DB-enforced.
3	SECURITY DEFINER RPCs gated by is_super_admin()	Postgres function. Begins with IF NOT public.is_super_admin() THEN RAISE EXCEPTION .... The ONLY supported write path.	No — DB-enforced.
4	requireSuperAdmin() in the API route	Server-side TS check before calling the RPC. Better error messages, same effect.	Yes — but DB still rejects. Belt-and-suspenders.
5	selectIsSuperAdmin UI gate	Hides the page/button. UX, not security.	Yes — anyone can curl. Layers 1–3 are what stop them.

Layers 1–3 are what actually keep the data safe. Layers 4–5 are good UX and clean error paths.

---

The "single path of resistance" rule

Every mutation to a protected table goes through ONE choke point: a SECURITY DEFINER RPC. There is no other way in. Audit logging hangs off that one path so you only have to monitor one thing.

                  ┌──── direct .from('admins').insert(...)  ❌ blocked by RLS
                  │
                  ├──── direct API route writing the table  ❌ blocked by RLS
   any caller ───┤
                  ├──── service-role client                 ⚠️ bypasses RLS — service-role-only paths must be tightly held
                  │
                  └──── RPC admin_promote()  ✅ checks is_super_admin() + writes audit row
                              │
                              ▼
                        public.admins (RLS denies direct writes)
                              │
                              ▼
                        audit trigger → public.admin_audit_log

Never add a second mutation path. If you find yourself calling createAdminClient().from('admins').update(...) from a route, stop — wrap it in an RPC instead. Two paths means two things to audit and two things to keep in sync. The whole point of this pattern is that there is exactly ONE place to look when investigating.

---

Current protected resources (Matrx Main)

Resource	RLS	Mutation RPCs	Audit
public.admins	self-select + super-admin-select; deny all writes	admin_promote, admin_update, admin_revoke	admin_audit_log (trigger captures every INSERT/UPDATE/DELETE)
public.admin_audit_log	super-admin-only select; deny all writes	(only the trigger writes)	self-evident; immutable by design

Read RPCs:

• admin_list() — all admins + email/last-sign-in
• admin_list_audit(limit, offset) — audit log with actor + target emails
• admin_find_user_by_email(email) — for the promote-by-email flow
• is_super_admin() — boolean, used inside other RPCs and as the RLS condition
• get_admin_status() — (is_admin, admin_level) for the current auth.uid(), used by SSR boot

API routes (all check requireSuperAdmin() first, then call the RPC):

• app/api/admin/admins/route.ts — GET (list) / POST (promote)
• app/api/admin/admins/[userId]/route.ts — PATCH (update level / permissions / metadata) / DELETE (revoke)
• app/api/admin/admins/audit/route.ts — GET (audit log)
• app/api/admin/admins/lookup/route.ts — GET (find user by email)

UI: app/(authenticated)/(admin-auth)/administration/admins/page.tsx

Bricking guards inside the RPCs (worth knowing — they raise 42501):

• Cannot demote yourself out of super_admin.
• Cannot revoke yourself.
• Cannot demote/revoke the last Super Admin.

---

Recipe: locking down a new table

When the user says "I don't want regular admins touching X", do exactly this — in order. Skipping a step breaks the model.

1. Service-role key isolation (one-time, project-wide)

If SUPABASE_SERVICE_ROLE_KEY is in any committed .env* file, that's the first thing to fix. Production env vars only. If it's already isolated, skip.

2. Migration: RLS + RPCs + audit

Use migrations/admin_management_rls_and_rpcs.sql as the template. Substitute your table name and adjust the gate (is_super_admin() is fine for most cases; if you want a different bar, define it once and reuse).

Mandatory pieces:

1. ALTER TABLE public.X ENABLE ROW LEVEL SECURITY;
2. CREATE POLICY ... FOR ALL USING (false) WITH CHECK (false); for writes.
3. A SELECT policy that's as restrictive as the data deserves (super-admin-only is a fine default).
4. One SECURITY DEFINER RPC per legitimate write operation. Each RPC starts with:

   IF NOT public.is_super_admin() THEN
     RAISE EXCEPTION 'Forbidden: Super Admin required' USING ERRCODE = '42501';
   END IF;
   

5. A public.X_audit_log table with the same shape as admin_audit_log (or extend admin_audit_log if it makes sense).
6. An AFTER INSERT OR UPDATE OR DELETE trigger that writes audit rows. The trigger is SECURITY DEFINER so it works regardless of caller.
7. GRANT EXECUTE ON FUNCTION ... TO authenticated; for each RPC.

3. API routes

Mirror app/api/admin/admins/:

• requireSuperAdmin() first — clean 401/403.
• Then supabase.rpc('your_rpc', { ... }) — the user's own JWT carries auth.uid() into the RPC, which is what is_super_admin() reads.
• Map RPC error codes: 42501 → 403, 23503 → 404, else 400.

Do not use createAdminClient() here. The whole point is that the user's JWT must reach the DB so auth.uid() can be checked. Service role bypasses RLS — fine for system jobs, wrong for user-initiated writes.

4. UI

Place under app/(authenticated)/(admin-auth)/administration/<resource>/. The (admin-auth) layout already redirects non-super-admins to /dashboard. Nothing else to do for the route guard.

For per-action confirmation, use the imperative confirm() from @/components/dialogs/confirm/ConfirmDialogHost (or <ConfirmDialog /> inline). Never window.confirm — see CLAUDE.md.

5. Register in categories.tsx and navigation-links.tsx

Add an entry to:

• app/(authenticated)/(admin-auth)/administration/categories.tsx — appears on the admin landing page.
• constants/navigation-links.tsx — appears in the admin sidebar.

6. Verify

After running the migration:

1. Hit the API as a non-admin → must 401/403.
2. Hit it as a super-admin → must succeed.
3. Try a destructive op via direct .from('X').delete() with a regular admin's JWT → must be blocked by RLS.
4. Confirm the audit log has rows after a successful change.

---

DO

• ✅ Put the gate in the RPC (is_super_admin()), not just the API route.
• ✅ Treat the audit log as the SINGLE place to look when investigating "who changed what".
• ✅ Use requireSuperAdmin() in the API route too — fast 401/403, doesn't depend on a network round-trip to the DB to know it's forbidden.
• ✅ Reuse the existing is_super_admin() helper across RPCs. Don't redefine the check.
• ✅ Use SECURITY DEFINER SET search_path = public on every RPC and trigger. Without SET search_path, you've opened a search-path injection foothold.
• ✅ When you add a new mutation RPC for an existing protected table, add a matching audit-trigger case (or extend the existing trigger). Don't let a write path skip the audit log.

DON'T

• ❌ Add a .from('protected_table').insert/update/delete(...) anywhere. There should be ZERO such call sites in the codebase. RLS will block them anyway, but the call site is a maintenance hazard — it implies the model is "RLS plus app-level checks" when it should be "RLS plus RPCs."
• ❌ Use createAdminClient() for user-initiated writes to a protected table. Service role bypasses RLS — that's the point — but it also bypasses auth.uid(), so is_super_admin() returns false (correctly: there is no current user). Use the user's normal client + RPC.
• ❌ Add a second mutation path "for convenience". Convenience is the enemy of monitoring. One choke point, one audit log, one thing to read.
• ❌ Disable RLS "temporarily" on a protected table. There is no temporarily.
• ❌ Set SECURITY INVOKER on the management RPCs. They have to be DEFINER so they can read/write past RLS — the gate inside the function is what makes it safe.
• ❌ Skip the bricking guards (last super admin, self-demote, self-revoke). The user can lock themselves out and there's no clean recovery without service-role access.
• ❌ Log secrets, tokens, or full row contents from the audit trigger if the table has sensitive columns. Audit before / after JSONB fields ARE persisted forever — sanitize at the trigger level if needed.

---

Monitoring (the "ensure admins never take advantage" part)

The audit log is the single source. Two ways to read it:

1. UI: /administration/admins — bottom of the page, last 50 entries.
2. SQL: SELECT * FROM public.admin_list_audit(p_limit := 500); (or query admin_audit_log directly with super-admin SQL access).

Things to look for periodically:

• actor_user_id IS NULL rows — those are service-role / system writes (e.g. a migration). Should be rare and explainable.
• update rows where before.level = 'super_admin' — privilege grants. Confirm each one was intentional.
• Rapid bursts (many rows in a short window) — could be automation gone wrong.
• revoke of an active user — confirm with the actor.

If a regular admin manages to bypass all five layers, the audit row is still written by the DB trigger (the trigger runs as DEFINER regardless of who triggered it). Tampering would require modifying the trigger function itself, which requires service-role / SQL editor access — the same key/access boundary as layer 1.

---

When the gate isn't is_super_admin()

For surfaces that should be open to "any admin level" (not Super-only), use:

• TS: requireAdmin() / selectIsAdmin / state.userAuth.isAdmin
• SQL: EXISTS (SELECT 1 FROM public.admins WHERE user_id = auth.uid()) — there's no shipped helper for this since the default IS super-only.

For surfaces that need a specific tier (e.g. "developer or above"), gate on state.userAuth.adminLevel directly — read selectAdminLevel and check the values you want. Don't invent new boolean selectors per tier; the level enum is the source of truth.

This skill, the is_super_admin() helper, and admins.level cover everything. If you find yourself adding a new gate primitive, stop and re-read this file — odds are the existing primitives compose into what you need.