Multi-Tenant SaaS Architecture with Postgres RLS: A Working Pattern

For most SaaS products, the right multi-tenant architecture is one shared Postgres database with a shared schema: every tenant row carries an account_id, and Row Level Security (RLS) enforces tenant isolation inside the database. You do not need a database per customer, and you should not rely on where tenant_id = ... filters spread across your application code. With RLS, Postgres refuses to return another tenant's rows even when a query in your app is missing a filter.

This guide covers the multi-tenant data model end to end: the accounts table, the RLS policy that isolates tenants, the roles and permissions model, column-level grants, and the indexing rule that keeps tenant queries fast. The SQL comes from the schema we ship in the MakerKit Next.js Supabase kit, which runs on Supabase with Postgres 17.

What is multi-tenant architecture?

Multi-tenant architecture is a software design where one application instance and one database serve many customers (tenants) while keeping each tenant's data private from the others. Tenants share infrastructure, which keeps costs low, and isolation is enforced logically, usually with a tenant identifier on each row plus access rules. Use it when you run one product for many customers and want a single deployment to operate and upgrade.

The operational benefit is that you deploy once, run each migration once, and ship each fix to every customer at the same time. The cost is that tenant isolation becomes your application's responsibility instead of being provided by separate servers.

Single-tenant vs multi-tenant architecture

Single-tenant gives each customer a dedicated application instance and database; multi-tenant shares both across customers. Choose multi-tenant for most SaaS, because it is much cheaper to operate and every customer gets updates at once. Choose single-tenant when a contract or regulation requires physical isolation.

For a typical B2B or B2C product the decision is how to isolate tenants, not whether to share infrastructure.

The three tenant isolation models in Postgres

Postgres supports three tenant isolation models, from most shared to most isolated: shared tables, schema per tenant, and database per tenant. Pick the most shared model your requirements allow, because each step toward isolation multiplies migrations, connections and infrastructure you have to manage.

• Shared schema, shared tables: all tenants live in the same tables, and an account_id column marks ownership. RLS enforces isolation. This is the cheapest model, needs one migration per change, and is the right default.
• Schema per tenant: one database with one Postgres schema per tenant. Isolation is stronger, but every migration runs once per tenant, and connection pooling and catalog size become problems past a few hundred tenants.
• Database per tenant: a full database per customer. This gives the strongest isolation and the highest operating cost. Reserve it for enterprise contracts that require it.

The rest of this guide covers the shared-schema model.

Why shared schema plus RLS is the right default

Row Level Security makes the database the enforcement point for tenant isolation, so a missing where clause in application code cannot leak data across tenants. In a shared-schema app without RLS, any query that forgets its tenant filter shows Customer A the rows of Customer B. With RLS, isolation is enforced by Postgres on every query.

Postgres has supported RLS since version 9.5. You attach policies to a table, and they restrict which rows each select, insert, update and delete can read or write. When RLS is enabled and no policy grants access, Postgres denies access. You write the policies once per table, and every query against that table goes through them, including queries written by a teammate next year.

MakerKit enables RLS on every table in the public schema. Application-layer checks are still useful for clear error messages and early returns, but in our model they are a second check; the database is the first.

The tenant model: personal and team accounts

In MakerKit the tenant is an account, which is either a personal account owned by one user or a team account shared by many users. Both live in one accounts table and are distinguished by the is_personal_account flag. This is the table definition from apps/web/supabase/schemas/03-accounts.sql:

create table if not exists
  public.accounts (
    id uuid unique not null default extensions.uuid_generate_v4(),
    primary_owner_user_id uuid references auth.users on delete cascade not null default auth.uid(),
    name varchar(255) not null,
    slug text unique,
    email varchar(320) unique,
    is_personal_account boolean default false not null,
    -- ...
    primary key (id)
  );

Two constraints in the same file keep the personal/team split consistent. A check constraint (accounts_slug_null_if_personal_account_true) requires personal accounts to have a null slug and team accounts to have one, and a partial unique index (unique_personal_account) allows each user to own only one personal account.

When a user signs up, the kit.setup_new_user() trigger on auth.users creates their personal account using the user's id as the account id. A personal account's id therefore equals the user's auth.users.id, which lets tenant policies check personal ownership with account_id = auth.uid() instead of a join.

Every table that holds tenant data references an account with the same foreign key. Here is the notifications table from apps/web/supabase/schemas/11-notifications.sql:

create table if not exists
  public.notifications (
    id bigint generated always as identity primary key,
    account_id uuid not null references public.accounts (id) on delete cascade,
    -- ...
  );

The line account_id uuid not null references public.accounts(id) on delete cascade is the tenant-linking convention. The same column appears on orders (10-orders.sql), subscriptions (09-subscriptions.sql), billing_customers (08-billing-customers.sql) and invitations (07-invitations.sql). When you add a feature table, add this column and copy the policy in the next section.

