Multi-tenant Security Patterns

Implementing team and organisation-based data isolation with RLS

Last updated: 8/15/2025

Multi-tenancy means isolating data between different groups (teams, organisations, or workspaces). RLS makes this bulletproof at the database level.

The Multi-tenant Pattern

Instead of users owning data directly, data belongs to organisations and users belong to organisations through memberships.

Users (Memberships) (Organisations) (Data)

This pattern lets you:

Share data within teams
Control access through roles
Scale from solo users to enterprise customers

Basic Schema Design

Core Tables

-- Organisations (teams, companies, workspaces)
CREATE TABLE organisations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Users can belong to multiple organisations
CREATE TABLE memberships (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL, -- references auth.users
  organisation_id UUID REFERENCES organisations(id) NOT NULL,
  role TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'member')),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(user_id, organisation_id)
);

-- All business data includes organisation_id
CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organisation_id UUID REFERENCES organisations(id) NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  created_by UUID NOT NULL, -- references auth.users
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Multi-tenant RLS Policies

Organisation Member Access

Users can only see data from organisations they belong to:

-- Enable RLS on all business tables
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Members can read projects from their organisations
CREATE POLICY "members_read_org_projects" ON projects
FOR SELECT USING (
  organisation_id IN (
    SELECT organisation_id 
    FROM memberships 
    WHERE user_id = auth.uid()
  )
);

Role-based Write Access

Different roles get different permissions:

-- Only admins and owners can create projects
CREATE POLICY "admins_create_projects" ON projects
FOR INSERT WITH CHECK (
  organisation_id IN (
    SELECT organisation_id 
    FROM memberships 
    WHERE user_id = auth.uid() 
    AND role IN ('owner', 'admin')
  )
);

-- Members can update projects they created
CREATE POLICY "creators_update_projects" ON projects
FOR UPDATE USING (
  created_by = auth.uid()
  AND organisation_id IN (
    SELECT organisation_id 
    FROM memberships 
    WHERE user_id = auth.uid()
  )
);

-- Only owners can delete projects
CREATE POLICY "owners_delete_projects" ON projects
FOR DELETE USING (
  organisation_id IN (
    SELECT organisation_id 
    FROM memberships 
    WHERE user_id = auth.uid() 
    AND role = 'owner'
  )
);

Helper Functions for Cleaner Policies

Create functions to simplify policy logic:

-- Check if user is member of organisation
CREATE OR REPLACE FUNCTION is_org_member(org_id UUID)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM memberships
    WHERE user_id = auth.uid() 
    AND organisation_id = org_id
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Check if user has specific role in organisation
CREATE OR REPLACE FUNCTION has_org_role(org_id UUID, required_role TEXT)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM memberships
    WHERE user_id = auth.uid() 
    AND organisation_id = org_id
    AND role = required_role
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Now policies become much cleaner:

-- Much simpler policy using helper function
CREATE POLICY "members_read_projects" ON projects
FOR SELECT USING (is_org_member(organisation_id));

CREATE POLICY "owners_delete_projects" ON projects
FOR DELETE USING (has_org_role(organisation_id, 'owner'));

Membership Management

Adding Users to Organisations

-- Function to invite user to organisation
CREATE OR REPLACE FUNCTION invite_user_to_org(
  user_email TEXT,
  org_id UUID,
  user_role TEXT DEFAULT 'member'
)
RETURNS UUID AS $$
DECLARE
  target_user_id UUID;
  membership_id UUID;
BEGIN
  -- Find user by email
  SELECT id INTO target_user_id 
  FROM auth.users 
  WHERE email = user_email;
  
  IF target_user_id IS NULL THEN
    RAISE EXCEPTION 'User not found: %', user_email;
  END IF;
  
  -- Check if inviter is admin/owner
  IF NOT has_org_role(org_id, 'admin') AND NOT has_org_role(org_id, 'owner') THEN
    RAISE EXCEPTION 'Insufficient permissions';
  END IF;
  
  -- Create membership
  INSERT INTO memberships (user_id, organisation_id, role)
  VALUES (target_user_id, org_id, user_role)
  ON CONFLICT (user_id, organisation_id) 
  DO UPDATE SET role = EXCLUDED.role
  RETURNING id INTO membership_id;
  
  RETURN membership_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Using from Your App

// Invite user to organisation
const { data, error } = await supabase
  .rpc('invite_user_to_org', {
    user_email: 'newuser@example.com',
    org_id: organisationId,
    user_role: 'member'
  });

