← All articles

6 min readSupabaseRow‑Level SecurityRealtimeMulti‑TenantSaaS

Real‑Time Multi‑Tenant SaaS with Supabase: RLS + Realtime

When a project‑management SaaS scales to ten thousand tenants, the first thing that breaks is data isolation. Fix it with Row‑Level Security and Realtime, not a custom API.

The isolation problem in multi‑tenant SaaS

A multi‑tenant application stores data for many customers in a single database. The most common approach is a shared‑schema model: every table has a tenant_id column that indicates which customer owns each row. This keeps the database simple, but it also opens the door to data leakage if the application layer cannot guarantee that every query is filtered by the tenant ID.

Traditionally, developers wrote an API layer that added a WHERE tenant_id = current_user.tenant_id clause to every request. That layer became a bottleneck when the number of tenants grew. Every new feature required changes to the API, and the risk of a copy‑paste error was high. The alternative is to let the database enforce the rule itself, using Row‑Level Security (RLS) in PostgreSQL. Supabase, which bundles Postgres with a serverless API, gives a straightforward way to declare RLS policies that automatically filter rows based on the authenticated user. When combined with Supabase Realtime, the same policies can also be used to push live updates to clients without writing any custom backend code.

Designing tenant tables

The first step is to decide how the tenant identifier is stored. In most cases, the tenant_id is a UUID that matches the Supabase user.id. In the examples below, the tables are:

CREATE TABLE projects (
  id          uuid PRIMARY KEY,
  tenant_id   uuid NOT NULL,
  name        text NOT NULL,
  created_at  timestamptz DEFAULT now()
);

CREATE TABLE tasks (
  id          uuid PRIMARY KEY,
  project_id  uuid REFERENCES projects(id),
  tenant_id   uuid NOT NULL,
  description text,
  status      text,
  created_at  timestamptz DEFAULT now()
);

The tenant_id is duplicated in both tables so that RLS can be applied at the row level without needing joins. Indexing tenant_id is a must for performance, especially when the tables grow to millions of rows.

CREATE INDEX idx_projects_tenant ON projects(tenant_id);
CREATE INDEX idx_tasks_tenant ON tasks(tenant_id);

Writing RLS policies

Supabase exposes the auth.uid() function that returns the ID of the currently authenticated user. A policy that allows a user to read only their own rows looks like this:

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY read_projects
  ON projects
  FOR SELECT
  USING (tenant_id = auth.uid());

CREATE POLICY insert_projects
  ON projects
  FOR INSERT
  WITH CHECK (tenant_id = auth.uid());

CREATE POLICY update_projects
  ON projects
  FOR UPDATE
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

CREATE POLICY delete_projects
  ON projects
  FOR DELETE
  USING (tenant_id = auth.uid());

The same policies are applied to the tasks table. By delegating all filtering to the database, the application code can issue plain SELECT * FROM projects and trust that only the rows belonging to the current tenant are returned.

Testing RLS with pgTAP

Supabase offers a test CLI that can run pgTAP tests against a sandbox database. A simple test that verifies isolation looks like this:

BEGIN;

SET LOCAL role = 'user_a';
INSERT INTO projects (id, tenant_id, name) VALUES ('a1', 'user_a', 'Project A');
INSERT INTO projects (id, tenant_id, name) VALUES ('b1', 'user_b', 'Project B');

SELECT * FROM projects;  -- should return only Project A

RESET role;
ROLLBACK;

Running this test through the Supabase test CLI guarantees that a regression will be caught before the code reaches production.

Integrating Realtime

Supabase Realtime watches every write to a table and pushes the change to all connected clients that have permission to see it. Because RLS policies are already in place, Realtime automatically filters the events. Clients subscribe with a simple JavaScript snippet:

import { createClient } from '@supabase/supabase-js';

const supabase = createClient('https://xyz.supabase.co', 'public-anon-key');

const projectChannel = supabase
  .channel('projects')
  .on('postgres_changes', { event: '*', schema: 'public', table: 'projects' }, payload => {
    console.log('Project change:', payload);
  })
  .subscribe();

When a tenant creates a new project, every other client belonging to the same tenant receives the event instantly. A client belonging to a different tenant never receives it because the policy blocks the row from being sent over the wire.

Avoiding cross‑tenant leakage

Even with RLS, certain queries can bypass the policy if not written carefully. For example, a SELECT * FROM projects WHERE tenant_id <> auth.uid() would return no rows, but a SELECT * FROM projects WHERE tenant_id = 'public' could expose data if a public tenant exists. The safest pattern is to avoid hard‑coding tenant IDs in queries and always let the database filter by auth.uid().

Another subtle risk is joining tables that have different policies. If a policy on tasks allows a user to see all tasks for a project, but the policy on projects restricts the user to only their own projects, a join could expose tasks belonging to other tenants. The solution is to apply the same tenant_id = auth.uid() filter to every table involved in the join.

Performance tuning

RLS policies add a small overhead to every query, but this cost is negligible compared to the benefit of eliminating a custom API layer. Still, there are a few optimizations:

  1. Index the policy column – as shown above, index tenant_id on every table.
  2. Avoid recursive policies – if a policy references another table, PostgreSQL may need to evaluate it repeatedly. Keep policies simple and flat.
  3. Use materialized views – for complex reports that aggregate data across tenants, a materialized view with a tenant_id filter can reduce query time.
  4. Cache query results – Supabase automatically caches SELECT queries in the client SDK. For read‑heavy workloads, enable caching on the client side.

Real‑world pattern: a project‑management SaaS

Consider a SaaS that offers project management to small businesses. Each tenant has its own set of projects and tasks. The database schema follows the shared‑schema model described earlier. RLS ensures that a user can only see and modify their own data. Realtime pushes updates to all devices a user is logged into, so a task marked as “in progress” on a mobile device appears instantly on the web dashboard.

When the customer base grows to ten thousand tenants, the database continues to handle the load because all isolation logic remains inside PostgreSQL. Adding a new tenant requires only inserting a row into a tenants table and provisioning a new user in Supabase Auth. No code changes, no new API endpoints, no extra servers.

The same pattern can be applied to other domains: a barbershop management system, a tutoring platform, or a local‑shop marketplace. In each case, the core idea is the same: store the tenant ID, enforce it with RLS, and let Realtime deliver live updates.

Conclusion

Supabase’s mature Row‑Level Security and Realtime features let developers ship multi‑tenant SaaS applications without the overhead of a custom backend. By designing tables with a shared schema, writing clear RLS policies, testing them with pgTAP, and integrating Realtime, you can guarantee data isolation and provide instant updates to every tenant. The result is a production‑grade architecture that scales to thousands of customers while keeping the codebase lean and maintainable.

For more practical examples of this approach in real projects, see the case studies on my portfolio at https://gabrielepau.com.

Sources