Re: RLS Design

On Tue, Jul 1, 2014 at 3:20 PM, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
> On 1 July 2014 17:42, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Tue, Jul 1, 2014 at 3:33 AM, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
>>> 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.
>>
>> Wow, that seems pretty horrible to me. That means that if I do:
>>
>> SELECT a FROM tab;
>>
>> and then:
>>
>> SELECT a, b FROM tab;
>>
>> ...the second one might return fewer rows than the first one.
>>
>> I think there's a good argument that RLS is unlike other grantable
>> privileges, and that it really ought to be defined as something which
>> is imposed rather than a kind of access grant. If RLS is merely a
>> modifier to an access grant, then every access grant has to make sure
>> to include that modifier, or you have a security hole. But if it's a
>> separate constrain on access, then you just do it once, and exempt
>> people from it only as needed. That seems less error-prone to me --
>> it's sort of a default-deny policy, which is generally viewed as good
>> for security -- and it avoids weird cases like the above, which I
>> think could easily break application logic.
>
> That seems like a pretty strong argument.
>
> If RLS quals are instead regarded as constraints on access, and
> multiple policies apply, then it seems that the quals should now be
> combined with AND rather than OR, right?

Yeah, maybe. I intuitively feel that OR would be more useful, so it
would be nice to find a design where that makes sense. But it depends
a lot, in my view, on what syntax we end up with. For example,
suppose we add just one command:

ALTER TABLE table_name FILTER [ role_name | PUBLIC ] USING qual;

If the given role inherits from multiple roles that have different
filters, I think the user will naturally expect all of the filters to
be applied. But you could do it other ways. For example:

ALTER TABLE table_name [ NO ] ROW LEVEL SECURITY;
ALTER TABLE table_name GRANT ROW ACCESS TO role_name USING qual;

If a table is set to NO ROW LEVEL SECURITY then it behaves just like
it does now: anyone who accesses it sees all the rows, restricted to
those columns for which they have permission. If the table is set to
ROW LEVEL SECURITY then the default is to show no rows. The second
command then allows access to a subset of the rows for a give role
name. In this case, it is probably logical for access to be combined
via OR.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company