Re: BUG #18941: PostgreSQL planner does not select a multicolumn btree_gin index under RLS

PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> When a non superuser tries to run the following query "select id from
> findings_default where tenant_id = 'a3b89764-af41-4a9c-a735-38b03d3473ac'
> and text_search @@ plainto_tsquery('analyzer');" the planner decides not to
> pick the btree_gin index with tenant_id and text_search in favor of just
> picking the tenant_id index.

I think what's happening there is that the RLS-derived filter
condition is required to be enforced before any user-provided
condition, so that the user-provided condition cannot be applied
to any rows the user isn't supposed to see. Without this,
the user-provided condition might be able to leak data about
those rows.

You would be okay, probably, if the system thought that the @@
operator is "leakproof", because then it can be applied before or
concurrently with the RLS condition, which is what's needed to allow
an indexscan using the @@ condition. But our rules for marking things
leakproof are pretty strict and TS search operators don't qualify.

If you're desperate enough, you could mark ts_match_vq() as leakproof
and hope that (a) there's not a way to abuse it or at least (b) your
users aren't smart enough to find one. I think you might also need to
use the 2-parameter form of plainto_tsquery(), to allow that call to
be reduced to a constant before these decisions are made.

Anyway, I don't see a bug here. The real story is that RLS is a
performance killer in multiple ways, so if you need speed you're
better off looking for an alternative solution to your security
requirements.

regards, tom lane