Re: RLS Design - Mailing list pgsql-hackers

On 25 June 2014 01:49, Stephen Frost <sfrost@snowman.net> wrote:
> Dean, all,
>
> Changing the subject of this thread (though keeping it threaded) as
> we've really moved on to a much broader discussion.
>
> * Dean Rasheed (dean.a.rasheed@gmail.com) wrote:
>> On 24 June 2014 17:27, Stephen Frost <sfrost@snowman.net> wrote:
>> > Single policy vs Multiple, Overlapping policies vs Multiple, Non-overlapping policies
>>
>> What I was describing upthread was multiple non-overlapping policies.
>
> Ok.
>
>> I disagree that this will be more complicated to use. It's a strict
>> superset of the single policy functionality, so if you want to do it
>> all using a single policy then you can. But I think that once the ACLs
>> reach a certain level of complexity, you probably will want to break
>> it up into multiple policies, and I think doing so will make things
>> simpler, not more complicated.
>
> If we keep it explicitly to per-role only, with only one policy ever
> being applied, then perhaps it would be, but I'm not convinced..
>
>> Taking a specific, simplistic example, suppose you had 2 groups of
>> users - some are normal users who should only be able to access their
>> own records. For these users, you might have a policy like
>>
>>   WHERE person_id = current_user
>>
>> which would be highly selective, and probably use an index scan. Then
>> there might be another group of users who are managers with access to
>> the records of, say, everyone in their department. This might then be
>> a more complex qual along the lines of
>>
>>   WHERE person_id IN (SELECT ... FROM person_department
>>                        WHERE mgr_id = current_user AND ...)
>>
>> which might end up being a hash or merge join, depending on any
>> user-supplied quals.
>
> Certainly my experience with such a setup is that it includes at least 4
> levels (self, manager, director, officer).  Now, officer you could
> perhaps exclude as being simply RLS-exempt but with such a structure I
> would think we'd just make that a special kind of policy (and not chew
> up those last 4 bits).  As for this example, it's quite naturally done
> with a recursive query as it's a tree structure, but if you want to keep
> the qual simple and fast, you'd materialize the results of such a query
> and simply have:
>
> WHERE EXISTS (SELECT 1 from org_chart
>                WHERE current_user = emp_id
>                  AND person_id = org_chart.id)
>
>> You _could_ combine those into a single policy, but I think it would
>> be much better to have 2 distinct policies, since they're 2 very
>> different queries, for different use cases. Normal users would only be
>> granted permission to use the normal_user_policy. Managers might be
>> granted permission to use either the normal_user_policy or the
>> manager_policy (but not both at the same time).
>
> I can't recall a system where managers have to request access to their
> manager role.  Having another way of changing the permissions which are
> applied to a session (the existing one being 'set role') doesn't strike
> me as a great idea either.
>

Actually I think it's quite common to build applications where more
privileged users might want to initially log in with normal
privileges, and then only escalate to a higher privilege level if
needed (much like only being root on a machine when absolutely
necessary). But as you say, that can be done through 'set role' so I
don't think being able to choose between policies is as important as
being able to define different policies for different roles.


>> That's a very simplified example. In more realistic situations there
>> are likely to be many more classes of users, and trying to enforce all
>> the logic in a single WHERE clause is likely to get unmanageable, or
>> inefficient if it involves lots of logic hidden away in functions.
>
> Functions and external security systems are exactly the real-world
> use-case which users I've talked to are looking for.  All of this
> discussion is completely orthogonal to their requirements.  I understand
> that there are simpler use-cases than those and we may be able to
> provide an approach which performs better for those.
>

OK.


