Common RLS Patterns

---

Pattern Library

After working with many Supabase applications, certain RLS patterns emerge repeatedly. Understanding these patterns helps you implement security quickly and correctly.

---

Pattern 1: User Ownership

The most common pattern—users can only access their own data.

Simple Ownership

-- Table structure
CREATE TABLE notes (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES auth.users(id) NOT NULL,
  title text NOT NULL,
  content text,
  created_at timestamptz DEFAULT now()
);

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

-- Policy: Users own their notes
CREATE POLICY "Users manage own notes"
  ON notes FOR ALL
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

With Soft Delete

CREATE TABLE notes (
  -- ... columns ...
  deleted_at timestamptz  -- NULL = not deleted
);

-- Only show non-deleted notes
CREATE POLICY "Users see own non-deleted notes"
  ON notes FOR SELECT
  USING (user_id = auth.uid() AND deleted_at IS NULL);

-- Can soft-delete own notes
CREATE POLICY "Users can soft delete own notes"
  ON notes FOR UPDATE
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

---

Pattern 2: Public Read, Owner Write

Content that everyone can view, but only the owner can modify.

CREATE TABLE posts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES auth.users(id) NOT NULL,
  title text NOT NULL,
  content text,
  created_at timestamptz DEFAULT now()
);

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Anyone can read
CREATE POLICY "Posts are publicly viewable"
  ON posts FOR SELECT
  USING (true);

-- Only owner can create (and must set themselves as owner)
CREATE POLICY "Users can create own posts"
  ON posts FOR INSERT
  WITH CHECK (user_id = auth.uid());

-- Only owner can update
CREATE POLICY "Users can update own posts"
  ON posts FOR UPDATE
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

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

---

Pattern 3: Draft/Published Status

Content with visibility states.

CREATE TABLE articles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES auth.users(id) NOT NULL,
  title text NOT NULL,
  content text,
  status text DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
  published_at timestamptz
);

ALTER TABLE articles ENABLE ROW LEVEL SECURITY;

-- Published articles visible to all
CREATE POLICY "Published articles are public"
  ON articles FOR SELECT
  USING (status = 'published');

-- Owners see all their articles (any status)
CREATE POLICY "Owners see all own articles"
  ON articles FOR SELECT
  USING (user_id = auth.uid());

-- Owners can create
CREATE POLICY "Owners can create articles"
  ON articles FOR INSERT
  WITH CHECK (user_id = auth.uid());

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

---

Pattern 4: Team/Organization Access

Multiple users sharing access to resources.

Simple Team Access

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

CREATE TABLE team_members (
  team_id uuid REFERENCES teams(id) ON DELETE CASCADE,
  user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE,
  role text DEFAULT 'member' CHECK (role IN ('owner', 'admin', 'member')),
  PRIMARY KEY (team_id, user_id)
);

CREATE TABLE team_documents (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  team_id uuid REFERENCES teams(id) ON DELETE CASCADE,
  title text NOT NULL,
  content text,
  created_by uuid REFERENCES auth.users(id)
);

-- Enable RLS
ALTER TABLE teams ENABLE ROW LEVEL SECURITY;
ALTER TABLE team_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE team_documents ENABLE ROW LEVEL SECURITY;

-- Team members can see their teams
CREATE POLICY "Members see their teams"
  ON teams FOR SELECT
  USING (
    id IN (
      SELECT team_id FROM team_members WHERE user_id = auth.uid()
    )
  );

-- Team members see membership
CREATE POLICY "Members see team membership"
  ON team_members FOR SELECT
  USING (
    team_id IN (
      SELECT team_id FROM team_members WHERE user_id = auth.uid()
    )
  );

-- Team members see documents
CREATE POLICY "Members see team documents"
  ON team_documents FOR SELECT
  USING (
    team_id IN (
      SELECT team_id FROM team_members WHERE user_id = auth.uid()
    )
  );

-- Team members can create documents
CREATE POLICY "Members can create documents"
  ON team_documents FOR INSERT
  WITH CHECK (
    team_id IN (
      SELECT team_id FROM team_members WHERE user_id = auth.uid()
    )
  );

With Role-Based Permissions

-- Helper function for team role check
CREATE FUNCTION user_has_team_role(
  check_team_id uuid,
  required_roles text[]
) RETURNS boolean AS $$
  SELECT EXISTS (
    SELECT 1 FROM team_members
    WHERE team_id = check_team_id
    AND user_id = auth.uid()
    AND role = ANY(required_roles)
  )
$$ LANGUAGE sql SECURITY DEFINER STABLE;

-- Only admins/owners can delete documents
CREATE POLICY "Team admins can delete documents"
  ON team_documents FOR DELETE
  USING (
    user_has_team_role(team_id, ARRAY['owner', 'admin'])
  );

---

Pattern 5: Profile Extension

Extending auth.users with additional profile data.

CREATE TABLE profiles (
  id uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  username text UNIQUE,
  full_name text,
  avatar_url text,
  bio text,
  updated_at timestamptz DEFAULT now()
);

ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- Profiles are publicly readable
CREATE POLICY "Profiles are public"
  ON profiles FOR SELECT
  USING (true);

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

-- Auto-create profile on signup (trigger)
CREATE FUNCTION handle_new_user()
RETURNS trigger AS $$
BEGIN
  INSERT INTO public.profiles (id)
  VALUES (NEW.id);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION handle_new_user();

---

Pattern 6: Admin Override

Admins can access everything; regular users have restrictions.

