Lesson 4.7 · 55 minSecurity review

What you will have

Access rules on the leads table are written, applied and tested: an anonymous request for leads returns nothing, while the signed-in owner can read them. Claude's security review has been run, and every finding is fixed or accepted in writing in docs/security-review.md.

Video

The video for this lesson is not recorded yet.

Prompts used in this lesson

Part 5 — Write the access rules

Purpose: write and prove the access rules for customer data before the CRM is built.

Context: checkpoint lesson-4.6. Table public.leads has RLS enabled with no policies and grants to service_role only (see supabase/migrations/). Public sign-up is off, so the only authenticated user is the owner. The server writes leads with the secret key. Section 5 will read and edit leads as the signed-in owner. Do not open or print .env.local or any key.

Done looks like:
1. A new migration (created with `npx supabase migration new`) that: confirms RLS is enabled on public.leads; grants select, insert, update, delete on it to authenticated; creates four policies allowing the authenticated role to select, insert, update and delete; gives anon no grant and no policy. Do not apply it — I run `npx supabase db push` myself.
2. The /admin placeholder page shows "Leads in database: N", read with the signed-in owner's session through the server client — not with the secret key — so it proves the policy works.
3. A script `npm run test:anon-access` that, using only the public URL and publishable key, tries as an anonymous client to (a) read leads, (b) insert a lead, (c) update all leads, (d) delete all leads. As a control it counts leads with the server helper before and after. PASS if the control count is above zero and unchanged, (a) returns zero rows or a permission error, and (b) is refused. One plain line per result; never print row contents or keys.
4. docs/architecture.md gets an "Access rules" table.

Constraints: check current Supabase docs for policy and grant syntax. Do not commit yet.

Before you report: show me the migration SQL in full.

Part 6 — Look for leaked keys

Purpose: check for the second common mistake, leaked keys.

Done looks like: a yes/no report with evidence: (1) Is any .env file tracked by Git, now or in the history? (2) Does any tracked file or past commit contain something that looks like a Supabase secret key, a Resend key, a Telegram bot token or a Turnstile secret other than Cloudflare's published test keys? (3) After a production build, does the browser bundle contain the secret key prefix or the name of a server-only variable? (4) Does any NEXT_PUBLIC_ variable hold something that should be secret?

Constraints: never print a key or a suspected key; report file, commit and line only. Change nothing.

Before you report: run the production build so check 3 uses fresh output.

Part 7 — Run Claude's security review

Purpose: a full security audit of the backend built in section 4, not only today's changes, with a written record.

Context: lead endpoint with validation, honeypot, Turnstile and a firewall rate limit; Supabase with RLS; email and Telegram notifications; admin login. Read docs/architecture.md, supabase/migrations/, everything under app/api and app/admin, the proxy file and the notification module. Include the results of the /security-review you just ran.

Done looks like: docs/security-review.md with the date and a table: finding, where, severity, how it could be exploited, recommended fix, and an empty Decision column for me. Check at least: open tables or policies; secrets reaching the browser, logs or Git; every /admin page and /api/admin endpoint calling the auth helper; input handling in /api/leads and the notification templates; what errors and logs reveal; response headers; dependencies with known vulnerabilities (run the package manager's audit).

Constraints: do not fix anything yet. Do not print secrets. Say "none found" for an area rather than inventing a finding.

Before you report: list the commands and tests you ran as evidence.

Do along

Pause the video where a step says so and do it on your own project.

1. Pause after Part 5. Give Claude the prompt from Part 5. Read the migration: no anon, four policies for authenticated.
2. Run npx supabase db push, then npm run test:anon-access.
3. Sign in at /admin and compare the lead count with the control count.
4. Pause after Part 6. Give Claude the leaked-keys prompt. Rotate any key it flags.
5. Pause after Part 7. Commit without pushing. Run /security-review, then give Claude the audit prompt.
6. Fill the Decision column for every finding: "Fixed", or "Accepted" with your reason and date.
7. Rerun every test script from this section, commit, tag and push.

Check your work

1. Run npm run test:anon-access. Expected: control count above zero; anonymous read returns zero rows or "permission denied"; insert refused; control count unchanged; PASS.
2. Sign in at /admin. Expected: "Leads in database: N", with N equal to the control count.
3. Open docs/security-review.md. Expected: every finding has a decision.
4. Run npm run test:bot -- https://<your-domain> and npm run test:signup-closed. Expected: both still PASS.

Common problems

• /security-review stops with ambiguous argument 'origin/HEAD...' → your clone has no record of GitHub's default branch → run git fetch origin, then git remote set-head origin --auto, and run the command again.
• /security-review finds nothing to review → everything is already pushed → run it before pushing; use the audit prompt for the whole project.
• The admin page shows "Leads in database: 0" or an error → the grant to authenticated or the select policy is missing or not applied → run npx supabase migration list; give Claude the exact error.
• The anonymous read returns rows → an open policy or RLS disabled → stop, do not deploy anything else; tell Claude: "anon can read leads; show me every policy and grant on public.leads and write a migration that removes anon access", then test again.
• Claude proposes to turn RLS off to fix an error → refuse → say: "RLS stays on; fix the policy or grant instead".

Homework

About 30 minutes. Tasks 1 and 2 add text to docs/security-review.md; commit them without a tag.

1. Where personal data lives. Add a section with that title to docs/security-review.md: a table with one row for each place a customer's details end up in your business (the leads table, your inbox, the Telegram chat, your email service, anything else you use). Columns: what is there, who can see it, how you would delete it. Done when: every place has all three cells filled, in your own words.
2. A date for every accepted risk. For each finding you marked "Accepted", add when you will look at it again: a date, or an event such as "when I add a staff account". Done when: no accepted finding is without one.
3. A deletion drill. In the Supabase table view, delete exactly one TEST lead by hand, as if that person had asked you to. Then sign in at /admin. Done when: "Leads in database" is one lower than before and npm run test:anon-access still ends in PASS. Keep the other test rows.

Save your work

git add -A
git commit -m "Lesson 4.7: access rules, tests and security review"
git tag lesson-4.7
git push
git push --tags