From: | Derek Hans <derek(dot)hans(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: GIST/GIN index not used with Row Level Security |
Date: | 2019-08-13 19:26:01 |
Message-ID: | CAGrP7a0RKh16LRX+HVgfMVTW3_pPvivnW-G1FZbP-boxRa6d=Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>
>
> What are the RLS policies on the table?
>
> From select * from pg_policies:
"((tenant_name)::name = CURRENT_USER)"
> What is the definition of the GIN index?
>
> CREATE INDEX search__gist
ON public.search USING gist
(search COLLATE pg_catalog."default" gist_trgm_ops)
TABLESPACE pg_default;
> Best guess is the RLS is preventing access to the field needed by the
> index.
>
> I didn't realize RLS can limit access to a specific field/index - my
understanding was that it only affects what rows get returned/can be
update/inserted.
>
> >
> > select * from search where search like '%yo'
> >
> > Creates this query plan:
> > "Seq Scan on search (cost=0.00..245.46 rows=1 width=163)"
> > " Filter: (((tenant_name)::name = CURRENT_USER) AND (search ~~
> > '%yo'::text))"
> >
> > Running this same query with the owner of the table, thereby disabling
> > RLS, the index gets used as expected:
> > "Bitmap Heap Scan on search (cost=4.49..96.33 rows=44 width=163)"
> > " Recheck Cond: (search ~~ '%yo'::text)"
> > " -> Bitmap Index Scan on search__gist (cost=0.00..4.48 rows=44
> width=0)"
> > " Index Cond: (search ~~ '%yo'::text)"
> >
> > I see the same behavior with more complex queries, switching to GIN
> > index, more complex RLS rules, using word_similarity instead of like,
> > using full text search and larger data sets (e.g. 100k rows). This is on
> > PostgreSQL v11.1 on Windows 10.
> >
> > --
> > *Derek*
> > +1 (415) 754-0519 |derek(dot)hans(at)gmail(dot)com <mailto:derek(dot)hans(at)gmail(dot)com> |
> > Skype: derek.hans
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
--
*Derek*
+1 (415) 754-0519 | derek(dot)hans(at)gmail(dot)com | Skype: derek.hans
From | Date | Subject | |
---|---|---|---|
Next Message | Derek Hans | 2019-08-13 19:31:25 | Re: GIST/GIN index not used with Row Level Security |
Previous Message | Adrian Klaver | 2019-08-13 19:12:57 | Re: GIST/GIN index not used with Row Level Security |