postgres://internals

Row-Level Security

// the problem

Most apps enforce “users only see their own data” in application code — a WHERE user_id = ? on every query. That works right up until someone forgets the clause, a second service queries the database directly, or a bug leaks another tenant's rows. Row-Level Security moves the rule into the database: a policy silently filters every query, no matter who runs it or how. It's how serious multi-tenant systems — including Supabase — guarantee isolation.

Switch roles below and watch the same SELECT * FROM documents return different rows. Toggle RLS off to see the table wide open — and notice what the superuser sees.

fig.01 · row-level security

connected as

rows visible

5 of 5

role

superuser ⚡

policy adds

bypassed — superuser ignores all policies

SELECT * FROM documents

id	tenant	title
1	acme	Acme roadmap
2	acme	Acme invoice
3	globex	Globex report
4	globex	Globex memo
5	initech	Initech TPS

⚡ a superuser bypasses RLS entirely — never let your app connect as one.

Roles are the “who”

Every connection acts as a role. Roles can log in (users), own objects, be granted into other roles (groups), and hold privileges. You control what a role may do to a table with GRANT / REVOKE:

GRANT SELECT, INSERT ON documents TO app_user;

But table privileges are all-or-nothing at the table level — SELECT lets a role read every row. To restrict which rows, you need RLS.

Enabling RLS and writing a policy

Turn it on, then add policies. A policy's USING expression is silently AND-ed onto every query — rows where it's false simply don't exist for that role:

sql · live postgresrun me first

DROP TABLE IF EXISTS documents;
CREATE TABLE documents (id int, tenant text, title text);
INSERT INTO documents VALUES
(1,'acme','Acme roadmap'), (2,'acme','Acme invoice'),
(3,'globex','Globex report'), (4,'globex','Globex memo'),
(5,'initech','Initech TPS');

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON documents
USING (tenant = current_setting('app.tenant', true));

CREATE ROLE app_user;
GRANT SELECT ON documents TO app_user;
SELECT 'setup done' AS status;

DROP TABLE IF EXISTS documents;
CREATE TABLE documents (id int, tenant text, title text);
INSERT INTO documents VALUES
(1,'acme','Acme roadmap'), (2,'acme','Acme invoice'),
(3,'globex','Globex report'), (4,'globex','Globex memo'),
(5,'initech','Initech TPS');

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON documents
USING (tenant = current_setting('app.tenant', true));

CREATE ROLE app_user;
GRANT SELECT ON documents TO app_user;
SELECT 'setup done' AS status;

⌘/ctrl + enter

Now connect as that non-superuser role, set the session's tenant, and query. The policy filters automatically — no WHERE in sight:

sql · live postgresbecome a tenant and query

SET ROLE app_user;
SET app.tenant = 'acme';
SELECT id, tenant, title FROM documents;   -- only acme rows
RESET ROLE;

SET ROLE app_user;
SET app.tenant = 'acme';
SELECT id, tenant, title FROM documents;   -- only acme rows
RESET ROLE;

⌘/ctrl + enter

Change 'acme' to 'globex' and re-run — different rows, same query. RLS guarantees the filter is always applied — it catches a forgotten WHERE — but the app still has to supply the correct identity.

// gotcha · the session identity must come from trusted code

The whole scheme is only secure if app.tenant (the session variable the policy reads) is set by trusted server-side code from a verified identity — never from anything a client controls. If a role could set its own app.tenant to any value from untrusted input, it could read every tenant's rows and RLS would protect nothing. That's exactly why the Supabase pattern below reads identity from a signed JWT the client can't forge.

`USING` vs `WITH CHECK`: reading vs writing

USING controls which rows a role can see (and update/delete). To control which rows it can write, add WITH CHECK — it validates new/updated rows, so a tenant can't insert a row for someone else:

CREATE POLICY tenant_writes ON documents
  FOR INSERT WITH CHECK (tenant = current_setting('app.tenant', true));

Without a WITH CHECK, USING is reused for writes.

The superuser footgun

// gotcha · RLS does not apply to superusers or table owners

A superuser, a role with BYPASSRLS, and (by default) the table owner all bypass RLS entirely — they see and change every row. In the explorer, that's the ⚡ postgres role seeing all five documents. So: your application must connect as an ordinary role, never the superuser or the table owner. For the owner case, ALTER TABLE … FORCE ROW LEVEL SECURITY makes policies apply to the owner too.

The Supabase pattern

// why it matters · policies + session context = multi-tenant SaaS

This is exactly how Supabase secures a database that browsers talk to directly. Every request runs as a limited role, and the user's identity is injected into the session (a JWT claim, read via current_setting('request.jwt.claims', …) or the auth.uid() helper). Policies like USING (user_id = auth.uid()) then scope every query to that user — enforced by Postgres itself, so even a compromised client can't read another user's rows. RLS turns the database into the security boundary.

Your turn

// challengeuses the documents table + app_user role from the setup cell

Connect as app_user scoped to the 'globex' tenant and count how many documents you can see. (Reset the role afterward.)

SET ROLE app_user;
SET app.tenant = 'globex';
SELECT count(*) AS rows FROM documents;
RESET ROLE;

SET ROLE app_user;
SET app.tenant = 'globex';
SELECT count(*) AS rows FROM documents;
RESET ROLE;

⌘/ctrl + enter

// challengeuses the documents table from the setup cell

Show the superuser bypass: as the (superuser) session role, count ALL documents regardless of policy.

RESET ROLE;
SELECT count(*) AS rows FROM documents;

RESET ROLE;
SELECT count(*) AS rows FROM documents;

⌘/ctrl + enter

// what you now understand

01Every connection acts as a role; GRANT/REVOKE control table-level privileges, but SELECT alone lets a role read every row.
02Row-Level Security adds per-row policies: a policy's USING expression is AND-ed onto every query, so a role sees only the rows it allows.
03USING governs which rows are visible (and updatable); WITH CHECK validates rows a role tries to write, so it can't insert for another tenant.
04Superusers, BYPASSRLS roles, and (unless FORCE'd) the table owner bypass RLS — so apps must connect as an ordinary role, never the superuser/owner.
05The Supabase pattern: run as a limited role, inject the user's identity into the session (JWT/auth.uid()), and write policies against it — Postgres becomes the security boundary.

// self-test

You enable RLS with a tenant-isolation policy, but your app still sees every tenant's rows. What's the most likely cause?

// self-test

What does a policy's `WITH CHECK` clause do that `USING` doesn't?

// go deeper

Row Security Policies (docs) — ENABLE/FORCE RLS, USING vs WITH CHECK, policy roles
CREATE POLICY — policy syntax, commands, permissive vs restrictive
Database Roles & GRANT — roles, membership, privileges, BYPASSRLS
Supabase: Row Level Security — auth.uid() and the real-world multi-tenant pattern

nextOne Query, End to End

Row-Level Security

Roles are the “who”

Enabling RLS and writing a policy

USING vs WITH CHECK: reading vs writing

The superuser footgun

The Supabase pattern

Your turn

`USING` vs `WITH CHECK`: reading vs writing