Re: RLS without leakproof restrictions?

>> On Tue, Feb 21, 2023 at 5:57 PM Tom Dunstan <pgsql(at)tomd(dot)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