Supabase RLS: The Copy-Paste Guide for Vibe Coders

-- Users can only see their own rows
CREATE POLICY "Users can view own rows"
ON your_table FOR SELECT
USING (auth.uid() = user_id);

-- Users can only insert rows for themselves
CREATE POLICY "Users can insert own rows"
ON your_table FOR INSERT
WITH CHECK (auth.uid() = user_id);

-- Users can only update their own rows
CREATE POLICY "Users can update own rows"
ON your_table FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

-- Users can only delete their own rows
CREATE POLICY "Users can delete own rows"
ON your_table FOR DELETE
USING (auth.uid() = user_id);

USING controls which rows you can read/modify. WITH CHECK controls which rows you can write. For INSERT, there's no existing row to check — only WITH CHECK applies.

-- Required: your table needs a user_id column
ALTER TABLE your_table ADD COLUMN user_id UUID REFERENCES auth.users(id);

-- Anyone (even unauthenticated) can read
CREATE POLICY "Public read access"
ON posts FOR SELECT
USING (true);

-- Only authenticated users can insert
CREATE POLICY "Authenticated users can post"
ON posts FOR INSERT
WITH CHECK (auth.uid() IS NOT NULL);

-- Authors can update their own posts
CREATE POLICY "Authors can edit own posts"
ON posts FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

-- Authors can delete their own posts
CREATE POLICY "Authors can delete own posts"
ON posts FOR DELETE
USING (auth.uid() = user_id);

-- First, create a way to identify admins
CREATE TABLE admins (
  user_id UUID REFERENCES auth.users(id) PRIMARY KEY
);

-- Only admins can read
CREATE POLICY "Admin read only"
ON sensitive_table FOR SELECT
USING (
  auth.uid() IN (SELECT user_id FROM admins)
);

-- Only admins can modify
CREATE POLICY "Admin write only"
ON sensitive_table FOR ALL
USING (
  auth.uid() IN (SELECT user_id FROM admins)
)
WITH CHECK (
  auth.uid() IN (SELECT user_id FROM admins)
);

Performance note: the subquery (SELECT user_id FROM admins) runs per-row. For small admin tables this is fine. For large datasets, use a security definer function instead (see Performance Anti-Patterns).

CREATE TABLE org_members (
  org_id UUID,
  user_id UUID REFERENCES auth.users(id),
  role TEXT DEFAULT 'member',
  PRIMARY KEY (org_id, user_id)
);

-- Members can read their org's data
CREATE POLICY "Org members can read"
ON projects FOR SELECT
USING (
  org_id IN (
    SELECT org_id FROM org_members
    WHERE user_id = auth.uid()
  )
);

-- Members can insert into their org
CREATE POLICY "Org members can create"
ON projects FOR INSERT
WITH CHECK (
  org_id IN (
    SELECT org_id FROM org_members
    WHERE user_id = auth.uid()
  )
);

-- Only org admins can delete
CREATE POLICY "Org admins can delete"
ON projects FOR DELETE
USING (
  org_id IN (
    SELECT org_id FROM org_members
    WHERE user_id = auth.uid() AND role = 'admin'
  )
);

-- Completely public: anyone can read, nobody can write via client
CREATE POLICY "Public read"
ON pricing_plans FOR SELECT
USING (true);

-- No INSERT/UPDATE/DELETE policies = no client-side writes
-- All writes go through your server-side admin client