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

Write RLS policies for org_admin role

epic-admin-portal-foundation-task-003 — Implement Supabase row-level security policies scoped to the org_admin role across users, activities, reimbursements, and organisations tables. Policies must restrict data access to the authenticated admin's organisation subtree using the JWT org_id claim. Include recursive CTE helper function for subtree resolution.

critical priority high complexity database pending database specialist Tier 1

Acceptance Criteria

A PostgreSQL function get_org_subtree(root_org_id uuid) RETURNS TABLE(org_id uuid) is created using a recursive CTE that traverses the organisations parent-child hierarchy and returns all descendant organisation IDs including the root
The helper function is defined as SECURITY DEFINER to allow it to bypass RLS on the organisations table during subtree resolution, with a comment documenting this intentional design
SELECT policy for org_admin on users table: USING (organisation_id IN (SELECT org_id FROM get_org_subtree((auth.jwt()->'claims'->>'org_id')::uuid)))
SELECT policy for org_admin on activities table scoped to organisation_id in subtree
SELECT policy for org_admin on reimbursements table scoped to organisation_id in subtree
SELECT policy for org_admin on organisations table scoped to id in subtree (admin can see their own org tree)
INSERT policy for org_admin on activities: WITH_CHECK restricts inserts to the admin's own organisation_id only (not subtree — admins can only create records in their direct org)
INSERT policy for org_admin on reimbursements: WITH_CHECK restricts to admin's own organisation_id
UPDATE policy for org_admin on users: USING scoped to subtree; WITH_CHECK prevents moving a user out of the admin's subtree by changing organisation_id to a non-subtree org
UPDATE policy for org_admin on activities and reimbursements: USING scoped to subtree; WITH_CHECK scoped to subtree
DELETE policy for org_admin on activities: USING scoped to subtree (soft-delete pattern preferred — update is_deleted flag rather than physical delete if the schema supports it)
No policy allows org_admin to modify the user_roles table beyond their own subtree
All policies are named using the convention: {role}_{operation}_{table} (e.g., org_admin_select_users)
Policies are applied via a migration SQL file committed to the repository under supabase/migrations/
Verification test: a JWT with org_admin role and org_id=X can only SELECT users from X's subtree — verified by running a test query with set role and set claim in psql

Technical Requirements

frameworks
Supabase (PostgreSQL 15+)
PostgreSQL RLS
PostgreSQL recursive CTEs
apis
Supabase Auth JWT custom claims (org_id, role)
data models
users
activities
reimbursements
organisations
user_roles
OrgSubtree
performance requirements
get_org_subtree() must complete in under 50ms for an organisation tree depth of 5 levels and 1000 organisations
Add an index on organisations(parent_organisation_id) if not already present to support recursive CTE performance
RLS policy evaluation overhead must not add more than 10ms to any query — verify with EXPLAIN ANALYZE
security requirements
The get_org_subtree function must be SECURITY DEFINER but owned by a non-superuser role to limit blast radius
JWT org_id claim must be cast to uuid explicitly — do not use text comparison to prevent type confusion attacks
Policies must handle the case where JWT org_id claim is null (unauthenticated or malformed token) by returning false (no access)
UPDATE WITH_CHECK must prevent privilege escalation — an org_admin must not be able to assign a user to an organisation outside their subtree
All policy USING expressions must be tested with a null org_id JWT to confirm they evaluate to false, not error

Execution Context

Execution Tier
Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

Implementation Notes

Recursive CTE pattern for get_org_subtree: CREATE OR REPLACE FUNCTION get_org_subtree(root_org_id uuid) RETURNS TABLE(org_id uuid) LANGUAGE sql SECURITY DEFINER STABLE AS $$ WITH RECURSIVE subtree AS (SELECT id FROM organisations WHERE id = root_org_id UNION ALL SELECT o.id FROM organisations o JOIN subtree s ON o.parent_organisation_id = s.id) SELECT id FROM subtree; $$; — mark it STABLE (not VOLATILE) so the query planner can cache results within a single query. For the JWT claim extraction, use (auth.jwt() -> 'claims' ->> 'org_id')::uuid — the double ->> extracts as text before casting to uuid. Add a NULL guard: COALESCE((auth.jwt() -> 'claims' ->> 'org_id')::uuid, '00000000-0000-0000-0000-000000000000'::uuid) which maps null to a non-existent UUID, ensuring no data is returned for unauthenticated requests without causing an error. Write policies in a single migration file with clearly commented sections per table.

Test each policy in isolation before combining — use psql with SET LOCAL SESSION AUTHORIZATION and SET LOCAL request.jwt.claims to simulate the org_admin JWT context.

Testing Requirements

Integration tests against a Supabase test project (or local Supabase via supabase start): (1) Test fixture setup — create organisations hierarchy (root → child → grandchild), create users in each org, create an org_admin JWT for the root org; (2) SELECT test — verify org_admin can select users in root, child, and grandchild orgs; cannot select users in a sibling org; (3) INSERT test — verify org_admin can insert an activity in their own org; cannot insert in a child org (restricted to direct org); (4) UPDATE test — verify org_admin can update a user in their subtree; cannot update a user outside subtree; attempt to change organisation_id to outside subtree is rejected; (5) DELETE test — verify org_admin can delete an activity in their subtree; cannot delete outside subtree; (6) Null JWT test — verify all operations fail when org_id claim is absent. Tests written in SQL using SET LOCAL role and SET LOCAL request.jwt.claims.

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