127 / 127 security assertions passing on PostgreSQL 17.11

Security Model

What is enforced, where it is enforced, and what was actually tested.

The one-sentence version Every permission that matters is a Row Level Security policy or a trigger inside PostgreSQL, so a student who opens the developer console, edits the JavaScript, replays a request or guesses an identifier gets the same answer as a student who does nothing: no data.

1. Where enforcement lives

PostgreSQL Row Level Security real control

47 policies across all 21 tables. Every select, insert, update and delete is filtered by the database against auth.uid(). This is the control.

Guard triggers real control

Row-level triggers reject privilege escalation that a policy cannot express — for example a user changing their own role, status or school_id — even on rows they are otherwise allowed to update.

`SECURITY DEFINER` RPCs real control

Ten narrow functions for operations that need more privilege than the caller has: joining with a class code, scoring a quiz, rotating a code, bootstrapping the first admin. Each one re-checks authorisation in its own body before doing anything.

Grants real control

anon has zero table privileges. Default privileges are revoked for future objects too, so a table added later is not accidentally world-readable.

Per-role navigation and hidden buttons cosmetic only

The sidebar and the route guard in js/app.js exist so people are not shown pages that would be empty. They are convenience, not security, and the code says so in its comments. Deleting them changes nothing about what data a user can obtain.

Client-side form validation cosmetic only

Improves error messages. Every constraint it checks is also a CHECK constraint or a policy in the database.

2. Role capability matrix

Action	Student	Teacher	Admin	Enforced by
Read their own profile	yes	yes	yes	`profiles` SELECT policy
Read another student's profile	no	only in their own classes	within their school	`can_view_profile()`
Change their own role or status	no	no	no	`guard_profile_escalation` trigger
Change someone else's role	no	no	yes, in their school	`is_admin()` + trigger + audit
Remove the last administrator	no	no	no	trigger refuses; school cannot lock itself out
See a class	only if enrolled	only if authorised	whole school	`is_class_member()` / `is_class_teacher()`
Create an assignment	no	only for their classes	no	`assignments` INSERT policy
See a draft assignment	no	yes	yes	`status='published'` in the student policy
See an assignment targeted at someone else	no	yes	yes	`assignment_targets` check in the policy
Read another student's submission	no	only in their classes	no	`submissions` SELECT policy
Submit as another student	no	no	no	`student_id = auth.uid()` WITH CHECK
Edit a submission after it is submitted	only if resubmission is allowed	no	no	status check in the UPDATE policy
Write a grade	no	only for their classes	no	`can_grade()`
Read an unreleased grade	no	yes	aggregates only	`and is_released` in the student policy
Read another student's grade	no	only in their classes	no	`grades` SELECT policy
Read another student's journal	no	no	no	`user_id = auth.uid()`, no exceptions
Read another student's paper trades	no	no	no	`user_id = auth.uid()`, no exceptions
Read a graded quiz answer key	no	no	no	no SELECT policy exists on that table at all
Enrol themselves in any class	only with a valid, active code	no	yes	`join_class_with_code()`
Enrol another student	no	no	yes	`class_members` INSERT policy
Authorise a teacher for a class	no	no	yes	`class_teachers` policies
Alter or delete an audit record	no	no	no	no UPDATE or DELETE policy exists

Administrators deliberately cannot grade. Administration and assessment are separate jobs here. An admin manages people, classes and enrolment; a teacher owns the teaching. The grades INSERT policy requires can_grade(), which checks class_teachers — being an admin is not enough. The admin's Assignments page is read-only for exactly this reason.

3. Design decisions worth explaining

Authorisation helpers are `SECURITY DEFINER` and `STABLE`

Functions such as is_class_teacher() and is_class_member() are declared security definer set search_path = '' and keyed on (select auth.uid()).

Why SECURITY DEFINER: it breaks the recursion. If a policy on class_members had to query class_members as the calling user, PostgreSQL would re-apply the same policy and recurse. A definer function runs outside RLS, so the lookup terminates.
Why set search_path = '': without it, a user who can create objects could shadow a table name and hijack the function body.
Why (select auth.uid()): wrapping it in a scalar sub-select lets the planner evaluate it once per statement rather than once per row — a large, free performance win on a 500 MB free-tier database.

Guard triggers are `SECURITY INVOKER`, not `SECURITY DEFINER`

This was a bug that had to be fixed during development, and the reasoning is worth recording.

Inside a SECURITY DEFINER trigger, current_user is always the function owner, so the trigger cannot tell a browser session apart from a trusted RPC. The first version therefore rejected the application's own trusted functions: join_class_with_code() and submit_quiz() were blocked by the very anti-escalation guard meant to protect them.

The fix is structural. Guards run as SECURITY INVOKER and enforce only when current_user = 'authenticated', i.e. only when the statement really did come from a logged-in browser session. A trusted definer function runs as the owner and is skipped.

A rejected alternative The obvious shortcut is a session flag — set_config('app.bypass_guards','on') — that trusted functions set before writing. It was rejected: anything a session can set, a session can set. A structural check on current_user cannot be forged from the browser.

RLS applies to the `WHERE` clause of an `UPDATE`, not only to the policy

