Row-Level Security — why your data can't leakConcept

Row-Level Security is the per-row bouncer inside Postgres. The Focaccia vote page shipped its database key to every dinner guest's phone, and four lines of RLS policy are the entire reason none of them could delete the votes.

Classic database permissions work per-table: a user either can or can't touch the votes table at all. Row-Level Security (RLS) moves the decision inside the table — every read, insert, update, or delete gets checked against per-operation rules called policies, row by row. The crucial behavior: the moment you run ALTER TABLE ... ENABLE ROW LEVEL SECURITY, the table flips to default-deny. With zero policies, the API sees an empty table and writes nothing. Policies don't restrict an open table; they punch specific holes in a sealed one.

Supabase gives every project two keys, and the difference is the whole security model. The anon key is a public identity — it's designed to be embedded in web pages, and every request made with it is filtered through the RLS policies. The service_role key is the master key: it bypasses RLS entirely. That's why the anon key sitting in vote.html is fine, but the service key appearing in any browser-delivered file would mean every guest could wipe the table. Public key + tight policies is the intended pattern, not a compromise.

In your workspace this lives at JARBUS/projects/plaud-2ee4b9a7e2b4988b905fd8b897a698e2/live/setup.sql. The votes table gets exactly two policies — anon_insert and anon_select — and deliberately no UPDATE or DELETE policy. That absence is load-bearing: it's why the README says 'anon DELETE blocked by design (clear via SQL Editor)' and why resetting between dinner events requires you, in the dashboard, rather than anyone with the URL.

Worked example

This is the real gate, verbatim from live/setup.sql, plus the query that shows what's actually live in Postgres.

-- live/setup.sql — the actual Focaccia Vote gate
ALTER TABLE votes ENABLE ROW LEVEL SECURITY;

CREATE POLICY "anon_insert" ON votes
  FOR INSERT WITH CHECK (true);

CREATE POLICY "anon_select" ON votes
  FOR SELECT USING (true);

-- Inspect what's live (run in the Supabase SQL Editor):
SELECT policyname, cmd, qual, with_check
FROM pg_policies
WHERE tablename = 'votes';

• ENABLE ROW LEVEL SECURITY flips votes to default-deny: from this line on, every request through the public API touches zero rows unless a policy explicitly grants it.
• anon_insert — FOR INSERT WITH CHECK (true): anyone may add a row, and the condition 'true' means no restriction on the row's contents. Deliberately wide open, because the whole point was anonymous dinner-party voting.
• anon_select — FOR SELECT USING (true): anyone may read every row. The live tally dashboard needs this to show results in real time.
• What's NOT there is the security: no FOR UPDATE and no FOR DELETE policy exists, so those operations match zero rows for the anon key. The rights you don't grant are the lock.
• pg_policies is Postgres's built-in catalog view — the live source of truth. The .sql file records intent; pg_policies shows what's actually enforced right now.

Gotchas

• RLS denials on SELECT/DELETE are SILENT — you get 0 rows, not an error. A query that 'works but returns nothing' very often means RLS is on and no policy matches. Only INSERT/UPDATE WITH CHECK violations fail loudly.
• The SQL Editor runs as postgres, which bypasses RLS — testing there without SET LOCAL ROLE anon proves nothing about what the public can do. That's why step 3 sets the role first.
• Never let the service_role key reach a browser, a git repo, or an HTML file. It skips RLS entirely; one leak undoes every policy. The anon key is the only key that belongs in vote.html.
• Enabling RLS with no policies seals the table completely — if a Supabase-backed prototype suddenly shows empty data through the API, check pg_policies before debugging your code.
• WITH CHECK (true) is right for anonymous party voting, but it allows unlimited inserts from anyone — for anything with accounts, policies should compare auth.uid() to an owner column instead.