Row estimates incorrect when using RLS and current_setting in policy
5
votes
0
answers
132
views
#### Context
**Postgres 14.4.**
I have a table which has RLS enabled, and I am using values pulled from
current_setting
in order to determine which rows should be returned when querying the table:
create table rls_protected_table (
id serial not null primary key,
tenant_id int not null,
value text not null
);
Here, tenant_id
is used to ensure that tenants may only view their own data. The policy for this table is written as such:
create policy select_tenant_data on rls_protected_table for all using (
tenant_id = (select current_setting('jwt.claims.tenant_id')::int)
and
(select current_setting('jwt.claims.permissions.read.data') = 'true')
);
The tenant_id
value is pulled from the jwt claims in order to only return values relevant for the current tenant. The jwt claims _also_ contain permissions data which controls whether or not the given table is readable at all.
The sub-selects are used to make these an InitPlan
so that the query planner doesn't have to query the settings for every row.
#### The problem
When connecting as a user that must go via RLS, the row estimates are roughly _half_ what I would expect:
explain(analyze, summary, verbose)
select * from rls_protected_table;
Results in a plan:
Seq Scan on public.rls_protected_table (cost=0.03..8.08 rows=50 width=10) (actual time=0.030..0.074 rows=101 loops=1)
Output: rls_protected_table.id, rls_protected_table.tenant_id, rls_protected_table.value
Filter: ($1 AND (rls_protected_table.tenant_id = $0))
Rows Removed by Filter: 303
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.02 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1)
Output: (current_setting('jwt.claims.tenant_id'::text))::integer
InitPlan 2 (returns $1)
-> Result (cost=0.00..0.01 rows=1 width=1) (actual time=0.006..0.006 rows=1 loops=1)
Output: (current_setting('jwt.claims.permissions.read.data'::text) = 'true'::text)
Planning Time: 0.108 ms
Execution Time: 0.108 ms
We can see that the query planner estimated 50
rows, but got 101
.
On the other hand, if I run this as a user which _doesn't_ need to go via RLS but include a similar where
clause I get correct row estimates:
explain(analyze, summary, verbose)
select * from rls_protected_table
where tenant_id = (select current_setting('jwt.claims.tenant_id')::int)
and (select current_setting('jwt.claims.permissions.read.data') = 'true');
Results in a plan:
Result (cost=0.03..8.08 rows=101 width=10) (actual time=0.037..0.103 rows=101 loops=1)
Output: rls_protected_table.id, rls_protected_table.tenant_id, rls_protected_table.value
One-Time Filter: $1
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.02 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)
Output: (current_setting('jwt.claims.tenant_id'::text))::integer
InitPlan 2 (returns $1)
-> Result (cost=0.00..0.01 rows=1 width=1) (actual time=0.007..0.007 rows=1 loops=1)
Output: (current_setting('jwt.claims.permissions.read.data'::text) = 'true'::text)
-> Seq Scan on public.rls_protected_table (cost=0.03..8.08 rows=101 width=10) (actual time=0.026..0.076 rows=101 loops=1)
Output: rls_protected_table.id, rls_protected_table.tenant_id, rls_protected_table.value
Filter: (rls_protected_table.tenant_id = $0)
Rows Removed by Filter: 303
Planning Time: 0.201 ms
Execution Time: 0.143 ms
Here, the estimate is correct.
Clearly these are two different plans. But why does the RLS variant end up with invalid row estimates and how can I prevent it? This is a snippet of a query which is used in a much larger query and these kinds of underestimates become significantly amplified further up the query causing the planner to choose nested loops and executing a poorly performing query plan.
Asked by cjheppell
(213 rep)
Dec 20, 2023, 01:05 PM
Last activity: May 2, 2024, 09:44 AM
Last activity: May 2, 2024, 09:44 AM