>> On Tue, Feb 21, 2023 at 5:57 PM Tom Dunstan <pg...@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
