Supabase RLS Patterns for Multi-Tenant SaaS

Row Level Security is the single most important feature Supabase offers for SaaS applications. Get it wrong and you leak data between tenants. Get it right and you never think about authorization at the database level again.

We've shipped RLS policies across six multi-tenant projects in the last 18 months. These are the patterns that survived production.

Why RLS Over Application-Level Checks

The traditional approach is adding WHERE tenant_id = current_tenant to every query in your application code. It works until it doesn't.

Problems with application-level filtering:

1. A single missed WHERE clause leaks all tenant data
2. Raw SQL queries bypass your ORM's middleware
3. Admin panels, reports, and debugging tools often skip filters
4. New team members don't know which queries need tenant scoping

RLS moves authorization to the database itself. No query can return rows the policy doesn't allow, regardless of what application code does.

Pattern 1: Simple Tenant Isolation

The foundation. Every tenant-scoped table gets this:

-- Every table with tenant data
ALTER TABLE public.projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Tenant isolation"
  ON public.projects
  FOR ALL
  USING (
    organization_id = (
      SELECT organization_id FROM public.profiles
      WHERE id = auth.uid()
    )
  );

The auth.uid() function returns the current authenticated user's ID from Supabase Auth. The subquery looks up their organization. Every SELECT, INSERT, UPDATE, and DELETE is filtered automatically.

Pattern 2: Role-Based Access Within a Tenant

Most SaaS apps need roles: owner, admin, member, viewer. Layer this on top of tenant isolation:

CREATE POLICY "Members can read"
  ON public.projects FOR SELECT
  USING (
    organization_id IN (
      SELECT organization_id FROM public.memberships
      WHERE user_id = auth.uid()
    )
  );

CREATE POLICY "Admins can modify"
  ON public.projects FOR ALL
  USING (
    organization_id IN (
      SELECT organization_id FROM public.memberships
      WHERE user_id = auth.uid()
      AND role IN ('owner', 'admin')
    )
  );

Key insight: Use separate policies for read vs. write. Supabase OR's policies of the same command type, so a user matching either the "Members can read" or "Admins can modify" SELECT policy can read data.

Pattern 3: Helper Functions for Complex Policies

When policies get complex, extract reusable functions:

CREATE OR REPLACE FUNCTION public.get_user_role(org_id UUID)
RETURNS TEXT AS $$
  SELECT role FROM public.memberships
  WHERE user_id = auth.uid()
  AND organization_id = org_id
  LIMIT 1;
$$ LANGUAGE sql SECURITY DEFINER STABLE;

CREATE POLICY "Role-based access"
  ON public.invoices FOR ALL
  USING (
    public.get_user_role(organization_id) IN ('owner', 'admin', 'billing')
  );

The SECURITY DEFINER keyword is critical — it runs the function with the permissions of the function creator, not the calling user. Without it, the function can't read the memberships table if the user doesn't have direct access.

Pattern 4: Public + Authenticated Hybrid

Content tables often need public read access with authenticated write access:

-- Published content is publicly readable
CREATE POLICY "Public reads published"
  ON public.blog_posts FOR SELECT
  USING (status = 'published' AND published_at <= NOW());

-- Authors manage own drafts
CREATE POLICY "Authors manage own"
  ON public.blog_posts FOR ALL
  USING (author_id = auth.uid());

-- Editors manage all content
CREATE POLICY "Editors manage all"
  ON public.blog_posts FOR ALL
  USING (
    EXISTS (
      SELECT 1 FROM public.profiles
      WHERE id = auth.uid() AND role IN ('admin', 'editor')
    )
  );

For anonymous access (no auth token), use auth.uid() IS NULL checks carefully. Supabase treats unauthenticated requests as having a null UID.

Pattern 5: Cross-Tenant Admin Access

Platform admins need to see everything. Don't disable RLS — add a super-admin policy:

CREATE POLICY "Super admins bypass"
  ON public.projects FOR ALL
  USING (
    EXISTS (
      SELECT 1 FROM public.profiles
      WHERE id = auth.uid() AND is_super_admin = TRUE
    )
  );

This keeps the audit trail intact. Every query still goes through RLS; the super-admin policy just happens to allow everything.

Performance Considerations

RLS policies run on every row for every query. Poorly written policies tank performance.

Always do:

• Index columns used in policy subqueries (organization_id, user_id, role)
• Use EXISTS instead of IN for correlated subqueries on large tables
• Mark helper functions as STABLE or IMMUTABLE when possible
• Use SECURITY DEFINER functions to avoid nested RLS evaluation

Never do:

• Join to tables that also have RLS (causes recursive policy evaluation)
• Use volatile functions in policies (they re-evaluate per row)
• Forget to create policies after enabling RLS (locks out everyone, including the service role in some configurations)

Debugging RLS

When policies don't work as expected:

-- Check what policies exist on a table
SELECT * FROM pg_policies WHERE tablename = 'projects';

-- Test as a specific user (in Supabase SQL editor)
SET request.jwt.claims = '{"sub": "user-uuid-here"}';
SELECT * FROM public.projects;

The Supabase dashboard's SQL editor runs with the service role by default, bypassing RLS. Use the API or set JWT claims explicitly to test policies.

Migration Strategy

Adding RLS to existing tables without downtime:

1. Enable RLS: ALTER TABLE ... ENABLE ROW LEVEL SECURITY
2. Add a permissive "allow all authenticated" policy immediately
3. Deploy application changes that pass the correct auth context
4. Replace the permissive policy with proper tenant-scoped policies
5. Test thoroughly with multiple tenant accounts

Skipping step 2 locks out all users the moment RLS is enabled.

Our Standard Template

Every new table in our projects starts with this template:

CREATE TABLE public.new_table (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  organization_id UUID REFERENCES public.organizations(id) ON DELETE CASCADE,
  -- ... columns
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE public.new_table ENABLE ROW LEVEL SECURITY;

-- Tenant isolation
CREATE POLICY "tenant_isolation_select" ON public.new_table
  FOR SELECT USING (
    organization_id IN (
      SELECT organization_id FROM public.memberships WHERE user_id = auth.uid()
    )
  );

CREATE POLICY "tenant_isolation_modify" ON public.new_table
  FOR ALL USING (
    public.get_user_role(organization_id) IN ('owner', 'admin')
  );

-- Indexes for RLS performance
CREATE INDEX idx_new_table_org ON public.new_table(organization_id);

RLS is not optional for multi-tenant. It's the difference between "we think our app is secure" and "the database guarantees it."