Row-Level Security for Multi-Tenant SaaS in PostgreSQL

In a multi-tenant SaaS, the worst bug you can ship is one tenant seeing another tenant's data. It usually happens the same way: a query somewhere forgets its WHERE org_id = ? filter, and suddenly one customer's dashboard shows another's records. Row-level security moves that filter out of application code, where it can be forgotten, and into the database, where it's enforced on every query automatically. It's PostgreSQL's strongest tool for tenant isolation, and it's worth understanding even if you decide not to use it everywhere.

This is part 31 and the last of the advanced-features module in the PostgreSQL Masterclass, following triggers and stored procedures.

The problem RLS solves

The standard multi-tenant approach is a shared table with an org_id column, and every query filters by the current tenant:

SELECT * FROM projects WHERE org_id = $1;   -- $1 = current tenant

This works right up until one query, in one code path, forgets the filter. A new endpoint, a reporting query, a background job written in a hurry. The filter is a convention, and conventions get missed. When they're missed here, the failure is a cross-tenant data leak, which is about the most serious bug a SaaS can have.

Row-level security makes the filter mandatory. You define a policy once, and Postgres applies it to every query against the table automatically, so a query that forgets the filter still only sees the current tenant's rows.

Enabling RLS and writing a policy

Two steps: enable RLS on the table, then create a policy describing which rows are visible. The policy references a session variable that your application sets to the current tenant:

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON projects
  USING (org_id = current_setting('app.current_org')::bigint);

The USING expression is a filter automatically added to every SELECT, UPDATE, and DELETE. Now this query returns only the current tenant's projects, even though it has no WHERE clause:

SET app.current_org = '42';
SELECT * FROM projects;   -- only org 42's rows, enforced by the policy

The application sets app.current_org once per request (per database session or transaction), and every query in that request is automatically scoped. Forgetting a WHERE org_id clause no longer leaks data, because the policy is doing the filtering underneath.

Separating read and write policies

A USING clause controls which rows are visible to read and which existing rows can be updated or deleted. For inserts and for the new values of updates, you use WITH CHECK, which validates that the rows being written belong to the current tenant:

CREATE POLICY tenant_isolation ON projects
  USING (org_id = current_setting('app.current_org')::bigint)
  WITH CHECK (org_id = current_setting('app.current_org')::bigint);

Now a tenant can't insert a row with someone else's org_id, or update a row to move it to another tenant. USING guards what you can see and change; WITH CHECK guards what you can write. A complete isolation policy uses both.

The critical detail: table owners bypass RLS

Here's the gotcha that turns RLS from real protection into a false sense of security. By default, the table owner and superusers bypass row-level security entirely. If your application connects as the same role that owns the tables (very common), RLS does nothing, because that role ignores the policies.

The fix is to run your application as a separate, non-owner role that RLS applies to, and optionally force it:

-- application role that policies apply to
CREATE ROLE app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- force RLS even for the table owner, belt and suspenders
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

Connect the application as app_user, not the owner. If you skip this and connect as the owner, your carefully written policies are silently ignored. This is the single most common RLS mistake, and it's worth double-checking with a test: connect as the app role, set a tenant, and confirm you cannot see another tenant's rows.

Is RLS worth it?

RLS is not free. It adds the policy filter to every query, which the planner handles well but which is still work, and it adds operational complexity: a session variable to set correctly on every connection, a separate role, and policies to maintain. Teams reasonably land in different places:

• Use RLS when tenant isolation is critical and you want defense in depth, so a forgotten filter can't leak data. For many SaaS products, that guarantee is worth the overhead.
• Rely on application-level filtering (with rigorous testing and a shared query layer that always adds the tenant filter) when you want simplicity and are confident in your discipline, or when a separate database or schema per tenant already provides isolation.

Some teams use RLS specifically as a safety net beneath application filtering: the app still adds WHERE org_id, and RLS catches the cases where it doesn't. That belt-and-suspenders approach costs a little performance for a strong guarantee against the worst-case bug.

Row-level security is the database enforcing what your application should be doing anyway, so that a single forgotten filter doesn't become a breach. Enable it on tenant-scoped tables, write USING and WITH CHECK policies against a session variable, and (the part people miss) connect as a non-owner role so the policies actually apply. Do that and cross-tenant leaks stop being one missed WHERE clause away.

That completes the advanced-features module. Next, we open the final module on scaling and operations with table partitioning, the first tool for keeping large tables fast.