RLS Design

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(dot)a(dot)rasheed(at)gmail(dot)com) wrote:
> On 24 June 2014 17:27, Stephen Frost <sfrost(at)snowman(dot)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.

> 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.

> 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).

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 ?

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;
}

and further:

role1=r|p1/postgres
role2=r|p2/postgres

or even:

bob=|policy1/postgres

with no table-level privileges and only column-level privileges granted
to role3 for this table.

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?

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?

Thanks!

Stephen