Database · 11 min read · 2026-05-09

PostgreSQL row-level security: the underrated multi-tenant tool.

TL;DR — RLS isn't a substitute for tenant filtering at the application layer; it's the safety net under it. When (not if) a developer forgets the WHERE org_id = ? clause on a query, RLS is what stops a customer from seeing another customer's data. Three patterns make it actually work in production. The same three patterns shipped on the Inara B2B platform; here they are.

Why most teams don't use RLS

Two reasons. The first is that RLS feels invisible. You add policies, the application code looks unchanged, the tests pass, and there's no satisfying "feature shipped" moment. The second is that RLS has a reputation for being awkward — rumours of slow queries, of policies firing in unexpected places, of needing to set session variables every time you open a connection. Both reputations have some truth and a lot of exaggeration.

What RLS gives you is one thing: a guarantee that if your application forgets to filter by tenant, the database will. In a B2B SaaS where the consequence of leaking data across tenants is a public incident and a churned customer, that guarantee is worth setup pain.

I shipped RLS on the Inara Enterprise platform when it became multi-tenant. The application layer already filtered by org_id on every query, of course it did. RLS was the second line — the line that catches the day a developer copy-pastes a query from a single-tenant context into a multi-tenant one and forgets the filter. That day will come.

What RLS actually does

An RLS policy is a SQL expression attached to a table. When the table is queried by a role with RLS enabled, every row is checked against the policy expression — only rows where the expression returns true are visible. Policies can be SELECT-specific, INSERT, UPDATE, DELETE, or ALL.

The simplest possible multi-tenant policy:

ALTER TABLE patients ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON patients
  USING (org_id = current_setting('app.current_org_id')::uuid);

Now, when the application opens a connection and runs:

SET app.current_org_id = 'a1f3...';
SELECT * FROM patients;

...the database returns only rows where org_id matches that session variable. A query without the SET — or with the wrong value — returns zero rows. Even SELECT * with no WHERE clause is safe; the database appended the filter for you.

That's the whole shape of it. The complexity is everywhere else.

Pattern 1 — the connection-pool aware setup

The naive RLS setup uses a session variable. That works for a single-process backend with a connection per request. It breaks the moment you put a connection pool in front of it (which you should). Connections are reused; the session variable from the last request leaks into the next.

The fix is to set the variable per transaction, not per session, using PostgreSQL's local-scoped settings:

BEGIN;
SET LOCAL app.current_org_id = 'a1f3...';
-- queries run here see only the right tenant's data
COMMIT;

SET LOCAL is automatically reset at the end of the transaction. Combined with a transaction-per-request pattern (which most ORMs default to), this gives you tenant scoping without leaking across requests.

The implementation in FastAPI on Inara looked roughly like:

async def tenant_scoped_session(
    request: Request,
    pool: asyncpg.Pool = Depends(get_pool)
):
    org_id = extract_org_from_jwt(request)
    async with pool.acquire() as conn:
        async with conn.transaction():
            await conn.execute(
                "SET LOCAL app.current_org_id = $1",
                str(org_id)
            )
            yield conn
            # transaction ends, LOCAL setting is cleared

Every endpoint depends on this; every query inside it sees only the requesting org's data. If extract_org_from_jwt fails to find an org, the dependency raises and the endpoint never runs.

Pattern 2 — separate roles for application and migrations

RLS policies don't apply to the table owner or to roles with BYPASSRLS. They should apply to your application role.

The setup that works:

-- the migration role owns the schema, can DDL anything
CREATE ROLE app_migrate WITH LOGIN PASSWORD '...';

-- the application role connects from the app, can DML
-- but RLS applies to it
CREATE ROLE app_runtime WITH LOGIN PASSWORD '...';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES
  IN SCHEMA public TO app_runtime;

-- explicit: app_runtime is NOT the table owner, RLS applies
ALTER TABLE patients OWNER TO app_migrate;
ALTER TABLE patients ENABLE ROW LEVEL SECURITY;
ALTER TABLE patients FORCE ROW LEVEL SECURITY; -- applies even
                                                -- to the owner

The FORCE ROW LEVEL SECURITY line is important. Without it, the table owner bypasses the policy. With it, even the migration role gets filtered — which catches a category of bug where a script written for migration purposes accidentally runs in production with cross-tenant effects.

The runtime app connects as app_runtime only. Migration tools connect as app_migrate. The two are never mixed.

Pattern 3 — the "platform-admin escape hatch", explicitly

