ALL 127 SECURITY TESTS PASSED — executed against PostgreSQL 17.11
What was tested, how to re-run it yourself, the complete register of results, and an explicit list of the things that were not tested.
cd trading-academy
sudo bash tests/run.shThe runner creates a scratch database, installs a small local emulation of the Supabase
auth schema (tests/00_shim.sql), applies supabase/schema.sql
unmodified, loads fixtures, and executes 127 assertions. The last line is either
ALL 127 SECURITY TESTS PASSED or a table of failures. Results are written to
tests/last-run-results.txt.
tests/00_shim.sql against your real Supabase project.
It fabricates an auth schema so the production schema can be tested offline. Supabase
already has a real one. The runner only ever touches a local scratch database.
Each test switches to a specific user with tests.login(), runs one statement, and
records what happened — inside a sub-transaction that is always rolled back, so tests cannot
contaminate one another:
-- A student must not be able to promote themselves.
select tests.login('student1');
select tests.run(
'B. Role escalation',
'student cannot promote self to admin',
$$update public.profiles set role = 'admin' where id = tests.uid('student1')$$,
'denied');The fourth argument is the expected outcome: denied, allowed, or an exact
row count. A statement that silently affects zero rows counts as denied; one that raises
42501 also counts as denied; one that succeeds when it should not is a failure.
begin; … rollback;. The rollback discarded the
result rows as well as the test effects, so 8 of 11 suites vanished from the report while the summary
line still printed green. If you adapt this harness, always check the assertion count, not
just the summary.
Serve the folder and drive it with Playwright. The repository does not vendor a browser; install one on demand:
python3 -m http.server 8080 &
npm i playwright && npx playwright install chromium
# then drive http://localhost:8080 — see section 5 for what was covered| Check | Command | Result |
|---|---|---|
| No service-role key, secret key or database URL in anything the browser downloads | grep -rniE "service_role|sb_secret|SUPABASE_SERVICE|DATABASE_URL" js/ *.html |
0 matches |
| No hard-coded password in the frontend (other than the documented offline demo password) | grep -rniE "password\s*[:=]\s*\"[^\"]{4,}" js/*.js |
0 matches |
| Every public table has RLS enabled | pg_tables.rowsecurity | 21 / 21 |
| Policy count | pg_policies | 47 |
| Anonymous role holds no table privilege | information_schema.role_table_grants | 0 grants |
Every SECURITY DEFINER function pins search_path |
pg_proc.proconfig | 28 / 28 |
bootstrap_first_admin is not executable by browser roles |
explicit REVOKE + suite B test | revoked |
| Schema applies cleanly and is idempotent | applied twice to a fresh database | exit 0 both times |
| Both seed scripts apply cleanly and are idempotent | each applied twice to a fresh database | no duplicate rows |
| All 36 lessons have a matching database answer key | left join in seed_curriculum.sql | 0 missing |
| Every JavaScript file parses | node --check js/*.js | 22 / 22 |
| Suite | What it proves | Assertions |
|---|---|---|
| A — Anonymous | A signed-out visitor reads nothing and can call no RPC | 6 |
| B — Role escalation | Nobody can raise their own privileges; admins can do their job; the bootstrap function is unreachable from a session | 10 |
| C — Profile visibility | People see only the people they legitimately share a class or school with | 9 |
| D — Classes & enrolment | Class membership and teacher authorisation gate everything; ID guessing fails; only admins enrol | 14 |
| E — Assignments | Drafts hidden, per-student targeting honoured, only authorised teachers may author | 16 |
| F — Submissions | Ownership enforced on write, visibility scoped on read, no submitting as somebody else | 14 |
| G — Grades | Unreleased grades invisible to students; only class teachers may grade; no self-grading | 12 |
| H — Private learner data | Journals, paper trades and practice attempts are readable by their owner and nobody else — including admins | 10 |
| I — Quiz integrity | Answer keys unreadable, scores computed server-side, forged scores rejected | 15 |
| J — Admin surface & audit | Admin powers stop at the school boundary; audit rows cannot be altered or deleted by anyone | 14 |
| K — Class join codes | Codes validated in the database, revocation is immediate, a code enrols only the caller | 7 |
Every assertion, exactly as recorded in tests/last-run-results.txt by the last run.
| Assertion | Result |
|---|---|
| A. Anonymous / unauthenticated 6 assertions | |
| anon cannot read profiles | PASS |
| anon cannot read classes | PASS |
| anon cannot read grades | PASS |
| anon cannot read assignments | PASS |
| anon cannot call submit_quiz RPC | PASS |
| anon cannot call admin_create_class RPC | PASS |
| B. Role escalation 10 assertions | |
| student cannot promote self to admin | PASS |
| student cannot promote self to teacher | PASS |
| student cannot activate own pending status | PASS |
| student cannot attach self to a school | PASS |
| teacher cannot promote self to admin | PASS |
| student cannot change another user's role | PASS |
| admin cannot touch another school's user | PASS |
| admin CAN activate a pending user (allowed) | PASS |
| student cannot execute bootstrap_first_admin | PASS |
| student may edit own display name (allowed) | PASS |
| C. Profile visibility 9 assertions | |
| student sees own profile | PASS |
| student cannot see unrelated student in other class | PASS |
| student cannot see a student at another school | PASS |
| student can see their own teacher | PASS |
| teacher can see enrolled student | PASS |
| teacher cannot see student of unrelated class | PASS |
| teacher cannot see other school's student | PASS |
| admin sees all profiles in own school | PASS |
| admin sees NO profiles from another school | PASS |
| D. Class visibility & enrollment 14 assertions | |
| student sees enrolled class | PASS |
| student cannot see unenrolled class (ID guess) | PASS |
| student cannot see other school class (ID guess) | PASS |
| teacher sees only authorized class | PASS |
| admin sees own school classes | PASS |
| admin cannot see other school classes | PASS |
| student cannot self-enroll into a class | PASS |
| student cannot enroll a friend | PASS |
| teacher cannot grant self a new class | PASS |
| teacher cannot enroll students (admin-only) | PASS |
| student cannot create a class | PASS |
| teacher cannot create a class (admin-only) | PASS |
| admin CAN create a class (allowed) | PASS |
| admin cannot create class in another school | PASS |
| E. Assignment access control 16 assertions | |
| enrolled student sees published assignment | PASS |
| student cannot see DRAFT assignment (ID guess) | PASS |
| student cannot see other class assignment (ID guess) | PASS |
| student3 cannot see class1 assignment (ID guess) | PASS |
| other-school student cannot see assignment | PASS |
| targeted assignment visible to the target | PASS |
| targeted assignment hidden from non-target classmate | PASS |
| student cannot create an assignment | PASS |
| student cannot publish the draft exam | PASS |
| student cannot delete an assignment | PASS |
| teacher CAN create assignment in own class (allowed) | PASS |
| teacher cannot create assignment in unrelated class | PASS |
| teacher cannot create assignment in another school | PASS |
| teacher cannot edit unrelated class assignment | PASS |
| teacher cannot move assignment into unrelated class | PASS |
| teacher cannot target a non-enrolled student | PASS |
| F. Submissions 14 assertions | |
| student sees own submission | PASS |
| classmate cannot read another student's submission | PASS |
| unrelated student cannot read submission | PASS |
| authorized teacher reads class submissions | PASS |
| unrelated teacher cannot read submissions | PASS |
| student CAN submit to a visible assignment (allowed) | PASS |
| student cannot submit to unrelated assignment | PASS |
| student cannot submit to a draft assignment | PASS |
| student cannot forge submission as another student | PASS |
| student cannot edit someone else's submission | PASS |
| student cannot revert submitted work to draft | PASS |
| student cannot edit already-submitted content | PASS |
| student cannot delete a submitted attempt | PASS |
| late submission blocked when allow_late = false | PASS |
| G. Grades & feedback 12 assertions | |
| student cannot see UNRELEASED grade | PASS |
| authorized teacher sees the grade | PASS |
| unrelated teacher cannot see the grade | PASS |
| classmate cannot see another student's grade | PASS |
| student cannot award themselves points | PASS |
| student cannot insert a grade row | PASS |
| unrelated teacher cannot grade the submission | PASS |
| authorized teacher CAN release the grade (allowed) | PASS |
| unrelated teacher cannot bulk-release grades | PASS |
| student cannot bulk-release their own grades | PASS |
| student sees grade AFTER teacher releases it | PASS |
| classmate still cannot see released grade | PASS |
| H. Private learner data 10 assertions | |
| student reads own trading journal | PASS |
| classmate cannot read the journal | PASS |
| teacher cannot read a student journal | PASS |
| admin cannot read a student journal | PASS |
| student cannot write a journal entry for someone else | PASS |
| student cannot forge a paper trade for someone else | PASS |
| student cannot read another student's paper trades | PASS |
| student cannot read another student's trading account | PASS |
| student cannot trade on another student's account | PASS |
| student CAN trade on own account (allowed) | PASS |
| I. Quiz integrity 15 assertions | |
| student cannot read quiz answer keys | PASS |
| teacher CAN read answer keys (allowed) | PASS |
| student cannot tamper with an answer key | PASS |
| student cannot insert a lesson | PASS |
| student CAN score a quiz via RPC (allowed) | PASS |
| official quiz RPC auto-grades for an enrolled student (allowed) | PASS |
| ...and the auto-graded submission exists | PASS |
| ...and the student can see the released auto-grade | PASS |
| ...and a classmate cannot see that auto-grade | PASS |
| quiz RPC refuses an unrelated assignment id | PASS |
| student cannot forge a quiz attempt for a classmate | PASS |
| student cannot rewrite history of quiz attempts | PASS |
| student cannot read classmate progress | PASS |
| teacher CAN read own student's progress (allowed) | PASS |
| unrelated teacher cannot read that progress | PASS |
| J. Admin surface & audit 14 assertions | |
| student cannot read audit logs | PASS |
| teacher cannot read audit logs | PASS |
| admin CAN read own school audit logs (allowed) | PASS |
| admin cannot read another school's audit logs | PASS |
| student cannot call admin_create_class | PASS |
| teacher cannot call admin_create_class | PASS |
| student cannot call admin_invite | PASS |
| teacher cannot invite themselves as admin | PASS |
| student cannot read school report | PASS |
| admin CAN read school report (allowed) | PASS |
| unrelated teacher cannot read class analytics | PASS |
| authorized teacher CAN read class analytics (allowed) | PASS |
| student cannot transfer themselves between classes | PASS |
| student cannot read invitations | PASS |
| K. Class join codes 7 assertions | |
| student cannot enable a class join code | PASS |
| teacher CAN rotate own class code (allowed) | PASS |
| invalid class code is rejected | PASS |
| revoked/inactive class code is rejected | PASS |
| valid code enrolls a pending student (allowed) | PASS |
| teacher cannot join a class with a code | PASS |
| other-school student cannot use the code | PASS |
Driven with Playwright against Chromium on the real application, in offline demo mode, signing in as genuine student, teacher and administrator accounts. These exercise the user interface; the database suite above is what proves the permissions.
| Role | Routes exercised | Result |
|---|---|---|
| Student | dashboard, classes, assignments, grades, academy, practice, trading, journal, tools, progress, settings | 11 / 11 |
| Teacher | dashboard, classes, assignments, new, submissions, gradebook, announcements, analytics, academy, practice, trading, tools, settings | 13 / 13 |
| Admin | dashboard, users, teachers, students, classes, enrollments, assignments, reports, audit, settings | 10 / 10 |
Run on 14 October 2026 with Chromium 1134 via playwright-core@1.47.2. The three
files are in tests/browser/ and tests/browser/README.md explains how to
re-run them.
| File | What it exercises | Result |
|---|---|---|
01-math-pages.js |
Sign-in, Mathematics nav group, catalogue (16 cards), course page (9 units + KPIs), unit page, unit-scoped practice, practice runner: wrong answer rejected, hint, worked solution on skip, correct answer accepted, attempt written to the backend, attempt present in the append-only log, progress survives navigation, grapher renders and re-renders, invalid syntax flagged inline, teacher refused an unrelated student | 28 / 28 |
02-lessons.js |
Unit lesson list with written/outline badges, a written lesson end to end (objective, worked examples, steps, inline MathML, mounted SVG figure with a drawn curve, key idea, common mistake, vocabulary, practice), previous/next navigation, the honest notice on an unwritten lesson, standards still listed there, out-of-range lesson index, light theme | 21 / 21 |
03-teacher-report.js |
A student answers three items for real, then a teacher opens Class Progress and sees
them: KPI row, per-student table, class switcher. A student navigating to
#/mathclass is refused and has no link to it. |
11 / 11 |
supabase/schema-math.sql — those need a real Postgres and were not run. See
section 6.
| Check | Result |
|---|---|
| Lesson page renders its chart figure | PASS |
| Interactive exercise marks an answer and explains it | PASS |
| Lesson quiz has five questions and scores correctly | PASS |
| Per-answer explanations appear for all five after submitting | PASS |
| Draft saves to the backend and reports the save time | PASS |
| Submitting changes the status and timestamps it | PASS |
| Teacher sees the new submission in the grade queue | PASS |
| Grading dialog shows the student's actual work | PASS |
| Grade saves, releases, and reaches the student's Grades page | PASS |
| Gradebook matrix builds from real grades | PASS |
| Teacher creates and publishes an assignment; student sees it | PASS |
| Teacher posts an announcement | PASS |
| Class code rotates; the new code enrols a student | PASS |
| A revoked / unknown class code is rejected with a clear message | PASS |
| Paper-trading order fills and changes cash, equity and positions | PASS |
| Advancing the simulated clock moves prices and evaluates stops | PASS |
| Journal entry is created and the statistics recompute | PASS |
| Position-size calculator returns the arithmetically correct answer | PASS |
| Admin approves a pending signup | PASS |
| Audit log is populated and visible to the admin | PASS |
| Student is refused the teacher gradebook route | PASS |
| Student is refused the admin audit route | PASS |
| Guessing another class's id returns a refusal, not data | PASS |
| Light theme toggles and every chart repaints legibly | PASS |
| Mobile 390 px: hamburger appears and the drawer opens | PASS |
| All 37 chart figures render with non-blank pixels and no error | PASS |
| Check | Result |
|---|---|
| 36 lessons, 0 structural errors, 0 duplicate slugs | PASS |
| Level distribution 8 / 5 / 6 / 5 / 6 / 6 | PASS |
| Every lesson has a visual aid | 36 / 36 |
| Every referenced chart id has a builder | 36 / 36 |
| Every quiz has exactly 5 questions, 4 options and an explanation per answer | PASS |
| All 19 numeric exercises accept the intended answer and reject wrong ones | PASS |
| Next-lesson chain is correct and terminates | PASS |
| Market engine: 0 invalid bars across 13 symbols × 5 timeframes; deterministic | PASS |
| Not tested | Why | What to do about it |
|---|---|---|
| The policies running on a real hosted Supabase project | No Supabase account was created for this build. The identical schema.sql was
exercised on PostgreSQL 17.11 with an emulated auth schema. |
After deploying, re-run the checklist in section 7 with two real browser sessions. |
| Supabase Auth itself — signup, confirmation email, password reset, JWT expiry, refresh | Auth is a hosted service and was not reachable from the build environment. | Walk through steps 12–14 and the password-reset flow in SETUP.html. |
supabase/schema-math.sql — its 8 RLS policies, 3 guard triggers and
4 functions |
Not executed against any PostgreSQL instance. The scratch database used
for the original 127 assertions was lost when the environment reset, and
sudo was broken in this session, so Postgres could not be restarted.
The file was written by following the patterns in schema.sql and reviewed
by reading, nothing more. |
Run it on your project and check the NOTICE reads
3 / 3 tables, 8 / 8 policies, 5 / 5 functions. Then sign in as a teacher who
does not teach a student and confirm
select * from math_skill_progress returns none of that student's rows. |
| Whether a student can forge a correct answer | Not tested, because it is a known and accepted property, not a bug. Practice is graded in
the browser, so math_record_attempt(p_correct => true) can be called
directly from the console. |
Nothing — but never use practice figures for grades. Graded work goes through
submit_quiz(), which scores inside Postgres. |
| Supabase Storage permissions | No bucket is used; file attachments are not implemented. | Not applicable unless you add uploads — in which case write bucket policies first. |
| Rate limiting and brute-force resistance | No custom limiting is implemented; only Supabase's gateway defaults apply. | If the deployment is public, add an Edge Function or a Cloudflare rule. |
| Load and concurrency | Correctness was the objective, not throughput. | A class of 30 submitting at once is well inside the free tier, but it was not measured. |
| Formal accessibility audit (screen reader, full WCAG pass) | Keyboard navigation, focus traps, aria-live regions, landmarks and colour contrast
were implemented and spot-checked, but no assistive technology was driven end to end. |
Test with NVDA or VoiceOver before using it with students who rely on one. |
| Browsers other than Chromium | Only Chromium was available in the build environment. | The code uses no Chromium-only API, but check Safari and Firefox before rollout. |
| Penetration testing by a third party | Out of scope. | Recommended before holding anything sensitive. |
Do this after step 27 of the setup guide, with two different browsers open side by side.
await API.grades.all() — the unreleased grade must not be in the result.await API.raw.from('profiles').update({role:'admin'}).eq('id',(await API.me()).id).
It must return an error.await API.raw.from('lesson_answer_keys').select('*').
It must return zero rows.await API.raw.from('trading_journals').select('*') and confirm
every row belongs to them. Repeat as the teacher and as the admin: both must see only their own.#/classes/<that-id>. You must get the refusal page.If any of these behaves differently from the description, stop and investigate before putting real student data in the system.