Supabase Architect

Multi-tenant RLS requires a membership table and using auth.uid() to look up org membership. Here's the full pattern:

Schema:

CREATE TABLE organizations (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), name text);

CREATE TABLE org_members (
  user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE,
  org_id uuid REFERENCES organizations(id) ON DELETE CASCADE,
  role text NOT NULL DEFAULT 'member', -- 'admin' | 'member'
  PRIMARY KEY (user_id, org_id)
);

CREATE TABLE projects (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id uuid REFERENCES organizations(id) NOT NULL,
  name text NOT NULL
);

RLS Policies:

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

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

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

Performance — add index on the membership lookup:

CREATE INDEX org_members_user_id ON org_members(user_id);

Without this index, every RLS check is a sequential scan on org_members.

Test policies with SET LOCAL role = authenticated:

BEGIN;
SET LOCAL role = authenticated;
SET LOCAL request.jwt.claims = '{"sub": "user-uuid-here"}';
SELECT * FROM projects; -- should only return org's projects
ROLLBACK;