Supabase does not have native support for multi-tenant backends. This article outlines a strategy for achieving multi-tenancy in a simple, performant way utilizing Supabase Auth and RLS policies.
A system where data belongs to a tenant and needs to be accessed by multiple users
For our purposes, we’ll define multi-tenancy as a system where data belongs to a tenant and needs to be accessed by multiple users who are associated with that tenant. As a practical example, maybe you’re building a B2B SaaS and each business that subscribes is allowed 5 individual users. The tenant is the business, all 5 users need access to the business’s data in your Supabase instance, and users should not be able to access data from other businesses
Define the Tenant
This is going to be very simple. You can adapt this setup to meet your needs. But for our illustrative example, we’ll assume the following table exists:
CREATE TABLE tenants (
id INT PRIMARY KEY,
name VARCHAR(255)
);
That’s all we’ll need. Tenants will be able to own data throughout your database and users associated with that tenant will have access to the data.
Associate Users
Normally you might think of using something like a `tenants_to_users` table here. Functionally, this works. However when it comes to building RLS policies later, this would end up incurring an additional SELECT for every query you execute! We can avoid this by storing `tenant_id` on the User’s `app_metadata`. This data is then easily accessible later on in our RLS policies.
To create a new user:
const { user, session, error } = await supabase.auth.signUp({
email: 'example@example.com',
password: 'examplepassword',
options: {
app_metadata: {
tenant_id: existingTenantId
}
}
})
To update an existing user (docs):
const { data: user, error } = await supabase.auth.admin.updateUserById(
existingUserId,
{ app_metadata: { tenant_id: existingTenantId } }
)
!!!IMPORTANT!!! You *must* use `app_metadata` and NOT `user_metadata`. The former is secure and cannot be modified by the user, where the latter can be.
Add tenant_id to tables
This is more of an exercise for you, the reader. But TLDR: for any table with data that needs to be isolated to tenants, add a `tenant_id` column and a foreign key back to `tenants.id`.
The RLS policies
The policies themselves are very simple, but first let’s give ourselves a handy way to retrieve the tenant_id from the authorized user making the query:
create or replace function auth.tenant_id()
returns text
language sql stable
as $$
select
nullif(
((current_setting('request.jwt.claims')::jsonb ->> 'app_metadata')::jsonb ->> 'tenant_id'),
''
)::text
$$;
This function grabs the `tenant_id` out of the `app_metadata` JSON for the current user. You can copy/paste that snippet and run it directly on the SQL Editor page in the Supabase UI to define the function in your database.
From there, the RLS policies are very simple:
(auth.tenant_id() = tenant_id)
And that’s it! We check the authorized user’s tenant_id against the tenant_id of the table being queried. This will automatically exclude data that belongs to any other tenant in the system. Note that you will still need to think through how to apply this policy for SELECT, UPDATE, INSERT, DELETE for each table. Perhaps data is visible to everyone but only updatable by the tenants who own it, etc.
Indexing Considerations
Something to note about RLS policies is that they are effectively equivalent to adding a `WHERE` clause to your query. This means that you are going to have lots of queries that are implicitly doing a `WHERE tenant_id = 123`, etc. So you’ll need to consider this when designing your indexes! In practice, you almost always just want to add `tenant_id` as the final column in your index. However use cases vary, so that’s not a hard rule and is up to you to think through for your specific application.
Nice pragmatic solution.
I would like to share with everyone my final solution.
Create a function to automatically add the tenant_id when inserting a new row or updating an existing ro in a table:
create or replace function set_tenant_id()
returns trigger
language plpgsql
as $$
begin
-- Setzt den Wert von auth.tenant_id() in die tenant_id-Spalte, falls diese NULL ist
if NEW.tenant_id is null then
NEW.tenant_id := auth.tenant_id();
end if;
return NEW;
end;
$$;
And the trigger for the table "article" as an example:
create trigger set_tenant_id_before_insert_or_update
before insert or update on article
for each row
execute function set_tenant_id();
Hi Ryan, thank you for this article, I found it very helpful. Just for reference (more in case anyone else had this issue), I wanted to update my auth.users table using Supabase's SQL editor, and I found that the column name I had to reference in the query was "raw_app_meta_data", not "app_metadata". Interestingly, when next creating the auth.tenant_id() function, it doesn't work with "raw_app_meta_data" - it must use "app_metadata" for the column name. It looks like there's 2 different names with which to read the column. But your instructions here work perfectly as long as one is doing it via JS as you have done.