Supabase RLS: Building Secure Multi-Tenant Applications with Row-Level Security

A few months into building a B2B application, one of our engineers accidentally ran a support request in the wrong session before realizing their mistake - for approximately two seconds, they were able to see another company's data. Fortunately, nothing was shared with a customer, but it was enough to realize that tenant isolation could not be a nice-to-have on the architecture diagram.

If you're building a multi-tenant application on Supabase, this scenario is the reason why you should be using PostgreSQL's Row-Level Security (RLS). Instead of trusting every query across your application to include the WHERE tenant_id = clause you're relying on, you want to make sure that every request is isolated by default at the database level, regardless of the client or access key.

Here's how to do it right: the schema design, policies, performance considerations, and the traps to avoid that would otherwise leak data between your customers.

Quick answer: Supabase achieves multi-tenancy through PostgreSQL's RLS, with a helper function or JWT claims being used to isolate queries by organisation/project.

The Problem

You're building a Saas where every customer (say, Acme and Globex) has their own teams of users and their own set of projects and data. The requirement is simple but occasionally tricky to implement and reason about:

An Acme user should not be able to see or modify any Globex data, and vice versa.

Some guides suggest using RLS to allow users to only see their own data, such as auth.uid() = user_id. However, in a multi-tenant scenario, a user belongs to a tenant organisation and has to be able to see all its data, while being isolated from data belonging to any other organisation.

This means that any code that reads or modifies data needs to include this isolation logic, and there are plenty of places where it could be forgotten: background jobs, ad-hoc queries run by support, or even an admin panel's misconfigured query could leak all data belonging to an organisation. Once such a bug appears, it's hard to find and even harder to regain customer trust.

Why Not Filter Queries in Your App Code?

You might think that using the organisation/user ID filter in your app code is easier or sufficient. Here's why it isn't:

❌ Developers need to remember to filter their queries every time they perform a query.

❌ It doesn't isolate background jobs or database maintenance scripts.

❌ An anonymous or service key may be used to access more data than intended.

❌ It's much harder to audit and secure since every query's safety depends on an application-layer check, and there are many places such checks could be missed.

In short, RLS is much safer because it ensures that by default, every query is isolated and cannot access data that the current user isn't supposed to see, even if an API key is leaked or a support engineer opens the devtools. The only place where the isolation needs to be implemented is the database.

Filtering in app code**

Row-Level Security

Isolation logic is duplicated in every query

Isolation logic is written once and applied to all queries

Easy to forget to apply for some queries

Harder to miss, especially after initial setup

Leaks if query logic for a specific request is changed

More resistant to changes, including accidental ones

Does not isolate scripts or background jobs

Isolates those by default

Harder to audit

Easier to audit since there's only one place to check

Easy to bypass if someone knows how the app works

Harder to bypass even if someone bypasses the application layer

Can be too slow for high-performance apps

Usually performs well, especially with proper indexing

Overview

When a logged-in user sends a request to your Supabase backend, it gets processed by PostgreSQL with the user's JWT attached, and their RLS policies are applied. The result of this is that either an authenticated user can see only the rows they are supposed to see, or an anonymous user gets filtered to a specific subset of data as well.

The general design is as follows:

• ***Create an organisations table, which defines the isolation boundary.*
• ***Add membership records for each organisation's users with their roles.*
• ***Add a foreign key to organisation_id to all relevant tables.*
• ***Add RLS policies to those tables which only allow reading and writing for members of organisations.*

Now let's go over the details of actually implementing this.

Prerequisites

Before getting started, make sure you have the following:

