Row-Level Security

Enforce tenant isolation at the database layer with ENABLE ROW LEVEL SECURITY and a USING policy — no application bug can bypass it

Most multi-tenant applications enforce tenant isolation with a WHERE tenant_id = ? clause added to every query, by hand, in application code. That works exactly until one query is written without it — a new endpoint, a rushed bug fix, a report generator someone forgot about. The moment that happens, one tenant's data is visible to another.\n\nRow-level security (RLS) moves that filter into PostgreSQL itself. Once enabled on a table, every SELECT, UPDATE, and DELETE is silently rewritten to include the policy's condition — the application does not need to remember anything, and cannot forget to. The policy expression typically reads a session setting injected by the application at connection or transaction time, such as app.tenant_id.\n\nTwo details matter enormously and are easy to get backwards. First, enabling RLS with zero policies means zero rows are visible to anyone except the table owner — not "no filtering," but complete default-deny. Second, superusers and any role with the BYPASSRLS attribute skip RLS checks entirely, regardless of policies or FORCE ROW LEVEL SECURITY — FORCE only extends enforcement to the table's owner, not to superusers.

Turning RLS On

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

This alone makes the table invisible to everyone except its owner (and superusers). A policy is what makes rows visible again — under specific conditions.

A Policy That Filters by Session Setting

CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id', true));

current_setting('app.tenant_id', true) reads a custom (application-defined) session setting; the second argument true means "return NULL instead of raising an error if it was never set" — which, combined with the policy, means an unset tenant sees zero rows rather than the query failing outright.

Injecting Tenant Context

-- Session-level: stays set until changed or the session ends
SET app.tenant_id = 'acme';

-- Transaction-scoped: the production pattern —
-- automatically reverts at COMMIT or ROLLBACK, so a pooled
-- connection can never leak one tenant's setting into the next
BEGIN;
SET LOCAL app.tenant_id = 'acme';
SELECT * FROM orders;
COMMIT;

SET LOCAL only works inside a transaction, and only affects that transaction — the standard pattern when connections are reused from a pool, so a value from one request can never bleed into the next.

A Policy With No WITH CHECK Still Protects Writes

When a policy is created without an explicit WITH CHECK, PostgreSQL reuses the USING expression as the check for INSERT and UPDATE too. That means a tenant cannot even insert a row claiming to belong to a different tenant — the same expression that filters reads also constrains writes.

The Superuser Bypass

SELECT relrowsecurity, relforcerowsecurity FROM pg_class WHERE relname = 'orders';

Superusers, and any role with the BYPASSRLS attribute, skip RLS entirely — always, regardless of policies. ALTER TABLE ... FORCE ROW LEVEL SECURITY extends enforcement to the table owner, but it has no effect on a superuser or a BYPASSRLS role. There is no policy expression that can override this — it is a role attribute checked before policies are even evaluated.

Row-Level Security (RLS)

A per-table mechanism where PostgreSQL transparently adds a policy's condition to every query against that table. Enabling RLS with no policies defined makes the table return zero rows to anyone except the owner — it is default-deny, not default-allow.

BYPASSRLS

A role attribute (superusers have it implicitly) that skips row-level security checks entirely for that role, on every table, regardless of any policy or FORCE ROW LEVEL SECURITY setting. It exists for administrative and maintenance tasks that must see every row unconditionally.

FORCE ROW LEVEL SECURITY

By default, a table's owner is exempt from its own RLS policies, even with RLS enabled — the assumption being that owners need unrestricted access for maintenance. FORCE ROW LEVEL SECURITY removes that exemption for the owner. It still has no effect on superusers or BYPASSRLS roles — nothing can force RLS onto those.

🔌 Connect as postgres

Connect to beer_db as postgres to set up and enable row-level security on orders.

psql -U postgres -d beer_db

SET psql (18.4) Type "help" for help. beer_db=#

🌱 Seed Two Tenants' Data

Insert order rows for two different tenants, acme and globex, into the shared orders table.

