Fwd: row level security (RLS)

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