Row-level security for multi-tenant data

conceptThe concept

ALTER TABLE t ENABLE ROW LEVEL SECURITY turns on default-deny: without a matching policy, a non-owner sees no rows. CREATE POLICY ... USING (expr) filters rows that can be read, updated or deleted; WITH CHECK (expr) validates rows being inserted or updated. Policies can target specific commands and roles, and several permissive policies are OR-ed together (restrictive ones are AND-ed).

A common pattern stores the tenant in a custom setting: the application runs SET app.tenant_id = '42' (or set_config) after taking a connection, and the policy uses current_setting('app.tenant_id'). With a transaction-mode pooler, use SET LOCAL inside each transaction.

Table owners and superusers bypass RLS, as do roles with BYPASSRLS. ALTER TABLE ... FORCE ROW LEVEL SECURITY applies policies to the owner too.

whyWhy it matters

One missing WHERE tenant_id = ? in application code leaks another customer's data. RLS makes the database refuse to return it, whatever the application forgets.

Use caseReal-world scenario

A B2B version of the shop hosts many merchants in the same tables. Each request sets the merchant id, and policies ensure a merchant can only see and change its own orders.

Try itExamples

Run and edit these in the interactive lesson.

BEGIN;
CREATE ROLE merchant_app NOLOGIN;
GRANT USAGE ON SCHEMA public TO merchant_app;
GRANT SELECT, UPDATE ON orders TO merchant_app;
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY orders_by_customer ON orders
  TO merchant_app
  USING (customer_id = current_setting('app.customer_id')::int)
  WITH CHECK (customer_id = current_setting('app.customer_id')::int);

SET LOCAL app.customer_id = '42';
SET LOCAL ROLE merchant_app;
SELECT count(*) AS visible_orders, min(customer_id), max(customer_id) FROM orders;
UPDATE orders SET status = 'cancelled' WHERE customer_id = 7 RETURNING id;
ROLLBACK;

BEGIN;
CREATE ROLE merchant_app NOLOGIN;
GRANT USAGE ON SCHEMA public TO merchant_app;
GRANT SELECT, UPDATE ON orders TO merchant_app;
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY orders_by_customer ON orders TO merchant_app
  USING (customer_id = current_setting('app.customer_id')::int)
  WITH CHECK (customer_id = current_setting('app.customer_id')::int);
SET LOCAL app.customer_id = '42';
SET LOCAL ROLE merchant_app;
UPDATE orders SET customer_id = 7 WHERE id = (SELECT min(id) FROM orders);

BEGIN;
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
SELECT count(*) AS owner_sees FROM orders;
SELECT relname, relrowsecurity, relforcerowsecurity FROM pg_class WHERE relname = 'orders';
ROLLBACK;

internalsUnder the hood

Policies are added to the query as security barrier quals, so user-supplied functions cannot see rows before the policy filters them (unless marked LEAKPROOF). Policy expressions run for every row; index the tenant column and keep the expression simple so it can use the index.

Avoid / preferDo this, not that

-- every query in the app remembers to add WHERE tenant_id = $1

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant ON orders USING (tenant_id = current_setting('app.tenant_id')::int);

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;  -- app connects as table owner

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

PitfallsCommon mistakes

• Testing RLS as the table owner or a superuser and concluding it works (or does not). Test with SET ROLE to the application role.
• Using session-level SET for the tenant with a transaction-pooling PgBouncer. The next client on that server connection inherits it. Use SET LOCAL.
• Forgetting WITH CHECK, so a tenant can insert or move rows into another tenant's space.

LimitsWhen not to use it

Avoid complex policies with subqueries over large tables on hot paths; they run for every row considered. Keep policies to simple comparisons on indexed columns.

SecuritySecurity notes

Use current_setting('app.tenant_id', true) so a missing setting returns NULL and hides rows instead of erroring, and set it with SET LOCAL inside each transaction when using a transaction-mode pooler.

ChallengeTry it yourself

BEGIN;
CREATE TABLE my_orders AS SELECT * FROM orders;
CREATE ROLE cust NOLOGIN;
GRANT SELECT ON my_orders TO cust;
ALTER TABLE my_orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY own_orders ON my_orders FOR SELECT TO cust
  USING (customer_id = current_setting('app.customer_id', true)::int);
SET LOCAL app.customer_id = '42';
SET LOCAL ROLE cust;
SELECT count(*) AS visible_orders FROM my_orders;
RESET ROLE;
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Privileges
• Row security policies
• Predefined roles