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

For almost every SaaS, the right multi-tenant architecture is a shared Postgres database with a shared schema, where every tenant row carries an account_id and Row Level Security enforces isolation at the database. You do not need a database per customer, and you do not want to scatter where tenant_id = ... across your application code. You want one place, the database, that refuses to return another tenant's rows even when your application code has a bug. This guide shows that pattern end to end, with the real schema, policies, and helper functions we ship in MakerKit.

Most articles on this topic stop at a diagram and a pros-and-cons table. This one hands you working code: the accounts table, the RLS policy that isolates tenants, the permission model, and the one index decision that keeps it fast at scale. Every snippet is from the MakerKit Supabase kit, tested with Supabase on Postgres 15.

What is multi-tenant architecture?

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

That last point is the whole reason multi-tenancy exists: one deployment to run, one migration to apply, one bug to fix for everyone. The cost is that isolation becomes your responsibility instead of the operating system's.

Single-tenant vs multi-tenant

Single-tenant gives each customer a dedicated application instance and database. Multi-tenant shares both across customers. Multi-tenancy wins for most SaaS because it is far cheaper to operate and lets you ship updates to everyone at once. Single-tenant wins only when a contract or regulation demands physical isolation.

If you are building a normal B2B or B2C SaaS, you are building multi-tenant. The interesting decision is not whether to share, it is how you isolate.

The three tenant isolation models

There are three ways to isolate tenants in Postgres, ordered from most shared to most isolated. Pick the most shared model your requirements allow, because every step toward isolation multiplies operational work.

• Shared schema, shared tables: all tenants live in the same tables, and an account_id column marks ownership. Cheapest, simplest to migrate, and the right default. Isolation is enforced by RLS.
• Schema per tenant: one database, one Postgres schema per tenant. Stronger isolation, but migrations now run N times and connection management gets awkward past a few hundred tenants.
• Database per tenant: a full database per customer. Strongest isolation, closest to single-tenant, and the most expensive to run. Reserve it for enterprise contracts that require it.

The rest of this guide is about the first model, because it is the one you should reach for first and the one a good starter kit sets up for you.

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 your application cannot leak data across tenants. That is the property that matters. In a shared-schema app without RLS, every query is one forgotten filter away from showing Customer A the rows of Customer B. RLS turns that from an application concern into a database guarantee.

Postgres RLS, available since Postgres 9.5, lets you attach policies to a table that restrict which rows a query can read or write. When RLS is enabled and no policy grants access, the default is deny. You write the policy once per table, and it applies to every select, insert, update, and delete from then on. This is defense in depth: even if a code path forgets to scope a query, the database returns only the current tenant's rows.

We treat RLS as non-negotiable in MakerKit for exactly this reason. Application-layer checks are still useful for good error messages and early exits, but they are the second line, not the first.

The tenant model in practice: personal and team accounts

The tenant in this model is an account, and an account is either a personal account owned by one user or a team account shared by many. Both live in one accounts table, distinguished by an is_personal_account flag. This is the schema we ship, from apps/web/supabase/schemas/03-accounts.sql:9:

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 make the personal-versus-team split safe. A check constraint (03-accounts.sql:82) requires personal accounts to have a null slug and team accounts to have one, and a partial unique index (03-accounts.sql:108) guarantees a user can own exactly one personal account.

There is one detail here that pays off in every policy you write later. When a user signs up, a trigger (kit.setup_new_user(), 03-accounts.sql:409) creates their personal account with id => new.id, so a personal account's id equals the user's auth.users id. That equality is why tenant policies can check personal ownership with a plain account_id = auth.uid() instead of a join.

Every table that holds tenant data links back to an account with the same foreign key. Here is a real one, the notifications table at apps/web/supabase/schemas/11-notifications.sql:12:

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,
    -- ...
  );

That account_id uuid not null references public.accounts(id) on delete cascade line is the entire tenant-linking convention. The same pattern appears on orders (10-orders.sql:12), subscriptions (09-subscriptions.sql:12), billing_customers (08-billing-customers.sql:11), and invitations (07-invitations.sql:12). New feature table, same column, and isolation comes for free from the policy you are about to see.