-- Store admin status (could also use app_metadata in JWT)
CREATE TABLE user_roles (
  user_id uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  is_admin boolean DEFAULT false
);

-- Helper function
CREATE FUNCTION is_admin() RETURNS boolean AS $$
  SELECT COALESCE(
    (SELECT is_admin FROM user_roles WHERE user_id = auth.uid()),
    false
  )
$$ LANGUAGE sql SECURITY DEFINER STABLE;

-- Apply to tables
CREATE POLICY "Admins have full access"
  ON sensitive_table FOR ALL
  USING (is_admin())
  WITH CHECK (is_admin());

CREATE POLICY "Users see own data"
  ON sensitive_table FOR SELECT
  USING (user_id = auth.uid());

---

Pattern 7: Multi-Tenancy

Data isolation between organizations/tenants.

Tenant Column Approach

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

CREATE TABLE tenant_users (
  tenant_id uuid REFERENCES tenants(id) ON DELETE CASCADE,
  user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE,
  role text DEFAULT 'member',
  PRIMARY KEY (tenant_id, user_id)
);

CREATE TABLE tenant_data (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid REFERENCES tenants(id) NOT NULL,
  -- other columns
);

-- Helper function to get current user's tenant
CREATE FUNCTION get_user_tenant_id() RETURNS uuid AS $$
  SELECT tenant_id FROM tenant_users WHERE user_id = auth.uid() LIMIT 1
$$ LANGUAGE sql SECURITY DEFINER STABLE;

-- Restrictive policy ensures ALL queries are tenant-scoped
CREATE POLICY "Tenant isolation"
  ON tenant_data
  AS RESTRICTIVE
  FOR ALL
  USING (tenant_id = get_user_tenant_id())
  WITH CHECK (tenant_id = get_user_tenant_id());

---

Pattern 8: Following/Friends

Social relationship patterns.

CREATE TABLE follows (
  follower_id uuid REFERENCES auth.users(id) ON DELETE CASCADE,
  following_id uuid REFERENCES auth.users(id) ON DELETE CASCADE,
  created_at timestamptz DEFAULT now(),
  PRIMARY KEY (follower_id, following_id)
);

CREATE TABLE posts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES auth.users(id) NOT NULL,
  content text NOT NULL,
  visibility text DEFAULT 'public' CHECK (visibility IN ('public', 'followers', 'private'))
);

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Public posts visible to all
CREATE POLICY "Public posts"
  ON posts FOR SELECT
  USING (visibility = 'public');

-- Own posts always visible
CREATE POLICY "Own posts"
  ON posts FOR SELECT
  USING (user_id = auth.uid());

-- Followers see followers-only posts
CREATE POLICY "Followers see followers posts"
  ON posts FOR SELECT
  USING (
    visibility = 'followers'
    AND user_id IN (
      SELECT following_id FROM follows WHERE follower_id = auth.uid()
    )
  );

---

Pattern 9: Invitation-Based Access

Resources shared via invitation.

CREATE TABLE documents (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id uuid REFERENCES auth.users(id) NOT NULL,
  title text NOT NULL,
  content text
);

CREATE TABLE document_shares (
  document_id uuid REFERENCES documents(id) ON DELETE CASCADE,
  user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE,
  permission text DEFAULT 'view' CHECK (permission IN ('view', 'edit')),
  PRIMARY KEY (document_id, user_id)
);

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Owner has full access
CREATE POLICY "Owner access"
  ON documents FOR ALL
  USING (owner_id = auth.uid())
  WITH CHECK (owner_id = auth.uid());

-- Shared users can view
CREATE POLICY "Shared view access"
  ON documents FOR SELECT
  USING (
    id IN (
      SELECT document_id FROM document_shares WHERE user_id = auth.uid()
    )
  );

-- Shared users with edit permission can update
CREATE POLICY "Shared edit access"
  ON documents FOR UPDATE
  USING (
    id IN (
      SELECT document_id FROM document_shares
      WHERE user_id = auth.uid() AND permission = 'edit'
    )
  )
  WITH CHECK (
    id IN (
      SELECT document_id FROM document_shares
      WHERE user_id = auth.uid() AND permission = 'edit'
    )
  );

---

Pattern 10: Audit Trail with Immutable Records

Records that can be created but not modified.

CREATE TABLE audit_log (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES auth.users(id),
  action text NOT NULL,
  details jsonb,
  created_at timestamptz DEFAULT now()
);

ALTER TABLE audit_log ENABLE ROW LEVEL SECURITY;

-- Anyone authenticated can insert
CREATE POLICY "Insert audit records"
  ON audit_log FOR INSERT
  WITH CHECK (user_id = auth.uid());

-- Can view own audit records
CREATE POLICY "View own audit records"
  ON audit_log FOR SELECT
  USING (user_id = auth.uid());

-- No UPDATE or DELETE policies = immutable

---

Key Takeaways

1. Start with ownership: Most apps need "users access their own data"
2. Layer visibility states: Draft/published adds public access
3. Teams need membership checks: Subquery for team_members
4. Use helper functions: Simplify complex policy logic
5. RESTRICTIVE for global rules: Tenant isolation, soft delete filters
6. Separate policies by operation: Different rules for SELECT vs DELETE

---

Next Steps

These patterns cover most common scenarios. Next, we'll explore performance considerations to ensure your RLS policies don't slow down your application.

---

Patterns are reusable solutions to common problems. Master these RLS patterns, and you'll be able to secure most Supabase applications quickly and correctly.