// Get user's organisations
const { data: orgs } = await supabase
  .from('memberships')
  .select(`
    role,
    organisations (
      id,
      name
    )
  `)
  .eq('user_id', userId);

Advanced Patterns

Hierarchical Permissions

Some roles inherit permissions from others:

-- Function to check if user has at least a certain role level
CREATE OR REPLACE FUNCTION has_min_org_role(org_id UUID, min_role TEXT)
RETURNS BOOLEAN AS $$
DECLARE
  user_role TEXT;
  role_hierarchy INTEGER;
BEGIN
  -- Get user's role in this org
  SELECT role INTO user_role
  FROM memberships
  WHERE user_id = auth.uid() AND organisation_id = org_id;
  
  IF user_role IS NULL THEN
    RETURN false;
  END IF;
  
  -- Convert roles to hierarchy levels
  role_hierarchy := CASE user_role
    WHEN 'owner' THEN 3
    WHEN 'admin' THEN 2
    WHEN 'member' THEN 1
    ELSE 0
  END;
  
  RETURN role_hierarchy >= CASE min_role
    WHEN 'owner' THEN 3
    WHEN 'admin' THEN 2
    WHEN 'member' THEN 1
    ELSE 0
  END;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Cross-organisation Data Sharing

Sometimes you need to share specific data across organisations:

-- Shared resources table
CREATE TABLE shared_resources (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  resource_id UUID NOT NULL,
  resource_type TEXT NOT NULL,
  shared_with_org_id UUID REFERENCES organisations(id) NOT NULL,
  shared_by_org_id UUID REFERENCES organisations(id) NOT NULL,
  permissions TEXT[] DEFAULT ARRAY['read'],
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Policy for accessing shared projects
CREATE POLICY "shared_projects_access" ON projects
FOR SELECT USING (
  -- Normal org access OR shared access
  is_org_member(organisation_id)
  OR EXISTS (
    SELECT 1 FROM shared_resources sr
    JOIN memberships m ON sr.shared_with_org_id = m.organisation_id
    WHERE sr.resource_id = projects.id
    AND sr.resource_type = 'project'
    AND m.user_id = auth.uid()
  )
);

Testing Multi-tenant Security

Verify Isolation

// Sign in as user from Org A
await supabase.auth.signInWithPassword({
  email: 'user-org-a@example.com',
  password: 'password'
});

// Should only see Org A's projects
const { data: projectsA } = await supabase
  .from('projects')
  .select('*');

// Sign in as user from Org B  
await supabase.auth.signInWithPassword({
  email: 'user-org-b@example.com', 
  password: 'password'
});

// Should only see Org B's projects (different set)
const { data: projectsB } = await supabase
  .from('projects')
  .select('*');

// Verify no overlap in accessible data
console.assert(
  !projectsA.some(a => projectsB.some(b => b.id === a.id)),
  'Data isolation failed!'
);

Performance Optimisation

Index Membership Lookups

-- Speed up membership queries
CREATE INDEX idx_memberships_user_org ON memberships(user_id, organisation_id);
CREATE INDEX idx_memberships_org_role ON memberships(organisation_id, role);

-- Speed up organisation-scoped data queries
CREATE INDEX idx_projects_org_id ON projects(organisation_id);
CREATE INDEX idx_tasks_org_id ON tasks(organisation_id);

Cache User Memberships

For frequently accessed membership data, consider caching:

// Cache user's organisations in JWT custom claims
const getUserOrganisations = async (userId) => {
  const { data } = await supabase
    .from('memberships')
    .select('organisation_id, role')
    .eq('user_id', userId);
    
  return data;
};

// Use in custom JWT claims for faster policy evaluation

Common Pitfalls

Forgetting Organisation Context

// Wrong: Creates project without organisation context
const { data } = await supabase
  .from('projects')
  .insert({ name: 'My Project' });

// Right: Always include organisation_id
const { data } = await supabase
  .from('projects')
  .insert({ 
    name: 'My Project',
    organisation_id: currentOrgId 
  });

Not Handling Multiple Memberships

Users might belong to multiple organisations. Your UI should let them switch context:

// Get user's organisations for org switcher
const { data: userOrgs } = await supabase
  .from('memberships')
  .select(`
    role,
    organisations (id, name)
  `)
  .eq('user_id', user.id);

// Filter all queries by current organisation
const { data: projects } = await supabase
  .from('projects')
  .select('*')
  .eq('organisation_id', currentOrgId);

Multi-tenant RLS ensures data stays isolated between organisations while allowing flexible sharing and role-based access within teams. Start with simple membership patterns and evolve toward more sophisticated permission systems as your application grows.

Learn