Using PostgreSQL RLS for Multi‑Tenant SaaS Isolation

When a barbershop chain tried to add a new location, the first thing that broke wasn’t the UI—it was the data model leaking customer appointments across stores. The problem wasn’t a missing feature; it was a missing guard. Row‑Level Security (RLS) in PostgreSQL offers a built‑in, declarative way to keep each tenant’s rows invisible to everyone else, and Supabase makes the feature accessible without writing a custom access‑control layer.

Understanding Row‑Level Security

RLS is a row‑filtering mechanism that PostgreSQL evaluates on every query. Instead of granting a role blanket SELECT, INSERT, UPDATE, or DELETE rights on a table, you attach policies that restrict which rows the role may touch. The engine rewrites the query behind the scenes, adding a WHERE clause derived from the active policies. Because the filtering happens at the database level, every client—whether a Next.js front‑end, a mobile app, or an Edge Function—gets the same protection.

A policy consists of three parts:

1. Target table – the table the rule applies to.
2. Command – SELECT, INSERT, UPDATE, DELETE, or ALL.
3. Condition – a Boolean expression that must be true for the row to be visible.

When a user connects, PostgreSQL knows the current role (current_user). Supabase adds a second variable, auth.uid(), which resolves to the authenticated user’s UUID. By combining these, you can write policies that say, for example, “a user may only see rows where shop_id matches the shop they belong to.”

Mapping Business Entities to Policies

BarberHub Pro manages multiple independent barbershops (tenants) on a single Supabase project. The core entities are:

• shops – one row per physical location.
• users – staff members, each linked to a shop_id.
• appointments – bookings that reference both a shop_id and a user_id.

The first step is to store the tenant identifier (shop_id) on every row that needs isolation. In Supabase you can add a column with a default that copies the value from the session:

ALTER TABLE appointments ADD COLUMN shop_id uuid NOT NULL;
ALTER TABLE appointments ALTER COLUMN shop_id SET DEFAULT auth.uid()::uuid;

Next, create policies that enforce the relationship. A minimal set looks like this:

-- Allow a user to read rows belonging to their shop
CREATE POLICY "shop_read" ON appointments
  FOR SELECT USING (shop_id = (SELECT shop_id FROM users WHERE id = auth.uid()));

-- Allow a user to insert appointments for their shop
CREATE POLICY "shop_insert" ON appointments
  FOR INSERT WITH CHECK (shop_id = (SELECT shop_id FROM users WHERE id = auth.uid()));

-- Allow a user to update only their own appointments
CREATE POLICY "shop_update" ON appointments
  FOR UPDATE USING (shop_id = (SELECT shop_id FROM users WHERE id = auth.uid()))
  WITH CHECK (shop_id = (SELECT shop_id FROM users WHERE id = auth.uid()));

Notice the use of a sub‑query to fetch the shop_id of the current user. Because the policy runs for every row, the database automatically discards rows that don’t match, eliminating the need for any manual WHERE shop_id = … logic in your application code.

Using Supabase’s Policy Simulator

Supabase provides a Policy Simulator in the dashboard that lets you test a policy against a mock JWT payload. This is invaluable for catching logic errors before they reach production. To use it:

1. Open the table view in the Supabase UI.
2. Click Policies → Simulate.
3. Paste a JWT payload that includes the user’s sub (UID) and any custom claims you use.
4. Run a SELECT, INSERT, or UPDATE simulation and inspect the generated SQL.

The simulator shows the exact WHERE clause that PostgreSQL will append. If the clause is missing a condition, the policy is effectively open. It also highlights privilege escalation risks—e.g., a policy that grants UPDATE without a matching WITH CHECK clause could let a user modify rows they shouldn’t.

Common Pitfalls and How to Avoid Them

Pitfall	Why it hurts	Mitigation
Missing WITH CHECK on INSERT/UPDATE	Rows can be created with arbitrary shop_id values, breaking isolation.	Always pair a USING clause with a matching WITH CHECK clause for write commands.
Policy ordering	PostgreSQL evaluates all applicable policies; a permissive policy earlier can override a restrictive one.	Keep policies simple and avoid overlapping conditions. Use FOR ALL only when you truly want a blanket rule.
Superuser bypass	Supabase’s service_role key bypasses RLS, which is convenient for migrations but dangerous in production code.	Restrict service‑role usage to backend jobs and never expose it to the client.
Performance impact	Complex policies that join many tables can add overhead to each query.	Keep conditions based on indexed columns (shop_id should be indexed). Test with EXPLAIN ANALYZE to verify query plans.
Privilege escalation via functions	A function that runs with SECURITY DEFINER can ignore RLS.	Audit all functions; either mark them SECURITY INVOKER or add explicit SET ROW SECURITY = ON; inside.

Addressing these issues early saves you from data leaks that are hard to detect after the fact.

Auditing with Edge Functions

RLS protects data at query time, but you often need an immutable audit trail for compliance or internal accountability. Supabase Edge Functions run close to the database and can log every mutation.

A typical pattern is:

import { serve } from "https://deno.land/[email protected]/http/server.ts";
import { supabaseClient } from "./supabase.ts";

serve(async (req) => {
  const { user, body } = await req.json();
  const { data, error } = await supabaseClient
    .from('appointments')
    .insert({ ...body, created_by: user.id });

  // Log the operation regardless of success
  await supabaseClient
    .from('audit_log')
    .insert({
      actor_id: user.id,
      action: 'INSERT',
      table: 'appointments',
      payload: body,
      success: !error,
      timestamp: new Date().toISOString(),
    });

  return new Response(JSON.stringify({ data, error }), { status: error ? 400 : 200 });
});

Because the function executes with the service_role key, it can write to the audit_log table even though RLS would block a normal client. However, the function still respects RLS when accessing the appointments table because the Supabase client is instantiated with the user’s JWT, not the service role. This hybrid approach gives you both security and observability.

Takeaway

Row‑Level Security is not a novelty; it’s a mature PostgreSQL feature that becomes a practical isolation layer when paired with Supabase’s developer‑friendly tooling. By storing a tenant identifier on every row, writing concise policies, and leveraging the Policy Simulator, you can replace hand‑rolled ACL code with declarative rules that the database enforces for every request. In projects like BarberHub Pro, this pattern keeps every tenant isolated by construction; as with any shared‑table design, indexing the tenant column and keeping policies simple is what keeps it fast. Finally, Edge Functions let you extend the model with audit logging for accountability requirements while keeping the core data store clean and secure.

If you’re curious about how these ideas look in a real multi‑tenant codebase, see the project overview on my site: https://gabrielepau.com/portfolio. The same patterns apply to any SaaS product that needs logical data separation without the overhead of a full micro‑service security layer.