Re: RLS Design

On 29 June 2014 20:42, Stephen Frost <sfrost@snowman.net> wrote:
> 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.
>

Actually I think they were both originally Robert's ideas in one form
or another, but at this point I'm losing track a bit :-)


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

Yes I think that's right. I had hoped to avoid overlapping policies,
but maybe they're more-or-less inevitable and we should just allow
them. It seems justifiable in terms of GRANTs --- one GRANT gives you
permission to access one set of rows from a table, another GRANT gives
you permission to access another set of rows, so in the end you have
access to the union of both sets.


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

Yes I think that's right too. I had thought that stand-alone policies
would be useful for selecting which policies to apply to each role,
but maybe that's not necessary if you rely entirely on GRANTs to
decide which policies apply.


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

Right.


> This could even be:
>
> GRANT SELECT USING (sec_level=manager) ON TABLE tbl TO role;
>

Maybe. The important thing is that it's granting the role access to a
{table,command,policy} set or equivalently a {table,command,quals} set
--- i.e., the right to access a sub-set of the table's rows with a
particular command.

Let's explore this further to see where it leads. In some ways, I
think it has ended up even simpler than I thought. To setup RLS, you
would just need to do 2 things:

1). Add a bunch of RLS policies to your tables (not connected to any
particular commands, since that is done using GRANTs). This could use
Robert's earlier syntax:

ALTER TABLE t1 ADD POLICY p1 WHERE p1_quals;
ALTER TABLE t1 ADD POLICY p2 WHERE p2_quals;
...
(or some similar syntax)

where the policy names p1 and p2 need only be unique within the table.

For maintenance purposes you'd also need to be able to do

ALTER TABLE t1 DROP POLICY pol;

and maybe in the future we'd support

ALTER TABLE t1 ALTER POLICY pol TO new_quals;


2). Once each table has the required set of policies, grant each role
permissions, specifying the allowed commands and policies together:

GRANT SELECT USING p1 ON TABLE t1 TO role1;
GRANT SELECT USING p2 ON TABLE t1 TO role1;
GRANT UPDATE USING p3 ON TABLE t1 TO role1;
...
(or some similar syntax)

So in this example, if role1 SELECTed from t1, the system would
automatically apply the combined quals (p1_quals OR p2_quals), whereas
when role1 UPDATEd t1, it would apply p3_quals. So that takes care of
the different-quals-for-different-commands requirement without even
needing any special syntax for it in ALTER TABLE.

A straight "GRANT SELECT ON TABLE .. TO .." would grant access to the
whole table without any RLS quals, as it always has done, which is
good because it means nothing changes for users who aren't interested
in RLS.

Finally, pg_dump would require a GUC to ensure that RLS was not in
effect. Perhaps something like SET require_direct_table_access = true,
which would cause an error to be thrown if the user hadn't been
granted straight select permissions on the tables in question.

That all seems relatively easy to understand, whilst giving a lot of
flexibility.

An annoying complication, however, is how this interacts with column
privileges. Right now "GRANT SELECT(col1) ON t1 TO role1" gives role1
access to every row in col1, and I think that has to remain the case,
since GRANTs only ever give you more access. But that leads to a
situation where the RLS quals applied would depend on the columns
selected. That could be avoided by consistent use of

GRANT SELECT(col1,col2,...) USING p1 ON TABLE t1 TO role1;

so that the same policy applied to all accessible columns. But what if
different policies applied to different columns? Logically that would
require the sets of quals for each of the selected columns to be ANDed
together, or perhaps we would throw an error in that case. My
inclination is to allow it, because it's probably as much effort to
detect and forbid it.

Despite this complication, I still quite like this approach because it
seems to build naturally on existing technology, giving a lot of
flexibility, without requiring too much additional syntax.

Regards,
Dean