• ***A Supabase project set up (it's completely free for development)*
• ***Some familiarity with PostgreSQL's SQL syntax*
• ***An understanding of how Supabase Auth works*
• ***A development environment for testing your front-end/back-end (Node.js with Next.js or similar)*
• ***Access to the Supabase SQL editor*

Step 1: Design the Multi-Tenant Schema

Instead of using the user to represent the isolation boundary, a user belongs to an organisation, which has its own set of data. This allows multiple users to work together on the same data.

-- The isolation boundary

create table organisations (

id uuid primary key default gen_random_uuid(),

name text not null,

slug text unique not null,

created_at timestamptz default now()

);

-- Organisation members have a role

-- This allows managing users with different access levels

create table organisation_members (

organisation_id uuid references organisations(id) on delete cascade,

user_id uuid references auth.users(id) on delete cascade,

role text not null default 'member', -- Can be 'owner', 'admin', or 'member'

joined_at timestamptz default now(),

primary key (organisation_id, user_id) -- Also the index

);

-- Example tenant-scoped table

create table projects (

id uuid primary key default gen_random_uuid(),

organisation_id uuid references organisations(id) on delete cascade not null,

name text not null,

created_at timestamptz default now()

);

There are two important things to note in the schema:

• ***Every table which needs to be isolated has an organisation_id.*
• ***The organisation_members table has two foreign keys to the organisation and to the Supabase Auth user.*

Step 2: Enable Row-Level Security

This step is often overlooked, and it is vital to remember to enable it for each table.

alter table organisations enable row level security;

alter table organisation_members enable row level security;

alter table projects enable row level security;

After this, any query which selects from the table will return no rows unless there is an explicit policy allowing access.

Step 3: Create the Membership Helper Function

To avoid writing the same logic in multiple policies, it's best to extract it into a single function:

create or replace function public.is_org_member(org_id uuid)

returns boolean

language sql

security definer -- reads organisation_members without recursion

stable -- allows the planner to cache results within a statement

set search_path = '' -- prevent privilege escalation, see below

as $$

select exists (

select 1

from public.organisation_members

where organisation_id = org_id

and user_id = (select auth.uid())

);

$$;

This creates a helper which is used to check if a user is a member of a given organisation. The most interesting parts here are:

• *security definer which allows the function to read the organisation_members table without recursion.*
• *set search_path = '' prevents privilege escalation attacks by ensuring that identifiers are resolved using the search_path. Always qualify identifiers in this function with the schema name.*
• *stable allows caching of results within a statement, which is safe since the function does not modify any database objects.*

Additionally, a role-aware version of the function can be created:

create or replace function public.has_org_role(org_id uuid, required_role text)

returns boolean

language sql

security definer

stable

set search_path = ''

as $$

select exists (

select 1

from public.organisation_members

where organisation_id = org_id

and user_id = (select auth.uid())

and role in ('owner', required_role) -- owners implicitly pass any role check

);

$$;

Step 4: Write the RLS Policies

This is the most involved part: writing the policies for each table. Each policy calls the helper function, allowing for flexible membership logic. Here's how policies for projects might look:

-- Any member can READ their organisation's projects

create policy "members read org projects"

on public.projects for select

to authenticated

using ( (select public.is_org_member(organisation_id)) );

-- Only admins/owners can CREATE projects

create policy "admins insert org projects"

on public.projects for insert

to authenticated

with check ( (select public.has_org_role(organisation_id, 'admin')) );

-- Only admins/owners can UPDATE (validated both ways)

create policy "admins update org projects"

on public.projects for update

to authenticated

using ( (select public.has_org_role(organisation_id, 'admin')) )

with check ( (select public.has_org_role(organisation_id, 'admin')) );

-- Only admins/owners can DELETE

create policy "admins delete org projects"

on public.projects for delete

to authenticated

using ( (select public.has_org_role(organisation_id, 'admin')) );

For each operation (select, insert, update, delete), a policy must be created, allowing or denying access based on the functions described. The USING clause specifies which database rows are visible to SELECT, UPDATE, and DELETE. WITH CHECK determines whether an INSERT or UPDATE is allowed, thus validating if the new row is valid.

Operation

USING

WITH CHECK

Needs a SELECT policy too?

SELECT

✅ required

-

-

INSERT

-

✅ required

-

UPDATE

✅ (rows it can touch)

✅ (resulting row)

✅ yes

DELETE

✅ required

-

✅ yes

While USING determines which existing rows a query can see, WITH CHECK determines whether an INSERT or UPDATE should be allowed. An UPDATE policy without a WITH CHECK clause or a SELECT policy could cause a user to leak information by updating a row's organisation_id to another value, thus making it visible to them in the future. Therefore, it's important to have all three policies set up.

Step 5: Prove It Works With Tests

Do not rely on intuition - prove that your policies are actually secure. PostgreSQL's pgTAP extension is great for writing tests directly in SQL:

alter role authenticated set default_transaction_isolation = 'read committed';

begin;

select plan(2);

-- Assume that the fixtures below have been created using the service role

-- These fixtures define two organisations (Acme and Globex) and two users (Alice and Bob)

-- Impersonate a member of Acme

set local role authenticated;

set local request.jwt.claim.sub = '11111111-1111-1111-1111-111111111111';

-- ✅ Positive test: the Acme member CAN see Acme's project

select isnt_empty(

$$ select id from public.projects where organisation_id = 'aaaaaaaa-...'::uuid $$,

'member can read their own org projects'

);

-- 🔒 Negative test: the Acme member CANNOT see Globex's project

select is_empty(

$$ select id from public.projects where organisation_id = 'bbbbbbbb-...'::uuid $$,

'member cannot read another org projects'

);

select * from finish();

rollback;

The pgTAP tests are simple to write, and their greatest advantage is being able to verify that a negative test actually fails. The most important tests are the ones ensuring that a user cannot access data they shouldn't be able to - these should always be written first.

Keep in mind that a failed SELECT/UPDATE/DELETE will return an empty result set, not an error - therefore it is vital to explicitly check that the number of rows is actually not zero. This is why tests are an integral part of this process.

Going Faster: JWT Claims

While the described method is flexible and safe, there is a significant performance consideration: every policy that checks membership will perform a subquery against organisation_members. This may be an expensive query for a very large table.

A faster alternative is to encode the organisation ID in the JWT claims directly, and use it to isolate data:

create or replace function public.custom_access_token_hook(event jsonb)

returns jsonb

language plpgsql

stable

as $$

declare

claims jsonb;

org uuid;

begin

select organisation_id into org

from public.organisation_members

where user_id = (event->>'user_id')::uuid

limit 1;

claims := event->'claims';

claims := jsonb_set(claims, '{app_metadata,organisation_id}', to_jsonb(org));

event := jsonb_set(event, '{claims}', claims);

return event;

end;

$$;

grant execute on function public.custom_access_token_hook to supabase_auth_admin;

revoke execute on function public.custom_access_token_hook from authenticated, anon, public;

After enabling this hook in the dashboard, organisation ID can be read as a JWT claim:

create policy "read via jwt organisation"

on public.projects for select

to authenticated

using (

organisation_id = ((select auth.jwt()) -> 'app_metadata' ->> 'organisation_id')::uuid

);

This allows PostgreSQL to use a B-tree index on organisation_id, improving query performance dramatically. This is especially useful for large-scale applications.

On the flip side, JWT claims are a single snapshot of the organisation information - if an organisation is reloaded, the token will not change until it expires. This is fine for read-heavy applications, but writes such as inserting projects need to be handled carefully.

In most cases, using organisation claims for reading and organisation_members for writing is a good approach - it isolates data and maintains performance, and organisation_members can be used to determine if the user has access to a particular organisation.

Finally, make sure to store information in app_metadata and not user_metadata - this field is controlled by the server and cannot be modified by the user, unlike the user_metadata field.

Common Problems (And Their Fixes)

RLS has been enabled, but users don't see any data.

You forgot to create a SELECT policy for this table. After enabling RLS, the tables return no rows unless a policy grants access.

Users can see other users' data.

RLS is not enabled for this table or organisation_id is not used, or a policy is using the wrong organisation column.

"infinite recursion detected in policy."

The policy is trying to read another table that refers back to this one. The best practice is to always put such logic inside a security definer function to avoid this issue.

An UPDATE or DELETE is failing to affect any rows.

The table needs a SELECT policy - these are always required for UPDATE and DELETE policies.

Data is being leaked despite policies being present.

Make sure that the client is not misusing the service_role key, which can be used to bypass RLS and access all data. Only the server should use service_role.

Best Practices Checklist

• ***✅ Enable RLS on every table where data needs to be isolated.*
• *✅ For frequently used functions, wrap them inside a subselect query - (select auth.uid()) instead of auth.uid()** - to help the query planner optimise the query.*
• *✅ Add indexes on all columns used in policies - create index on public.projects (organisation_id);**.*
• *✅ Always put complex logic in a security definer** function, with proper qualifiers and search_path set.*
• *✅ Store organisation_id** server-side, not client-side.*
• *✅ Never use user_metadata** for anything that needs to be secured - it is easily accessible to users.*
• ***✅ Write tests, including negative tests, and run them regularly.*

Performance And Security Notes

When benchmarking multi-tenancy performance later, it becomes apparent that the following three practices reduce latency significantly:

• *Function wrapping: Adding select  before auth.uid()** allows PostgreSQL to process these as initPlans and only once per statement, as opposed to once per row.*
• *Indexing: A composite primary key plus a secondary index on organisation_id** for querying are must-have for performance. Without these, queries would use a sequential scan and be much slower.*
• ***Using claims for reads: The organisation claims allow the fastest possible performance since no extra tables need to be queried; a B-tree index can be used directly.*

When it comes to security, RLS is not a silver bullet on its own. There are multiple ways to reduce attack surface and exposure, such as denying direct access to certain columns and instead providing views which only expose what the user needs to see. Additionally, security_invoker = true must be set on any views which use policies so that they are properly isolated.

Always ensure that production queries are benchmarked using an actual authenticated role, not the postgres account, as query planning and performance can be vastly different.

When To Use This Strategy

RLS multi-tenancy is best suited for the following situations:

✅ You are building a B2B or team-based application

✅ You want to enforce isolation at the database level

✅ You are using PostgreSQL and want to avoid setting up an entirely separate auth system

✅ Your application's tenants are organisations with collaborating members

Consider other approaches in the following situations:

⚠️ You need to meet strict regulatory requirements (schema or database isolation)

⚠️ Your tenants require extensive customisations at the schema level

⚠️ Your application has demanding performance requirements beyond what RLS can handle (unlikely, but possible)

FAQ

Is Supabase suitable for building multi-tenant applications?

Yes, as PostgreSQL's RLS provides the necessary features to build a multi-tenant application on top of Supabase.

What's the best way to store tenant information in Supabase?

Every tenant-scoped table should contain an organisation_id, and organisation members should be stored in another table.

Does enabling RLS add any overhead to queries?

It can, but following performance best practices should eliminate most of it. Queries which use RLS are often faster than those using application-layer filters. Additionally, PostgreSQL's query planner will optimise queries using its knowledge of the policies' constraints.

Can users see other users' data with RLS enabled?

No, assuming RLS has been configured correctly. If users can see other users' data, it is likely because RLS had not been enabled for some tables or policies were using the wrong organisation column.

What's the difference between USING and WITH CHECK?

The USING clause is used to determine which database rows are visible to SELECT, UPDATE, and DELETE. WITH CHECK determines whether an INSERT or UPDATE is allowed.

Should organisation_id be stored in app_metadata or user_metadata?

Always use app_metadata as user_metadata is user-editable.

Key Takeaways

• *Multi-tenancy is not simply auth.uid() = user_id**. That approach isolates individual users, while multi-tenancy isolates organisations or teams of users which share data.*
• ***Enable RLS on all tenant-specific tables to make sure they are not accessible to other organisations or the public.*
• *Create security definer** functions which help with policy management and avoid recursion.*
• *Use USING and WITH CHECK** to ensure that users can only access or modify the rows they are supposed to see.*
• *Always wrap RLS checks in subselects and use initplans** for performance.*
• ***Put claims in JWTs when performance is more important than up-to-date information.*
• ***Perform pgTAP tests regularly to ensure that nothing can access data it shouldn't be able to access.*

As RLS is a feature of PostgreSQL, it is less prone to errors, and isolates data regardless of which client or server key is used to access it, which is why it is such a powerful security tool for a SaaS application.

Build It With Confidence

Making sure that your multi-tenancy setup correctly isolates each organisation's data and is performant can be a challenge. At MatlabInfotech, we specialise in building secure and scalable applications on Supabase, PostgreSQL, and Next.js: schema design, policy writing, performance improvements, and tests are all part of the process. Want to discuss your multi-tenant product idea? Let's talk through your architecture.

References

• *Row Level Security - Supabase Docs*
• *Custom Claims & RBAC - Supabase Docs*
• *Custom Access Token Hook - Supabase Docs*

Secondary keywords: Supabase RLS, tenant isolation PostgreSQL, Supabase multi-tenant SaaS, Row-Level Security policies, Supabase JWT claims, multi-tenant architecture, Supabase RLS performance.

Suggested internal links: Next.js development guide, PostgreSQL optimization guide, Supabase authentication guide, Backend development services, SaaS development services.

Suggested images: supabase-multi-tenant-rls-architecture.png (alt: "Multi-tenant SaaS request flow through Supabase RLS to PostgreSQL"), supabase-rls-using-vs-with-check.png (alt: "Diagram comparing USING and WITH CHECK clauses in Supabase RLS policies").