5

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.

2
  • Can't say for sure about PostgreSQL, but in SQL Server the data statistics on a table aren't stored individualized for RLS. They're just holistic statistics about the entire table. I'd imagine PostgreSQL is similar. So despite two users being able to see two different amounts of rows from the table, the early row estimates are probably constant. (Maybe a static factor is used when RLS is enabled, to reduce the estimation, but that'll be constant for every user then.) Again in SQL Server a mis-estimation by a factor of 2 is usually ok, 10x+ is where things get impacted. Sure this is the issue?
    – J.D.
    Commented Dec 20, 2023 at 13:19
  • In isolation, this underestimate is fine. It's when it is part of a bigger query that this seems to be an issue. We have many tables with similar policies which all end up getting joined and it gets progressively worse and worse as nested loops are being chosen where hash or merge joins really should be. Performance problem aside, I'd also like to understand more about how this works too!
    – cjheppell
    Commented Dec 20, 2023 at 13:51

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.