Re: RLS without leakproof restrictions?

Greetings,

* Tom Dunstan (pgsql(at)tomd(dot)cc) wrote:
> I'm currently researching different strategies for retrofitting some
> multi-tenant functionality into our existing Postgres-backed application.
> One of the options is using RLS policies to do row filtering. This is quite
> attractive as I dread the maintenance and auditing burden of adding
> filtering clauses to the majority of our queries. I'm somewhat concerned
> though about getting unexpected query plans based on the planner avoiding
> non-leakproof functions until row filtering has occurred - warning about
> this seems common in articles on RLS.

This is certainly something to be aware of as it helps in debugging
cases where RLS impacts performance but that doesn't make it necessarily
likely that there'll be an issue.

> Our application is the only "user" of the database, and we do not pass
> database errors through to the user interface, so for our case leakproof
> plans are overkill - we'd just like the implicit filtering clauses added
> based on some session GUCs that we set.
>
> Is there any way to get what we're looking for here? I don't see anything
> documented on CREATE POLICY, ALTER TABLE or any GUCs.

There isn't today. It's possible that this feature could be added in
the future, perhaps.

> Alternatively, are the concerns about changed plans unfounded? For example
> we don't use many expression indexes or exotic types, it's mostly btrees on
> text and ints. We do use tsearch a certain amount, but constructing
> tsvectors and tsqueries manually rather than through stemmers etc.

If you know the operators that are being used and the data types you're
using with them, then it's not too hard to check the leakproof status of
them-

select
oprname,
l.typname as left,
r.typname as right
from
pg_operator
join pg_proc on oprcode = pg_proc.oid
join pg_type l on oprleft = l.oid
join pg_type r on oprright = r.oid
where
proleakproof
and oprname = '='
and l.typname in ('text','int4','int8')
and r.typname in ('text','int4','int8');

oprname | left | right
---------+------+-------
= | int8 | int8
= | int4 | int8
= | int8 | int4
= | int4 | int4
= | text | text
(5 rows)

For the complete list:

select oprname,l.typname as left,r.typname as right from pg_operator
join pg_proc on oprcode = pg_proc.oid join pg_type l on oprleft = l.oid
join pg_type r on oprright = r.oid where proleakproof;

Thanks,

Stephen