row level security (RLS)
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
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
On Mon, 2021-03-15 at 16:28 +0100, Rafal Pietrak wrote:
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!).[lots of questions about how to solve this is some other way]
Those questions come from my bad experience with POLICY performance.
You should figure out why RLS was so slow.
The key to this is "EXPLAIN (ANALYZE, BUFFERS)" for the query -
that will tell you what is slow and why, so that you can tackle the
problem.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
W dniu 15.03.2021 o 18:01, Laurenz Albe pisze:
On Mon, 2021-03-15 at 16:28 +0100, Rafal Pietrak wrote:
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!).[lots of questions about how to solve this is some other way]
Those questions come from my bad experience with POLICY performance.
You should figure out why RLS was so slow.
Yes I should... although I didn't. Somewhat because I thought it was
obvious (an additional function call on every row). Still, as I've
mentioned in my initial post, I'm going to revisit the case in the
couple of days and gather more evidence.
Having said that, I'm really interested in any comments on the way I've
"imagined" addressing RLS years ago (and described it in the post), when
I've looked for a solution and settled for the rule system. The question
about partition/check/role approach irrespective of where they come from.
Pls address the following reasoning:
1. POLICY calls a function on every row to check it's visibility to the
client (for 1mln rows, 1mln checks).
2. "alternative" does just one check on all the rows contained in a
particular partition (for 100 tenets 100 checks)
No matter how hard one optimises the POLICY function, it will always loose.
Then again, I'll be back with some "ANALYSE" in a couple of days.
-R