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 15 October 2026 with Chromium 1134 via playwright-core@1.47.2. The four
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, and the multiple-choice runner: four options rendered and lettered A–D, the text box hidden, clicking an option selects without grading, the right option accepted and highlighted, options locked afterwards, a wrong option refused, an empty submission asking for a pick rather than scoring a miss, text items keeping their input, and Geometry / Calculus / Competition units now offering practice | 46 / 46 |
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 |
04-all-skills.js |
Renders every one of the 150 practice skills three times each in the live browser (450 generated items) and checks each one states a prompt, is actually answerable (options for a choice item, a visible box otherwise), raises no MathML error span, ships a worked solution, and has an answer key the in-page grader marks correct. Also asserts all 102 catalogue units map to at least one skill. | 9 / 9 |
05-features.js |
Command palette: opens on Ctrl K, lists only the pages the signed-in role's own
sidebar rendered (a student is not offered Gradebook), ranks courses / units / skills /
formulas, folds accents so lhopital finds L'Hôpital, navigates on
Enter, closes on Esc, g r jumps to Review and
? opens the shortcut sheet.
Worksheet builder: sheet plus answer key, problem count, working space, steps toggle, and
seed determinism — a new seed changes the whole sheet, the old seed reproduces it
byte for byte; every unit offered as enabled in Algebra 1, Geometry and AP Statistics
actually fills a sheet. Formula reference: 192 entries in 9 sections, >120 rendered as real
MathML with no error spans, keyword search, subject tabs, empty state, and the palette landing
on a pre-searched formula. Roles: a teacher can build worksheets and read the standards map but
is refused #/review. |
36 / 36 |
06-review.js |
Review queue from an empty start (true empty state, disabled button), then seven skills
answered for real through API.math.record. Asserts the exact bucket counts
(2 due, 3 under 70 %, 5 scheduled, 143 untouched), that the two five-in-a-row skills read
as held, that a one-day item says “tomorrow” and a mastered one
“in 21 days”, that Start review drops you into a real practice item, and that
the Leitner ladder is exactly 0/1/2/4/7/21 days. Standards map: >150 codes grouped into
domains, tinted by that same history, the summary separating codes referenced from the
catalogue count, and the per-standard dialog. |
23 / 23 |
07-secrets.js |
Hidden panels: one code, one switch, one page. Nothing is advertised before the
code — #/secrets says only “nothing here”. The Konami code
unlocks it, adds a single nav entry, and opens a four-tab page. The terminal does real work
(eval 2^10 + sqrt(144) = 1036, an identity reported as equivalent,
factor 1729 = 7 × 13 × 19, a generated
item graded, unknown commands rejected) and states that the role is enforced by the database,
not the prompt; graph hands a function to the grapher. The arcade lights its 32
turn pips. The Lore tab explains the 32, the 1729 and the $0. The old #/terminal
and #/arcade addresses redirect into the right tab. The switch in Settings hides
the nav entry and every panel, says it is off rather than gone, restores instantly, and does
not strand you if you flip it while standing inside. The code does not fire from inside
a form field, and a fresh browser profile starts locked again. |
49 / 49 |
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.