Supabase RLS patterns for vibe-coded apps: every common shape with the SQL.

2026-05-09 · vibecheck team · 16 min read · Database · RLS · Pillar

Row-Level Security is what makes Supabase's "your client talks to the database directly" architecture safe. The anon key in the bundle is supposed to be public; the security boundary is the policies. When the policies are wrong, the boundary is gone — see the Moltbook breach for what that looks like in production.

This is the reference. Each section gives the pattern's name, when to use it, the policy SQL, the gotcha that always trips people up, and the test query that proves it works. Examples use Supabase but the patterns transfer to any Postgres + RLS setup.

Refresher: how RLS evaluates

RLS is a per-table feature. After ALTER TABLE foo ENABLE ROW LEVEL SECURITY, every query against that table is filtered by whatever policies you've created — and if no policy applies, the query returns nothing (default-deny). Each policy targets one or more operations:

• SELECT — controls which rows a query can see. Uses the USING expression.
• INSERT — controls which rows can be inserted. Uses WITH CHECK against the proposed row.
• UPDATE — controls both: USING on the existing row (must be allowed to read it to update it) and WITH CHECK on the proposed new state.
• DELETE — uses USING on the existing row.
• ALL — shorthand for all four. Tempting but usually wrong; UPDATE-with-CHECK semantics differ from SELECT.

Critical: USING determines visibility; WITH CHECK determines what's allowed to be written. Confusing them is the most common policy bug after "forgetting to enable RLS at all."

Inside policies, you have:

• auth.uid() — the UUID of the authenticated user, or NULL for anonymous.
• auth.role() — usually 'anon' or 'authenticated'.
• auth.jwt() — the full JWT payload (use auth.jwt() -> 'app_metadata' -> 'role' for custom claims).

1. Per-user ownership

The most common shape. Every row belongs to one user; that user reads, writes, deletes; no one else does anything.

Schema:

CREATE TABLE public.notes (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id),
  title text NOT NULL,
  body text,
  created_at timestamptz DEFAULT now()
);
ALTER TABLE public.notes ENABLE ROW LEVEL SECURITY;

Policies:

CREATE POLICY "notes_select_own" ON public.notes
  FOR SELECT TO authenticated
  USING (auth.uid() = user_id);

CREATE POLICY "notes_insert_own" ON public.notes
  FOR INSERT TO authenticated
  WITH CHECK (auth.uid() = user_id);

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

CREATE POLICY "notes_delete_own" ON public.notes
  FOR DELETE TO authenticated
  USING (auth.uid() = user_id);

The gotcha. The WITH CHECK on the UPDATE policy is what stops a user from changing user_id to someone else's UUID and then losing access to their own row (or worse, transferring ownership to themselves from someone else's row, if they could ever read it). Never write an UPDATE policy with only USING.

Test:

-- As user A:
INSERT INTO notes (user_id, title) VALUES ('<A's UUID>', 'mine');     -- ✓
INSERT INTO notes (user_id, title) VALUES ('<B's UUID>', 'forge');   -- denied (WITH CHECK fails)
UPDATE notes SET user_id = '<B's UUID>' WHERE id = '<A's note>';     -- denied (WITH CHECK fails)
SELECT * FROM notes;                                                  -- only A's rows

2. Public-readable with owner-write

Profile pages, blog posts, public artifacts. Everyone reads; only owner writes.

Schema: add a boolean is_public if some rows are private:

CREATE TABLE public.posts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id),
  title text NOT NULL,
  body text,
  is_public boolean NOT NULL DEFAULT false,
  created_at timestamptz DEFAULT now()
);
ALTER TABLE public.posts ENABLE ROW LEVEL SECURITY;

Policies:

-- Anyone (including anon) can read public rows; owners can read their own.
CREATE POLICY "posts_select_public" ON public.posts
  FOR SELECT TO anon, authenticated
  USING (is_public = true OR auth.uid() = user_id);

