Re: GIST/GIN index not used with Row Level Security

Derek Hans <derek(dot)hans(at)gmail(dot)com> writes:
>> However, wild-guess time: it might be that without access to the
>> table statistics, the "search like '%yo'" condition is estimated
>> to be too unselective to make an indexscan profitable. And putting
>> RLS in the way would disable that access if the ~~ operator is not
>> marked leakproof, which it isn't.

> I didn't realize you could set access to table statistics. How do I enable
> this access for this user?

You don't --- there's a hard-wired restriction against applying leaky
operators to the table statistics, because they might leak values that
the current user isn't supposed to be able to see. Overriding that
would pretty much destroy the value of RLS.

It's possible you could get away with marking ~~ (textlike) as leakproof.
The default marking is based on the assumption that textlike might throw
errors based on its RHS input not being a valid pattern ... but I think
the only such error is "LIKE pattern must not end with escape character",
and maybe that's not a big enough leak to concern you.

> If that's not possible, it sounds like it
> effectively blocks the use of GIN/GIST indexes when RLS is in use.

There's a whole lot of daylight between "it doesn't pick an indexscan in
this one example" and "it effectively blocks the use of GIN/GIST".

regards, tom lane