Sample Header Ad - 728x90

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