Some tables are scoped to a user instead of an account. They use a user_id column and policies that check user_id = auth.uid(). If the data belongs to a workspace, scope it to account_id. If it belongs to one person regardless of workspace, scope it to user_id.

How RLS enforces tenant isolation

The isolation policy on a tenant table returns a row only if the current user owns the account personally or is a member of it. This is the read policy on notifications, and it is the policy we copy onto every account-scoped table:

create policy notifications_read_self on public.notifications for select
  to authenticated using (
    account_id = (select auth.uid())
    or has_role_on_account(account_id)
  );

The using clause has two branches. account_id = (select auth.uid()) covers personal accounts, and it depends on the id equality created by the signup trigger. has_role_on_account(account_id) covers team accounts by checking membership.

has_role_on_account(), defined in 05-memberships.sql, is a security definer function that returns true when the current user has a membership row on the given account, optionally with a specific role:

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)
    );
  $$;

It runs as security definer so the membership lookup is not itself filtered by the RLS policies on accounts_memberships, and set search_path = '' prevents a caller from redirecting unqualified names to objects in another schema.

Membership lives in accounts_memberships (05-memberships.sql), a join table with a composite primary key of (user_id, account_id) and an account_role column that references the roles table:

create table if not exists
  public.accounts_memberships (
    user_id uuid references auth.users on delete cascade not null,
    account_id uuid references public.accounts (id) on delete cascade not null,
    account_role varchar(50) references public.roles (name) not null,
    -- ...
    primary key (user_id, account_id)
  );

The full flow: a query hits notifications, Postgres evaluates the policy for each candidate row, and a row is returned only if its account_id is the caller's personal account or an account where the caller has a membership. The application does not add a tenant filter, so there is no filter to forget.

When to use RLS, and when to bypass it

Enable RLS on every table the anon and authenticated roles can reach. In a Supabase app the browser client queries Postgres directly through the Data API, so RLS is the authorization layer for anything a user's session can query, not an extra safeguard. Every tenant table gets RLS plus the personal-or-member policy from the previous section.

Bypass RLS only in trusted server code that uses the service_role key, which skips RLS entirely. That is correct for webhook handlers, background jobs and admin operations that need to read or write across tenants. Never use the service role in a user's request path without an explicit authorization check first, because once you use it, isolation is your code's job again.

RLS does not cover three things:

• Column-level control. RLS filters rows, not columns. Column grants and triggers handle columns (next section).
• Rate limiting and noisy neighbors. Add per-account limits and query timeouts separately.
• Complex business rules. RLS handles "is this your account" well and "do you hold this permission" through helper functions. Multi-step business rules belong in server actions.

Use RLS when:

• The table holds tenant data and the authenticated or anon role can reach it
• You want isolation to hold even when application code has a bug

Bypass RLS with the service role when:

• A webhook, cron job or admin task has to operate across tenants
• Server code has already performed an explicit authorization check

If unsure: enable RLS and write a policy. Treat every bypass as an exception you justify individually.

Roles and permissions in a multi-tenant app

Authorization inside a tenant uses two tables: roles, which ranks roles by hierarchy_level, and role_permissions, which maps roles to permissions. Isolation decides whether a user can see an account at all; permissions decide what the user can do inside it.

Each role has a numeric rank, and a lower number means more authority (04-roles.sql):

create table if not exists
  public.roles (
    name varchar(50) not null,
    hierarchy_level int not null check (hierarchy_level > 0),
    primary key (name),
    unique (hierarchy_level)
  );

The seed data ships two roles: owner at level 1 and member at level 2. Permissions are a Postgres enum, so the set is fixed and type-checked (01-enums.sql):

create type public.app_permissions as enum(
  'roles.manage',
  'billing.manage',
  'settings.manage',
  'members.manage',
  'invites.manage'
);

role_permissions (06-roles-permissions.sql) grants specific permissions to each role, and has_permission() checks whether a user holds a permission on an account by joining memberships to role permissions. You can call it from RLS policies and from server actions before a mutation. The permissions and roles documentation shows how to add your own permissions.

The hierarchy prevents privilege escalation: a member cannot remove or demote someone ranked above them. can_action_account_member() in 05-memberships.sql enforces this. It requires the members.manage permission, and it requires the caller's hierarchy level to be strictly lower than the target member's. The delete policy on accounts_memberships calls this function, so the rule holds in the database even if a UI button is enabled by mistake.

Lock down writes with column-level grants

RLS decides which rows a user can modify; grants decide which columns. Give each role only the column privileges it needs, and never let end users write identity columns such as id, account_id or primary_owner_user_id. With RLS alone, a user who may update a row can update every column on it, including ones you never meant to expose.

Start each tenant table with no privileges, then grant back what each role needs. The notifications table is a good template. Authenticated users can read their notifications and change the dismissed flag, and every other write goes through the service_role:

