Security Architecture & Threat Model
Security boundaries, threat models, PostgREST queue isolation, Supabase JWT verification, and defense-in-depth rules.
Security Architecture & Threat Model
Attendrix enforces a multi-layer defense-in-depth security model. Because the mobile client communicates directly with PostgreSQL through Supabase PostgREST, authentication tokens, Row-Level Security policies, and queue grants form the core defensive perimeter.
No tenant may query, insert, or mutate records outside their auth.uid() scope, verified strictly by PostgreSQL Row-Level Security. Administrative overrides require explicit verification via is_admin().
1. Threat Model & Attack Vectors
| Threat Vector | Severity | Potential Impact | Defensive Mitigation |
|---|---|---|---|
| Queue Snooping / Eavesdropping | High | Unauthorized reading of pending push notifications, user IDs, and timetable reminders. | Engine-level RLS activation on notification_queue and revocation of SELECT from anon / authenticated roles. |
| Cross-Tenant Attendance Tampering | Critical | Malicious student marking absences or unmarking records for another student. | public.absences RLS policy (abs_insert_own, abs_delete_own) enforcing user_id = auth.uid(). |
| OAuth Token Exfiltration | Critical | Theft of student Google Calendar refresh tokens. | Storage inside Supabase Vault (supabase_vault); decrypted only within Deno isolates via SECURITY DEFINER RPCs. |
| JWT Replay & Session Hijacking | Medium | Unauthorized API access using stolen bearer tokens. | Short-lived JWTs (1 hour), TLS 1.3 encryption, and secure local storage (FlutterSecureStorage). |
| Advisory Lock Starvation / DoS | Medium | Flooding schedule regeneration to exhaust database connections. | Scoped transaction advisory locks (pg_advisory_xact_lock) restricted to administrative accounts. |
2. PostgREST Queue Security Analysis
During the Phase 2 security audit of database catalogs, an engine configuration inconsistency was identified and addressed:
The table public.notification_queue had an administrative policy (nq_admin_all), but Row-Level Security was disabled at the PostgreSQL table level (relrowsecurity = false).
Because PostgreSQL grants public read privileges by default in the public schema, clients could query pending notification jobs directly over PostgREST.
Defense-in-Depth Remediation Commands
To enforce complete isolation across all message queues:
-- 1. Enable RLS on the table
ALTER TABLE public.notification_queue ENABLE ROW LEVEL SECURITY;
-- 2. Revoke PostgREST table permissions from public client roles
REVOKE ALL ON public.notification_queue FROM anon, authenticated;
REVOKE ALL ON public.gcal_sync_queue FROM anon, authenticated;
-- 3. Grant full permissions strictly to service_role and postgres
GRANT ALL ON public.notification_queue TO service_role, postgres;
GRANT ALL ON public.gcal_sync_queue TO service_role, postgres;public.notification_queue and public.gcal_sync_queue must never be accessible via PostgREST to anon or authenticated roles. All queue interactions are confined strictly to Deno Edge Functions using SUPABASE_SERVICE_ROLE_KEY.
3. Credential Encryption & Supabase Vault
Google Calendar synchronization requires persistent access to user refresh tokens. Storing plain-text OAuth tokens in relational tables represents a severe security risk.
Attendrix uses Supabase Vault (powered by pgcrypto and AES-256-GCM):
sequenceDiagram
autonumber
participant App as Flutter Client
participant EF as google-calendar-auth
participant Vault as Supabase Vault (vault.secrets)
participant Worker as google-calendar-sync-worker
App->>EF: Complete OAuth 2.0 PKCE Flow
EF->>EF: Exchange auth code with Google
EF->>Vault: Call upsert_gcal_vault_secret(p_user_id, p_refresh_token)
Note over Vault: AES-256 encrypted at rest; returns vault_secret_id (UUID)
EF->>App: Integration active (stores only secret UUID reference)
Note over Worker,Vault: During asynchronous synchronization:
Worker->>Vault: Call get_gcal_vault_secret(vault_secret_id)
Vault-->>Worker: Decrypted refresh token in memory isolate
Worker->>Worker: Solicits ephemeral access token from Google- Zero Secret Exposure: The student's device and public API responses receive only the opaque
vault_secret_idUUID. - Strict Definer Isolation: The
get_gcal_vault_secretRPC is executable only by theservice_rolerole.
Was this page helpful?
Your feedback directly guides the engineering documentation roadmap.