🏠 Overview › ✅ Implementation Tasks ›✅ epic-geographic-peer-mentor-map-data-infrastructure-task-003

Implement RLS policies for location data isolation

epic-geographic-peer-mentor-map-data-infrastructure-task-003 — Implement row-level security policies on the mentor_locations table enforcing organisation-scoped access. Coordinators may only SELECT rows where organisation_id matches their JWT claim. Peer mentors may only INSERT and UPDATE their own row. Verify policies block cross-organisation reads via Supabase policy tests.

critical priority high complexity database pending database specialist Tier 2

Acceptance Criteria

Policy `mentor_locations_coordinator_select` restricts SELECT to rows where `organisation_id` matches the coordinator's organisation derived from `user_profiles` joined on `auth.uid()`
Policy `mentor_locations_mentor_insert` restricts INSERT so a peer mentor can only insert a row where `mentor_id = auth.uid()`
Policy `mentor_locations_mentor_update` restricts UPDATE so a peer mentor can only update their own row (`mentor_id = auth.uid()`)
No DELETE policy exists for peer mentors — deletes are admin-only via service role
Cross-organisation read test: coordinator of org A receives zero rows when querying for a mentor who belongs to org B
Cross-mentor write test: peer mentor A receives a Postgres permission error when attempting to UPDATE mentor B's row
Anonymous (unauthenticated) user receives zero rows on SELECT and a permission error on INSERT
Service role (used by backend functions) bypasses all RLS policies and can SELECT/INSERT/UPDATE/DELETE freely
All policy tests are implemented as SQL test scripts (pgTAP or Supabase-compatible SQL test functions) and run via `supabase test db`
Policy definitions are verified with `\dp mentor_locations` output documented in the PR

Technical Requirements

frameworks
Supabase
apis
Supabase Auth (auth.uid(), auth.jwt())
Supabase CLI (supabase test db)
pgTAP
data models
mentor_locations
user_profiles
organisations
performance requirements
RLS policy subqueries must use indexed columns (user_profiles.id, user_profiles.organisation_id) to avoid sequential scans on large tables
Policy evaluation overhead must not increase query latency by more than 10 ms for coordinator SELECT on 10,000-row tables
security requirements
Policies must use `USING` clause for SELECT/UPDATE/DELETE and `WITH CHECK` clause for INSERT/UPDATE to enforce both read and write isolation
Never use `auth.jwt() ->> 'organisation_id'` directly from JWT claims unless the claim is explicitly set in a Supabase auth hook — always derive from `user_profiles` to prevent JWT tampering
Audit any use of `SECURITY DEFINER` functions in policy expressions — prefer `SECURITY INVOKER` to maintain RLS context
GDPR/Norwegian privacy law compliance: location coordinates are personal data — policies must enforce the minimum necessary access principle

Execution Context

Execution Tier
Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

Implementation Notes

To simulate different user roles in pgTAP tests, use `SET LOCAL role = authenticated;` combined with `SET LOCAL request.jwt.claims = '{"sub": ""}'` within a transaction that is rolled back after each test. This avoids permanent test data pollution. The coordinator SELECT policy joins `user_profiles`: `USING (organisation_id = (SELECT organisation_id FROM user_profiles WHERE id = auth.uid()))` — ensure `user_profiles` has a B-tree index on `id` (primary key) for this subquery. For `WITH CHECK` on INSERT: `WITH CHECK (mentor_id = auth.uid())` prevents a mentor inserting a row claiming to be someone else.

Consider a Postgres security label or comment on each policy documenting the business rule it enforces, to aid future audits. Cross-reference these policies against the Norwegian Personal Data Act (Personopplysningsloven) requirements for location data minimisation.

Testing Requirements

Automated policy tests using `supabase test db` with pgTAP. Write SQL test functions covering: (1) coordinator same-org SELECT returns rows; (2) coordinator cross-org SELECT returns zero rows; (3) mentor self INSERT succeeds; (4) mentor other-user INSERT fails; (5) mentor self UPDATE succeeds; (6) mentor other-user UPDATE fails; (7) anonymous SELECT returns zero rows; (8) anonymous INSERT fails; (9) service role SELECT returns all rows; (10) service role INSERT bypasses policies. Each test uses `set_config('request.jwt.claims', ...)` to simulate different authenticated users. All 10 tests must pass in CI.

Document test setup/teardown to avoid polluting the database between runs.

Component
Mentor Location Repository
data high
Dependencies (1)
Write and apply a Supabase migration that enables the PostGIS extension, creates the mentor_locations table with all required columns including GEOGRAPHY point column and spatial GIST index, and sets up the organisation_id-scoped row-level security policies to ensure coordinators can only read mentors within their organisation. epic-geographic-peer-mentor-map-data-infrastructure-task-002
Epic Risks (3)
high impact medium prob integration

Supabase's hosted PostGIS extension behaviour may differ from the local emulator for spatial RPC functions, causing bounding-box queries to return incorrect results or fail in production while passing locally.

Mitigation & Contingency

Mitigation: Write integration tests against the Supabase emulator from the start and run the same test suite against a staging Supabase project before merging. Use ST_DWithin and ST_MakeEnvelope in plain SQL first, validate with psql, then wrap as RPC.

Contingency: If PostGIS RPC proves unreliable, fall back to client-side bounding box filtering on a full fetch of consented mentor locations (acceptable for up to ~200 mentors per chapter) until the spatial query is stabilised.

medium impact low prob dependency

OpenStreetMap tile usage may require attribution handling and rate limiting. Switching to Google Maps Flutter plugin mid-implementation would require significant rework of the map-provider-integration abstraction.

Mitigation & Contingency

Mitigation: Define the map-provider-integration abstraction interface before selecting the SDK so that the concrete implementation is swappable. Implement OSM first with correct attribution. Document Google Maps as the alternate with its API key setup steps.

Contingency: If OSM tiles are rejected by stakeholders or tile server limits are hit, activate the Google Maps Flutter plugin implementation behind the same interface without touching any UI or service code.

high impact low prob security

Incorrect RLS configuration could allow a coordinator to query mentor locations from a different organisation, constituting a GDPR data breach.

Mitigation & Contingency

Mitigation: Write dedicated RLS integration tests with two isolated test organisations and assert that cross-organisation queries return zero rows. Include these tests in CI. Have a second developer review all RLS policy SQL before migration is applied.

Contingency: If a cross-organisation data leak is discovered post-deployment, immediately disable the map feature via the organisation feature flag, revoke the affected Supabase RLS policy, and notify the data protection officer per the organisation's GDPR incident response procedure.

Quick Links
All Tasks Execution Plan