🏠 Overview › ✅ Implementation Tasks ›✅ epic-role-based-access-control-data-infrastructure-task-002

Implement RLS policies for user_roles table

epic-role-based-access-control-data-infrastructure-task-002 — Write and apply Row Level Security policies on the user_roles table ensuring users can only read their own role assignments. Policies must prevent cross-user data leakage while allowing service-role access for administrative operations. Validate that coordinators cannot read role data for users outside their org unit scope.

critical priority medium complexity database pending database specialist Tier 1

Acceptance Criteria

RLS is enabled on the user_roles table (ALTER TABLE user_roles ENABLE ROW LEVEL SECURITY)
A SELECT policy exists that restricts rows to user_id = auth.uid() for authenticated users
A service-role bypass policy or use of SECURITY DEFINER functions allows admin operations without RLS restriction
An authenticated user querying user_roles directly can only see their own rows — verified by querying as two different test users
A coordinator user cannot read role assignments for users in a different org unit — verified via direct table query
INSERT/UPDATE/DELETE operations via the Supabase client (non-service-role) are rejected with a 403 or empty result
The get_my_roles() SECURITY DEFINER RPC from task-001 still works correctly with RLS enabled (bypasses RLS as intended)
RLS policies are defined in a dedicated migration file following the project naming convention
Policy names are descriptive (e.g., 'user_roles_select_own', 'user_roles_service_bypass')
No performance regression: get_my_roles() still returns in under 100ms with RLS enabled

Technical Requirements

frameworks
Supabase
PostgreSQL RLS
apis
Supabase Auth (auth.uid(), auth.role())
data models
UserRole
performance requirements
RLS policy predicate must use indexed column (user_id) to avoid full table scans
Policy evaluation overhead must not push get_my_roles() beyond 100ms
security requirements
Principle of least privilege: authenticated users can only read their own role rows
Service role (used by admin backend operations) must bypass RLS using the built-in Supabase service_role key
No USING clause that references another table should be used to avoid N+1 policy checks
Cross-org data leakage must be impossible through any direct table access path

Execution Context

Execution Tier
Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

Implementation Notes

The primary SELECT policy should be: CREATE POLICY user_roles_select_own ON user_roles FOR SELECT TO authenticated USING (user_id = auth.uid()). Do NOT add INSERT/UPDATE/DELETE policies for authenticated users — all writes should go through service-role backend functions only, making the mobile app read-only on this table. The SECURITY DEFINER on get_my_roles() means it runs as the function owner and bypasses RLS, so it will still work. Document this behavior explicitly in a code comment in the migration.

Avoid using auth.jwt() claims in RLS policies for this table — stick to auth.uid() for simplicity and reliability.

Testing Requirements

Test RLS policies by creating two test users (user_a, user_b) with separate role assignments. Log in as user_a and confirm only user_a's rows are returned from a direct SELECT. Log in as user_b and confirm only user_b's rows are returned. Attempt an INSERT as user_a and confirm it is rejected (or silently ignored based on policy).

Confirm the service role key bypasses RLS and can read all rows. Confirm get_my_roles() RPC still works correctly post-RLS because it is SECURITY DEFINER.

Component
Supabase Role Data Provider
infrastructure low
Dependencies (1)
Design and implement the user_roles database table in Supabase, including columns for user_id, org_unit_id, role_name (peer_mentor, coordinator, org_admin), and org_id. Add appropriate indexes, foreign key constraints, and a get_my_roles RPC function that returns the calling user's role assignments scoped to their organization units. epic-role-based-access-control-data-infrastructure-task-001
Epic Risks (2)
medium impact medium prob technical

The get_my_roles RPC call adds a network round-trip immediately after login, potentially increasing the time before the home screen renders. If Supabase RPC is slow or the roles table lacks proper indexing, users with multiple org affiliations could experience noticeable delays.

Mitigation & Contingency

Mitigation: Index user_roles on user_id and org_unit_id. Use JWT claim extraction as the primary fast path; fall back to the RPC only when claims are absent or stale. Set a 3-second timeout with a fallback to cached roles.

Contingency: If RPC latency exceeds acceptable thresholds in production, pre-fetch and embed roles into the session JWT at login time via a Supabase Auth hook, eliminating the post-login RPC entirely.

high impact medium prob integration

Users who belong to multiple organizations (e.g., a coordinator in one NHF chapter who is also a peer mentor in another) may have conflicting role assignments. The repository layer must correctly scope roles to the active organization context set during the organization selection step, or it could return roles from the wrong org.

Mitigation & Contingency

Mitigation: Always filter role queries by the active org_unit_id stored in the tenant session. Write integration tests that simulate multi-org users and verify only the correct org's roles are returned.

Contingency: If org-scoping logic is found to be incorrect during QA, add an explicit org_unit_id parameter to get_my_roles RPC and require the client to always pass the active org context, making the scoping explicit rather than inferred.

Quick Links
All Tasks Execution Plan