IBRAVRA Media Network
Website & Hosting • 12 min read

Postgres Row-Level Security for Multi-Tenant Apps: The Complete Operator Playbook

Turn tenant isolation from an application promise into a database guarantee — exact CREATE POLICY sequences, USING vs WITH CHECK, the BYPASSRLS holes, index math, and a rollout checklist.

By Enow A. Jovial • Published 2026-11-19

Postgres Row-Level Security (RLS) turns tenant isolation from an application promise into a database guarantee: once you enable it on a table and write a policy, no query from an ordinary role can return, change, or delete a row that fails the policy expression — even if the application forgets the WHERE tenant_id = ... clause. This guide covers the CREATE POLICY sequences, USING versus WITH CHECK, the BYPASSRLS holes, index behaviour, and rollout.

What row-level security is

Row-level security is a Postgres feature, documented in Chapter 5.9 of the official PostgreSQL documentation, that restricts on a per-user basis which rows can be returned by normal queries or inserted, updated, or deleted by data-modification commands. It sits alongside GRANT: grants decide whether a role may touch a table at all; policies decide which rows it may see or change. By default a table has no policies, so a role with table privileges sees every row. Run ALTER TABLE ... ENABLE ROW LEVEL SECURITY and all normal access must be allowed by a policy — otherwise a default-deny policy applies and no rows are visible or modifiable.

Three properties drive every decision that follows. Policies are evaluated per row, before the query's own conditions, with the privileges of the user running the query. Superusers and BYPASSRLS roles always bypass the system, and table owners normally bypass it too — the owner can opt back in with FORCE ROW LEVEL SECURITY. Only the table owner can enable or disable row security and add policies.

RLS is a second lock on the same door, not a replacement for the first. Grants control table access; policies control row access. An application that relies on policies while leaving broad grants in place has built one lock and left the other open.

Why isolation belongs in the database

Multi-tenant applications usually start with a tenant_id column and a convention: every query filters on it. The convention works until it does not — a new endpoint, a reporting query, a background job — and one missing filter hands one tenant another tenant's rows. The failure is silent: the query succeeds, and the leak is discovered by a customer, not by a monitor. RLS moves the filter below the application: the policy is attached to the table, so every path into it passes through the same check. But RLS is not a substitute for application-level checks: a policy reading current_setting('app.current_tenant') is only as honest as the value the application sets at the start of each session.

The tenant-isolation pattern

The standard shape: one shared table per entity, one tenant_id column on every tenant-owned table, one session variable carrying the current tenant — set once per request, inside a transaction.

