GIST/GIN index not used with Row Level Security

From: Derek Hans <derek(dot)hans(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: GIST/GIN index not used with Row Level Security
Date: 2019-08-13 18:57:59
Message-ID: CAGrP7a2t+JbeuxpQY+RSvNe4fr3+==UmyimwV0GCD+wcrSSb=w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

When using row level security, GIN and GIST indexes appear to get ignored.
Is this expected behavior? Can I change the query to get PostgreSQL using
the index? For example, with RLS enabled, this query:

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 | Skype: derek.hans

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2019-08-13 19:12:46 Re: GIST/GIN index not used with Row Level Security
Previous Message Bikram MAJUMDAR 2019-08-13 18:57:32 RE: Question on pgwatch