From: | Alastair McKinley <a(dot)mckinley(at)analyticsengines(dot)com> |
---|---|
To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Index selection issues with RLS using expressions |
Date: | 2020-03-31 18:53:24 |
Message-ID: | PR1PR02MB534059127D2BA0B8D926413EE3C80@PR1PR02MB5340.eurprd02.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi all,
I am running in to an issue with RLS and index selection in my queries. I created a toy example to try to illustrate the issue below. Postgres version is PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit.
Is there some subtle reason as to why the role "new_user" cannot seem to generate a query plan that uses the gin index?
Best regards,
Alastair
-- Example below
create table test as
select array_agg(v order by v) a,
(random()*4)::bigint as n
from (
select (random()*250)::bigint as v ,
(random()*100000)::bigint as g
from generate_series(1,1000000)
) s group by g;
create index on test using gin(a);
create or replace function has_permission(n bigint) returns boolean as
$$
select n in (1,2);
$$ language sql stable leakproof;
alter table test enable row level security;
create role new_user;
grant select on test to new_user;
grant execute on function has_permission(bigint) to new_user;
create policy new_user_select on test for select to new_user using ( has_permission(test.n) );
set role new_user;
explain select count(*) from test where a && array[100::bigint];
-- Aggregate (cost=3233.94..3233.95 rows=1 width=8)
-- -> Seq Scan on test (cost=0.00..3228.93 rows=2005 width=0)
-- Filter: ((n = ANY ('{1,2}'::bigint[])) AND (a && '{100}'::bigint[]))
set role postgres;
explain select count(*) from test where a && array[100::bigint];
-- Aggregate (cost=1833.21..1833.22 rows=1 width=8)
-- -> Bitmap Heap Scan on test (cost=43.41..1823.07 rows=4053 width=0)
-- Recheck Cond: (a && '{100}'::bigint[])
-- -> Bitmap Index Scan on test_a_idx (cost=0.00..42.40 rows=4053 width=0)
-- Index Cond: (a && '{100}'::bigint[])
-- even with the has_permission() function the postgres user gets a bitmap index scan
explain select count(*) from test where a && array[100::bigint] and has_permission(test.n);
QUERY PLAN
-- -----------------------------------------------------------------------------------
-- Aggregate (cost=1837.71..1837.72 rows=1 width=8)
-- -> Bitmap Heap Scan on test (cost=42.90..1832.69 rows=2005 width=0)
-- Recheck Cond: (a && '{100}'::bigint[])
-- Filter: (n = ANY ('{1,2}'::bigint[]))
-- -> Bitmap Index Scan on test_a_idx (cost=0.00..42.40 rows=4053 width=0)
-- Index Cond: (a && '{100}'::bigint[])
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2020-03-31 19:18:22 | Re: Index selection issues with RLS using expressions |
Previous Message | Ben Chobot | 2020-03-31 17:52:27 | streaming slaves can't keep up? |