[HACKERS] RLS - permissive vs restrictive
Hi, It appears that I'm not the only person who finds it somewhat unintuitive for overlapping RLS policies to be permissive rather than restrictive (OR vs AND) (at least 3 others seem to expect AND behaviour), although I understand the reasoning behind it. And I've since discovered that the same feature in another database system uses the latter rather than the former. I posted a brain coredump of my thoughts on the matter on Depesz's blog (http://www.depesz.com/2014/10/02/waiting-for-9-5-row-level-security-policies-rls/#comment-187800) and I was wondering if there's a future in allowing both systems. The syntax is less important than the functionality, where restrictive policies would be AND'd, permissive policies would (like they currently do) be OR'd, and a combination would involve all restrictive plus at least one permissive (i.e. restr1 AND restr2 AND (perm3 OR perm4)). I'm just interested to know what others' thoughts on the matter are. Thom -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RLS - permissive vs restrictive
On Tue, Oct 7, 2014 at 6:44 AM, Thom Brown t...@linux.com wrote: It appears that I'm not the only person who finds it somewhat unintuitive for overlapping RLS policies to be permissive rather than restrictive (OR vs AND) (at least 3 others seem to expect AND behaviour), although I understand the reasoning behind it. And I've since discovered that the same feature in another database system uses the latter rather than the former. I posted a brain coredump of my thoughts on the matter on Depesz's blog (http://www.depesz.com/2014/10/02/waiting-for-9-5-row-level-security-policies-rls/#comment-187800) and I was wondering if there's a future in allowing both systems. The syntax is less important than the functionality, where restrictive policies would be AND'd, permissive policies would (like they currently do) be OR'd, and a combination would involve all restrictive plus at least one permissive (i.e. restr1 AND restr2 AND (perm3 OR perm4)). I'm just interested to know what others' thoughts on the matter are. I think that could make sense. I think the main thing to consider is the case where different policies apply to different users: what will be the combined effect for users who are subjected to any subset of those policies? If the same policies applies to everyone, then you can just do it all as a single policy and put whatever Boolean logic you like inside of it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RLS - permissive vs restrictive
* Robert Haas (robertmh...@gmail.com) wrote: On Tue, Oct 7, 2014 at 6:44 AM, Thom Brown t...@linux.com wrote: It appears that I'm not the only person who finds it somewhat unintuitive for overlapping RLS policies to be permissive rather than restrictive (OR vs AND) (at least 3 others seem to expect AND behaviour), although I understand the reasoning behind it. And I've since discovered that the same feature in another database system uses the latter rather than the former. I posted a brain coredump of my thoughts on the matter on Depesz's blog (http://www.depesz.com/2014/10/02/waiting-for-9-5-row-level-security-policies-rls/#comment-187800) and I was wondering if there's a future in allowing both systems. The syntax is less important than the functionality, where restrictive policies would be AND'd, permissive policies would (like they currently do) be OR'd, and a combination would involve all restrictive plus at least one permissive (i.e. restr1 AND restr2 AND (perm3 OR perm4)). I'm just interested to know what others' thoughts on the matter are. I think that could make sense. I think the main thing to consider is the case where different policies apply to different users: what will be the combined effect for users who are subjected to any subset of those policies? If the same policies applies to everyone, then you can just do it all as a single policy and put whatever Boolean logic you like inside of it. Agreed- this, as I recall, was the crux of the concern when we were discussing it back in March(?). One option might be to have a flag on the table which basically says and or or, but that feels pretty limiting to me. Another option would be to allow the user to define the specific and-vs-or for each policy like so: ALTER TABLE t SET ROW SECURITY POLICY p1 OR (p2 AND p3); ... or you could just only have one policy on the table and do whatever you'd like with it (which was the original idea, actually, though I've found myself very much liking the ability to have multiple policies, and to have them set for specific roles and commands, rather than having to have one very complicated policy or having to use a function..). As mentioned previously, I'm certainly not against any of these options, but we need to figure out what we actually want first. I do think 'OR' is still the right answer if we're only going to do one thing as this wouldn't make any sense to me: CREATE ROLE r1; CREATE ROLE r2; CREATE ROLE r3; GRANT r2, r3 TO r1; CREATE TABLE t (a int); ALTER TABLE t ENABLE ROW LEVEL SECURITY; CREATE POLICY p1 ON t TO r2 USING (a = 1); CREATE POLICY p2 ON t TO r3 USING (a = 2); GRANT SELECT ON t TO r1, r2, r3; INSERT INTO t VALUES (1); INSERT INTO t VALUES (2); INSERT INTO t VALUES (3); SET ROLE r1; TABLE t; a --- (0 rows) SET ROLE r2; TABLE t; a --- 1 (1 row) Given that r2 and r3 are granted to r1, using AND would mean that r1 can't actually see any rows (and this could end up happening at a later point too- perhaps r1 only had r2 granted originally and was then granted r3..). We could try to come up with convoluted rules for policies which have roles associated vs. policies which are for all users, or try to define the policy overlap handling in some other way, but I don't think we'd be doing our users a favor by doing so. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] RLS - permissive vs restrictive
On Tue, Oct 7, 2014 at 9:55 AM, Stephen Frost sfr...@snowman.net wrote: ... or you could just only have one policy on the table and do whatever you'd like with it (which was the original idea, actually, though I've found myself very much liking the ability to have multiple policies, and to have them set for specific roles and commands, rather than having to have one very complicated policy or having to use a function..). The key point from my angle is that if you grant user alice the right to see records where a = 1 and user bob the right to see records where a = 2, the multiple-policy approach allows those quals to be implemented as index-scans. If you had a single policy granting all users the right to see records where policyfunc() returns true, it would never be indexable. I think that Thom's idea of having some policies that are additional filter conditions on top of everything else is a pretty good one. It's probably possible to construct a case where you need multiple levels of AND and OR logic, which Thom's proposal does not provide for. But are there really cases like that which anyone cares about? I think we're going to be tempted to think about that question for about 60 seconds and say nope, and that's probably not enough thought. It deserves serious reflection, because I think Thom's proposal is terminal: if we do what he's proposing, it'll be hard to extend the idea any further if we later discover that it isn't general enough. That having been said, what he's proposing is simple and covers a fair amount of ground, and is thus worthy of serious consideration, at least IMHO. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RLS - permissive vs restrictive
* Robert Haas (robertmh...@gmail.com) wrote: The key point from my angle is that if you grant user alice the right to see records where a = 1 and user bob the right to see records where a = 2, the multiple-policy approach allows those quals to be implemented as index-scans. If you had a single policy granting all users the right to see records where policyfunc() returns true, it would never be indexable. Right, that is certainly an important aspect also. I think that Thom's idea of having some policies that are additional filter conditions on top of everything else is a pretty good one. It's probably possible to construct a case where you need multiple levels of AND and OR logic, which Thom's proposal does not provide for. But are there really cases like that which anyone cares about? I keep coming back to the feeling that we'd need some kind of exception capability (more than just excluding the owner), without which this feature wouldn't end up being practical. I think we're going to be tempted to think about that question for about 60 seconds and say nope, and that's probably not enough thought. It deserves serious reflection, because I think Thom's proposal is terminal: if we do what he's proposing, it'll be hard to extend the idea any further if we later discover that it isn't general enough. That having been said, what he's proposing is simple and covers a fair amount of ground, and is thus worthy of serious consideration, at least IMHO. Even given the above, I do like the idea in general and have been thinking we need to provide something along these lines. I've been trying to work out if we could provide a way to get to a generalized CNF capability for policies, but I agree that it's unclear if there's a real-world need for such. Thanks, Stephen signature.asc Description: Digital signature
