Re: GIST/GIN index not used with Row Level Security - Mailing list pgsql-general

From Derek Hans
Subject Re: GIST/GIN index not used with Row Level Security
Date
Msg-id CAGrP7a0RKh16LRX+HVgfMVTW3_pPvivnW-G1FZbP-boxRa6d=Q@mail.gmail.com
Whole thread Raw
In response to Re: GIST/GIN index not used with Row Level Security  (Adrian Klaver <adrian.klaver@aklaver.com>)
List 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.hans@gmail.com <mailto:derek.hans@gmail.com> |
> Skype: derek.hans


--
Adrian Klaver
adrian.klaver@aklaver.com


--

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: GIST/GIN index not used with Row Level Security
Next
From: Derek Hans
Date:
Subject: Re: GIST/GIN index not used with Row Level Security