Supabase RLS Best Practices: Production Patterns for Secure Multi-Tenant Apps

Row Level Security (RLS) is a PostgreSQL feature that enforces row-level access control by adding implicit WHERE clauses to every query. In Supabase, RLS policies define which rows users can SELECT, INSERT, UPDATE, or DELETE based on their authentication context. Without RLS, anyone with your anon key can read or modify all data in your public schema, which is a security risk if you expose your database to the public via the Data API in Supabase or PostgREST.

This guide covers the Supabase RLS patterns we've refined across 100+ production deployments at MakerKit. You'll learn performance optimizations that turn 3-minute queries into 2ms responses, multi-tenant patterns for team-based access, and automated testing strategies that catch policy bugs before they ship.

Tested with PostgreSQL 15+ and Supabase. The schemas in this guide are simplified for clarity. Production implementations typically include additional fields, constraints, and audit columns. See MakerKit's database architecture for the complete patterns.

Why RLS is Non-Negotiable

Supabase exposes your PostgreSQL database directly to the browser via PostgREST. Without RLS enabled, your anon API key gives anyone full read/write access to every table in your public schema.

Consider what happens without RLS:

// Anyone with your anon key can do this
const { data } = await supabase
  .from('users')
  .select('*'); // Returns ALL users in your database

RLS adds implicit WHERE clauses to every query based on policies you define. The database enforces access rules, not your application code. This means:

• Security works regardless of client (web, mobile, direct API)
• Bugs in your app logic can't bypass access controls
• You can safely expose your database to untrusted clients

The rule is simple: enable RLS on every table in your public schema. No exceptions. (Note: the service_role key intentionally bypasses RLS for admin operations.)

When to Use Different RLS Patterns

Here's a decision framework for choosing the right pattern:

Use inline policies when:

• Simple ownership checks (auth.uid() = user_id)
• Single-table access control
• No joins required in the policy

Use security definer functions when:

• Permission checks involve 3+ tables
• Query times exceed 100ms with inline subqueries
• Same permission logic is reused across 5+ policies

Avoid security definer functions when:

• Simple single-table ownership checks suffice
• The function would only wrap auth.uid() = user_id

If unsure: Start with inline policies. Measure with EXPLAIN ANALYZE. Optimize if queries exceed 50ms on your typical data size.

RLS Fundamentals

Let's start with the basics.

Enabling RLS

For any table you create:

-- Create your table
create table public.documents (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id),
  title text not null,
  content text,
  created_at timestamptz default now()
);
-- Enable RLS (required)
alter table public.documents enable row level security;

With RLS enabled and no policies defined, the table is completely inaccessible. This is the safe default, unless you were to use the service role key to bypass RLS for admin operations.

Policy Types

RLS policies use two clauses:

• USING: Filters which existing rows a user can see or affect
• WITH CHECK: Validates new or modified row data

Here's when each applies:

A common pattern for user-owned data:

-- Users can only see their own documents
create policy "Users read own documents"
  on public.documents
  for select
  to authenticated
  using ((select auth.uid()) = user_id);
-- Users can only insert documents they own
create policy "Users insert own documents"
  on public.documents
  for insert
  to authenticated
  with check ((select auth.uid()) = user_id);
-- Users can update their own documents
create policy "Users update own documents"
  on public.documents
  for update
  to authenticated
  using ((select auth.uid()) = user_id)
  with check ((select auth.uid()) = user_id);

Notice the to authenticated clause. Always specify the role, so that it prevents unnecessary policy evaluation for anonymous users.

Performance Optimizations That Matter

RLS policies are implicit WHERE clauses evaluated for every row. On large tables, a naive policy can turn a simple SELECT into a slow operation. Here are the key optimizations.

1. Index Columns Used in Policies

If your policy filters on user_id, index it:

create index ix_documents_user_id
  on public.documents using btree (user_id);

This speeds up the query by a lot as your query planner will be able to use the index to find the rows that match the policy.

2. Wrap Functions in SELECT