Not every table is account-scoped, and that is fine. Some tables are user-scoped instead, keyed on user_id with policies checking user_id = auth.uid(). The rule is simple: if the data belongs to a workspace, scope it to account_id; if it belongs to a single user regardless of workspace, scope it to user_id.

Enforcing isolation with RLS

The isolation policy on a tenant table has one job: return a row only if the current user owns the account personally or is a member of it. Here is the read policy on notifications (11-notifications.sql:86), which is the template 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)
  );

Read that using clause carefully, because it is the load-bearing line of the whole architecture. The first branch, account_id = (select auth.uid()), covers personal accounts, and it works only because of the id equality set up by the signup trigger. The second branch, has_role_on_account(account_id), covers team accounts by checking membership. We will call that check has_account_role() from here on, though its real name in the schema is has_role_on_account() (05-memberships.sql:132).

has_account_role() is a security definer function that returns true when the current user has a membership row on the given account, optionally matching 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)
    );
  $$;

Membership itself lives in accounts_memberships (05-memberships.sql:9), a join table with a composite primary key of (user_id, account_id) and an account_role 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)
  );

Put together, the flow is: a query hits notifications, RLS evaluates the policy, and the row is visible only if account_id matches the caller's personal account or the caller has a membership on that account. No application code required, and no way for a forgotten filter to leak data.

When to use RLS, and when to reach past it

Enable RLS on every table the anon and authenticated roles can reach. In a Supabase app the client talks to Postgres directly, so RLS is not optional hardening, it is the authorization layer for anything a user's session can query. For each tenant table, turn RLS on and write the personal-or-member policy from the previous section. This is the default, and the default should always be on.

You step around RLS in exactly one situation: trusted server code running with the service_role key, which bypasses RLS entirely. That is the right tool for webhook handlers, background jobs, and admin operations that legitimately need to see across tenants. The rule is that the service role never runs in a user's request path without a manual authorization check first, because the moment you use it you have switched isolation off and made it your job again.

Three things RLS does not do, so do not ask it to:

• Column-level control. RLS filters rows, not columns. Scoped grants and triggers handle that, which is the next section.
• Rate limiting or noisy-neighbor protection. Add per-account limits and query timeouts separately.
• Complex business authorization. RLS is ideal for "is this your account," workable for "do you hold this permission" through helper functions, and the wrong place for multi-step business rules. Keep those in server actions.

Use RLS when:

• The table holds tenant data and is reachable by the authenticated or anon role
• You want isolation guaranteed even when application code has a bug

Reach past RLS with the service role when:

• A webhook, cron job, or admin task must operate across tenants
• You have already done an explicit authorization check in server code

If unsure: enable RLS and add a policy. Bypassing is the exception you justify case by case, never the default.

Roles and permissions

Authorization builds on top of isolation with two tables: roles, which ranks roles by a hierarchy_level, and role_permissions, which maps roles to granular permissions. Isolation answers "can this user see the account at all," and permissions answer "what can they do inside it."

Roles carry a numeric rank where a lower number means more power (04-roles.sql:9):

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 (18-roles-seed.sql). Permissions are a Postgres enum, so the set is fixed and type-checked (01-enums.sql:14):

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

role_permissions (06-roles-permissions.sql:10) then grants specific permissions to each role, and a helper called has_permission() (06-roles-permissions.sql:49) checks whether a user holds a given permission on an account by joining membership to role permissions. That is what your server actions call before a mutation.

The hierarchy exists to stop privilege escalation. A member must not be able to remove or promote someone above them. The function that guards member management, can_action_account_member() in the schema (05-memberships.sql:188), which we will call can_manage_member() in prose, does two things: it requires the members.manage permission, and it requires the actor's hierarchy level to be strictly lower than the target's (05-memberships.sql:267). In plain terms, you can only act on members below you. The delete policy on accounts_memberships (05-memberships.sql:332) calls straight into it, so the rule is enforced at the database, not just in a button's disabled state.

Lock down writes with column-level grants

RLS decides which rows a user can touch, and grants decide which columns. The best practice is least privilege on both axes: give each role the narrowest column privileges it needs, and never let end users write identity columns like id, account_id, or primary_owner_user_id. RLS alone will happily let a user update a column they own on a row they own, including columns you never meant to expose.

