Multi-tenant SaaS databases face a simple truth: if you rely on application code alone to filter company_id, one bug can expose another tenant’s data. PostgreSQL’s Row-Level Security (RLS) lets you enforce that boundary inside the engine itself. Below is the blueprint we use in production—trimmed to essentials so it’s safe to publish.

1. Threat Model at a Glance

  • SaaS, many tenants. Every query must be siloed to its own company.
  • Credential leaks are inevitable. The DB login used by the API must be low-privilege.
  • Soft-deleted data still matters. Hidden rows should stay hidden even from creative joins.

2. Ground Rules We Never Bend

  • One low-privilege login role. It owns nothing, uses NOINHERIT, and has BYPASSRLS = false.
  • JWT claims → session settings. The API runs SET LOCAL request.jwt.claims = '…' once per connection so SQL can read the caller’s context.
  • RLS enabled before launch. A multi-tenant table never sees production traffic until ALTER TABLE … ENABLE ROW LEVEL SECURITY; is in place.

3. Policy Skeleton (Sector Table Example)

-- ===== internal.sector policies (simplified) =====
CREATE POLICY tenant_read
ON internal.sector
FOR SELECT
USING (
    company_id = (
        current_setting('request.jwt.claims', true)::json ->> 'company_id'
    )::uuid
);

CREATE POLICY tenant_write
ON internal.sector
FOR INSERT, UPDATE, DELETE
USING (
    company_id = (
        current_setting('request.jwt.claims', true)::json ->> 'company_id'
    )::uuid
)
WITH CHECK (
    current_setting('request.jwt.claims', true)::json ->> 'app_role'
        IN ('admin', 'editor')
);

4. Proving It Works with pgTAP

PgTAP lets us run unit tests inside the database, imitating the exact session context the application uses. Below is a trimmed harness.

-- ===== sector_rls_test.sql (excerpt) =====
BEGIN;
SELECT plan(5);

-- Connect as the same low-privilege role used by the API
SET LOCAL ROLE api_role;

------------------------------------------------------------
-- 1. Viewer in Company A
------------------------------------------------------------
SET LOCAL request.jwt.claims = $$
{
  "company_id":"00000000-0000-0000-0000-000000000001",
  "app_role":"viewer"
}
$$;

-- 1.1 Can see own rows
SELECT ok(
  (SELECT count(*)
     FROM internal.sector
    WHERE company_id = '00000000-0000-0000-0000-000000000001') > 0,
  'viewer sees own company rows'
);

-- 1.2 Cannot update rows
SELECT throws_ok(
  $$UPDATE internal.sector SET name = 'x';$$,
  'permission denied for table sector',
  'viewer cannot update'
);

------------------------------------------------------------
-- 2. Editor in the same company
------------------------------------------------------------
SET LOCAL request.jwt.claims = $$
{
  "company_id":"00000000-0000-0000-0000-000000000001",
  "app_role":"editor"
}
$$;

-- 2.1 Can insert row for own company
SELECT lives_ok(
  $$INSERT INTO internal.sector (id, company_id, name)
       VALUES ('11111111-1111-1111-1111-111111111111',
               '00000000-0000-0000-0000-000000000001',
               'QA');$$,
  'editor can insert'
);

SELECT * FROM finish();
ROLLBACK;

Key points:

  • The session sets JWT claims the same way the API does (SET LOCAL), so the test hits real predicates.
  • Every positive test (lives_ok) has a matching negative test (throws_ok) to catch privilege creep.
  • Running tests inside a transaction (ROLLBACK) keeps fixtures pristine for parallel CI jobs.

5. Performance Side-Notes

RLS costs a few extra planner cycles. Composite or partial indexes mirroring policy predicates (company_id, deleted_at IS NULL) usually pay that back. We also cache immutable derivations—e.g., a created_year field maintained by a trigger—instead of calling EXTRACT() on every query.

6. Takeaways

  • Trust one role and one JWT payload. The rest is SQL.
  • Write policies once. They automatically govern every query, join or CTE.
  • Gate migrations with pgTAP. A failing unit test is cheaper than a data-leak headline.

Follow these patterns, tailor the details to your stack, and you’ll sleep better knowing the database itself enforces the rules—even if a forgotten WHERE slips through code review.