Wrap the membership lookup in a security definer function — policies that select from memberships while memberships has its own policies are the classic 42517 infinite recursion trigger, and the wrapper is the standard way out.
2. Write: members post, and only into classrooms they belong to. The one that gets skipped:
Note WITH CHECK, not USING — USING is ignored for inserts, which is how "anyone can DM the classroom" ships by accident.
3. The write-leak test most apps never run: can a student UPDATE someone else's message? With RLS on and no FOR UPDATE policy they can't — but if an early "make it work" fix added FOR ALL ... USING (true), they can. Either leave update without a policy, or scope it: USING (auth.uid() = sender_id).
The one-line suite to keep beside the migration: member reads classroom → rows; non-member reads → zero; member inserts into another classroom → rejected. If any of the three fails in CI, the "private" classroom is a public one with extra steps.
Self-taught tech enthusiast and founder of DeTLeng (www.detleng.com). Exploring AI, automation, Python, and web development. Not a professional coder, but a passionate builder and lifelong learner.
Thanks, Cenk—I appreciate the thoughtful feedback!
I’m not a programmer; I’m building this around real teaching needs, with Codex helping me implement and test it. My role is to define the classroom workflows, make the product decisions, and test the system with teachers and students.
I asked Codex to review your points against our existing architecture. Text messages go through an authenticated database function that derives the sender from auth.uid() and checks class access, rather than allowing direct table inserts.
The local PostgreSQL checks passed for cross-class reads, cross-class sends, and unauthorized message updates. All 28 checks passed locally, though the deployed Supabase configuration still needs separate verification.
Your comment helped me better understand what “private” needs to mean in practice—and turned that understanding into additional regression checks. Thank you for taking the time to help improve the project!
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
Nice build — and "private" is the load-bearing word here, so worth flagging the three policies that decide whether the chat is actually private:
1. Read: only classroom members see messages.
Wrap the membership lookup in a
security definerfunction — policies that select frommembershipswhilemembershipshas its own policies are the classic42517 infinite recursiontrigger, and the wrapper is the standard way out.2. Write: members post, and only into classrooms they belong to. The one that gets skipped:
Note
WITH CHECK, notUSING—USINGis ignored for inserts, which is how "anyone can DM the classroom" ships by accident.3. The write-leak test most apps never run: can a student
UPDATEsomeone else's message? With RLS on and noFOR UPDATEpolicy they can't — but if an early "make it work" fix addedFOR ALL ... USING (true), they can. Either leave update without a policy, or scope it:USING (auth.uid() = sender_id).The one-line suite to keep beside the migration: member reads classroom → rows; non-member reads → zero; member inserts into another classroom → rejected. If any of the three fails in CI, the "private" classroom is a public one with extra steps.
Thanks, Cenk—I appreciate the thoughtful feedback!
I’m not a programmer; I’m building this around real teaching needs, with Codex helping me implement and test it. My role is to define the classroom workflows, make the product decisions, and test the system with teachers and students.
I asked Codex to review your points against our existing architecture. Text messages go through an authenticated database function that derives the sender from
auth.uid()and checks class access, rather than allowing direct table inserts.The local PostgreSQL checks passed for cross-class reads, cross-class sends, and unauthorized message updates. All 28 checks passed locally, though the deployed Supabase configuration still needs separate verification.
Your comment helped me better understand what “private” needs to mean in practice—and turned that understanding into additional regression checks. Thank you for taking the time to help improve the project!