Supabase Multi Tenancy - Simple and Fast

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.