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[])