You will need to query across tenants. Support tools, customer-success dashboards, billing reconciliation, integrity audits. The wrong way is to add a "I'm a platform admin" flag to the application's JWT and bypass RLS in code. The right way is a separate role with explicit BYPASSRLS, used by a separate tool, audit-logged.

-- a role for cross-tenant queries, used by support tooling
CREATE ROLE app_platform WITH LOGIN BYPASSRLS PASSWORD '...';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_platform;
-- note: only SELECT. Cross-tenant writes are a separate
-- conversation — usually no, occasionally yes with double
-- approval.

Now your support tool connects as app_platform, can read across tenants, and every action it takes is identifiable in the database logs as having come from that role. If a developer wires up the support tool to write back into customer tables, the role doesn't have UPDATE — they get an error and have to think about it.

The discipline this enforces is real: most cross-tenant queries you think you need are actually two single-tenant queries with results aggregated outside the database. Forcing the question "do you really need to bypass RLS for this?" filters out the lazy answers.

Three gotchas I've hit

The "WITH CHECK" trap on INSERT/UPDATE

By default, an RLS policy applies to reads. Writes need a WITH CHECK clause, otherwise an authenticated user for org A could insert a row with org_id = B and the database would let them. The fix:

CREATE POLICY tenant_isolation_select ON patients
  FOR SELECT
  USING (org_id = current_setting('app.current_org_id')::uuid);

CREATE POLICY tenant_isolation_modify ON patients
  FOR ALL
  USING      (org_id = current_setting('app.current_org_id')::uuid)
  WITH CHECK (org_id = current_setting('app.current_org_id')::uuid);

Without WITH CHECK, RLS guards reads but lets writes leak. Always set both.

The covering-index lie

RLS adds a predicate to every query. If your existing indexes don't include the tenant column, you can hit query-plan regressions. The fix is mechanical: every multi-tenant table should have org_id as the first column of every index that's used by query patterns under RLS. This is good practice anyway, but RLS makes the cost of skipping it concrete.

-- Before RLS, this was fine
CREATE INDEX idx_patients_email ON patients (email);

-- After RLS, this is what you actually want
CREATE INDEX idx_patients_org_email ON patients (org_id, email);

The aggregate-bypass surprise

Window functions and certain aggregate queries can return information about rows that RLS technically hides — counts, sums, existence checks — through side channels. PostgreSQL is generally careful about this, but if your app exposes raw query interfaces (e.g., a reporting tool that lets users write SQL), assume RLS is not a complete information-flow guarantee against a determined attacker. For most apps this is irrelevant. For the apps where it matters, you already know.

The 60-minute migration on a small/medium app

If you have an existing single-tenant app with a tenant column, here's the rough sequence to add RLS without an outage:

Audit your indexes — make sure every index used in production has the tenant column first. Add the missing ones, in a migration that runs concurrently if it's a large table.
Create the application and platform roles with the privileges above. Don't switch the app to use them yet.
Create the policies with USING and WITH CHECK on every multi-tenant table. Enable RLS but use the table owner for the app connection — the policies are inert.
Add the SET LOCAL wrapper in your application's connection-acquisition code. Test it; queries should still work because the role still bypasses.
Switch the app's role to app_runtime in staging. Run the test suite. Hunt the queries that now return zero rows because the SET LOCAL wasn't applied — those are the bugs RLS just exposed.
Force-RLS on the owner. Now even ad-hoc queries from the migration role need a SET LOCAL to see data.
Production rollout — change the app's connection role; monitor; have the rollback rehearsed (it's a single config change).

That sequence shipped in two weeks on Inara Enterprise's first multi-tenant cohort. The hardest part was step 5 — the test suite found three queries that had been working only because RLS wasn't on. Each was a one-line fix. Each was a query that, in production, would have eventually leaked data.

What RLS isn't

It isn't application authorisation. RLS protects against forgotten tenant filters. It doesn't decide whether user X within org A can read patient Y. That's still your application's job.
It isn't free. The performance overhead is small but nonzero — usually a single-digit percent on selective queries with the right indexes, larger on full-table scans (which you shouldn't be doing anyway in a multi-tenant app).
It isn't a substitute for proper backups, encryption-at-rest, and audit logging. RLS is one layer; you need the others too.

Used as the safety net under a properly written application — not as the application's only auth layer — RLS earns its place in any B2B SaaS schema. The first time it catches a missing tenant filter in code review, you'll wonder why you didn't add it on day one.

Building or scaling a multi-tenant SaaS and wondering whether RLS is worth the setup?

Book a System Review Read the Inara Enterprise case study