-- Only authenticated users can insert; must claim themselves as owner.
CREATE POLICY "posts_insert_own" ON public.posts
  FOR INSERT TO authenticated
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "posts_update_own" ON public.posts
  FOR UPDATE TO authenticated
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "posts_delete_own" ON public.posts
  FOR DELETE TO authenticated
  USING (auth.uid() = user_id);

The gotcha. Granting SELECT to anon means you've made an explicit decision to publish data via the anon key. Don't grant SELECT to anon on tables that hold any private data — even if the policy filters correctly today, future schema changes can leak. Best practice: every public-readable table is its own table, separate from anything sensitive.

Also: when the row is private (is_public = false), the policy still lets the owner see it. If you don't want owner-readable drafts (e.g., a "publish or it doesn't exist" workflow), drop the OR auth.uid() = user_id from the SELECT policy.

3. Tenant-scoped via membership table

Multi-tenant apps. Users belong to teams/orgs/workspaces; data belongs to teams; users read/write team data they're members of.

Schema:

CREATE TABLE public.organizations (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL
);

CREATE TABLE public.memberships (
  user_id uuid NOT NULL REFERENCES auth.users(id),
  organization_id uuid NOT NULL REFERENCES organizations(id),
  role text NOT NULL CHECK (role IN ('owner', 'admin', 'member', 'viewer')),
  PRIMARY KEY (user_id, organization_id)
);

CREATE TABLE public.documents (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id uuid NOT NULL REFERENCES organizations(id),
  created_by uuid NOT NULL REFERENCES auth.users(id),
  title text NOT NULL,
  body text
);

ALTER TABLE public.organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.memberships ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.documents ENABLE ROW LEVEL SECURITY;

Policies on documents:

CREATE POLICY "documents_select_member" ON public.documents
  FOR SELECT TO authenticated
  USING (
    organization_id IN (
      SELECT organization_id FROM memberships WHERE user_id = auth.uid()
    )
  );

CREATE POLICY "documents_insert_member" ON public.documents
  FOR INSERT TO authenticated
  WITH CHECK (
    organization_id IN (
      SELECT organization_id FROM memberships WHERE user_id = auth.uid()
    )
    AND created_by = auth.uid()
  );

-- Edit if you're a member with role admin/owner OR you're the creator.
CREATE POLICY "documents_update_writer" ON public.documents
  FOR UPDATE TO authenticated
  USING (
    organization_id IN (
      SELECT organization_id FROM memberships
      WHERE user_id = auth.uid() AND role IN ('owner', 'admin')
    )
    OR created_by = auth.uid()
  )
  WITH CHECK (
    organization_id IN (
      SELECT organization_id FROM memberships WHERE user_id = auth.uid()
    )
    AND created_by = auth.uid()
  );

And memberships:

-- Members can see who else is in their org.
CREATE POLICY "memberships_select_org" ON public.memberships
  FOR SELECT TO authenticated
  USING (
    organization_id IN (
      SELECT organization_id FROM memberships WHERE user_id = auth.uid()
    )
  );

-- Only owners/admins add new members.
CREATE POLICY "memberships_insert_admin" ON public.memberships
  FOR INSERT TO authenticated
  WITH CHECK (
    organization_id IN (
      SELECT organization_id FROM memberships
      WHERE user_id = auth.uid() AND role IN ('owner', 'admin')
    )
  );

The gotcha. The WITH CHECK on the UPDATE policy must restrict organization_id too — otherwise an admin in org A can move documents into org B by updating the organization_id field. The check above requires the new organization_id to still be one the user is a member of. Even tighter: forbid changing organization_id in updates entirely (drop it from the SET clause server-side).

