Row-Level Security in Supabase: Multi-Tenant SaaS from Day One

In a multi-tenant SaaS, one misplaced query can leak another customer's data. Row-Level Security moves that enforcement into the database, where it can't be skipped by a tired developer or a missing middleware.

I went all-in on RLS from the start with IssueCapture. Every table has it. Every query is automatically scoped to the right account. No WHERE account_id = $1 that future-me accidentally omits. We're running around 29 RLS-protected tables in production. Here's how it works and where it'll bite you.

What RLS Actually Does

Row-Level Security is a PostgreSQL feature that lets you attach policies to tables. When RLS is enabled, a SELECT, INSERT, UPDATE, or DELETE only succeeds for rows that pass the policy check.

ALTER TABLE issues ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users see own issues"
  ON issues FOR SELECT
  USING (user_id = auth.uid());

Supabase builds on top of this with auth.uid() — a function that returns the UUID of the authenticated user from the JWT.

But user-level isolation is the easy case. Most SaaS apps need account-level isolation, where multiple users share data within a tenant.

The team_members Join Pattern

The pattern I use for multi-tenant isolation:

• accounts table — one row per tenant
• team_members table — many-to-many between auth.users and accounts
• Everything else — has an account_id foreign key

CREATE TABLE accounts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  subscription_tier text DEFAULT 'free'
);

CREATE TABLE team_members (
  user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE,
  account_id uuid REFERENCES accounts(id) ON DELETE CASCADE,
  role text DEFAULT 'member',
  PRIMARY KEY (user_id, account_id)
);

CREATE TABLE issues (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id uuid REFERENCES accounts(id) ON DELETE CASCADE,
  title text NOT NULL,
  created_at timestamptz DEFAULT now()
);

Now the RLS policy on issues checks membership:

ALTER TABLE issues ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Team members can view account issues"
  ON issues FOR SELECT
  USING (
    account_id IN (
      SELECT account_id FROM team_members
      WHERE user_id = auth.uid()
    )
  );

This single subquery does the tenant isolation. Any user who is a member of the account can read its issues. Non-members get zero rows — not an error, just nothing.

The service_role Key Bypasses All RLS Policies

The service role key bypasses RLS entirely.

Supabase gives you two keys:

• anon key — respects RLS, safe to expose in the browser
• service_role key — superuser, skips all RLS policies, never expose this

// Browser / user-facing: RLS enforced
const supabase = createClient(url, process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY);

// Server / admin: RLS bypassed — be careful
const supabaseAdmin = createClient(url, process.env.SUPABASE_SERVICE_KEY);

The rule I follow: API routes that handle user requests use the anon key with the user's JWT. Internal jobs and admin operations use the service key explicitly.

The failure mode if you get this wrong is data leakage across tenants. You won't notice in testing because your test user probably has access to everything in your dev database.

Policies on JOINed Tables

Another common pitfall: you write a policy on table A, but your query joins to table B, and table B also has RLS enabled. Each table's policy is checked independently.

SELECT c.* FROM comments c
JOIN issues i ON i.id = c.issue_id
WHERE i.account_id = $1;

If the user doesn't have access to a given issues row, the join will return no matching row for it — the comment becomes invisible even if the comments policy would technically allow it. The query doesn't error, it just returns fewer rows than you expect.

The fix is being explicit about what each table's policies need to allow.

Performance: The Subquery Problem

The account_id IN (SELECT account_id FROM team_members WHERE user_id = auth.uid()) pattern executes that subquery on every row evaluation. For large tables, this can be slow.

Index the foreign keys:

CREATE INDEX ON issues (account_id);
CREATE INDEX ON team_members (user_id);
CREATE INDEX ON team_members (account_id);

Consider a security definer function for complex membership checks:

CREATE OR REPLACE FUNCTION get_accessible_account_ids()
RETURNS SETOF uuid
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
  SELECT account_id FROM team_members
  WHERE user_id = auth.uid()
$$;

CREATE POLICY "Team members can view account issues"
  ON issues FOR SELECT
  USING (account_id IN (SELECT get_accessible_account_ids()));

SECURITY DEFINER means the function runs with the permissions of whoever defined it, which lets it bypass RLS on team_members itself. STABLE tells the planner the function won't change the database — this enables caching and better optimization.

Testing RLS Policies

The best way to test a policy is to set the role and JWT claim manually:

BEGIN;

SET LOCAL role = 'authenticated';
SET LOCAL "request.jwt.claims" = '{"sub": "user-uuid-here"}';

SELECT * FROM issues;

ROLLBACK;

It's much more reliable than inferring correctness from application behavior, because application bugs can mask policy bugs.

Why RLS Will Hurt You (and Why You Should Still Use It)

RLS is not free. The policies add overhead to every query, the service key bypass is a footgun, and debugging policy interactions across joined tables takes practice.

But for a multi-tenant SaaS, application-layer filtering is worse. The guarantee that a bug in your API code cannot leak tenant data is worth the overhead. When your database is the last line of defense, it tends to actually hold.

---

I'm specifically interested in cases where RLS policies on joined tables produced surprising results — rows disappearing from queries for non-obvious reasons. That was our biggest debugging timesink.