Secure database engineering
Does this row-level security actually isolate tenants?
The idea in 30 seconds
Row-level security looks like a one-line guarantee that tenants cannot see each other's data. It holds only if the login is not the table owner or a superuser, if the tenant setting cannot leak from one request to the next through a pooled connection, and if a test proves isolation as the real application login. Review an RLS rollout for those three things before anything else.
Helps to know first: PostgreSQL security and multi-tenancy (coming soon), Functions, procedures and triggers, Connection management and pooling (coming soon)
On this page
The situation
The shop is becoming a platform: many merchants (tenants) in one database. Every table has a tenant_id. Until now, every query had to remember WHERE tenant_id = ?, and last month one did not. A teammate opens a pull request to make the database enforce it.
Review taskEnforce tenant isolation with row-level security
Pull request: “Turn on row-level security so a missing WHERE tenant_id can never leak data again.”
-- migration
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
USING (tenant_id = current_setting('app.tenant_id')::int);
-- (same for the other 14 tenant tables)# request middleware: runs at the start of every web request
def set_tenant(conn, tenant_id):
conn.execute("SET app.tenant_id = %s", [str(tenant_id)])# config/production.yaml: unchanged
database:
host: db
name: shop
user: shop_owner # owns all tables; also runs migrations
pool_size: 20Tested locally: logged in as tenant 2, the invoices page shows only tenant 2’s invoices.
Would you approve it? Write the review you would leave.
Your call
Model review
Request changes. The policy is right; the way it is used means it does not isolate anything in production, and in one case actively leaks.
1. The application logs in as the table owner, so the policy is ignored. Table owners bypass row-level security unless the table is forced, and nothing in this PR forces it. We reproduced it with this exact policy on a nine-row table with three tenants:
| Login | With app.tenant_id = 2, tenants visible |
|---|---|
| A role with only granted privileges | 2 |
The table owner (as in production.yaml) | 1, 2 and 3 |
The local test passed only because the developer’s local login differed from production’s. Superusers and roles with BYPASSRLS bypass row security even when the table is forced, so the fix is a separate login, not just FORCE.
2. The tenant setting outlives the request. SET without LOCAL lasts for the whole session, and with a pool of 20 the session outlives the request. Any code path that skips the middleware, such as a background job, a webhook handler or an error path, runs with whatever tenant the connection last served. We reproduced it on one connection:
r | tenant_id | count
-----------+-----------+-------
request 1 | 1 | 3 -- SET app.tenant_id = '1'
request 2 | 1 | 3 -- forgot to set it: sees tenant 1's invoicesIn one way this is worse than no RLS at all: before, a missing filter showed every tenant’s rows at once, which someone tends to notice; now it shows exactly one other tenant’s data, which looks plausible.
3. Nothing proves isolation. The only test is a manual check as the developer’s own login. RLS mistakes are silent, so the test must be automated and must use the production login.
What I would ask for instead:
- A new
applogin with onlySELECT,INSERT,UPDATE,DELETEgrants, no ownership, not a superuser, withoutBYPASSRLS. Migrations keep using the owner. ALTER TABLE ... FORCE ROW LEVEL SECURITYon every tenant table, so even an owner login fails closed.- Set the tenant per transaction:
SET LOCAL app.tenant_id = ...orSELECT set_config('app.tenant_id', $1, true)at the start of each transaction. When the transaction ends, the value is gone; a path that forgets to set it gets an error, not someone else’s rows. In our run, a query afterSET LOCALhad ended failed withinvalid input syntax for type integer: ""instead of returning data. - An automated test that connects as
app, sets tenant 2, and asserts that tenant 1’s rows are invisible and that inserting a row for tenant 1 is refused (with no separateWITH CHECK, theUSINGexpression also checks new rows). - An index on
tenant_idfor each table, or one leading with it: every query now filters on it.
Compare it with yours. Did your review catch the main problems?
How to review a security control
Find who it does not apply to. Every access control has exceptions: owners, superusers, BYPASSRLS, SECURITY DEFINER functions, replicas, backups and exports. Check which of them production uses.
Ask how it fails. A control that fails closed (an error, no rows) is noisy but safe. One that fails open (all rows, or the wrong rows) is silent. The session-level SET turned a forgotten filter into a wrong-tenant leak, the worst failure available.
Test as the real login. Security bugs hide in the gap between the login developers use and the one production uses. The test that matters connects with production’s role and settings.
Check yourself
The PR’s policy is correct. Why did tenant 2 still see every tenant’s invoices in production?
Show answer
Production logs in as
shop_owner, the table owner, and the tables are notFORCEd, so row-level security is bypassed entirely.Why is
SET LOCALsafer thanSETwith a connection pool?Show answer
SET LOCALends with the transaction, so the next request on the same connection starts without a tenant and fails closed.SETpersists for the session, so a request that forgets to set it silently runs as the previous request’s tenant.Would
FORCE ROW LEVEL SECURITYalone have fixed this if production logged in as a superuser?Show answer
No. Superusers and roles with
BYPASSRLSbypass row security even on forced tables. The application must use a login without those attributes.
Finished reading?
Saved in this browser only.
Sources
Where each claim on this page comes from.
- 1.PostgreSQL documentation: 5.9 Row Security PoliciesPrimary· §5.9 Row Security Policies
Superusers and roles with BYPASSRLS always bypass row security; table owners bypass it unless FORCE ROW LEVEL SECURITY is set.
- 2.PostgreSQL documentation: CREATE POLICYPrimary· Description; Parameters: USING, WITH CHECK
For ALL policies without WITH CHECK, the USING expression also decides which new rows may be written; with RLS enabled and no applicable policy, nothing is visible (default deny).
- 3.PostgreSQL documentation: SETPrimary· Parameters: SESSION, LOCAL
SET without LOCAL lasts for the rest of the session; SET LOCAL lasts only until the end of the current transaction.
- 4.PostgreSQL documentation: 9.28 System Administration FunctionsPrimary· §9.28.1 Configuration Settings Functions: set_config
set_config with is_local true applies the value only for the current transaction.
Go further
What to read next, and why it is worth the time.
- 1.PostgreSQL documentation: CREATE POLICYPrimary· Notes; Table: Policies Applied by Command Type
Which expression is checked for which command, which is where most real RLS mistakes hide.
Applies to PostgreSQL 18. Draft: not yet fact-checked end to end.