This is the single most impactful optimization most developers miss.

-- Slow: auth.uid() called for every row
create policy "Users read own documents"
  on public.documents
  for select
  to authenticated
  using (auth.uid() = user_id);
-- Fast: auth.uid() cached and reused
create policy "Users read own documents"
  on public.documents
  for select
  to authenticated
  using ((select auth.uid()) = user_id);

The (select auth.uid()) pattern tells PostgreSQL to evaluate the function once and cache the result. Without it, auth.uid() runs for every row being checked.

This is a very important optimization, so I recommend to always use it.

3. Optimize Join Query Direction

When checking membership in related tables, query direction matters:

-- Slow: Correlates with each row in the source table
create policy "Team members read documents"
  on public.documents
  for select
  to authenticated
  using (
    (select auth.uid()) in (
      select user_id from team_members
      where team_members.team_id = documents.team_id
    )
  );
-- Fast: Get user's teams once, then filter
create policy "Team members read documents"
  on public.documents
  for select
  to authenticated
  using (
    team_id in (
      select team_id from team_members
      where user_id = (select auth.uid())
    )
  );

The fast version builds a set of the user's team IDs first, then filters documents. The slow version runs a subquery for every document row.

4. Use Security Definer Functions wisely

For very complex permission checks involving multiple tables, security definer functions can bypass RLS overhead on intermediate tables, by allowing the function to be called by any authenticated user:

create or replace function public.has_role_on_account(
  account_id uuid,
  account_role varchar(50) default null
) returns boolean
language sql
security definer
set search_path = ''
as $$
  select exists(
    select 1
    from public.accounts_memberships membership
    where membership.user_id = (select auth.uid())
      and membership.account_id = has_role_on_account.account_id
      and (
        membership.account_role = has_role_on_account.account_role
        or has_role_on_account.account_role is null
      )
  );
$$;

Then use it in policies:

create policy "Account members read documents"
  on public.documents
  for select
  to authenticated
  using ((select public.has_role_on_account(account_id)));

