RLS without leakproof restrictions?

On Tue, Feb 21, 2023 at 5:57 PM Tom Dunstan <pgsql@tomd.cc> wrote:

Hi all

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.

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.

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.

Thanks

Tom

<<

OK, I don't have that PG >> look quite right.

Anyway, Tom if it is feasible to put each tenant into its own database on
the same server instance, that is what I recommend. Even with row level
security, a tenant ID on each row in each table and each view and each
function or procedure that deals with tenant-specific data, is a headache
that can also complicate queries and query plans.

(Am speaking for myself as a PG developer for the last 2.5 years, not for
my employer.)

Sincerely,

Martin L Buchanan
Laramie, WY, USA

Hi Martin

On Wed, 22 Feb 2023 at 13:12, Martin L. Buchanan <martinlbuchanan@gmail.com>
wrote:

Anyway, Tom if it is feasible to put each tenant into its own database on
the same server instance, that is what I recommend.

It is not, unfortunately. For brevity I skipped over some details - the
"tenants" are possibly hundreds or thousands of different parts of large
organisations and a single application user may be granted access to some
or all of them. So strategies involving separate databases or schemas are
mostly out.

Thanks

Tom

Tom Dunstan <pgsql@tomd.cc> writes:

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.

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.

If you're happy allowing the application to decide if the filters will
be enforced, maybe just create some views embodying those filters, and
query those views when you want restrictions?

Alternatively, are the concerns about changed plans unfounded?

Hard to tell without experimentation.

regards, tom lane

Hi Tom!

On Wed, 22 Feb 2023 at 14:16, Tom Lane <tgl@sss.pgh.pa.us> wrote:

If you're happy allowing the application to decide if the filters will
be enforced, maybe just create some views embodying those filters, and
query those views when you want restrictions?

Yeah, thanks very much for the suggestion. It's more maintenance than RLS
policies as we'll need to update views as tables are modified, and we'll
need to ensure that the app never selects from the underlying table, but it
still beats needing to add filter clauses across the codebase.

Thanks

Tom

Greetings,

* Tom Dunstan (pgsql@tomd.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