Hello, I was told, that pgsql-hackers is not the right list for the following questions. So I'm reposting to general. Does anybody have an opinion regarding the following questions? ------------------------------- Hello, Forgive me for probably naive questions, being so talkative like the following. But the less one knows the more one must explain. And I don't know much regarding RLS. 1. Some time ago I've implemented in my schema a poore mans' RLS using the rule system. 2. like half a year ago I've discovered postgreSQL native implementation with policies, so I've decided to give it a try. 3. to my ultimate surprise, this turned out to be like 10 times slower. So I abondened the project. 4. but it bites me, one question in particular .... which requires the lengthy explanations: 5. My experiments with RLS was like following: - I've implemented a STABLE function, that returns INTEGER 1/0 - I've linked that function as POLICY to my tables - I've GRANTED PUBLIC access to those tables ---> and all works as predicted.... only slow (10x slower!). As I understand it, RLS took time to get implemented in postgreSQL for may reasons, one of which was the requirement to prevent "not belonging" rows from leaking into the query results of library buffers. Eventually, this was somehow achieved. FMHE (for my eyes) the most striking change the policy (as of step 5) introduces is a change from "access denied" error, which GRANT would raise when it declines access, to a "silent omission", which POLICY does ... AT THE SAME SITUATION. This lead me to the following conclusions: 1. in the pass (like I was implementing poor mans RLS with rules), I found it very useful for some GRANTs to silently omit access to object instead of raising an error. But this is impossible, isn't it? 2. in particular, I thought I could partition a table (using inheritance) and do RLS on GRANT/REVOKE into individual partitions. It would certainly hard limit any rows leaking into library buffers, particularly if partitions are on separate tablespaces. But unfortunately GRANT/REVOKE did raises an error, (and doesn't simply silently ignore those not granted). 3. So, what if one could change the way GRANT/REVOKE behave when denying access? 4. one feature necesary for such scenario to work, is the ability to select one particular (single) ROLE, from all the ROLEs a particular session__user has, that would "solely" be used for RLS checking of such "silent GRANT/REVOKE" validates. (a multitenet database). I mean here something along the lines of: "SET ROLE XXXX [FOR RLS]". 5. the above should come in pair with "CHECK (RLS = XXXX)" at partition level. This way, when postgresql-session does NOT HAVE the "role for rls" set, all GRANT/REVOKE would work as usual, i.e.: ignore that CHECK and normally raise "access denied". IMHO, such implementation would not suffer performance hit, that current implementation of POLICIES do. So, I have two questions here: 1. does the above scenario look like safe enough regarding unauthorised rows leaking (and as substitute for POLICIES)? 2. would it be feasible to add such variant of RLS, should one attempt to implement it? (i.e. would the community accept it?). Those questions come from my bad experience with POLICY performance. Unfortunatly I did that test like half a year ago, so I don't have results at hand to quote them, but should anybody be interested, I may try to do it again in a couple of days. with best regards, -R