Performance note. The organization_id IN (SELECT ...) subquery runs for every row evaluated. Make sure memberships(user_id) is indexed (it's part of the PK so it is, but if you change the PK shape, double-check). For tables with millions of rows, consider materializing the user's org list into a JWT custom claim and reading it via auth.jwt() instead.

4. Profile pattern (read-public, write-self)

User profiles. Anyone can read; only the user can edit their own.

CREATE TABLE public.profiles (
  id uuid PRIMARY KEY REFERENCES auth.users(id),
  display_name text NOT NULL,
  bio text,
  avatar_url text,
  updated_at timestamptz DEFAULT now()
);
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

CREATE POLICY "profiles_select_all" ON public.profiles
  FOR SELECT TO anon, authenticated
  USING (true);

CREATE POLICY "profiles_insert_self" ON public.profiles
  FOR INSERT TO authenticated
  WITH CHECK (auth.uid() = id);

CREATE POLICY "profiles_update_self" ON public.profiles
  FOR UPDATE TO authenticated
  USING (auth.uid() = id)
  WITH CHECK (auth.uid() = id);

-- No DELETE policy → no one can delete via the API.
-- Profile rows are deleted server-side via the auth trigger when the user
-- account is deleted.

The gotcha. The id column doubles as the FK to auth.users(id) — that's deliberate, so the profile UUID is always the user UUID. Don't add a separate user_id column; the row IS the user.

Also: don't store sensitive PII (email, phone, billing info) in the public-readable profiles table. Put those in a private_profiles table with strict per-user ownership policies.

5. Member-of-conversation

Chat apps, comment threads, anything where access depends on being part of a relationship rather than owning a row.

Schema:

CREATE TABLE public.conversations (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at timestamptz DEFAULT now()
);

CREATE TABLE public.conversation_members (
  conversation_id uuid REFERENCES conversations(id),
  user_id uuid REFERENCES auth.users(id),
  PRIMARY KEY (conversation_id, user_id)
);

CREATE TABLE public.messages (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  conversation_id uuid NOT NULL REFERENCES conversations(id),
  author_id uuid NOT NULL REFERENCES auth.users(id),
  body text NOT NULL,
  created_at timestamptz DEFAULT now()
);

ALTER TABLE public.conversations ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.conversation_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.messages ENABLE ROW LEVEL SECURITY;

Policies on messages:

CREATE POLICY "messages_select_member" ON public.messages
  FOR SELECT TO authenticated
  USING (
    conversation_id IN (
      SELECT conversation_id FROM conversation_members
      WHERE user_id = auth.uid()
    )
  );

CREATE POLICY "messages_insert_member" ON public.messages
  FOR INSERT TO authenticated
  WITH CHECK (
    conversation_id IN (
      SELECT conversation_id FROM conversation_members
      WHERE user_id = auth.uid()
    )
    AND author_id = auth.uid()
  );

-- Authors can edit their own messages within a conversation they're a member of.
CREATE POLICY "messages_update_author" ON public.messages
  FOR UPDATE TO authenticated
  USING (author_id = auth.uid())
  WITH CHECK (
    author_id = auth.uid()
    AND conversation_id IN (
      SELECT conversation_id FROM conversation_members
      WHERE user_id = auth.uid()
    )
  );

The gotcha. The WITH CHECK on INSERT requires both that the user is a member AND that author_id is the user. Without the second check, member A can post messages with author_id set to member B, impersonating them in the conversation. This is the most common subtle bug in chat-app RLS.

Performance note. For large message tables, the conversation_id IN (SELECT ...) subquery is run per row — at scale, this can be slow. Two options: (a) ensure conversation_members(user_id) is indexed (it's part of the PK, but the column order matters — Postgres can use the leading column of a composite PK efficiently), or (b) cache the user's conversation list in a JWT claim if the membership is reasonably stable.

6. Soft-delete

You want UI to "delete" rows but keep them for audit/recovery. RLS treats deleted rows as invisible.

ALTER TABLE public.notes ADD COLUMN deleted_at timestamptz;

DROP POLICY IF EXISTS "notes_select_own" ON public.notes;
CREATE POLICY "notes_select_own" ON public.notes
  FOR SELECT TO authenticated
  USING (auth.uid() = user_id AND deleted_at IS NULL);

-- Update policy unchanged — owner can soft-delete by setting deleted_at.
-- Hard-delete via DELETE policy is no longer needed; remove it.
DROP POLICY IF EXISTS "notes_delete_own" ON public.notes;

The gotcha. When you're using soft-delete, your client-side code that previously called delete() via PostgREST now needs to call update({ deleted_at: new Date() }) instead. Easy to miss. The other gotcha: an indexed WHERE deleted_at IS NULL filter is critical for performance — most queries will be against not-deleted rows, but Postgres can't use a regular B-tree index efficiently for this. Use a partial index: CREATE INDEX ON notes (user_id) WHERE deleted_at IS NULL;.

7. Append-only audit log

Logs, events, financial records. Insert allowed, read allowed (per ownership), updates and deletes denied entirely.

CREATE TABLE public.audit_events (
  id bigserial PRIMARY KEY,
  user_id uuid REFERENCES auth.users(id),
  action text NOT NULL,
  payload jsonb,
  created_at timestamptz DEFAULT now()
);
ALTER TABLE public.audit_events ENABLE ROW LEVEL SECURITY;

CREATE POLICY "audit_events_select_own" ON public.audit_events
  FOR SELECT TO authenticated
  USING (auth.uid() = user_id);

CREATE POLICY "audit_events_insert_authenticated" ON public.audit_events
  FOR INSERT TO authenticated
  WITH CHECK (auth.uid() = user_id);

-- No UPDATE policy. No DELETE policy. RLS default-denies → those operations
-- fail for everyone via the anon and service-role keys alike (well, almost —
-- service_role bypasses RLS by design; see "Pitfalls" section).

The gotcha. Skipping UPDATE/DELETE policies makes RLS deny those operations for the anon and authenticated keys. But service_role bypasses RLS entirely. If your server-side code holds the service_role key (e.g., Supabase Edge Functions, your own backend), it can update and delete audit_events. Solution: use a triggered constraint to refuse non-INSERT operations even via service_role:

CREATE OR REPLACE FUNCTION audit_events_no_modify()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'audit_events is append-only';
END;
$$;

CREATE TRIGGER audit_events_no_update
  BEFORE UPDATE OR DELETE OR TRUNCATE ON public.audit_events
  FOR EACH STATEMENT EXECUTE FUNCTION audit_events_no_modify();

Triggers are evaluated regardless of role. The audit log is now actually append-only.

8. Visibility levels

Documents that can be private, link-shared, or fully public. Three levels, one column.

CREATE TYPE visibility_level AS ENUM ('private', 'link', 'public');

CREATE TABLE public.documents (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id),
  title text NOT NULL,
  body text,
  visibility visibility_level NOT NULL DEFAULT 'private',
  share_token text  -- null unless visibility = 'link'
);
ALTER TABLE public.documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY "documents_select_visible" ON public.documents
  FOR SELECT TO anon, authenticated
  USING (
    visibility = 'public'
    OR (visibility = 'link' AND share_token IS NOT NULL
        AND share_token = current_setting('request.headers', true)::jsonb->>'x-share-token')
    OR auth.uid() = user_id
  );

CREATE POLICY "documents_write_own" ON public.documents
  FOR ALL TO authenticated
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

The gotcha. The link-shared case reads from request headers via current_setting('request.headers', true). PostgREST sets that on every request — but only headers your client actually sent. The client must send x-share-token: <token> on the fetch. If you forget the header on the request, the policy denies; if you forget to validate share_token IS NOT NULL, public-not-shared documents leak whenever the token comparison resolves to NULL = something (which evaluates to NULL, which evaluates to false, but the AND/OR boolean logic isn't always intuitive). Always include the IS NOT NULL check.

For pure-token-based access without auth, you can store a hash of the share token instead of the plaintext, and have your backend (an edge function) look up the document by hash and serve it. RLS in that case is bypassed by the service role and replaced with explicit lookup logic — sometimes the right tradeoff for sharing flows.

Pitfalls that apply to every pattern

RLS not enabled

The Moltbook bug. Policies don't matter if RLS is off — Postgres just executes the query. Verify per table:

SELECT schemaname, tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY tablename;

Every row should show rowsecurity = true. Any false in public is a public-readable table waiting to be enumerated. Dedicated fix-guide: /fix/supabase_anon_only_no_rls.

service_role bypasses RLS by design

The service_role key skips every policy. That's intentional — it's the admin escape hatch. The mistake is putting that key in a place where untrusted code can use it (the client bundle — see the service_role response post) or trusting client-supplied data on a server endpoint that uses service_role.

Rule: never read service_role-using request bodies without validating them as if they were anon-key requests. If your edge function receives a body that says "delete user X's data" and it executes that with service_role, you've reinvented the lack-of-RLS bug at the application layer.

Forgetting per-operation policies

The default FOR ALL shorthand creates one policy that applies to SELECT/INSERT/UPDATE/DELETE. The semantic is "this expression must hold." For UPDATE specifically, that means the same expression is used for both USING and WITH CHECK — which is usually what you want, but for tenant-scoped patterns where you want to allow admins to read but only members to write, you need separate per-operation policies.

When in doubt, write four separate policies (SELECT, INSERT, UPDATE, DELETE). It's more SQL, but the logic is explicit and the gotchas are obvious.

USING vs WITH CHECK on UPDATE

The most subtle bug in RLS. Consider:

-- WRONG: only USING.
CREATE POLICY "notes_update_own" ON public.notes
  FOR UPDATE TO authenticated
  USING (auth.uid() = user_id);
-- An attacker can: UPDATE notes SET user_id = '<A's UUID>' WHERE id = '<B's note>';
-- USING fails (B's note isn't theirs), so the row isn't matched.
-- But what if the attacker reads B's note id via another vulnerability, then updates? Same — USING denies.

-- The actual bug shows up when there's BOTH a SELECT and an UPDATE policy.
-- A user updates one of their own rows: USING passes (auth.uid() = user_id).
-- The UPDATE sets user_id to someone else's UUID.
-- Without WITH CHECK, the row is updated to someone else's ownership.
-- The user has just transferred the row out of their own ownership.
-- That's not a critical security bug, but it's a data-integrity bug.

-- A worse variant: if the policy is "team admin can update", USING passes
-- (admin in same team). The admin's UPDATE moves the row to another team.
-- Without WITH CHECK on team_id, that succeeds.

Always pair USING with WITH CHECK on UPDATE policies. The two expressions can be identical or different — what matters is that WITH CHECK is always present.

JWT custom claims as policy guards

Tempting performance optimization: store the user's role / team list in JWT custom claims, read it from auth.jwt() in the policy, avoid the subquery against memberships. The trap: JWT claims are set at login time. If the user's role changes (admin → member), they keep the old claims until their access token refreshes — possibly an hour. For sensitive role demotions, fall back to the membership-table lookup or force a token refresh.

Testing strategy

Three levels of test:

1. Unit-test policies in SQL. Open psql connected to your Supabase project. SET LOCAL ROLE authenticated; SET LOCAL "request.jwt.claims" TO '{"sub": "<test-user-uuid>"}';. Run queries. Verify exactly what the user can read, insert, update, delete.
2. Integration test from your client. Use a test user account, hit the same PostgREST endpoints your real app uses, assert on the responses. @supabase/supabase-js in tests is fine for this.
3. Active probing in production. vibecheck's scanner does this from outside — it extracts the anon key from your bundle, enumerates tables via OpenAPI, and reports which return rows. Run it after every deploy.

How vibecheck fits in

vibecheck doesn't write your policies — it tells you which tables don't have any. The scanner extracts your Supabase anon key from the bundle, hits the PostgREST endpoint, and lists every table that responds with rows. The autofix module then suggests RLS policy SQL based on the observed schema using six pattern templates that match the ones above.

For the broader vibe-coding security workflow, see the pillar guide. For incident response when a service_role key leaks, the service_role post. For the canonical case study of "RLS not enabled in production," Moltbook.