Skip to content
Sandeep Kumar
Updated 5 min read

Postgres row-level security for multi-tenant apps, and the bypass you forget

Postgres row-level security for a multi-tenant SaaS: policies, SET LOCAL for the tenant, FORCE for table owners, PgBouncer pitfalls and a tenant_id index.

Sandeep Kumar, Full-stack EngineerSandeep KumarFull-stack Engineer
Postgres row-level security for multi-tenant apps, and the bypass you forget: The same count(*) as the table owner, on a sample million-row table
On this page
  1. 1. Write the policy: a WHERE clause you cannot forget
  2. 2. Stop the table owner bypassing every policy
  3. 3. Set the tenant id inside every transaction
  4. 4. Use SET LOCAL, never SET, behind a pooler
  5. 5. Index tenant_id, because the policy runs on every query
  6. 6. Test that the policy says no
  7. Conclusion

Picture Prerna, three weeks after shipping row-level security for her clinic-booking SaaS. A clinic owner in Pune emails a screenshot of the billing page, and the invoices on it belong to a clinic in Indore.

Postgres row-level security fails open in one common setup: when your app connects as the table owner, every policy is skipped. On a sample table the owner's count(*) returned all 1,000,000 rows with RLS enabled.

This post covers the policy, how the tenant id reaches it, and the three ways it quietly stops applying. (Prerna is made up; the bypass is not.) It is the practical version of multi-tenant data isolation, where I argued the tenant filter belongs in the database.

1. Write the policy: a WHERE clause you cannot forget

Turn it on per table, then say which rows each query may see and write:

sql
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
  USING      (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::bigint)
  WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::bigint);

USING filters the rows a query can read, update or delete. WITH CHECK validates rows being written.

The difference shows when you break them. On my sample table an UPDATE aimed at another tenant's rows reported UPDATE 0, silently, because those rows were invisible. An INSERT with the wrong tenant_id failed loudly: new row violates row-level security policy for table "invoices".

The PostgreSQL docs add the property I care most about. With security enabled and no policy at all, Postgres applies a default deny: no rows visible, none modifiable.

Forget the tenant and you get an empty page, not someone else's invoices.

The NULLIF is there because of something I only found by running it. current_setting('app.tenant_id', true) returns NULL in a fresh session.

Once the session has set the value even once, it returns an empty string after the transaction ends, and ''::bigint throws invalid input syntax for type bigint. The NULLIF turns that back into NULL, which matches nothing. Quietly empty is the failure you want.

2. Stop the table owner bypassing every policy

Here is Prerna's leak. Her migrations created the tables as app, and her app server also connects as app.

That makes it the table owner, and the docs are plain about owners: they "normally bypass row security as well". Superusers and roles with BYPASSRLS always bypass it.

I ran the same count(*) as the owner of a sample table of a million invoices spread over 500 tenants:

1,000,000RLS enabled, no FORCEthe owner bypasses every policy
0FORCE, no tenant setdefault deny
2,000FORCE, tenant 42one tenant's rows
The same count(*) as the table owner, on a sample million-row tableSource: Sample table, 1M rows over 500 tenants, Postgres 14 on a dev machine

The first number is the leak. Two fixes, and I would apply both:

sql
-- 1. Make the owner obey the policies too.
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

-- 2. Better: the app is not the owner. Migrations run as one role, the app as another.
CREATE ROLE app_runtime LOGIN NOSUPERUSER NOBYPASSRLS;
GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO app_runtime;

Check the role your app uses with SELECT current_user, rolbypassrls, rolsuper FROM pg_roles WHERE rolname = current_user;. If either flag is true, no policy you write will ever run for that connection.

3. Set the tenant id inside every transaction

The policy reads app.tenant_id. The app sets it at the start of every transaction, from the authenticated user, never from a request parameter:

ts
async function withTenant<T>(tenantId: number, fn: (c: PoolClient) => Promise<T>) {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    await client.query("SELECT set_config('app.tenant_id', $1, true)", [String(tenantId)]);
    const result = await fn(client);
    await client.query("COMMIT");
    return result;
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

set_config(name, value, true) is SET LOCAL in function form. Unlike SET it accepts a bind parameter, so there is no string concatenation near your security boundary.

The SET docs give the rule that matters: SET LOCAL lasts "only till the end of the current transaction, whether committed or not". Outside a transaction block it emits a warning and has no effect, which is why the BEGIN comes first.

4. Use SET LOCAL, never SET, behind a pooler

This is the pitfall that turns a correct policy into a wrong answer. A plain SET app.tenant_id = '42' lasts for the session.

On my sample table, after a session-level SET and a committed transaction, the next transaction still counted 2,000 rows for tenant 42 without setting anything.

Now put PgBouncer in transaction mode in front. A server connection is handed to a different client after every transaction.

The session setting stays on the server connection, so the next user's query arrives already wearing tenant 42. That is Prerna's screenshot again, through a different door. SET LOCAL ends with the transaction, so the pooled connection carries nothing forward.

Here's the catch: the fix Prerna needs is not a better policy. It is a different role for the app and SET LOCAL everywhere, and neither shows up in a psql test run as a superuser.

5. Index tenant_id, because the policy runs on every query

The policy is appended to every statement, so its condition needs an index like any hot WHERE clause. Same sample table, one tenant's sum(amount), timed with EXPLAIN ANALYZE:

Without the index the plan was a parallel sequential scan that removed 332,667 rows per worker to keep 667. With it, a bitmap index scan touched the 2,000 rows that belong to the tenant.

For composite indexes that lead with tenant_id, the order rules in the Postgres indexes I'd add first apply unchanged.

One more caveat from the docs: unique, primary key and foreign key checks always bypass row security.

A unique index on email alone tells a user of tenant A that the address exists in tenant B. Scope those constraints with the tenant: UNIQUE (tenant_id, email).

6. Test that the policy says no

A test that only checks the happy path will pass on Prerna's broken setup. The useful tests run as the app's real role and assert refusals:

sql
SET ROLE app_runtime;
BEGIN;
SELECT set_config('app.tenant_id', '42', true);
SELECT count(*) FROM invoices WHERE tenant_id <> 42;              -- expect 0
INSERT INTO invoices (tenant_id, amount) VALUES (7, 10);           -- expect an error
ROLLBACK;
SELECT count(*) FROM invoices;                                     -- expect 0, nothing set

I would also run a scheduled query that lists every table with a tenant_id column where relrowsecurity or relforcerowsecurity in pg_class is false. New tables are where isolation goes missing, months after everyone stopped thinking about it.

Conclusion

Use row-level security for any SaaS where a leak between customers ends the business, and treat the policy as the easy part. The work is:

  • the role your app connects as (not the owner, no BYPASSRLS)
  • SET LOCAL in one wrapper every query goes through
  • tests that run as that role and expect a refusal.

Had Prerna's app connected as app_runtime, the Pune clinic would have seen an empty billing page: an annoying bug instead of a breach. If you pool connections, read Postgres connection pooling with PgBouncer next.

Which role does your app connect to Postgres as right now?

FAQs

Does row-level security apply to the table owner in Postgres?

Not by default. The PostgreSQL docs say table owners normally bypass row security, as do superusers and roles with the BYPASSRLS attribute. ALTER TABLE ... FORCE ROW LEVEL SECURITY makes the owner subject to the policies too. Superusers and BYPASSRLS roles still bypass it, so the application should connect as a role that is neither.

Should I use SET or SET LOCAL for the tenant id with row-level security?

SET LOCAL, inside a transaction. Its effect ends at COMMIT or ROLLBACK, so a pooled connection never carries one tenant into the next request. A plain SET lasts for the session, and under PgBouncer transaction mode the next transaction on that server connection can belong to a different user. Outside a transaction block, SET LOCAL only emits a warning and does nothing.

What happens if row-level security is enabled but no policy exists?

Postgres uses a default-deny policy: no rows are visible and none can be modified, for every role the security applies to. That fail-closed behaviour is the main reason to use it for tenant isolation. A missing tenant id returns nothing instead of everything.

Does row-level security slow down queries?

The policy adds its condition to every query, so the cost is whatever that condition costs. With an index on tenant_id it is usually a cheap index lookup. Without one, every query scans the whole table and filters. On a sample table of a million rows the same tenant query took 64.8 ms without the index and 1.6 ms with it.

Found this useful?Share on XShare on LinkedIn

Related reading