Row-Level Security (RLS) in PostgreSQL: Policies & Multi-Tenancy

Q: What's the difference between USING and WITH CHECK?

USING controls which existing rows a role can see or act on (SELECT, and the target rows of UPDATE/DELETE). WITH CHECK controls which new or modified rows a role may write (INSERT, and the resulting rows of UPDATE). USING is the read filter; WITH CHECK is the write guard. For writable tables you generally need both, or users can create rows they shouldn't.

Row-Level Security (RLS)

Row-Level Security (RLS) is a PostgreSQL feature that enforces access control at the level of individual rows, inside the database itself. Instead of relying on your application to remember WHERE tenant_id = $current_tenant on every query, you declare policies on a table, and Postgres automatically filters which rows each user can see, insert, update, or delete — no matter how the query arrives.

RLS matters because application-layer authorization is easy to get wrong: one forgotten WHERE clause, one new endpoint that skips the filter, and a user sees another tenant's data. RLS moves the boundary into the database as a last line of defense that's enforced consistently. It has become especially prominent through platforms like Supabase, where RLS is the mechanism that lets clients talk to the database directly and still stay isolated.

TL;DR

RLS enforces per-row access control inside PostgreSQL — the database filters rows automatically, so a missed WHERE clause can't leak data.
You enable RLS on a table, then attach policies that define which rows a role can read or write.
Policies use two expressions: USING (which existing rows are visible/affected) and WITH CHECK (which new/updated rows are allowed).
Policies read the current role and session variables (e.g. current_setting('app.user_id')) to scope access to the logged-in user or tenant.
It's the backbone of secure multi-tenancy and of client-direct architectures like Supabase.
RLS is defense in depth, not a silver bullet — the table owner and superusers bypass it, and policies add query overhead you must design well.

Quick Example

Enable RLS and add a policy so each user only sees their own rows — enforced no matter what query runs:

The filtering happens in the engine — the application can't forget it, and a bug in one query can't expose another user's rows.

Core Concepts

Enabling RLS and the Default-Deny Model

RLS is off by default. Once you ENABLE ROW LEVEL SECURITY on a table, the default becomes deny: with no policies, non-owner roles see zero rows. You then add policies that grant access to specific rows. This default-deny is the safe posture — you explicitly open up what should be visible rather than hoping you closed every hole.

USING vs WITH CHECK

Every policy can specify two expressions, and the distinction trips people up:

USING filters what's visible; WITH CHECK prevents writing rows that would violate the policy (e.g. inserting a row for a different tenant). A common mistake is setting USING but forgetting WITH CHECK, letting a user insert rows they then can't see — or worse, rows attributed to someone else.

Identity: Roles and Session Variables

Policies need to know who is asking. Two mechanisms:

Database roles — current_user / current_role. Works when each app user maps to a DB role (less common in web apps).
Session settings — the app authenticates the user, then sets a session variable (SET app.user_id = '...' or via a JWT claim), and policies read it with current_setting(...). This is the dominant web pattern: one DB role, per-request identity injected into the session.

The security of this rests on the app setting that variable correctly and unforgeably per request — see best practices.

Multi-Tenancy and the Supabase Pattern

RLS's premier use case is multi-tenant isolation: many customers' data in shared tables, with each tenant able to touch only their own rows. A tenant policy:

Every query is automatically tenant-scoped, so a bug in application code can't cross tenants. Supabase takes this further: it lets browser clients query Postgres directly (through PostgREST), and RLS — reading the authenticated user's JWT claims — is what makes that safe. Policies reference auth.uid() (the logged-in user) so the database itself enforces that clients only access their own data. Without RLS, client-direct database access would be an open door; with it, it's a viable architecture.

Best Practices

Set Identity Unforgeably, Per Transaction

The whole model depends on the session variable (or JWT claim) being set correctly and immune to tampering. Set it server-side per request/transaction, use SET LOCAL so it's scoped to the transaction (critical with connection pooling, where connections are reused), and never let client input set it directly. A leaked or spoofable identity variable defeats RLS.

Always Pair USING With WITH CHECK

For any table users can write, define both clauses. USING alone lets a user insert or update rows into states they shouldn't create (including rows for other users). Make the write policy mirror the read policy unless you deliberately want them to differ.

Design Policies for Performance

Policies are appended to every query as conditions, so they must be efficient. Index the columns policies filter on (tenant_id, user_id), keep policy expressions simple, and avoid expensive subqueries or function calls per row. A poorly written policy taxes every query against the table.

Test Isolation Explicitly

Write tests that assume the identity of user A and assert they cannot see or modify user B's rows — for SELECT, INSERT, UPDATE, and DELETE. RLS bugs are silent until they leak; explicit cross-tenant tests catch them.

Remember Who Bypasses RLS

The table owner and superusers bypass RLS by default, and roles with BYPASSRLS do too. Your application should connect as a role that is subject to RLS, not the owner/superuser — otherwise policies are silently ignored. Migrations and admin tasks may run as the owner; app traffic must not.

Common Mistakes

Connecting as a Role That Bypasses RLS

USING Without WITH CHECK

Leaking Identity Across Pooled Connections

Using SET (session-wide) instead of SET LOCAL (transaction-scoped) with a connection pool means one request's identity can bleed into the next request that reuses the connection. Scope identity to the transaction.

FAQ

What problem does RLS actually solve?

It moves authorization into the database so access control can't be bypassed by an application bug. Application-layer checks rely on remembering the right WHERE clause on every query and every new endpoint; one omission leaks data. RLS enforces per-row rules in the engine consistently, as a reliable last line of defense — especially valuable for multi-tenant apps and any architecture where clients reach the database more directly.

What's the difference between USING and WITH CHECK?

USING controls which existing rows a role can see or act on (SELECT, and the target rows of UPDATE/DELETE). WITH CHECK controls which new or modified rows a role may write (INSERT, and the resulting rows of UPDATE). USING is the read filter; WITH CHECK is the write guard. For writable tables you generally need both, or users can create rows they shouldn't.

How does the database know who the current user is?

Through the current database role or, more commonly in web apps, a session variable the application sets after authenticating the user (e.g. SET LOCAL app.user_id = '...', or a JWT claim like auth.uid() in Supabase). Policies read that value with current_setting(...) to scope rows. Security depends on the app setting it correctly, per transaction, and unforgeably.

Does RLS make my app slower?

It adds the policy expressions as conditions on every query, so there's overhead — but well-designed policies are cheap. Index the columns policies filter on (like tenant_id), keep the expressions simple, and avoid per-row subqueries or function calls. Poorly written policies can noticeably tax queries; good ones add little.

Can RLS be bypassed?

Yes, by design in specific cases: the table owner, superusers, and roles with BYPASSRLS are not subject to policies. That's why your application must connect as a normal, RLS-subject role rather than the owner or a superuser — otherwise policies are silently ignored. RLS is defense in depth, not a substitute for good application-layer authorization and connection hygiene.