Start every tenant table from deny, then grant back only what each role needs. The notifications table is a clean template (11-notifications.sql:45). Authenticated users can read their notifications and toggle the dismissed flag, and nothing else; every other write belongs to 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;

That grant update (dismissed) is the whole idea. A user cannot rewrite account_id, body, or type even on a row they own, because Postgres never handed them those columns, and it rejects the write before any RLS policy runs. The invitations table applies the same rule to two columns, letting a member change only an invitation's role and expiry (07-invitations.sql:102):

-- 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 your select grants the same way when a table holds columns you do not want served through the data API.

Grants and triggers compose for the columns that must never change. The accounts table already scopes its authenticated update grant to the editable columns (grant update (name, slug, picture_url, public_data), 03-accounts.sql:76), and then adds a trigger, kit.protect_account_fields (03-accounts.sql:215, trigger at :238), that raises if id, is_personal_account, primary_owner_user_id, or email ever 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 is the first gate, and the trigger is defense-in-depth for any privileged path a column grant alone would not catch. The is distinct from comparison matters, because it treats null as a real value and catches transitions to or from null that plain <> would miss. Then harden the schema once at the top, in 00-privileges.sql, which revokes the default execute on functions from public and strips privileges from the anon role so nothing leaks by accident.

Least-privilege checklist for tenant tables:

• Run revoke all, then grant back only the operations the role actually needs
• Scope update and select grants to specific columns when only a few fields are user-writable
• Never grant users write access to id, account_id, or ownership columns
• Back any broad grant with a trigger that rejects changes to immutable fields
• Keep RLS as the row filter, and treat grants and triggers as the column filter

Performance and the pitfalls that bite

RLS is not free, and the single biggest performance mistake is forgetting that account_id needs to be the leading column of the indexes that serve your tenant queries. Every policy adds an implicit filter on account_id, so a query that scans by account without a matching index degrades as tables grow. Index tenant tables on account_id first, then on whatever you sort or filter by second.

Three more pitfalls are worth naming, because they are the ones we see teams hit.

• Scattering where account_id = ... in application code. This is the anti-pattern RLS exists to kill. One forgotten filter is a cross-tenant leak. Let the policy do it, and keep application checks for UX, not security.
• Using the service-role key in user request paths. The Supabase service role bypasses RLS entirely. It is the right tool for trusted server jobs and webhooks, and the wrong tool anywhere a user's request reaches it. Reach for the admin client only with a manual authorization check, and never as a convenience.
• Assuming RLS is enough on its own. RLS protects rows. It does not rate-limit a noisy neighbor or stop an expensive query. Add per-account limits and sensible timeouts separately.

We learned the index lesson the ordinary way, by watching a tenant query get slower as a table grew, then adding the account_id-leading index and watching it snap back. Design for it up front and you never have that afternoon.

When not to use shared-schema

Shared-schema plus RLS is the default, not a law. Move to schema-per-tenant or database-per-tenant when a hard requirement forces physical separation, not because it feels safer. The concrete triggers are a compliance regime or contract that mandates data isolation, data-residency rules that require a customer's data to live in a specific region, or a small number of very large enterprise tenants whose scale or security demands justify a dedicated database each. Absent one of those, the shared model is cheaper to run and easier to reason about, and RLS gives you the isolation guarantee without the operational tax.

Quick Recommendation

Multi-tenant SaaS with shared-schema and Postgres RLS is best for:

• Any B2B or B2C SaaS serving many customers from one deployment
• Teams that want isolation enforced by the database, not by careful coding
• Products with teams, roles, and per-account billing to model

Skip the shared model if:

• A contract or regulation requires physical data isolation
• Data-residency rules pin customer data to specific regions
• You serve a handful of enormous enterprise tenants that each warrant a dedicated database

Our pick: shared schema, an account_id on every tenant table, and an RLS policy that checks personal ownership or membership, because it gives you database-enforced isolation with a single migration and no per-tenant operational overhead. This is the model MakerKit ships, and it is the one we would choose again.

Next steps

Isolation and authorization both rest on Postgres RLS, so the natural next read is our deeper guide to Supabase RLS best practices, which covers policy patterns and testing in more depth. For where this tenant model plugs into your login layer, see how we choose authentication for Next.js SaaS, and for the full production picture, the 2026 SaaS stack we build on.