>> 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.)
