🏠 Overview › ✅ Implementation Tasks ›✅ epic-admin-portal-foundation-task-004

Write RLS policies for super_admin role

epic-admin-portal-foundation-task-004 — Implement Supabase row-level security policies for the super_admin role granting cross-organisation read access and restricted write access across all relevant tables. Policies must be additive to org_admin policies without conflict. Validate that super_admin cannot bypass audit trail writes.

critical priority high complexity database pending database specialist Tier 1

Acceptance Criteria

SELECT policy for super_admin on users, activities, reimbursements, organisations, and user_roles tables uses USING (true) — cross-organisation read access with no org restriction
INSERT policy for super_admin on activities and reimbursements: WITH_CHECK allows any organisation_id (super_admin can create records in any org)
UPDATE policy for super_admin on users and organisations: USING (true) with WITH_CHECK (true) — can update any record
UPDATE policy for super_admin on user_roles: allows role changes across all organisations with an explicit audit log write requirement (see note below)
DELETE policy for super_admin on activities: USING (true) — cross-org delete allowed (soft-delete preferred)
super_admin policies are PERMISSIVE and named with convention super_admin_{operation}_{table}
No conflict with org_admin policies — PostgreSQL evaluates PERMISSIVE policies with OR logic so both policy sets coexist correctly; this is verified by confirming an org_admin JWT still has restricted access after super_admin policies are added
super_admin cannot INSERT into an audit_trail table without the system-generated fields (created_by, created_at) — audit trail rows created by super_admin actions must still be attributable to the super_admin's user ID via auth.uid()
A test confirms that a super_admin JWT cannot delete from the audit_trail table (audit records are immutable — no DELETE policy is granted to any role on audit tables)
All super_admin policies are in a dedicated migration file supabase/migrations/{timestamp}_super_admin_rls.sql
Role check in policies uses (auth.jwt() -> 'claims' ->> 'role') = 'super_admin' — not a custom function — to keep policies auditable
Verification: a super_admin JWT can SELECT from all five tables and sees records from multiple organisations in a single query

Technical Requirements

frameworks
Supabase (PostgreSQL 15+)
PostgreSQL RLS
apis
Supabase Auth JWT custom claims (role)
data models
users
activities
reimbursements
organisations
user_roles
audit_trail (if exists)
performance requirements
USING (true) policies have near-zero overhead — no performance concerns for super_admin SELECT
super_admin queries across all organisations may return large result sets — ensure application-layer pagination is enforced; the RLS layer does not limit result size
Index on jwt claim role extraction is not possible — keep role check simple and consistent
security requirements
super_admin role must only be assignable by another super_admin or via direct database access — no application flow should allow self-elevation to super_admin
All super_admin write operations must generate an audit trail entry — either via a PostgreSQL trigger on modified tables or via application-enforced audit writes; verify the trigger exists before deploying policies
super_admin JWT must be issued with short expiry (1 hour max) and require re-authentication for write operations in sensitive tables — document this requirement for the Auth configuration task
No super_admin policy should allow truncation, DROP, or schema modification — these are database-level permissions outside RLS scope but must be confirmed absent from the super_admin Postgres role
JWT role claim must be validated server-side — document that client-side role spoofing is mitigated by Supabase Auth's signed JWT

Execution Context

Execution Tier
Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

Implementation Notes

The key design principle for additive policies: PostgreSQL evaluates multiple PERMISSIVE policies for the same role with OR logic — if any policy grants access, the operation succeeds. This means org_admin and super_admin policies do not conflict as long as they are both PERMISSIVE (the default). Avoid RESTRICTIVE policies unless intentionally blocking access that another policy would grant. For the audit trail protection, create a separate RESTRICTIVE policy on the audit_trail table: CREATE POLICY no_delete_audit ON audit_trail AS RESTRICTIVE FOR DELETE USING (false); — this blocks DELETE for ALL roles including super_admin regardless of other policies.

For the role check expression, use consistent casing: (auth.jwt() -> 'claims' ->> 'role')::text = 'super_admin' — define this pattern once in a comment at the top of the migration file and copy it exactly to avoid typos causing silent policy failures. After deploying, use the Supabase Dashboard → Authentication → Policies view to visually confirm all policies appear under the correct tables before running integration tests.

Testing Requirements

Integration tests against Supabase test project: (1) Cross-org SELECT — create users in org A and org B; super_admin JWT must return both in a single users SELECT; (2) Cross-org INSERT — super_admin inserts an activity in org B; verify it appears in org B's activity list; (3) Role isolation — after adding super_admin policies, re-run org_admin tests from task-003 and confirm org_admin still cannot access org B's data; (4) Audit trail immutability — attempt DELETE on audit_trail table with super_admin JWT; confirm it is rejected with permission denied; (5) Audit trail write — perform a super_admin UPDATE on user_roles; confirm a corresponding audit_trail row is created with the super_admin's auth.uid() as created_by; (6) Role elevation prevention — attempt to UPDATE a user's role to super_admin using an org_admin JWT; confirm it is rejected. All test SQL must be committed to supabase/tests/rls_super_admin_test.sql.

Component
Supabase RLS Policy Configuration
infrastructure high
Dependencies (1)
Systematically audit Supabase tables (users, activities, reimbursements, organisations, user_roles) for missing or incomplete RLS policies. Document every gap, verify that policies exist for INSERT, SELECT, UPDATE, and DELETE for both org_admin and super_admin roles. Produce a gap report that drives task-003 and task-004. epic-admin-portal-foundation-task-002
Epic Risks (3)
high impact medium prob security

Missing RLS policies on one or more tables (e.g., a newly added join table or a Supabase view) could expose cross-org data to org_admin queries, creating a GDPR-reportable data breach.

Mitigation & Contingency

Mitigation: Enumerate all tables and views accessed by admin queries before writing any policy. Create an automated test that attempts a cross-org query for each table from an org_admin JWT and asserts an empty result set.

Contingency: If a gap is discovered post-deployment, immediately disable the affected query surface and deploy a hotfix policy before re-enabling. Log the incident and notify DPO if any cross-org data was returned.

high impact medium prob technical

The recursive CTE for NHF's deeply nested org tree (up to 5 levels, 1,400 local chapters) may exceed the 2-second dashboard load target when resolving large subtrees on every request.

Mitigation & Contingency

Mitigation: Benchmark the recursive CTE against a synthetic NHF-scale dataset during development. Introduce a short-TTL server-side cache for subtree resolution results. Index the parent_id column on the organisations table.

Contingency: If CTE performance remains insufficient, materialise the org subtree as a precomputed closure table updated on org structure changes, and switch the RLS guard to query the closure table instead.

high impact low prob security

Incorrect JWT claim injection in AdminRlsGuard (e.g., wrong claim key name or missing refresh on org switch) could silently apply the wrong org scope, causing org_admin to see a different organisation's data without an explicit error.

Mitigation & Contingency

Mitigation: Write unit tests for the guard that verify the injected claim value against the authenticated user's org_id for every admin route. Add a server-side assertion that the claim matches the user's database record before executing any query.

Contingency: Roll back the guard to a deny-all fallback, invalidate active admin sessions, and re-issue corrected JWTs. Audit query logs to identify any sessions that received incorrect scope.

Quick Links
All Tasks Execution Plan