INSERT INTO orders (tenant_id, customer_name, amount) VALUES ('acme', 'Wile E. Coyote', 129.99), ('acme', 'Road Runner', 45.00), ('globex', 'Hank Scorpio', 998.50);

beer_db=# INSERT INTO orders (tenant_id, customer_name, amount) VALUES beer_db-# ('acme', 'Wile E. Coyote', 129.99), beer_db-# ('acme', 'Road Runner', 45.00), beer_db-# ('globex', 'Hank Scorpio', 998.50); INSERT 0 3

🔍 Confirm RLS Is Currently Off

Check pg_class to confirm row-level security is not yet enabled on orders.

SELECT relrowsecurity, relforcerowsecurity FROM pg_class WHERE relname = 'orders';

relrowsecurity | relforcerowsecurity ----------------+--------------------- f | f (1 row)

🔐 Enable Row-Level Security

Turn on RLS for the orders table.

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

ALTER TABLE

🙈 Confirm Default-Deny Before Any Policy Exists

Connect as tenant_acme and query orders — with RLS on and no policy, it should return zero rows, not an error.

\q

psql -U tenant_acme -d beer_db

SELECT * FROM orders;

beer_db=# \q student@lab:~$ psql -U tenant_acme -d beer_db SET psql (18.4) Type "help" for help. beer_db=> SELECT * FROM orders; order_id | tenant_id | customer_name | amount ----------+-----------+---------------+-------- (0 rows)

📜 Create the Tenant-Isolation Policy

Create a policy that only allows rows where tenant_id matches the app.tenant_id session setting.

\q

psql -U postgres -d beer_db

CREATE POLICY tenant_isolation ON orders USING (tenant_id = current_setting('app.tenant_id', true));

beer_db=> \q student@lab:~$ psql -U postgres -d beer_db SET psql (18.4) Type "help" for help. beer_db=# CREATE POLICY tenant_isolation ON orders USING (tenant_id = current_setting('app.tenant_id', true)); CREATE POLICY

🏢 tenant_acme Sees Only Its Own Rows

Connect as tenant_acme, set app.tenant_id to acme, and query orders — only acme rows should appear.

\q

psql -U tenant_acme -d beer_db

SET app.tenant_id = 'acme';

SELECT * FROM orders;

🏭 tenant_globex Sees Only Its Own Rows

Connect as tenant_globex, set the session to globex, and confirm isolation holds in the other direction.

\q

psql -U tenant_globex -d beer_db

SET app.tenant_id = 'globex';

SELECT * FROM orders;

beer_db=> \q student@lab:~$ psql -U tenant_globex -d beer_db SET psql (18.4) Type "help" for help. beer_db=> SET app.tenant_id = 'globex'; SET beer_db=> SELECT * FROM orders; order_id | tenant_id | customer_name | amount ----------+-----------+---------------+-------- 3 | globex | Hank Scorpio | 998.50 (1 row)

🚫 A Policy Without WITH CHECK Still Blocks Cross-Tenant Writes

Still as tenant_globex, attempt to insert a row claiming to belong to acme. The USING expression doubles as the write check.

INSERT INTO orders (tenant_id, customer_name, amount) VALUES ('acme', 'Impersonated Row', 1.00);

ERROR: new row violates row-level security policy for table "orders"

👑 Confirm the Superuser Bypass

Connect back as postgres and query orders with no app.tenant_id set at all — a superuser sees every row regardless of RLS.

\q

psql -U postgres -d beer_db

SELECT order_id, tenant_id, customer_name FROM orders ORDER BY order_id;

Lab 2.2.3 complete. You can now design and verify database-enforced tenant isolation:\n\n\n ENABLE ROW LEVEL SECURITY : ✅ default-deny confirmed\n CREATE POLICY USING (...) : ✅ session-scoped filter\n SET app.tenant_id : ✅ tenant context injected\n Isolation verified : ✅ both tenants, both directions\n Implicit WITH CHECK : ✅ cross-tenant insert blocked\n Superuser bypass : ✅ confirmed and explained\n

Enable JavaScript to run the live terminal and track your progress.