If you create security definer functions in schemas exposed via the API (like public if it's in your exposed schemas) always make sure to add the required checks to prevent privilege escalation by making sure the user (denoted by auth.uid()) has the required permissions to perform the operation the procedure is trying to perform.

If you don't, any authenticated user will be able to call the function and bypass the RLS policies.

5. Add Application-Level Filters

RLS policies are implicit WHERE clauses. Adding explicit filters in your queries helps the optimizer:

// Without explicit filter: relies entirely on RLS
const { data } = await supabase
  .from('documents')
  .select('*');
// With explicit filter: helps query planner
const { data } = await supabase
  .from('documents')
  .select('*')
  .eq('account_id', accountId);

Even though RLS would filter the same rows, the explicit filter allows PostgreSQL to use indexes more effectively and make the query faster.

Production Patterns for Multi-Tenant Apps

Simple user-owned data is straightforward but real SaaS apps need team-based access with role hierarchies and granular permissions. Here are patterns we use in production at Makerkit.

Team-Based Access with Role Checking

Let's assume we have the following tables.

A table for accounts, in which we store the account details. For personal user accounts, we use the Supabase auth.users table as the primary key, and for team/organization-based accounts, we use a custom UUID. The primary_owner_user_id tracks the original creator of the account.

create table public.accounts (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  slug text unique,
  primary_owner_user_id uuid references auth.users(id),
  created_at timestamptz default now()
);

A table for account memberships, in which we store the user-to-account relationships with roles.

create table public.accounts_memberships (
  id uuid primary key default gen_random_uuid(),
  account_id uuid not null references public.accounts(id),
  user_id uuid not null references auth.users(id),
  account_role varchar(50) not null,
  created_at timestamptz default now()
);

For team/organization-based access, we can use the has_role_on_account function to check if the user has a role on the account (defined below).

For example, we can create a policy to allow users to read accounts they're members of (or if they are the primary owner of the account, as it happens in personal user accounts).

-- Users can read accounts they're members of
create policy accounts_read
  on public.accounts
  for select
  to authenticated
  using (
    ((select auth.uid()) = primary_owner_user_id)
    or public.has_role_on_account(id)
  );

The has_role_on_account function (shown earlier) encapsulates the membership check. This keeps policies readable and centralizes the permission logic.

Permission-Based Policies

Let's now assume we have the following table for role permissions, in which we store the permissions for each role. First, we define an enum for the available permissions:

create type public.app_permissions as enum (
  'members.manage',
  'invites.manage',
  'settings.manage'
);
create table public.role_permissions (
  id uuid primary key default gen_random_uuid(),
  role varchar(50) not null,
  permission public.app_permissions not null,
  created_at timestamptz default now()
);

For feature-level permissions (like "can manage team members" vs "can only view"), we can use the has_permission function to check if the user has a specific permission on the account.

create or replace function public.has_permission(
  p_user_id uuid,
  p_account_id uuid,
  p_permission public.app_permissions
) returns boolean
language plpgsql
security definer
set search_path = ''
as $$
begin
  return exists(
    select 1
    from public.accounts_memberships m
    join public.role_permissions rp
      on m.account_role = rp.role
    where m.user_id = p_user_id
      and m.account_id = p_account_id
      and rp.permission = p_permission
  );
end;
$$;

Note: We use security definer here to bypass RLS on the lookup tables (accounts_memberships and role_permissions) during the permission check. The function validates the passed user_id against the permission tables, so there's no privilege escalation risk.

Then use it in policies:

create policy invitations_update
  on public.invitations
  for update
  to authenticated
  using (
    public.has_permission(
      (select auth.uid()),
      account_id,
      'invites.manage'::public.app_permissions
    )
  );

This pattern lets you define granular permissions per role without modifying policies when roles change.

Preventing Privilege Escalation

Let's now assume we have the following table for invitations, in which we store the invitations for each account.

create table public.invitations (
  id uuid primary key default gen_random_uuid(),
  account_id uuid not null references public.accounts(id),
  email text not null,
  role varchar(50) not null,
  created_at timestamptz default now()
);

When users can modify other users' roles, we need to prevent them from granting higher privileges than they have, to prevent privilege escalation.

create policy invitations_update
  on public.invitations
  for update
  to authenticated
  using (
    public.has_permission(
      (select auth.uid()),
      account_id,
      'invites.manage'::public.app_permissions
    )
    and public.has_more_elevated_role(
      (select auth.uid()),
      account_id,
      role
    )
  )
  with check (
    public.has_permission(
      (select auth.uid()),
      account_id,
      'invites.manage'::public.app_permissions
    )
    and public.has_more_elevated_role(
      (select auth.uid()),
      account_id,
      role
    )
  );

The has_more_elevated_role function checks that the current user's role hierarchy level is higher than the role they're trying to assign, to prevent privilege escalation by allowing users to assign themselves roles higher than their own.

Feature Flags in RLS

Let's now assume we have the following table for subscriptions, in which we store the subscriptions for each account.

create table public.subscriptions (
  id uuid primary key default gen_random_uuid(),
  account_id uuid not null references public.accounts(id),
  created_at timestamptz default now()
);
alter table public.subscriptions enable row level security;

And a table for storing feature flags, in which we store the feature flags for each account.

create table public.feature_flags (
  id uuid primary key default gen_random_uuid(),
  account_id uuid not null references public.accounts(id),
  feature_flag varchar(50) not null,
  created_at timestamptz default now()
);
alter table public.feature_flags enable row level security;
create policy feature_flags_read
  on public.feature_flags
  for select
  to authenticated
  using (
    public.has_role_on_account(account_id) or account_id = (select auth.uid())
  );

The RLS policy for the feature_flags table is very simple - it allows users to read feature flags for their own account.

Then we can create a policy to allow users to read subscriptions they're members of (or if they are the primary owner of the account, as it happens in personal user accounts).

We can now define a function to check if a feature flag is enabled for an account by checking the feature_flags table.

create or replace function public.is_feature_flag_enabled(
  account_id uuid,
  feature_flag varchar(50)
) returns boolean
set search_path = ''
as $$
begin
  return exists(
    select 1
    from public.feature_flags
    where account_id = is_feature_flag_enabled.account_id
    and feature_flag = is_feature_flag_enabled.feature_flag
  );
end;
$$ language plpgsql;

For conditional features based on account settings or subscription status:

create policy subscriptions_read
  on public.subscriptions
  for select
  to authenticated
  using (
    (
      public.has_role_on_account(account_id)
      and public.is_feature_flag_enabled(account_id, 'enable_team_account_billing')
    )
    or (
      account_id = (select auth.uid())
      and public.is_feature_flag_enabled(account_id, 'enable_account_billing')
    )
  );

Nested Relationships with EXISTS

Let's now assume we have the following table for subscription items, in which we store the items for each subscription.

create table public.subscription_items (
  id uuid primary key default gen_random_uuid(),
  subscription_id uuid not null references public.subscriptions(id),
  created_at timestamptz default now()
);

For child tables that should inherit parent access, you can use the EXISTS clause to check if the user has access to the parent table:

create policy subscription_items_read
  on public.subscription_items
  for select
  to authenticated
  using (
    exists (
      select 1
      from public.subscriptions
      where id = subscription_id
        and (
          account_id = (select auth.uid())
          or public.has_role_on_account(account_id)
        )
    )
  );

This pattern checks if the user has access to the parent subscription before allowing access to subscription items.

Security Best Practices

Never Trust user_metadata

This is specific to Supabase, but it's a very important security best practice.

JWT claims in raw_user_meta_data can be modified by the user. Don't use them for authorization:

-- DANGEROUS: user can modify their own metadata
create policy "Admins only"
  on public.admin_data
  for select
  to authenticated
  using (
    (auth.jwt() -> 'user_metadata' ->> 'role') = 'admin'
  );
-- SAFE: app_metadata can only be set server-side
create policy "Admins only"
  on public.admin_data
  for select
  to authenticated
  using (
    (auth.jwt() -> 'app_metadata' ->> 'role') = 'admin'
  );

Even better, check against a database table rather than JWT claims.

Protect Service Role Keys

Service role keys bypass RLS entirely. Keep them server-side only:

• Never expose in client-side code
• Never commit to version control
• Use environment variables or a Secret Vault to store the service role key
• Rotate periodically

For server-side functions that need to bypass RLS, use the service role client:

import { createClient } from '@supabase/supabase-js';
// Server-side only
const adminClient = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!
);

