Authoritative RLS policy matrix, tenant isolation models, and PostgREST queue security analysis.

Row-Level Security (RLS) Policies

Attendrix relies on PostgreSQL Row-Level Security (RLS) as the primary isolation boundary between student tenants. Because the Flutter mobile client interacts directly with Supabase via PostgREST, RLS policies must prevent unauthorized read, write, or cross-tenant modification under all runtime circumstances.

TENANT BOUNDARY INVARIANT

INV-SEC-01

Every table containing personal student data enforces user_id = auth.uid() on SELECT, INSERT, UPDATE, and DELETE operations. Administrative overrides require explicit verification via is_admin().

Critical Security Assessment: PostgREST Queue Isolation

A comprehensive audit of table-level security grants and engine flags reveals an important architectural distinction between the system's background queues:

Critical Finding: notification_queue RLS Engine Flag

Vulnerability Identified: The public.notification_queue table has policies created (such as nq_admin_all), but Row Level Security was disabled at the engine level (relrowsecurity = false).

Furthermore, default PostgreSQL permissions granted SELECT privileges to the anon and authenticated roles. Consequently, any authenticated or unauthenticated client querying supabase.from('notification_queue').select('*') via PostgREST could inspect pending push notification payloads, user IDs, and meal/class reminders.

Defense-in-Depth Remediation

To completely isolate internal queues from PostgREST exposure, execute the following defense-in-depth commands:

-- 1. Enable RLS on notification_queue
ALTER TABLE public.notification_queue ENABLE ROW LEVEL SECURITY;

-- 2. Revoke PostgREST access from client roles
REVOKE ALL ON public.notification_queue FROM anon, authenticated;
REVOKE ALL ON public.gcal_sync_queue FROM anon, authenticated;

-- 3. Restrict queue operations strictly to the service_role
GRANT ALL ON public.notification_queue TO service_role;
GRANT ALL ON public.gcal_sync_queue TO service_role;

QUEUE ISOLATION INVARIANT

INV-SEC-02

public.notification_queue and public.gcal_sync_queue must never be readable or writable by anon or authenticated roles via PostgREST. Queue interactions must occur exclusively through Deno Edge Functions authenticated with the service_role key or SECURITY DEFINER database triggers.

RLS Architectural Patterns

Attendrix uses four standard RLS patterns across its tables:

Strict User Ownership: Tables where user_id = auth.uid() governs all CRUD actions (e.g. absences, user_preferences, user_google_integrations, student_task_records).
Public Read Catalogs: Read-only reference tables accessible by anon and authenticated users, with modifications restricted to administrators (e.g. campus_buildings, courses, buses, bus_stops, bus_timings, messes, mess_menu).
Active Semester Filtering: Timetable occurrences and classes queryable only if their parent semester is currently active (semesters.is_active = true) or by admins.
Service Role Queues: Background worker queues intended to be locked out from general client consumption.

Authoritative Table Policy Matrix

The following matrix documents the exact policies active in the Supabase PostgreSQL catalog:

`absences`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`abs_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	`is_admin()`
`abs_delete_own`	`DELETE`	`authenticated`	PERMISSIVE	`(user_id = auth.uid())`	—
`abs_insert_own`	`INSERT`	`authenticated`	PERMISSIVE	—	`((user_id = auth.uid()) AND (source = 'student'::text))`
`abs_select_own`	`SELECT`	`authenticated`	PERMISSIVE	`(user_id = auth.uid())`	—

`academic_calendar_events`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`ace_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	`is_admin()`
`ace_select_auth`	`SELECT`	`authenticated`	PERMISSIVE	`true`	—

`academic_calendar_overrides`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`Allow public read academic_calendar_overrides`	`SELECT`	`public`	PERMISSIVE	`true`	—

`achievement_progress`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`ap_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	`is_admin()`
`ap_select_own`	`SELECT`	`authenticated`	PERMISSIVE	`(student_id = auth.uid())`	—

`achievement_templates`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`achievement_templates_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	`is_admin()`
`achievement_templates_select_authenticated`	`SELECT`	`authenticated`	PERMISSIVE	`true`	—