revoke all on public.notifications from authenticated, service_role;
grant select on table public.notifications to authenticated, service_role;
-- authenticated may only toggle the dismissed flag; every other column
-- (account_id, type, body, link, channel, expires_at) is system-managed
grant update (dismissed) on table public.notifications to authenticated;
-- inserts, deletes, and full updates stay with the service_role
grant update on table public.notifications to service_role;
grant insert, delete on table public.notifications to service_role;

Because of grant update (dismissed), a user cannot change account_id, body or type even on a row they own. Postgres checks column privileges before it evaluates any RLS policy and rejects the statement. The invitations table applies the same rule to two columns, so a member can change only an invitation's role and expiry:

-- authenticated may only UPDATE the invitation role and expiry.
-- Identity and delivery columns (id, email, account_id, invite_token, ...)
-- are written only by the service_role.
grant update (role, expires_at) on table public.invitations to authenticated;

Scope select grants the same way when a table has columns you do not want exposed through the Data API.

For columns that must never change, combine a grant with a trigger. The accounts table limits the authenticated update grant to the editable columns (name, slug, picture_url, public_data), and the kit.protect_account_fields trigger raises an error if id, is_personal_account, primary_owner_user_id or email change:

if new.id is distinct from old.id
   or new.is_personal_account is distinct from old.is_personal_account
   or new.primary_owner_user_id is distinct from old.primary_owner_user_id
   or new.email is distinct from old.email then
    raise exception 'You do not have permission to update this field';
end if;

The grant blocks the write for ordinary users, and the trigger catches privileged code paths that the grant does not cover. Use is distinct from rather than <>: <> returns null when either side is null, so a change from null to a value (or back) would pass unnoticed. Finally, 00-privileges.sql hardens the whole schema by revoking the default execute privilege on functions from public and removing privileges from the anon role. The migrations guide shows the same revoke-then-grant pattern applied to a new table.

Least-privilege checklist for tenant tables:

• Run revoke all, then grant back only the operations each role needs
• Scope update and select grants to specific columns when only a few fields are user-writable
• Never give users write access to id, account_id or ownership columns
• Pair any broad grant with a trigger that rejects changes to immutable columns
• Use RLS to filter rows, and grants plus triggers to control columns

RLS performance and common multi-tenant pitfalls

The most common RLS performance mistake is a missing index with account_id as its leading column. Every policy adds a filter on account_id, so tenant queries without a matching index slow down as the table grows. Index tenant tables on account_id first, then on the columns you filter or sort by. MakerKit's notifications table, for example, has a composite index on (account_id, dismissed, expires_at), matching how the app lists a user's active notifications.

A second detail is in the policy itself: auth.uid() is wrapped as (select auth.uid()). Supabase recommends this form because Postgres evaluates the subquery once per statement instead of once per row.

Other pitfalls we see teams hit:

• Tenant filters in application code instead of policies. A where account_id = ... in every query is the pattern RLS replaces. Any query that misses the filter leaks data. Let the policy filter rows, and use application checks for user-facing errors.
• The service-role key in user request paths. The Supabase service role bypasses RLS. Use it for trusted jobs and webhooks, and use the admin client in a request path only after a manual authorization check.
• Treating RLS as the only control. RLS filters rows. It does not rate-limit a tenant or stop an expensive query. Add per-account limits and statement timeouts.

We added the account_id-leading index after watching a tenant query slow down as its table grew; with the index in place, the query returned to its earlier speed. Adding the index when you create the table avoids that investigation.

When not to use a shared schema

Move to schema-per-tenant or database-per-tenant only when a hard requirement forces separation. The requirements that justify it are a compliance regime or contract that mandates physical data isolation, data-residency rules that require a customer's data to stay in a specific region, or a few very large enterprise tenants whose load or security requirements justify a dedicated database each. Without one of those, a shared schema is cheaper to run and simpler to migrate, and RLS provides the isolation.

Quick recommendation

Shared schema with Postgres RLS is the best fit for:

• Any B2B or B2C SaaS serving many customers from one deployment
• Teams that want isolation enforced by the database rather than by every query author
• Products that need teams, roles and per-account billing

Choose a different model if:

• A contract or regulation requires physical data isolation
• Data-residency rules require customer data to stay in specific regions
• You serve a handful of very large enterprise tenants that each need a dedicated database

Our pick: a shared schema, an account_id on every tenant table, and an RLS policy that checks personal ownership or membership. It gives database-enforced isolation with one migration per change and no per-tenant infrastructure. This is the model MakerKit ships.

Next steps

Tenant isolation and authorization both depend on Postgres RLS, so the next guide to read is Supabase RLS best practices, which covers policy patterns and how to test them. For how the tenant model connects to your login layer, see how we choose authentication for Next.js SaaS. For the database choice underneath it, read best database software for startups, and for the full production setup, the 2026 SaaS stack we build on.