Handle Views Carefully

Views bypass RLS by default because they run as the user who created them (typically postgres):

-- This view bypasses RLS on the underlying table
create view public.user_summary as
  select id, email from auth.users;

In PostgreSQL 15+, use security_invoker:

create view public.user_summary
with (security_invoker = true)
as select id, email from public.profiles;

With security_invoker = true, the view respects RLS policies of the tables it queries.

Watch for Null Comparisons

When auth.uid() returns null (for anonymous users), comparisons silently fail:

-- If auth.uid() is null, this returns false (not an error)
using (auth.uid() = user_id)

Where appropriate, be explicit about authentication requirements:

-- Explicit check prevents silent failures
using (
  auth.uid() is not null
  and auth.uid() = user_id
)

Or better, use the to authenticated clause to prevent anonymous access entirely.

Testing RLS Policies

Shipping untested RLS policies is asking for trouble. Here are two approaches we use in production at Makerkit.

Manual Testing with Impersonation

Supabase Studio lets you impersonate users to test policies:

1. Open the SQL Editor in your Supabase dashboard
2. Click the "RLS" dropdown
3. Select "Impersonate user"
4. Run queries as that user

This works for quick checks but doesn't scale - and as a programmer, you probably want things to be automated and repeatable as much as possible.

Automated Testing with pgTap

pgTap is a unit testing framework for PostgreSQL. We use it to test every RLS policy in CI, to catch any bugs early and prevent them from shipping.