`admin_audit_log`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`aal_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	`is_admin()`

`amplix_ledger`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`ledger_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	`is_admin()`
`ledger_select_own`	`SELECT`	`authenticated`	PERMISSIVE	`(user_id = auth.uid())`	—

`app_version_control`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`app_version_control_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	`is_admin()`
`app_version_control_select_all`	`SELECT`	`public`	PERMISSIVE	`true`	—

`audit_log`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`audit_admin_read`	`SELECT`	`authenticated`	PERMISSIVE	`is_admin()`	—

`batch_courses`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`bc_select_auth`	`SELECT`	`authenticated`	PERMISSIVE	`true`	—

`batches`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`batches_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	`is_admin()`
`batches_select_auth`	`SELECT`	`authenticated`	PERMISSIVE	`true`	—

`bus_stops`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`Allow public read access to bus_stops`	`SELECT`	`public`	PERMISSIVE	`true`	—

`bus_timings`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`bus_timings_public_read`	`SELECT`	`public`	PERMISSIVE	`true`	—

`buses`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`buses_public_read`	`SELECT`	`public`	PERMISSIVE	`true`	—

`campus_buildings`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`Public Read campus_buildings`	`SELECT`	`public`	PERMISSIVE	`true`	—

`challenge_progress`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`cp_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	`is_admin()`
`cp_select_own`	`SELECT`	`authenticated`	PERMISSIVE	`(user_id = auth.uid())`	—

`challenge_progress_classes`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`cpc_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	`is_admin()`
`cpc_select_own`	`SELECT`	`authenticated`	PERMISSIVE	`(EXISTS ( SELECT 1 FROM challenge_progress cp WHERE ((cp.progress_id = challenge_progress_classes.progress_id) AND (cp.user_id = auth.uid()))))`	—

`challenge_templates`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`challenge_templates_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	`is_admin()`
`challenge_templates_select_authenticated`	`SELECT`	`authenticated`	PERMISSIVE	`true`	—

`classes`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`classes_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	`is_admin()`
`classes_select_active`	`SELECT`	`authenticated`	PERMISSIVE	`((EXISTS ( SELECT 1 FROM semesters s WHERE ((s.semester_id = classes.semester_id) AND (s.is_active = true)))) OR is_admin())`	—

`course_meetings`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`Allow public read course_meetings`	`SELECT`	`public`	PERMISSIVE	`true`	—

`course_syllabi`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`course_syllabi_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	`is_admin()`
`course_syllabi_select_auth`	`SELECT`	`authenticated`	PERMISSIVE	`true`	—

`courses`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`courses_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	`is_admin()`
`courses_select_auth`	`SELECT`	`authenticated`	PERMISSIVE	`true`	—

`departments`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`departments_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	`is_admin()`
`departments_select_authenticated`	`SELECT`	`authenticated`	PERMISSIVE	`true`	—

`elective_courses`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`ec_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	—
`ec_select_auth`	`SELECT`	`authenticated`	PERMISSIVE	`true`	—

`elective_offering_semesters`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`eos_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	—
`eos_select_auth`	`SELECT`	`authenticated`	PERMISSIVE	`true`	—

`graph_edges`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`Allow public read graph_edges`	`SELECT`	`public`	PERMISSIVE	`true`	—

`graph_nodes`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`Allow public read graph_nodes`	`SELECT`	`public`	PERMISSIVE	`true`	—

`mess_menu`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`mess_menu_public_read`	`SELECT`	`public`	PERMISSIVE	`true`	—

`messes`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`messes_public_read`	`SELECT`	`public`	PERMISSIVE	`true`	—

`notification_devices`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`nd_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	—
`nd_delete_own`	`DELETE`	`authenticated`	PERMISSIVE	`(user_id = auth.uid())`	—
`nd_insert_own`	`INSERT`	`authenticated`	PERMISSIVE	—	`(user_id = auth.uid())`
`nd_select_own`	`SELECT`	`authenticated`	PERMISSIVE	`(user_id = auth.uid())`	—
`nd_service_all`	`ALL`	`service_role`	PERMISSIVE	`true`	—
`nd_update_own`	`UPDATE`	`authenticated`	PERMISSIVE	`(user_id = auth.uid())`	—

