Re: RLS Design - Mailing list pgsql-hackers

Robert, Dean,

* Dean Rasheed (dean.a.rasheed@gmail.com) wrote:
> On 26 June 2014 18:04, Robert Haas <robertmhaas@gmail.com> wrote:
> >> ALTER TABLE t1 SET POLICY p1 ON SELECT TO t1_p1_sel_quals;
> >> GRANT SELECT ON TABLE t1 TO role1 USING p1;
> >
> > As I see it, the downside of this is that it gets a lot more complex.
> > We have to revise the ACL representation, which is already pretty darn
> > complicated, to keep track not only of the grantee, grantor, and
> > permissions, but also the policies qualifying those permissions.  The
> > changes to GRANT will need to propagate into GRANT ON ALL TABLES IN
> > SCHEMA and AFTER DEFAULT PRIVILEGES.
>
> No, it can be done without any changes to the permissions code by
> storing the ACLs on the catalog entries where the RLS quals are held,
> rather than modifying the ACL items on the table. I.e., instead of
> thinking of "USING polname" as a modifier to the grant, think of it as
> as an additional qualifier on the thing being granted.

Yeah, I agree that we could do it without changing the existing ACL
structure by using another table and having a flag in pg_class which
indicates if there are RLS policies on the table or not.

Regarding the concerns about users not using the RLS capabilities
correctly- I find that concern to be much more appropriate for the
current permissions system rather than RLS.  If a user is going to the
level of even looking at RLS then, I'd hope at least, they'll be able to
understand and make good use of RLS to implement what they need and they
would appreciate the flexibility.

To try and clarify what this distinction is-

Dean's approach with GRANT allows specifying the policy to be
used when a given role queries a given table.  Through this mechanism,
one role might have access to many different tables, possibly with a
different policy granting that access for each table.

Robert's approach defines a policy for a user and that policy is used
for all tables that user accesses.  This ties the policy very closely to
the role.

With either approach, I wonder how we are going to address the role
membership question.  Do you inherit policies through role membership?
What happens on 'set role'?  Robert points out that we should be using
"OR" for these situations of overlapping policies and I tend to agree.
Therefore, we would look at the RLS policies for a table and extract out
all of them for all of the roles which the current user is a member of,
OR them together and that would be the set of quals used.

I'm leaning towards Dean's approach.  With Robert's approach, one could
emulate Dean's approach but I suspect it would devolve quickly into one
policy per user with that policy simply being a proxy for the role
instead of being useful on its own.  With Dean's approach though, I
don't think there's a need for a policy to be a stand-alone object.  The
policy is simply a proxy for the set of quals to be added and therefore
the policy could really live as a per-table object.

> That means the syntax I proposed earlier is wrong/misleading. Instead of
>
> GRANT SELECT ON TABLE tbl TO role USING polname;
>
> it should really be
>
> GRANT SELECT USING polname ON TABLE tbl TO role;

This would work, though the 'polname' could be a per-table object, no?

This could even be:

GRANT SELECT USING (sec_level=manager) ON TABLE tbl TO role;

> > There is administrative
> > complexity as well, because if you want to policy-protect an
> > additional table, you've got to add the table to the policy and then
> > update all the grants as well.  I think what will happen in practice
> > is that people will grant to PUBLIC all rights on the policy, and then
> > do all the access control through the GRANT statements.

I agree that if you want to policy protect a table that you'll need to
set the policies on the table (that's required either way) and that,
with Dean's approach, you'd have to modify the GRANTs done to that table
as well.  I don't follow what you're suggesting with granting to PUBLIC
all rights on the policy though..?

With your approach though, if you have a policy which covers all
managers and one which covers all VPs and then you have one VP whose
access should be different, you'd have to create a new policy just for
that VP and then modify all of the tables which have manager/VP access
to also have that new VP's policy too, or something along those lines,
no?

> If you assume that most users will only have one policy through which
> they can access any given table, then there is no more administrative
> overhead than we have right now. Right now you have to grant each user
> permissions on each table you define. The only difference is that now
> you throw in a "USING polname". We could also simplify administration
> by supporting
>
> GRANT SELECT USING polname ON ALL TABLES IN SCHEMA sch TO role;
>
> The important distinction is that this is only granting permissions on
> tables that exist now, not on tables that might be created later.

Sure, that's the same as it is now..  Robert's correct, imv, that we'll
need to make GRANT .. ON ALL, and ALTER DEFAULT PRIVS work with this.

> > An interesting question we haven't much considered is: who can set up
> > policies and add then to users?  Maybe we should flip this around, and
> > instead of adding users to policies, we should exempt users from
> > policies.
> >
> > CREATE POLICY p1;
> >
> > And then, if they own p1 and t1, they can do:
> >
> > ALTER TABLE t1 SET POLICY p1 TO t1_p1_quals;
> > (or maybe we should associate it to the policy instead of the table:
> > ALTER POLICY p1 SET TABLE t1 TO t1_p1_quals)
> >
> > And then the policy applies to everyone who doesn't have the grantable
> > EXEMPT privilege on the policy.  The policy owner and superuser have
> > that privilege by default and it can be handed out to others like
> > this:
> >
> > GRANT EXEMPT ON POLICY p1 TO snowden;
> >
> > Then users who have row_level_security=on will bypass RLS if possible,
> > and otherwise it will be applied.  Users who have
> > row_level_security=off will bypass RLS if possible, and otherwise
> > error.  And users who have row_level_security=force will apply RLS
> > even if they are entitled to bypass it.
>
> That's interesting. I need to think some more about what that means.

I'm not a fan of the EXEMPT approach..
Thanks,
    Stephen