CREATE TABLE invoices (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL,
  number text NOT NULL,
  total_cents bigint NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.current_tenant')::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

The application side is one statement per transaction:

SET LOCAL app.current_tenant = '3f8a2c1e-0000-4000-8000-000000000001';

SET LOCAL matters. A plain SET persists for the whole session, and a pooled connection handed to the next request still carries the previous tenant's value — the exact cross-tenant leak RLS exists to prevent. SET LOCAL dies with the transaction. Our editorial analysis found this is the most common RLS deployment error: a correct policy with a session variable leaking across pooled requests. With PgBouncer in transaction mode, the pooler already resets session state between transactions on the same TCP connection — see the Postgres connection pooling guide first.

PostgreSQL CREATE POLICY syntax reference
PostgreSQL CREATE POLICY syntax reference

USING versus WITH CHECK

USING and WITH CHECK answer different questions, and confusing them is the second most common RLS error. USING governs existing rows: which rows a query may see, update, or delete. WITH CHECK governs new rows: which rows an INSERT or UPDATE may create. When WITH CHECK returns false or null, the write fails with an error. When USING returns false or null, the row is invisible — no error, no warning.

The official documentation's paired example shows it: a FOR SELECT USING (true) policy plus a modification policy on user_name = current_user lets every user read the whole users table but change only their own row. For ALL or UPDATE policies, omitting WITH CHECK does not leave writes unchecked: the USING expression is reused as the WITH CHECK expression — which is why the single tenant_isolation policy above is sufficient.

ClauseGovernsFalse or null resultCommandsAuthority
USINGWhich existing rows are visible or modifiableRow silently invisibleSELECT, UPDATE, DELETE, ALLPostgreSQL 18, CREATE POLICY reference
WITH CHECKWhich new rows may be created or writtenWrite fails with an errorINSERT, UPDATE, ALLPostgreSQL 18, CREATE POLICY reference

Permissive versus restrictive

A table can carry several policies, and the combination rule is the part operators most often get backwards. Permissive policies — the default — combine with OR: a row is accessible if any permissive policy passes. Restrictive policies combine with AND: every restrictive policy must pass. A row is accessible only if at least one permissive policy passes and all restrictive policies pass. A table with only restrictive policies grants access to nothing. In practice: one permissive tenant policy per table, plus a restrictive policy for the rare global rule — for example, AS RESTRICTIVE USING (legal_hold = false) for rows under a legal hold.

Policy typeCombinationEffect of adding oneWhen to use itAuthority
Permissive (default)ORWidens accessThe tenant boundaryPostgreSQL 18, Chapter 5.9
RestrictiveANDNarrows accessGlobal rules for every rolePostgreSQL 18, Chapter 5.9
Restrictive only—Grants nothingNever — a misconfigurationPostgreSQL 18, CREATE POLICY reference

The bypass list

RLS never constrains a short, explicit list of roles — and every one is a real path into your data. Superusers always bypass row security. Roles with the BYPASSRLS attribute always bypass it. Table owners normally bypass row security, though the owner can opt in with ALTER TABLE ... FORCE ROW LEVEL SECURITY. Whole-table operations are not subject to row security at all: TRUNCATE and REFERENCES bypass policies by design.

That is a role-design rule, not a policy rule. Application connections must never run as a superuser, a BYPASSRLS role, or the table owner. The migration role owns the tables; the application role holds only the grants it needs. A missing grant raises a 42501 error before any policy runs.

A policy is not a permission. If the application role still holds a broad GRANT and also carries BYPASSRLS — or connects as the table owner — the policy is decoration. Audit pg_roles for rolbypassrls and table ownership before you declare any table isolated.

The Supabase pattern

On Supabase, the tenant identity usually comes from the authenticated user. The auth.uid() helper returns the requesting user's ID; the canonical policy shape is create policy "Users can view their own profile." on profiles for select to authenticated using ((select auth.uid()) = user_id);

The TO authenticated clause scopes the policy to signed-in users only. The (select auth.uid()) form — a scalar subquery, not a bare function call — lets the planner evaluate the value once per query instead of once per row. And the policy alone is not the whole control: Supabase's documentation instructs you to enable RLS, then revoke existing grants from both client roles and grant back only what each role needs — no data is accessible through the JSON API over HTTP with a publishable key until policies exist.

Indexes and the planner

A policy expression is a predicate the planner must apply to every candidate row, and its cost depends entirely on whether an index can serve it. A policy on tenant_id = current_setting(...) is only cheap when an index starts with tenant_id. Without one, every query scans every row and discards the ones that fail the policy.

Take 480,000 invoice rows across 1,200 tenants — 400 each — indexed on (tenant_id, created_at):

rows scanned with index=480,000 rows1,200 tenants=400 rows\text{rows scanned with index} = \frac{480{,}000\ \text{rows}}{1{,}200\ \text{tenants}} = 400\ \text{rows}
rows scanned without index=480,000 rows (full sequential scan)\text{rows scanned without index} = 480{,}000\ \text{rows (full sequential scan)}

The index does not change what the policy allows; it changes how much work the database does to enforce it — 400 rows examined instead of 480,000, a selectivity of 0.083%. Keep the policy expression and the application's WHERE clause in agreement so the planner uses the same index.

Related: the Cloudflare Workers D1 versus Hyperdrive benchmark, the vector database architecture comparison, and the website cost calculator.

The leak arithmetic

The reason RLS exists is the blast radius of a single omission. Take the same 480,000-row, 1,200-tenant table: if its policy is missing — RLS never enabled, or the policy dropped in a migration — every row is visible to every tenant with a grant:

rows exposed=480,000×1,1991,200=479,600 rows belonging to other tenants\text{rows exposed} = 480{,}000 \times \frac{1{,}199}{1{,}200} = 479{,}600\ \text{rows belonging to other tenants}
exposure share=479,600480,000=99.92%\text{exposure share} = \frac{479{,}600}{480{,}000} = 99.92\%

One missing policy exposes 99.92% of that table to the wrong tenants. That is the number to carry into the rollout meeting: isolation is a chain, and the chain fails at its weakest table. Sampling is a weak detector for rare leaks: if 1% of rows leak, a random sample of 50 rows detects at least one with probability:

P(detect)=1−(1−0.01)50=1−0.605=0.395P(\text{detect}) = 1 - (1 - 0.01)^{50} = 1 - 0.605 = 0.395

A 39.5% chance of catching a 1% leak from 50 rows is not a test. Isolation verification must be exhaustive per tenant: run a query as that tenant's role and assert the result contains zero rows with a different tenant_id.

Verify isolation with a negative probe: log in as tenant A and assert zero rows where tenant_id <> 'A'. A probe that only confirms tenant A can see its own rows has tested nothing.

Failure modes

The missing policy. RLS enabled, no policy created, and the application goes dark: every query returns zero rows. This is default-deny working as designed: it fails closed. The dangerous version is the reverse: RLS never enabled on a new table added by a migration, so the table has no policies and no enforcement, and everything works perfectly while leaking.

The superuser connection. The application connects as the table owner or a BYPASSRLS role, often because migration credentials were copied into the application config. Every policy passes trivially and no tenant boundary exists. Audit the connection role before you audit the policies.

The pooled session variable. The application sets app.current_tenant with a plain SET on a pooled connection, and the next request served by that connection inherits the previous tenant. Use SET LOCAL and test by reusing a connection across two tenants in staging.

The app-level tenant confusion. The application computes the tenant from a request parameter the caller controls — a query string, a header, an unverified JWT claim — and sets the session variable from it. RLS then enforces the attacker's chosen boundary. The tenant identity must come from the authenticated session, never from client-supplied input.

The policy-plus-JOIN cliff. A policy on the parent table does not constrain the joined child table. A query joining invoices to invoice_lines needs a policy on both tables, or the join can surface child rows whose parent is invisible. Every table in the join needs its own policy and leading index column.

Rollout checklist

Roll out in three phases: Phase 1, inventory and index; Phase 2, policies and probes in staging; Phase 3, enable and assert in production.

  • [x] Inventory every tenant-owned table. Every table with a tenant_id column; the rest is global reference data or a leak waiting for a name.
  • [x] Create the application role. Only the grants it needs, no BYPASSRLS, never the table owner.
  • [x] Add the tenant index first. (tenant_id, ...) indexes before enabling RLS.
  • [x] Write policies in migration files. ENABLE ROW LEVEL SECURITY and CREATE POLICY in the same migration as the table.
  • [x] Set the tenant with SET LOCAL. One statement per transaction, from the authenticated session.
  • [x] Force the owner into the policy. FORCE ROW LEVEL SECURITY on every tenant table.
  • [x] Revoke and re-grant. Remove broad grants from anon and authenticated; grant back only what each role needs.
  • [x] Run the negative probe per tenant. As each tenant's role, assert zero rows with a foreign tenant_id, including child tables and views.
  • [x] Test the pooled path in staging. Reuse one pooled connection across two tenants; confirm the second sees only its own rows.
  • [x] Assert policies after every deploy. Query pg_policies; every tenant table must still have its policy.

Cross-border notes

RLS is a database mechanism, not a compliance regime. A SaaS product serving customers in the EU, the UK, and the US typically keeps all tenants in one cluster, and the tenant boundary is then the only thing standing between one customer's data and another's. Data-protection rules do not prescribe Postgres policies, but they do prescribe that personal data be protected against unauthorised access — and an isolation failure exposing 99.92% of a table is an unauthorised-access event by any definition.

Founder & CEO Insight — Enow A. Jovial

"Every multi-tenant product I have reviewed had the same gap: the team trusted the application to remember the tenant, and the application forgot exactly once. Put the boundary in the database, where forgetting is not an option — then prove it with a probe that tries to cross it."
— Enow A. Jovial, Founder & Chief Executive Officer

The bottom line

Row-level security is a small feature with a large blast radius. Done correctly, it converts tenant isolation from a convention the whole engineering team must remember into a property of the schema that holds even when someone forgets. Done carelessly — a missing policy, a bypassing role, a pooled session variable — it only looks like isolation while the data flows freely. The work is not writing the policy; a tenant-isolation policy is three lines.