Pilotbase listing the tables with row-level security on PostgreSQL 18 and the roles that skip the policies: the table owner, postgres and a BYPASSRLS role

Every multi-tenant app has the same bug waiting for it: one query, somewhere, without WHERE tenant_id = ?. A report written in a hurry, an admin endpoint, a join that filters the first table but not the second. One customer sees another customer's invoices, and you find out from the customer.

PostgreSQL's row-level security (RLS) moves that filter into the database: you write the rule once as a policy on the table, and every query from the app's role gets it added, whether the developer remembered or not. It works well. It also has a handful of ways to quietly not apply, and those are what this article is about. We built a small multi-tenant schema on PostgreSQL 18.6 (1,000 tenants, a million tasks, one tenant with 100,000 of them) and tried each one.

The setup: a policy, three roles and a setting

Every tenant-owned table carries tenant_id. The app tells Postgres which tenant the current request is for with a custom setting (any name with a dot in it works, here app.tenant_id), and the policy compares the two:

sql
CREATE ROLE app_owner LOGIN;            -- runs migrations, owns the tables
CREATE ROLE app_user  LOGIN;            -- what the web app connects as
GRANT SELECT, INSERT, UPDATE, DELETE ON tenants, projects, tasks TO app_user;

ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON tasks
  USING (tenant_id = current_setting('app.tenant_id')::int);
-- the same for projects and every other table with a tenant_id

Then, per request:

sql
BEGIN;
SELECT set_config('app.tenant_id', '42', true);   -- true = only for this transaction
SELECT count(*) FROM tasks;                        -- 900: tenant 42's rows only
COMMIT;

A policy with only USING and no command (FOR ALL) also guards writes: Postgres uses the same expression as the WITH CHECK for new rows. With the setting at 42, inserting a row for tenant 43 fails with new row violates row-level security policy for table "tasks", and so does an UPDATE that tries to move a row to tenant 43. So far so good.

Who the policy doesn't apply to

We set app.tenant_id to 42 for four sessions and ran SELECT count(*), count(DISTINCT tenant_id) FROM tasks in each:

text
role                        rows        tenants
postgres (superuser)        1,000,000   1,000
app_owner (table owner)     1,000,000   1,000
app_reports (BYPASSRLS)     1,000,000   1,000
app_user                          900       1

Superusers and roles with BYPASSRLS skip every policy; that's what the attribute is for. The surprise for most people is the second line: the table's owner skips its own table's policies too. If your app connects as the same role that ran the migrations (very common: one DATABASE_URL for everything), RLS is enabled, the policies are there, and none of them apply to a single query the app runs. Nothing warns you.

Two fixes, and you can use both. Connect the app as a separate role that owns nothing, as above. And ALTER TABLE tasks FORCE ROW LEVEL SECURITY; makes the policies apply to the owner as well: after it, app_owner saw the same 900 rows as app_user. (Superusers and BYPASSRLS roles still skip them.)

This query lists, for every table in a schema, whether RLS is on, whether it's forced, the policy expression and the roles that skip it. Run it after every migration that adds a table:

sql
SELECT c.relname AS "table",
       c.relrowsecurity AS rls_on,
       c.relforcerowsecurity AS forced,
       string_agg(pg_get_expr(p.polqual, p.polrelid), ' | ') AS policy_using,
       concat_ws(', ',
         CASE WHEN NOT c.relforcerowsecurity THEN pg_get_userbyid(c.relowner) || ' (owner)' END,
         (SELECT string_agg(rolname, ', ') FROM pg_roles
           WHERE (rolsuper OR rolbypassrls) AND NOT starts_with(rolname, 'pg_'))) AS skips_policies
FROM pg_class c
LEFT JOIN pg_policy p ON p.polrelid = c.oid
WHERE c.relkind = 'r' AND c.relnamespace = 'public'::regnamespace
GROUP BY c.oid, c.relname, c.relrowsecurity, c.relforcerowsecurity, c.relowner
ORDER BY 1;
Pilotbase result grid: projects and tasks with rls_on true, forced false and the tenant_id policy; tenants with RLS off; the owner app_owner, postgres and app_reports skip the policies
Our test schema in Pilotbase: the policies are there, the owner still skips them, and tenants has no RLS at all.

It caught one more thing in our own schema: tenants has no RLS, so tenant 42 can SELECT name FROM tenants and read the names of all 1,000 customers. Lookup tables that hold one row per customer need a policy too (USING (id = current_setting('app.tenant_id')::int)).

Setting the tenant with a connection pool

The setting lives on the connection, and in a web app the connection belongs to the pool, not to the request. We used one connection for two requests in a row, the way a pool does. Request A, for tenant 1, used a plain SET. Request B, for tenant 2, had a bug and didn't set anything:

text
request A:  SET app.tenant_id = '1';  SELECT ...   -> 100,000 rows, tenant 1
request B:  (nothing set)             SELECT ...   -> 100,000 rows, tenant 1

Request B served tenant 1's data to tenant 2. A session-level SET stays on the connection until someone changes it, so it outlives the request. With set_config('app.tenant_id', '1', true) inside a transaction, the value ends with the transaction, and request B got an error instead: invalid input syntax for type integer: "". After a transaction-local set, the setting exists but is empty, and the cast fails. On a brand-new connection where it was never set, the error is unrecognized configuration parameter "app.tenant_id".

Both errors are good news. A forgotten tenant id should break the request loudly, not return someone else's rows or an empty page. So:

sql
-- per request, always in a transaction
BEGIN;
SELECT set_config('app.tenant_id', $1, true);
-- ... the request's queries ...
COMMIT;

The transaction-local form is also the only one that works behind PgBouncer in transaction mode, where consecutive transactions of one client can land on different server connections. We covered what else breaks there in PgBouncer transaction pooling. If you'd rather have "no tenant, no rows" than an error (for example in a background job that sometimes runs without one), write the policy as tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::int: the true returns NULL instead of an error when the setting is missing, and the comparison with NULL matches nothing.

The leaks: views, functions, foreign keys and error messages

Views run as their owner

A view checks permissions, and applies RLS, as the view's owner, not as the user querying it. We created open_tasks as app_owner (a plain SELECT * FROM tasks WHERE status = 'open') and granted it to app_user. With the tenant set to 42, app_user read 333,333 rows from 667 tenants through it. The owner skips RLS, so the view does too.

sql
ALTER VIEW open_tasks SET (security_invoker = true);   -- PostgreSQL 15+

After that the view runs with the caller's rights and returned 500 rows, all tenant 42's. On PostgreSQL 15 and later, create every view in a multi-tenant schema with WITH (security_invoker = true). On older versions, the view's owner must be a role that the policies apply to.

SECURITY DEFINER functions

Same story for functions: SECURITY DEFINER runs the body as the function's owner. Our task_count('open'), owned by app_owner, returned 333,333 when called by app_user for tenant 42; the same count(*) written in the query returned 500. Use SECURITY INVOKER (the default) unless the function really needs more rights, and if it does, filter by current_setting('app.tenant_id') inside it yourself.

Foreign keys look past the policy

Referential integrity checks always bypass row security; the Postgres docs say so, and it matters. With the tenant set to 42, app_user inserted a task for tenant 42 with project_id = 43, a project that belongs to tenant 43 and that tenant 42 can't even see (SELECT count(*) FROM projects WHERE id = 43 returns 0). The foreign key check found the project and let it through. Now tenant 42 has a task pointing into another tenant's project, and any join from tasks to projects that runs as the owner or a reporting role will mix the two.

Put the tenant into the key, so a reference can only point inside the same tenant:

sql
ALTER TABLE projects ADD UNIQUE (tenant_id, id);
ALTER TABLE tasks DROP CONSTRAINT tasks_project_id_fkey,
  ADD FOREIGN KEY (tenant_id, project_id) REFERENCES projects (tenant_id, id);

Then the same insert fails with violates foreign key constraint "tasks_tenant_id_project_id_fkey", and a task for project 42 still goes in.

Unique constraints answer questions

Unique checks also see every row. invoice_no was unique across the whole table, so when tenant 1 tried to insert INV-11, a number that belongs to another tenant, it got duplicate key value violates unique constraint "tasks_invoice_no_key". Postgres is careful here and leaves out the usual Key (invoice_no)=(...) already exists detail when RLS applies to the user, but the error alone says that some other tenant has that number. For most apps that's harmless; for email addresses or document numbers it's a small oracle anyone can query. If values only need to be unique per tenant, make the constraint UNIQUE (tenant_id, invoice_no).

What it costs

We compared each query written with an explicit WHERE tenant_id = ... (run as the BYPASSRLS role, so no policy) with the same query without the WHERE under the policy (as app_user). Best of seven runs, PostgreSQL 18.6 in Docker with default settings, an index on (tenant_id, status, created_at DESC) and a GIN index on tags. Every pair returned the same rows.

text
                                       explicit WHERE    RLS policy
tenant 42 (900 rows)
  newest 50 open tasks                       0.53 ms       0.52 ms
  count(*) of the tenant                     0.48 ms       0.48 ms
  group by status                            0.56 ms       0.56 ms
  tags @> '{"label":"l7"}' (GIN)             6.76 ms       1.18 ms