An UPDATE … WHERE id = $1 must first find the row, and finding it goes through the SELECT policy. An administrator could not activate a pending signup because the SELECT policy hid the row before the UPDATE policy was ever consulted. can_view_profile() therefore has an explicit approval-queue branch for unclaimed rows (school_id is null and status = 'pending') so admins can see the queue they are expected to act on — and nothing more.

Quiz answer keys are server-side for graded work only

	Self-study quiz	Graded quiz (attached to an assignment)
Scored by	the browser	`public.submit_quiz()` in the database
Answers reachable by a determined student	yes — they are in the JS bundle	no
Counts toward a grade	no	yes
Why	instant feedback while the lesson is fresh matters more than secrecy for practice	a grade must not be forgeable

The graded path sends only the chosen option indices. The function reads lesson_answer_keys — a table with RLS on and no student-readable policy — compares, writes the attempt and the score, and returns the result. Test suite I verifies both that a student cannot read the key table and that a forged auto_score on a submission is rejected.

No service-role key anywhere in the frontend

The browser receives exactly two Supabase values: the project URL and the publishable (anon) key. Both are designed to be public. The schema is written so that this is safe: anon holds no table privileges, and every authenticated query is filtered by RLS.

$ grep -rniE "service_role|SUPABASE_SERVICE|secret_key|sb_secret" js/ *.html
  (no matches)

Class codes are revocable and validated server-side

A code is never checked in JavaScript. join_class_with_code() looks up the class, verifies join_code_active, enrols the caller as themselves only, and writes an audit row. Rotating a code sets a new value in the same statement, so the previous code stops working for everyone at once — there is no window in which the old code still functions.

4. What a hostile student actually gets

Attempt	Result
Open DevTools and set `App.state.me.role = "admin"`	The sidebar changes. Every query still returns their own data only. RLS does not read JavaScript variables.
Navigate to `#/audit` or `#/gradebook` directly	"Not available for your role" — and had the page rendered, the queries behind it would have returned nothing anyway.
Guess another class's id in the address bar	A refusal page. The row is not in their result set.
Call `API.submissions.forAssignment(id)` from the console for someone else's work	Returns only their own submission for that assignment.
`UPDATE profiles SET role='teacher' WHERE id=auth.uid()` via the REST API	Rejected by `guard_profile_escalation`.
`INSERT INTO grades …` for themselves	Rejected: the INSERT policy requires `can_grade()`.
`SELECT * FROM lesson_answer_keys`	Zero rows. No SELECT policy exists for them on that table.
`SELECT * FROM trading_journals`	Only their own entries, regardless of role.
Submit a quiz with a fabricated `auto_score`	Rejected. The score is computed inside `submit_quiz()`; the submitted value is ignored.
Sign out and query the REST API anonymously	Nothing. `anon` has no table privileges at all.

Each row above corresponds to at least one executed assertion in tests/02_rls_tests.sql. See TESTING.html.

5. Honest limitations

These are real and are not fixed by this project.

Self-study quiz answers are in the client bundle. Deliberate, and stated in the UI. Only assignment-linked quizzes are scored server-side.
No rate limiting beyond what Supabase provides. A determined user can hammer the API. Supabase's gateway limits apply; this project adds none of its own, because doing so properly needs an Edge Function and that complicates a free-tier deployment.
Class codes are seven characters. Guessable in principle by brute force. They are intended for a classroom, and rotation is one click. For a public deployment, rotate codes after enrolment closes or disable them entirely and enrol students manually.
No file uploads. The attachments columns exist but no Storage bucket is wired, so there is no file-upload attack surface — and no file uploads.
No two-factor authentication. Supabase supports MFA; this app does not expose a UI for it. Account security is email + password + whatever the Supabase project enforces.
A compromised administrator account is total. As in any school system. The audit log makes the damage visible after the fact; it does not prevent it.
The free plan takes no automatic backups. If data matters, schedule your own pg_dump, or budget for a paid plan. This is the single biggest operational risk of running on the free tier.
Teachers can read submissions and grades in their own classes. Intended, and it is why teacher authorisation is admin-controlled and audited.

6. Things a reviewer should check themselves

Do not take this document's word for any of it. These all run in under a minute:

# 1. No privileged secret has leaked into the frontend
grep -rniE "service_role|sb_secret|SUPABASE_SERVICE|DATABASE_URL|password" js/ *.html

# 2. Every table really has RLS enabled (expect 21 / 21)
select count(*) filter (where rowsecurity) as with_rls, count(*) as total
  from pg_tables where schemaname = 'public';

# 3. Anonymous visitors hold no table privileges (expect 0)
select count(*) from information_schema.role_table_grants
 where grantee = 'anon' and table_schema = 'public';

# 4. Every SECURITY DEFINER function pins its search_path (expect 0 rows)
select p.proname from pg_proc p join pg_namespace n on n.oid = p.pronamespace
 where n.nspname = 'public' and p.prosecdef
   and not coalesce(array_to_string(p.proconfig,',') like '%search_path%', false);

# 5. Re-run the whole security suite (expect: ALL 127 SECURITY TESTS PASSED)
sudo bash tests/run.sh