`notification_log`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`nl_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	—
`nl_select_own`	`SELECT`	`authenticated`	PERMISSIVE	`(user_id = auth.uid())`	—
`nl_service_all`	`ALL`	`service_role`	PERMISSIVE	`true`	—
`nl_update_own`	`UPDATE`	`authenticated`	PERMISSIVE	`(user_id = auth.uid())`	—

`notification_queue`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`nq_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	—

`observed_walking_trips`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`Users can insert their own trips`	`INSERT`	`public`	PERMISSIVE	—	`(auth.uid() = user_id)`
`Users can view their own trips only`	`SELECT`	`public`	PERMISSIVE	`(auth.uid() = user_id)`	—

`route_cache`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`Allow public read route_cache`	`SELECT`	`public`	PERMISSIVE	`true`	—

`route_statistics`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`Authenticated users can read aggregated stats`	`SELECT`	`authenticated`	PERMISSIVE	`true`	—

`routing_dataset_routes`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`Anyone can view routing dataset routes`	`SELECT`	`public`	PERMISSIVE	`true`	—

`routing_dataset_versions`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`Anyone can view active routing dataset versions`	`SELECT`	`public`	PERMISSIVE	`true`	—

`scheduling_audit_log`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`sal_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	`is_admin()`

`semesters`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`semesters_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	`is_admin()`
`semesters_select_authenticated`	`SELECT`	`authenticated`	PERMISSIVE	`true`	—

`slot_occurrences`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`slot_occurrences_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	`is_admin()`
`slot_occurrences_select_authenticated`	`SELECT`	`authenticated`	PERMISSIVE	`true`	—

`student_task_records`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`str_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	`is_admin()`
`str_insert_own`	`INSERT`	`authenticated`	PERMISSIVE	—	`(user_id = auth.uid())`
`str_select_own`	`SELECT`	`authenticated`	PERMISSIVE	`(user_id = auth.uid())`	—
`str_update_own`	`UPDATE`	`authenticated`	PERMISSIVE	`(user_id = auth.uid())`	`(user_id = auth.uid())`

`tasks`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`tasks_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	`is_admin()`

`user_calendar_events`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`Users can view own event mappings`	`SELECT`	`public`	PERMISSIVE	`(auth.uid() = user_id)`	—

`user_course_enrollments`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`uce_delete_own`	`DELETE`	`authenticated`	PERMISSIVE	`(user_id = auth.uid())`	—
`uce_insert_own`	`INSERT`	`authenticated`	PERMISSIVE	—	`(user_id = auth.uid())`
`uce_select_own`	`SELECT`	`authenticated`	PERMISSIVE	`((user_id = auth.uid()) OR is_admin())`	—

`user_google_integrations`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`Users can delete own integration`	`DELETE`	`public`	PERMISSIVE	`(auth.uid() = user_id)`	—
`Users can update own integration`	`UPDATE`	`public`	PERMISSIVE	`(auth.uid() = user_id)`	`(auth.uid() = user_id)`
`Users can view own integration`	`SELECT`	`public`	PERMISSIVE	`(auth.uid() = user_id)`	—

`user_preferences`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`up_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	—
`up_insert_own`	`INSERT`	`authenticated`	PERMISSIVE	—	`(user_id = auth.uid())`
`up_select_own`	`SELECT`	`authenticated`	PERMISSIVE	`(user_id = auth.uid())`	—
`up_service_all`	`ALL`	`service_role`	PERMISSIVE	`true`	—
`up_update_own`	`UPDATE`	`authenticated`	PERMISSIVE	`(user_id = auth.uid())`	—

`users`

Policy Name	Command	Roles	Permissive	Using Expression	With Check Expression
`users_admin_all`	`ALL`	`authenticated`	PERMISSIVE	`is_admin()`	`is_admin()`
`users_select_self_or_admin`	`SELECT`	`authenticated`	PERMISSIVE	`((id = auth.uid()) OR is_admin())`	—
`users_update_self`	`UPDATE`	`authenticated`	PERMISSIVE	`(id = auth.uid())`	`(id = auth.uid())`

Row-Level Security (RLS) Policies

Was this page helpful?

On this page