Postgres Row-Level Security: A Multi-Tenant Checklist

A single missing filter is all it takes. In a multi-tenant SaaS, every query that touches customer data has to carry a tenant filter, and every new developer, ad-hoc report, background job and support query is another chance to forget it. The failure mode is not a slow dashboard — it is one customer's invoice landing in another customer's inbox, which in India also means a DPDP Act incident you have to notify and explain. The fix that holds is to stop trusting the application layer and let Postgres enforce the boundary.

Why application-level scoping stops being enough

ORM default scopes, repository patterns and code review all help. They also all sit above the query, and plenty of things run below them: raw SQL in a reporting endpoint, a psql session during an incident, a Metabase dashboard, a one-off backfill script, a queue worker reusing a pooled connection. Each one is a tenant filter somebody has to remember.

Row-level security (RLS) moves the filter into the table. You write a policy once, and every role that is not the table owner or a superuser obeys it — whether the query came from your ORM, your BI tool or your laptop.

Step 1: get the schema ready

• Every tenant-scoped table needs a tenant_id uuid NOT NULL column. No exceptions, not even small tables that 'happen to' belong to one tenant.
• Put tenant_id first in composite indexes: (tenant_id, created_at DESC), (tenant_id, status). A policy check is only cheap if an index can serve it.
• Unique constraints must include tenant_id, or two tenants cannot both have invoice number INV-001.
• On an existing database, add the column as nullable, backfill from the parent table, then set NOT NULL in a separate migration. Doing both in one transaction locks the table for the length of the backfill.

A 40-table schema typically has 8-12 tables that genuinely need this; the rest are global lookups. Start with the ones holding customer-identifiable rows.

Step 2: pass the tenant through the session

The policy reads a session variable rather than a query parameter. For invoices, that looks like: CREATE POLICY tenant_isolation ON invoices USING (tenant_id = current_setting('app.tenant_id')::uuid) WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid).

Set the value at the start of every request, inside the transaction: SET LOCAL app.tenant_id = '<uuid>'. Use SET LOCAL, not SET. A session-level value survives the connection going back into the pool and will hand the next tenant your data.

WITH CHECK matters as much as USING. Without it, reads are scoped but INSERT and UPDATE are not, so a bug can write a row into another tenant's account and only fail later, on read.

Step 3: close the bypasses

• Table owners bypass RLS unless you run FORCE ROW LEVEL SECURITY on the table. Do it for every one.
• Superusers, and any role with BYPASSRLS, ignore policies entirely. Your application must connect as a plain, non-owner role. Keep a separate migration role for DDL.
• Views run with the permissions of their owner on Postgres 14 and earlier. On 15+, create them with security_invoker = true.
• SECURITY DEFINER functions need an explicit SET search_path and their own tenant check.
• TRUNCATE is not subject to RLS at all. Rare in application code, useful to know during an incident.

Step 4: pooling, rollout and tests

PgBouncer in transaction mode works fine with SET LOCAL inside a transaction. It does not work with session-level SET, and that mismatch is the most common way teams get this wrong in production after it passed in staging.

Roll out in stages. Add the policy for SELECT first, watch the slow query log for a week, then extend it to INSERT, UPDATE and DELETE with WITH CHECK. Create policies on busy tables during a quiet window.

Then test the way production runs. If your suite connects as the table owner, everything passes while production still leaks, because FORCE is what makes the owner obey — and only if you set it. Run tests as the application role, and add one assertion to your boot sequence: connect, run a count on a tenant table with nothing set, and fail startup if it returns anything but zero.

A worked example: a B2B portal with 300 tenant companies, one shared Postgres database, one application role. A support engineer opens psql to debug a stuck invoice. Without RLS, a mistyped query returns billing data for all 300 companies. With a forced policy, the same session returns zero rows until it sets app.tenant_id — and if it sets the wrong one, it sees that tenant and nothing else. The blast radius of a mistake becomes one tenant instead of all of them.

Where to start

Pick one table — invoices, documents or messages — and take it end to end: column, index, policy, FORCE, application role, and a test that runs with nothing set. Then repeat on the next one. If you would rather have the path mapped against your own schema before you touch production, that is the kind of work we do at Maysan Labs when we build multi-tenant platforms and internal tools.