How to Write Supabase RLS Policies

Real-world examples for common scenarios

Policy Syntax Basics

CREATE POLICY "policy_name"
ON table_name
FOR operation  -- SELECT, INSERT, UPDATE, DELETE, or ALL
TO role        -- Usually 'authenticated' or 'anon'
USING (expression)      -- For SELECT, UPDATE, DELETE (existing rows)
WITH CHECK (expression) -- For INSERT, UPDATE (new data)

Pattern 1: User-Owned Data

Most common pattern: users can only access their own data.

-- Table structure
CREATE TABLE todos (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id uuid REFERENCES auth.users(id) NOT NULL,
  title text NOT NULL,
  completed boolean DEFAULT false,
  created_at timestamp with time zone DEFAULT now()
);

-- Enable RLS
ALTER TABLE todos ENABLE ROW LEVEL SECURITY;

-- Users can view their own todos
CREATE POLICY "Users can view own todos"
ON todos FOR SELECT
TO authenticated
USING (auth.uid() = user_id);

-- Users can create their own todos
CREATE POLICY "Users can create own todos"
ON todos FOR INSERT
TO authenticated
WITH CHECK (auth.uid() = user_id);

-- Users can update their own todos
CREATE POLICY "Users can update own todos"
ON todos FOR UPDATE
TO authenticated
USING (auth.uid() = user_id);

-- Users can delete their own todos
CREATE POLICY "Users can delete own todos"
ON todos FOR DELETE
TO authenticated
USING (auth.uid() = user_id);

Pattern 2: Public Profiles

Anyone can view profiles, but only owners can edit.

-- Table structure
CREATE TABLE profiles (
  id uuid REFERENCES auth.users(id) PRIMARY KEY,
  username text UNIQUE,
  avatar_url text,
  bio text,
  updated_at timestamp with time zone DEFAULT now()
);

ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- Anyone can view profiles
CREATE POLICY "Public profiles are viewable"
ON profiles FOR SELECT
TO anon, authenticated
USING (true);

-- Users can update their own profile
CREATE POLICY "Users can update own profile"
ON profiles FOR UPDATE
TO authenticated
USING (auth.uid() = id)
WITH CHECK (auth.uid() = id);

-- Users can insert their own profile (on signup)
CREATE POLICY "Users can insert own profile"
ON profiles FOR INSERT
TO authenticated
WITH CHECK (auth.uid() = id);

Pattern 3: Published vs Draft Content

Public can see published content, authors can see their drafts.

CREATE TABLE posts (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  author_id uuid REFERENCES auth.users(id) NOT NULL,
  title text NOT NULL,
  content text,
  status text DEFAULT 'draft' CHECK (status IN ('draft', 'published')),
  created_at timestamp with time zone DEFAULT now()
);

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Anyone can view published posts
CREATE POLICY "Published posts are public"
ON posts FOR SELECT
TO anon, authenticated
USING (status = 'published');

-- Authors can view their own posts (including drafts)
CREATE POLICY "Authors can view own posts"
ON posts FOR SELECT
TO authenticated
USING (auth.uid() = author_id);

-- Authors can create posts
CREATE POLICY "Authors can create posts"
ON posts FOR INSERT
TO authenticated
WITH CHECK (auth.uid() = author_id);

-- Authors can update their posts
CREATE POLICY "Authors can update own posts"
ON posts FOR UPDATE
TO authenticated
USING (auth.uid() = author_id);

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

Pattern 4: Team/Organization Access

Users can access data for teams they belong to.

-- Team membership table
CREATE TABLE team_members (
  team_id uuid REFERENCES teams(id),
  user_id uuid REFERENCES auth.users(id),
  role text DEFAULT 'member' CHECK (role IN ('owner', 'admin', 'member')),
  PRIMARY KEY (team_id, user_id)
);

-- Team projects
CREATE TABLE projects (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  team_id uuid REFERENCES teams(id) NOT NULL,
  name text NOT NULL,
  created_at timestamp with time zone DEFAULT now()
);

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Helper function to check team membership
CREATE OR REPLACE FUNCTION is_team_member(team_id uuid)
RETURNS boolean AS $$
  SELECT EXISTS (
    SELECT 1 FROM team_members
    WHERE team_members.team_id = $1
    AND team_members.user_id = auth.uid()
  );
$$ LANGUAGE sql SECURITY DEFINER;

-- Team members can view team projects
CREATE POLICY "Team members can view projects"
ON projects FOR SELECT
TO authenticated
USING (is_team_member(team_id));

-- Only admins/owners can create projects
CREATE OR REPLACE FUNCTION is_team_admin(team_id uuid)
RETURNS boolean AS $$
  SELECT EXISTS (
    SELECT 1 FROM team_members
    WHERE team_members.team_id = $1
    AND team_members.user_id = auth.uid()
    AND team_members.role IN ('owner', 'admin')
  );
$$ LANGUAGE sql SECURITY DEFINER;

CREATE POLICY "Admins can create projects"
ON projects FOR INSERT
TO authenticated
WITH CHECK (is_team_admin(team_id));

Pattern 5: Private Messages

Users can see messages they sent or received.

CREATE TABLE messages (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  sender_id uuid REFERENCES auth.users(id) NOT NULL,
  recipient_id uuid REFERENCES auth.users(id) NOT NULL,
  content text NOT NULL,
  read boolean DEFAULT false,
  created_at timestamp with time zone DEFAULT now()
);

ALTER TABLE messages ENABLE ROW LEVEL SECURITY;

-- Users can view messages they sent or received
CREATE POLICY "Users can view own messages"
ON messages FOR SELECT
TO authenticated
USING (
  auth.uid() = sender_id OR auth.uid() = recipient_id
);

-- Users can only send messages as themselves
CREATE POLICY "Users can send messages"
ON messages FOR INSERT
TO authenticated
WITH CHECK (auth.uid() = sender_id);

-- Recipients can mark messages as read
CREATE POLICY "Recipients can update read status"
ON messages FOR UPDATE
TO authenticated
USING (auth.uid() = recipient_id)
WITH CHECK (auth.uid() = recipient_id);

Testing Your Policies

-- Test as a specific user in SQL Editor
SET request.jwt.claims.sub = 'user-uuid-here';

-- Try to select data
SELECT * FROM todos;

-- Try to access another user's data (should fail or return empty)
SELECT * FROM todos WHERE user_id = 'other-user-uuid';

-- Reset
RESET request.jwt.claims.sub;

Related guides:How to Set Up Supabase RLS · How to Test Supabase RLS · Supabase Security Guide