tenant 1 (100,000 rows)
  newest 50 open tasks                       0.51 ms       0.48 ms
  count(*) of the tenant                     5.06 ms       5.86 ms
  group by status                            8.21 ms       8.22 ms
  tags @> '{"label":"l7"}' (GIN)            11.67 ms      25.81 ms

For ordinary queries the policy is free. The plan shows why: current_setting() is stable within a query, so the policy becomes an index condition, Index Cond: ((tenant_id = (current_setting('app.tenant_id'))::integer) AND (status = 'open')), exactly as if you'd typed the number.

The jsonb rows are the exception, and they go both ways. Postgres won't evaluate a function from your query before the policy unless the function is marked LEAKPROOF, because a function that raises an error or logs its input could reveal rows the policy would hide. Integer and text equality are leakproof; jsonb @>, LIKE and ILIKE are not (SELECT proname, proleakproof FROM pg_proc WHERE ... shows it). So under the policy the GIN index on tags isn't used at all: Postgres reads the tenant's rows by the tenant index and filters the jsonb condition afterwards. For a small tenant that was actually faster than the plan it chose with the explicit WHERE (which combined both indexes, reading 20,000 GIN entries). For the 100,000-row tenant it was more than twice as slow.

An expression index doesn't get around it: a btree on (tenant_id, (tags->>'label')) was ignored for tags->>'label' = 'l7' under the policy, because ->> isn't leakproof either. What worked was moving the value into a real column. With label text GENERATED ALWAYS AS (tags->>'label') STORED and an index on (tenant_id, label), the query WHERE label = 'l7' is a plain text equality, both conditions became the index condition, and the 100,000-row tenant's search took 0.14 ms instead of about 30.

Whatever you change, check the plan as the app role, not as yourself. A superuser's EXPLAIN doesn't show the policy at all, so it shows a plan the app never gets.

What MySQL offers instead

MySQL 8.4 has no row-level security. The usual answers are a filter in every query, enforced by the ORM (global scopes, a query hook), a database per tenant, or a view per table that filters by the current tenant. The view needs a detour, because a view can't read a session variable directly:

sql
CREATE VIEW my_tasks AS SELECT * FROM tasks WHERE tenant_id = @tenant_id;
-- ERROR 1351 (HY000): View's SELECT contains a variable or parameter

CREATE FUNCTION current_tenant() RETURNS int DETERMINISTIC NO SQL RETURN @tenant_id;
CREATE SQL SECURITY DEFINER VIEW my_tasks AS
  SELECT * FROM tasks WHERE tenant_id = current_tenant();

SET @tenant_id = 42;
SELECT * FROM my_tasks;   -- tenant 42's rows; EXPLAIN shows ref on the tenant_id index

With @tenant_id unset (NULL), the view returns nothing. It only isolates anything if the app's MySQL user has privileges on the views and none on the base tables, and every new table needs its view. It's a convention you have to keep, where Postgres gives you a rule the database keeps.

A checklist

1. The app connects as a role that owns no tables and doesn't have BYPASSRLS; migrations run as another role. 2. ENABLE and FORCE ROW LEVEL SECURITY on every table with tenant data, including lookup tables like tenants. 3. The tenant is set with set_config(..., true) inside each request's transaction, and a missing tenant is an error. 4. Views are security_invoker; SECURITY DEFINER functions filter by tenant themselves. 5. Foreign keys and unique constraints include tenant_id. 6. Indexes start with tenant_id, and plans are checked as the app role. 7. Rerun the audit query after every migration.

Most of these checks are SQL you run once and then again after each change: the audit query above, EXPLAIN as the app role, a count per role. In Pilotbase you can save them in a tab, open one connection per role (owner, app, reporting) and run the same query in each, side by side, before a deploy rather than after a customer email.

Open one connection per role and run the same query in each: what the owner sees, what the app sees, and what the plan does under the policy. Pilotbase is free for Windows, macOS and Linux, or Docker on a server.

Get Pilotbase

Sources

PostgreSQL 18 documentation: Row Security Policies (owner, superuser and BYPASSRLS bypass; FORCE ROW LEVEL SECURITY; "referential integrity checks ... always bypass row security"), CREATE POLICY (USING used as WITH CHECK when none is given), CREATE VIEW (security_invoker, PostgreSQL 15+), CREATE FUNCTION (SECURITY DEFINER, LEAKPROOF) and set_config / current_setting (is_local, missing_ok). PgBouncer documentation: transaction pooling and session state. MySQL 8.4 Reference Manual: CREATE VIEW restrictions (error 1351, no variables in a view). All row counts, errors and timings are from our own runs.