>> Allowing multiple, non-overlapping policies allows the problem to be
>> broken up into more manageable pieces, which also makes the planner's
>> job easier, since only a single, simpler policy is in effect in any
>> given query.
>
> Let's try to outline what this would look like then.
>
> Taking your approach, we'd have:
>
> CREATE POLICY p1;
> CREATE POLICY p2;
>
> ALTER TABLE t1 SET POLICY p1 TO t1_p1_quals;
> ALTER TABLE t1 SET POLICY p2 TO t1_p2_quals;
>
> GRANT SELECT ON TABLE t1 TO role1 USING p1;
> GRANT SELECT ON TABLE t1 TO role2 USING p2;
>
> I'm guessing we would need to further support:
>
> GRANT INSERT ON TABLE t1 TO role1 USING p2;
>
> as we've already discussed being able to support per-action (SELECT,
> INSERT, UPDATE, DELETE) policies.  I'm not quite sure how to address
> that though.
>
> Further, as you mention, users would be able to do:
>
> SET rls_policy = whatever;
>
> and things would appear fine, until they tried to access a table to
> which they didn't have that policy for, at which point they'd get an
> error.
>
> You mention:
>
> GRANT SELECT (col1, col2), UPDATE (col1) ON t1 TO bob USING policy1;
>
> but, to be clear, there would be no option for policies to be
> column-specific, right?  The policy would apply to the whole row and
> just the SELECT/UPDATE privileges would be on the specific columns (as
> exists today).
>

I think that would be OK for the first release. It could be extended
in a future release to support column-specific policy ACLs, as long as
we don't preclude that in the syntax we choose now. The syntax

GRANT <command> [,<command>] ON table TO role USING policy

works because columns can be added to it later.


> From this what I'm gathering is that we'd need catalog tables along
> these lines:
>
> rls_policy
>   oid, polname name, polowner oid, polnamespace oid, polacl aclitme[]
>   (oid, policy name, policy owner, policy namespace, ACL, eg: usage?)
>
> rls_policy_table
>   ptblpolid oid, ptblrelid oid, ptblquals text(?), ptblacl aclitem[]?
>   (policy oid, table/relation oid, quals, ACL)
>
> pg_class
>   relhasrls boolean ?
>

Seems about right.


> An extension to the existing ACLs which are for GRANT to include a
> policy OID, eg:
>
> typedef struct AclItem
> {
>         Oid             ai_grantee;
>         Oid             ai_grantor;
>         AclMode ai_privs;
>         Oid             rls_policy;
> }
>

Alternatively, use the ACLs on rls_policy_table - i.e., to SELECT from
a table using a particular policy, you would need to have the SELECT
bit assigned to you in the corresponding rls_policy_table entry's
ACLs. That seems like it would be a less invasive change, but I don't
know if there are other problems with that approach.


> and further:
>
> role1=r|p1/postgres
> role2=r|p2/postgres
>

Or just

table1: role1=rw/grantor
table1 using policy1: role2=rw/grantor

to avoid changing the privilege display pattern. That's also more in
keeping with the model of storing the per-policy ACLs in
rls_policy_table.


> or even:
>
> bob=|policy1/postgres
>
> with no table-level privileges and only column-level privileges granted
> to role3 for this table.
>

I don't get that last one. If there are no table-level privileges,
would it not just be empty?


> The plan cache would include what policy OID a given plan was run under
> (with InvalidOid indicating an "everything-allowed" policy).
>
> This doesn't address the concern raised about having different policies
> depending on the action type (SELECT, INSERT, etc) though, as mentioned
> above..  For that we may have to add "Oid rls_select_policy", etc, to
> AclItem, which would be pretty painful.  Other thoughts?
>

Huh? Isn't it just another column in rls_policy_table to specify the
action type?


> This certainly feels like quite a bit to try and bite off for 9.5 and,
> as mentioned, this would be a strict superset of the current approach,
> which could be implemented under this structure as:
>
> CREATE POLICY t1_p1_policy;
> ALTER TABLE t1 SET POLICY p1 TO t1_p1_quals;
> GRANT (user's rights) ON t1 TO user USING policy1;
>
> Tha main downside here is that we'd have to create a policy for every
> table in the system which had RLS applied, to avoid granting more than
> should be.  Perhaps the 9.4 approach could include the 'CREATE POLICY'
> and 'ALTER TABLE' bits, but not the GRANT parts, meaning that we would,
> for the 9.5 -> 9.6 upgrade, pg_dump:
>
> GRANT (user's rights) ON t1 TO user USING policy1;
>
> We would still need the GUCs for "rls_enable = on/off" and perhaps the
> role-level "bypass_rls" attribute, but those wouldn't change with this.
>
> Thoughts?
>

Well I think you'd have to flesh out the alternatives to a similar
level of detail to assess the relative effort involved, but I think
it's encouraging to see this level of design this early in the 9.5
cycle.

Regards,
Dean