Supabase Row-Level Security: The Complete Guide for Vibe Coders

// Without RLS, this returns ALL users
const { data } = await supabase.from('users').select('*')

// With RLS, this returns only the current user's data
const { data } = await supabase.from('users').select('*')
// Actually executes: SELECT * FROM users WHERE id = auth.uid()

-- Enable RLS on a table
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
-- Now ALL queries are blocked until you add policies

CREATE POLICY "policy_name"
  ON table_name
  FOR [SELECT 
INSERT
UPDATE
DELETE
 ALL]
  TO [role_name | PUBLIC]
  USING (condition_for_existing_rows)      -- Filter rows
  WITH CHECK (condition_for_new_rows);     -- Validate inserts/updates

-- Users table: users see only themselves
CREATE POLICY "Users see own profile"
  ON users
  FOR SELECT
  USING (auth.uid() = id);

-- Posts table: users manage their own posts
CREATE POLICY "Users manage own posts"
  ON posts
  FOR ALL
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- Blog posts: anyone can read, only authors can write
CREATE POLICY "Public read access"
  ON blog_posts
  FOR SELECT
  TO PUBLIC
  USING (published = true);
CREATE POLICY "Authors manage posts"
  ON blog_posts
  FOR ALL
  USING (auth.uid() = author_id)
  WITH CHECK (auth.uid() = author_id);

-- Projects: team members can access
CREATE POLICY "Team members access projects"
  ON projects
  FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM team_members
      WHERE team_members.team_id = projects.team_id
      AND team_members.user_id = auth.uid()
    )
  );

-- Admins can do everything
CREATE POLICY "Admins have full access"
  ON users
  FOR ALL
  USING (
    EXISTS (
      SELECT 1 FROM users
      WHERE users.id = auth.uid()
      AND users.role = 'admin'
    )
  );

CREATE POLICY "Users read own orders"
  ON orders
  FOR SELECT
  USING (auth.uid() = user_id);

-- WITH CHECK validates new rows
CREATE POLICY "Users create own orders"
  ON orders
  FOR INSERT
  WITH CHECK (auth.uid() = user_id);

-- USING: which rows can be updated
-- WITH CHECK: what the new values must satisfy
CREATE POLICY "Users update own orders"
  ON orders
  FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users delete own orders"
  ON orders
  FOR DELETE
  USING (auth.uid() = user_id);

-- AI-generated
CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id UUID REFERENCES users(id),
  total DECIMAL
);
-- Forgot to enable RLS!

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
-- No policies = all queries blocked

-- DANGEROUS: Anyone can read all orders
CREATE POLICY "Read all orders"
  ON orders
  FOR SELECT
  TO PUBLIC
  USING (true);

CREATE POLICY "Read own orders"
  ON orders
  FOR SELECT
  USING (auth.uid() = user_id);

-- User could insert orders for other users
CREATE POLICY "Create orders"
  ON orders
  FOR INSERT
  WITH CHECK (true);  -- No validation!

CREATE POLICY "Create own orders"
  ON orders
  FOR INSERT
  WITH CHECK (auth.uid() = user_id);

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

SELECT tablename, policyname, permissive, roles, cmd, qual, with_check
FROM pg_policies
WHERE schemaname = 'public';

-- Set role to authenticated user
SET ROLE authenticated;
SET request.jwt.claim.sub = 'user-uuid-here';
-- Test query
SELECT * FROM orders;
-- Reset
RESET ROLE;

-- Policy uses user_id
USING (auth.uid() = user_id)
-- Add index for performance
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- SLOW: Subquery on every row
USING (
  EXISTS (
    SELECT 1 FROM team_members
    WHERE team_members.team_id = projects.team_id
    AND team_members.user_id = auth.uid()
  )
)
-- FASTER: Denormalize if needed
-- Add user_ids array to projects table
USING (auth.uid() = ANY(user_ids))

-- Users table
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  role TEXT DEFAULT 'user'
);
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users see own profile"
  ON users FOR SELECT
  USING (auth.uid() = id);
CREATE POLICY "Users update own profile"
  ON users FOR UPDATE
  USING (auth.uid() = id)
  WITH CHECK (auth.uid() = id AND role = 'user'); -- Can't self-promote
-- Teams table
CREATE TABLE teams (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  owner_id UUID REFERENCES users(id)
);
ALTER TABLE teams ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Team members see team"
  ON teams FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM team_members
      WHERE team_members.team_id = teams.id
      AND team_members.user_id = auth.uid()
    )
  );
CREATE POLICY "Owners manage team"
  ON teams FOR ALL
  USING (owner_id = auth.uid())
  WITH CHECK (owner_id = auth.uid());
-- Team members junction table
CREATE TABLE team_members (
  team_id UUID REFERENCES teams(id),
  user_id UUID REFERENCES users(id),
  role TEXT DEFAULT 'member',
  PRIMARY KEY (team_id, user_id)
);
ALTER TABLE team_members ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Members see team roster"
  ON team_members FOR SELECT
  USING (
    user_id = auth.uid() OR
    EXISTS (
      SELECT 1 FROM teams
      WHERE teams.id = team_members.team_id
      AND teams.owner_id = auth.uid()
    )
  );
-- Projects table
CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  team_id UUID REFERENCES teams(id),
  name TEXT NOT NULL,
  created_by UUID REFERENCES users(id)
);
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Team members access projects"
  ON projects FOR ALL
  USING (
    EXISTS (
      SELECT 1 FROM team_members
      WHERE team_members.team_id = projects.team_id
      AND team_members.user_id = auth.uid()
    )
  )
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM team_members
      WHERE team_members.team_id = projects.team_id
      AND team_members.user_id = auth.uid()
    )
  );

FOR EVERY TABLE:
================
[ ] RLS enabled
[ ] SELECT policy defined
[ ] INSERT policy with WITH CHECK
[ ] UPDATE policy with USING and WITH CHECK
[ ] DELETE policy defined (if needed)
[ ] Indexes on policy columns
TESTING:
========
[ ] Verified users can't see others' data
[ ] Verified users can't insert as others
[ ] Verified users can't update others' data
[ ] Verified users can't delete others' data
[ ] Tested admin access (if applicable)