מפתחים

Authorization and roles

Maintainer reference for the post-hardening model. Source of truth: src/db/security-authz-hardening-migration.sql (applied on the live Supabase project).

Role model

Role	How it is assigned
`student`	Default for every new signup
`teacher`	Via `claim_teacher_role()` only (student → teacher)
`super_admin`	Not claimable from the client; service / controlled ops only

Never authorize from user_metadata. Clients can edit that object. App code and the proxy read profiles.role only.

Signup trigger

handle_new_user() inserts a profile with:

full_name from metadata (or email)
role = 'student' always — ignores any role in raw_user_meta_data

Blocking role escalation

Trigger protect_profile_role on profiles BEFORE UPDATE:

Rejects changes to role unless app.allow_role_change = on (set inside trusted RPCs) or the caller is service_role.

Teacher claim

claim_teacher_role():

Requires auth.uid()
Updates profiles.role from student → teacher only
Temporarily enables app.allow_role_change
EXECUTE granted to authenticated; revoked from anon / PUBLIC

App path: teacher signup → claimTeacherRole(); email confirm may use ?intent=teacher then claim in the auth callback.

Classroom join

Lookup

lookup_workspace_by_join_code(p_code):

Normalizes code (trim + upper)
Returns limited fields: id, name, school_name, grade
One active workspace match
Callable by anon and authenticated (needed before / during signup UX)

Join

join_workspace_with_code(p_code):

Requires authentication
Inserts into workspace_members for the matching active workspace
EXECUTE for authenticated only (not anon)

Policies removed

Open “public join code lookup” SELECT on workspaces
Open student INSERT on workspace_members

Clients cannot list all classrooms or self-insert membership without a valid code RPC.

Hardened helper RPCs

my_role, is_workspace_member, is_workspace_owner — SECURITY DEFINER with fixed search_path = public
award_points — requires auth + workspace membership/ownership rules; cannot award another student unless owner/super_admin
notify_student — teacher/owner scoped; anon execute revoked

App entry points

Concern	Location
Join with code	`src/app/actions/auth.ts` → `join_workspace_with_code`
Claim teacher	`src/app/actions/auth.ts` → `claim_teacher_role`
Auth callback	`src/app/auth/callback/route.ts`
Shared guards	`src/lib/auth/guards.ts` (`requireUser`, `requireTeacher`, `requireTeacherWorkspace`)

Join code format

New classroom codes are 8-character CSPRNG alphanumeric. Older 6-character codes may still exist and work if still active.

Re-apply / audit

Re-read and re-apply from:

src/db/security-authz-hardening-migration.sql

After schema changes, verify:

No policy allows unauthenticated SELECT of all workspaces
No open INSERT on workspace_members
claim_teacher_role / join_workspace_with_code not executable by anon