The basejump-supabase_test_helpers extension provides a set of utilities to help you write tests for RLS policies - and it's an invaluable tool for testing Supabase with pgTap:

begin;
-- Load test helpers
create extension if not exists "basejump-supabase_test_helpers" version '0.0.6';
select no_plan();
-- Create test users
select tests.create_supabase_user('owner', 'owner@test.com');
select tests.create_supabase_user('member', 'member@test.com');
select tests.create_supabase_user('outsider', 'outsider@test.com');
-- Create test data (as service_role to bypass RLS)
set local role service_role;
-- Insert test account directly
insert into public.accounts (id, name, slug, primary_owner_user_id)
values (
  gen_random_uuid(),
  'Test Team',
  'test-team',
  tests.get_supabase_uid('owner')
);
-- Add owner as member
insert into public.accounts_memberships (account_id, user_id, account_role)
select id, tests.get_supabase_uid('owner'), 'owner'
from public.accounts where slug = 'test-team';
-- Test: Owner can read account
select tests.authenticate_as('owner');
select isnt_empty(
  $$ select * from public.accounts where slug = 'test-team' $$,
  'Owner should see their team account'
);
-- Test: Outsider cannot read account
select tests.authenticate_as('outsider');
select is_empty(
  $$ select * from public.accounts where slug = 'test-team' $$,
  'Outsider should not see team account'
);
-- Test: Owner has permission to manage members
select tests.authenticate_as('owner');
select row_eq(
  $$ select public.has_permission(
    auth.uid(),
    (select id from public.accounts where slug = 'test-team'),
    'members.manage'::public.app_permissions
  ) $$,
  row(true::boolean),
  'Owner should have members.manage permission'
);
select * from finish();
rollback;

Run tests with:

supabase test db

Key patterns:

• Wrap tests in begin/rollback to avoid polluting the database
• Use set local role service_role for test setup—RLS policies would otherwise block your fixture inserts
• Use tests.authenticate_as() to switch user context (provided by the basejump helper)
• Switch back to service_role when verifying results after an "attack" to see the true database state
• Test both positive cases (user CAN access) and negative cases (user CANNOT access)
• Test permission boundaries and edge cases

Understanding RLS Silent Failures

A critical testing quirk: RLS failures don't always throw errors. The behavior differs by operation:

This means you cannot use throws_ok() to test SELECT/UPDATE/DELETE failures:

-- WRONG: This test will FAIL because no error is thrown
select throws_ok(
  $$ select * from projects where id = 'inaccessible-id' $$,
  'some error message'
);
-- RIGHT: Test that the result is empty
select is_empty(
  $$ select * from projects where id = 'inaccessible-id' $$,
  'Unauthorized user cannot see project'
);

For UPDATE and DELETE, verify the result state instead of expecting errors:

-- Setup: Record state before attack
set local role service_role;
select is(
  (select name from projects where id = 'target-id'),
  'Original Name',
  'Project name before unauthorized update'
);
-- Attempt unauthorized update
select tests.authenticate_as('attacker');
update projects set name = 'HACKED!' where id = 'target-id';
-- Verify data unchanged (not that an error was thrown)
set local role service_role;
select is(
  (select name from projects where id = 'target-id'),
  'Original Name',
  'Project name unchanged after unauthorized update attempt'
);

For DELETE, count rows before and after:

set local role service_role;
select is((select count(*)::int from projects), 5, 'Count before delete attempt');
select tests.authenticate_as('attacker');
delete from projects where id = 'target-id';
set local role service_role;
select is((select count(*)::int from projects), 5, 'Count unchanged after failed delete');

For a complete example with schema, policies, and tests demonstrating these patterns, see our RLS Testing Example on GitHub.

The testing patterns use the excellent Basejump test helpers which make pgTap testing much simpler. For a deep dive on testing Supabase with pgTap, see their comprehensive guide.

Storage RLS

Supabase Storage uses the same RLS system. Here's a pattern for account-based file access:

-- Create a helper to extract account ID from filename
create or replace function kit.get_storage_filename_as_uuid(name text)
returns uuid
set search_path = ''
as $$
begin
  return replace(
    storage.filename(name),
    concat('.', storage.extension(name)),
    ''
  )::uuid;
end;
$$ language plpgsql;
-- Policy: Users can read/write files for their accounts
create policy account_image
  on storage.objects
  for all
  using (
    bucket_id = 'account_image'
    and (
      kit.get_storage_filename_as_uuid(name) = auth.uid()
      or public.has_role_on_account(
        kit.get_storage_filename_as_uuid(name)
      )
    )
  )
  with check (
    bucket_id = 'account_image'
    and (
      kit.get_storage_filename_as_uuid(name) = auth.uid()
      or public.has_permission(
        auth.uid(),
        kit.get_storage_filename_as_uuid(name),
        'settings.manage'::public.app_permissions
      )
    )
  );

This pattern:

• Names files with account UUIDs (e.g., 123e4567-e89b-12d3-a456-426614174000.png)
• Extracts the UUID from the filename to check access
• Uses different permissions for read (any team member) vs write (settings.manage permission)

Common Pitfalls

Forgetting to Enable RLS

The most common mistake. Use a pre-commit hook or CI check:

-- Find tables without RLS enabled
select t.schemaname, t.tablename
from pg_tables t
join pg_class c on c.relname = t.tablename
join pg_namespace n on n.oid = c.relnamespace and n.nspname = t.schemaname
where t.schemaname = 'public'
  and c.relkind = 'r'
  and c.relrowsecurity = false;

RLS with No Policies

Enabling RLS with zero policies makes the table inaccessible. This is safe but often unintentional:

-- Find tables with RLS but no policies
select c.relname as table_name
from pg_class c
join pg_namespace n on c.relnamespace = n.oid
where n.nspname = 'public'
  and c.relkind = 'r'
  and c.relrowsecurity = true
  and not exists (
    select 1 from pg_policies p
    where p.tablename = c.relname
      and p.schemaname = n.nspname
  );

UPDATE Without SELECT

UPDATE policies require a corresponding SELECT policy. Without it, updates will silently fail:

-- This alone won't work
create policy "Users update own"
  on documents for update
  to authenticated
  using (user_id = auth.uid());
-- Need this too
create policy "Users read own"
  on documents for select
  to authenticated
  using (user_id = auth.uid());

Debugging Policy Failures

When queries return empty but shouldn't, check:

1. Is RLS enabled? (select relrowsecurity from pg_class where relname = 'tablename')
2. Do policies exist? (select * from pg_policies where tablename = 'tablename')
3. Is the user authenticated? (Check JWT claims)
4. Does the user meet policy conditions? (Test with impersonation)

Use EXPLAIN to see policy evaluation:

set session role authenticated;
set request.jwt.claims to '{"role":"authenticated", "sub":"user-uuid-here"}';
explain analyze select * from documents;

Production Checklist

Before shipping:

• [ ] RLS enabled on all public schema tables
• [ ] Every table has appropriate policies for SELECT, INSERT, UPDATE, DELETE
• [ ] Policies use to authenticated (or to anon if intentional)
• [ ] Functions wrapped in (select ...) for caching
• [ ] Columns used in policies are indexed
• [ ] Complex permission checks use security definer functions
• [ ] Security definer functions are in non-exposed schemas
• [ ] Views use security_invoker = true (PostgreSQL 15+)
• [ ] Service role key is server-side only
• [ ] pgTap tests cover policy logic
• [ ] Manual testing done with user impersonation

Next Steps

RLS is one piece of database security. For related topics:

• Storing secrets securely with Supabase Vault
• Managing API keys in Supabase projects
• MakerKit RLS documentation for kit-specific implementation details
• MakerKit database architecture documentation

The patterns in this guide come from MakerKit's production codebase. If you want a head start with pre-configured RLS policies for multi-tenant SaaS, check out the Next.js Supabase SaaS Starter Kit.