A role per tenant vs a session variable for PostgreSQL RLS

Row-level security needs to know which tenant is asking. There are two ways to tell PostgreSQL: a session variable (set_config('app.tenant', …), with a policy on current_setting), or a database role per tenant (SET ROLE, with a policy on current_user). Most guides pick the variable and dismiss roles in one sentence: “unwieldy if you have many tenants”. None of them say what “unwieldy” costs.

We measured both on PostgreSQL 17.10, in a throwaway container with default settings: 311,205 invoices across 100 tenants with data (the largest 60,000, a middle one 1,200) and 1,000 to 10,000 tenant roles. There is also a third variant that turns out to matter most: not switching roles, but logging in as the tenant.

Role per tenant or session variable: the short answer

The query speeds are for counting the largest tenant’s invoices and fetching the middle tenant’s latest 50 — the same indexes, the same order of magnitude. Where the two approaches differ is everything around the query.

What does SET ROLE cost with thousands of tenants?

The first SET ROLE in a session is slow, and it grows with the number of roles the login role is a member of. Three fresh sessions each:

That is roughly 20 microseconds per membership, paid once per connection — PostgreSQL works out what the session is a member of and caches it. With a connection pool that keeps connections alive, you might accept that. The catch is the cache’s lifetime. We kept a session open with 5,000 memberships, warmed it up, and meanwhile created one new tenant (CREATE ROLE, GRANT … TO app) from another session. The next SET ROLE in the first session took 87 ms again. Every new tenant makes the next switch in every open connection pay in full. Changes to roles that have nothing to do with the app login count too, at a smaller price: after an unrelated CREATE ROLE or a password change on another role, the next switch took 8.3–8.4 ms instead of 0.1.

For comparison, SET app.tenant took 0.10–0.11 ms the first time in a session and 0.05–0.08 ms after that, regardless of how many tenants exist.

Creating the roles themselves is not the problem: 1,000 roles with their grants took 0.2 seconds, 9,000 more 1.9 seconds.

Do prepared statements behave differently?

Slightly. A prepared statement without parameters (select … order by issued_on desc limit 50) always gets a generic plan — pg_prepared_statements showed only generic plans in both cases — so the planner does not know which tenant it plans for. Executing it 200 times for the same tenant cost 0.18 ms per execution with the variable and 0.22 ms with a role (the role policy does a lookup in tenant_roles; the variable is a cast). Alternating between two tenants on every execution: still 0.18 ms with the variable, 0.30 ms with roles. A cached statement that depends on row security is tied to the role it was prepared for, so after a role switch PostgreSQL analyses, rewrites and plans it again; a changed variable does not trigger that. The rebuilt plan has the same shape, so a role per tenant does not fix the frozen-plan problem we measured in PgBouncer and RLS — it only pays for a new plan on every switch.

Does SET ROLE leak behind PgBouncer?

Exactly like SET. With PgBouncer 1.25.2 in transaction mode and one server connection, client A ran SET ROLE t1 and disconnected. Client B, a new client that set nothing, got current_user t1 and 60,000 invoices — the largest tenant’s. With SET LOCAL ROLE t1 inside a transaction, the next client was back to app and saw 0 rows.

PgBouncer is clear about why: in transaction mode it does not run its reset query, “because in that mode, clients must not use any session-based features”. The rule from our PgBouncer article applies unchanged: tenant context only per transaction, whether it is a variable or a role.

Does a role per tenant protect against SQL injection?

Not if the application switches roles. SET ROLE is checked against the session user, and the application’s login role has to be a member of every tenant role to switch to it. As tenant role t5, SET ROLE t6 succeeded and returned tenant 6’s 10,000 invoices; RESET ROLE went back to the login role. Anyone who can run SQL on that connection can pick a tenant — just as with set_config('app.tenant', '6', false), which did the same with the variable.

It is different when the tenant is the login. Connected as t5 itself, SET ROLE t6 gave permission denied to set role "t6", RESET ROLE stayed on t5, and SET ROLE app was refused too. That is the one real security gain of roles: an injected statement cannot leave its tenant. It has a price. PgBouncer keeps a pool per user and database — its pool size is the maximum number of server connections “per user/database pair” — so a thousand tenants means a thousand pools, and every tenant with a request in flight needs its own server connection, against a default max_connections of 100. And the application still holds every tenant’s password: this protects against injected SQL, not against a compromised application.

What happens in a SECURITY DEFINER function?

It runs as its owner, and current_user becomes that owner. We created a SECURITY DEFINER function that counts invoices, owned by the application role. Called as tenant role t5 it returned 0 — inside the function the policy looked up the owner, not the tenant. With the session variable the same function returned tenant 5’s 12,000 invoices: the variable belongs to the session, not to the role. Had the owner been a superuser, a role with BYPASSRLS, or the table owner without FORCE ROW LEVEL SECURITY, the function would have seen every tenant instead of none.

Which one should you use?

• Session variable with SET LOCAL or set_config(…, true) for almost every SaaS: cheap to switch, cheap to add tenants, one pool, works in definer functions. Its weakness — SQL on the connection can change the tenant — is shared with SET ROLE.
• SET ROLE per tenant over a shared login costs you a first-switch penalty that grows with every tenant and resets with every sign-up, and buys no protection the variable does not give. In our measurements it wins nowhere. What it does offer is privileges per tenant role and policies with TO role — useful if tenants on different plans may see different tables, which is not what we tested.
• A login per tenant is the only variant where an injected statement cannot leave its tenant. It fits a product with tens of large tenants and a pool per tenant, not one with thousands of small ones.

What we did not measure

Roles with their own table privileges (all tenant roles here inherit the same grants from one group role), how pg_stat_statements counts statements per role, pg_dump and catalogue size with 10,000 roles, managed services that limit role creation, and connection start-up time per login role.

Sources: the PostgreSQL 17 documentation on SET ROLE (permission checks against the session user) and row security policies, and the PgBouncer configuration reference (server_reset_query, default_pool_size), all read on 1 October 2026. All measurements are our own, on PostgreSQL 17.10 and PgBouncer 1.25.2, on 1 October 2026.