Your First Supabase RLS Policy, Without Exposing Your Whole Database

Every week I audit a handful of AI-generated apps (VibeScan is the service behind this). The single most common "how is this in production" finding is a broken Row Level Security policy. Usually it's one of:

• RLS is disabled and the table is just public
• RLS is enabled but every policy is USING (true) — so it's still public, it just looks secure
• The policy scopes reads correctly, but the UPDATE policy lets users rewrite their own role = 'admin' column

This post is the RLS primer I wish I could hand to every Lovable / Bolt / v0 user on day one. By the end you'll have a correct policy for a "notes" table where each user sees only their own rows, you'll know how to verify it, and you'll recognize the three patterns that break it.

The model

You have a notes table. Each row belongs to one user. Your app should let a signed-in user:

1. Read only their own notes
2. Create new notes for themselves
3. Edit / delete only their own notes

Nobody should be able to read, create for, edit, or delete anyone else's notes. Not even anonymous users. Not even signed-in users who know how to open DevTools.

Here's the schema:

CREATE TABLE public.notes (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  body text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

Step 1 — turn RLS on

RLS is off by default. Turn it on explicitly:

ALTER TABLE public.notes ENABLE ROW LEVEL SECURITY;

The moment you run this, all queries against the table return zero rows — for every user, including authenticated. RLS is deny-by-default; you add policies to carve out what each role can do.

This is a good thing. If you turn RLS on and your app breaks, you now know exactly which tables need policies.

Step 2 — the four policies

One policy per CRUD verb. Each scopes the rows a user can touch.

-- READ: a user sees only their own notes.
CREATE POLICY "notes_select_own"
  ON public.notes FOR SELECT
  TO authenticated
  USING (user_id = auth.uid());

-- INSERT: a user can only create rows under their own user_id.
CREATE POLICY "notes_insert_own"
  ON public.notes FOR INSERT
  TO authenticated
  WITH CHECK (user_id = auth.uid());

-- UPDATE: a user can edit only their own notes, and can't change the owner.
CREATE POLICY "notes_update_own"
  ON public.notes FOR UPDATE
  TO authenticated
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

-- DELETE: a user can delete only their own notes.
CREATE POLICY "notes_delete_own"
  ON public.notes FOR DELETE
  TO authenticated
  USING (user_id = auth.uid());

The difference between USING and WITH CHECK is the subtle part:

• USING filters rows you can see for the operation (the "before" predicate).
• WITH CHECK validates rows you're trying to write (the "after" predicate).

On UPDATE you need both: USING to decide which rows you can target, WITH CHECK to stop you from flipping user_id to someone else's uid mid-update.

Step 3 — verify the policy actually works

Writing policies is easy. Verifying them is the step most people skip. Supabase ships set role — use it.

-- Pretend to be user A and insert a row
SET LOCAL ROLE authenticated;
SET LOCAL "request.jwt.claims" = '{"sub": "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa", "role": "authenticated"}';

INSERT INTO public.notes (user_id, body)
VALUES ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'hello from A');
-- ✅ succeeds

INSERT INTO public.notes (user_id, body)
VALUES ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'malicious row for B');
-- ❌ fails with: "new row violates row-level security policy"

SELECT * FROM public.notes;
-- returns only A's rows, not B's

If you skip verification, you're trusting your mental model. The mental model is wrong more often than you'd think.

The three patterns I keep seeing break

❌ 1. USING (true) — "looks like RLS, isn't RLS"

CREATE POLICY "authenticated users can read"
  ON public.notes FOR SELECT TO authenticated
  USING (true);

This is RLS-in-name-only. It lets every signed-in user read every row. If your app has open signup, every visitor can sign up with a throwaway email and read every other user's data.

Fix: USING (user_id = auth.uid()).

❌ 2. UPDATE policy without WITH CHECK

CREATE POLICY "notes_update_own"
  ON public.notes FOR UPDATE TO authenticated
  USING (user_id = auth.uid());
  -- no WITH CHECK

The user can update their own row (USING passes), and inside that update flip user_id to another user's id. Now that note belongs to someone else.

Same pattern bites harder on a profiles table that has a role or subscription_tier column. The user can UPDATE their own profile and set role = 'admin'.

Fix: add WITH CHECK (user_id = auth.uid()). On sensitive columns like role or subscription_tier, go further — split them into a separate table that only service_role can write.

❌ 3. Admin actions done from the client

The third pattern isn't an RLS mistake directly — it's a consequence of trying to get around RLS. Someone needs to be able to do something "admin-ish" (mark a payment as completed, promote a user), so they write a policy that lets any authenticated user do the write. Then everyone can.

Fix: keep the RLS policy strict, and move admin actions into a Supabase Edge Function that uses the service_role key. The service_role bypasses RLS by design — that's its job. Just make sure the function itself verifies the caller has the right permission before doing the write.

// edge function
import { createClient } from "npm:@supabase/supabase-js";

const supabase = createClient(
  Deno.env.get("SUPABASE_URL")!,
  Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!  // bypasses RLS
);

// verify the caller is authenticated *and* has admin role
const jwt = req.headers.get("Authorization")?.replace("Bearer ", "");
const { data: { user } } = await supabase.auth.getUser(jwt);
if (!user) return new Response("unauthorized", { status: 401 });
// check user.app_metadata.role === "admin" or a user_roles table lookup

// now you can do the write that regular authenticated users can't
await supabase.from("payments").update({ status: "completed" }).eq("id", paymentId);

The 10-minute self-audit

Run this against your own Supabase project before you ship:

1. For every public table, is RLS enabled? (SELECT tablename FROM pg_tables WHERE schemaname = 'public' and check each.)
2. For every enabled table, are there policies for SELECT / INSERT / UPDATE / DELETE?
3. Do any policies use USING (true) or WITH CHECK (true)?
4. On every UPDATE policy, is there a WITH CHECK that prevents ownership / role flipping?
5. Are there any columns on public-readable tables that shouldn't be readable (Stripe customer IDs, internal notes, moderator flags)? Columns with SELECT granted to authenticated are readable by every signed-in user whose policy match returns a row.

If you want this done automatically on your full codebase — including the 40 other security patterns that show up in AI-generated apps — that's what I built VibeScan for. $49, runs on your public GitHub repo, PDF report with severity-graded findings and copy-paste fixes. Most Lovable / Bolt / v0 / Cursor-built apps come back with 1 critical + 5-10 high severity findings on first scan, and roughly half of them are RLS patterns like the ones above.

Either way — if you've read this far and your app has a USING (true) policy somewhere, go fix it before you close this tab.