Re: unclear about row-level security USING vs. CHECK

On Wed, Sep 23, 2015 at 12:01 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
>> On Wed, Sep 23, 2015 at 11:24 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>> > I'm working on a documentation patch with Adam to improve the docs
>> > around this (and other parts as well). I agree it doesn't come off as
>> > naturally intuitive to everyone (it did to me, but I'm clearly biased
>> > as, I think anyway, it was my idea) and so I'm not sure that's enough.
>> >
>> > Is there strong feeling that USING and WITH CHECK should both always be
>> > required when specifying ALL and UPDATE policies? It's not a difficult
>> > change to make if people want it.
>>
>> My expectation would have been:
>>
>> If you specify USING, you can see only those rows, but you can give
>> rows away freely. If you don't want to allow giving rows away under
>> any circumstances, then specify the same expression for USING and WITH
>> CHECK.
>
> Having an implicit 'true' for WITH CHECK would be very much against what
> I would ever expect. If anything, I'd think we would have an implicit
> 'false' there or simply not allow it to ever be unspecified.

Huh? If you had an implicit false, wouldn't that prevent updating or
deleting any rows at all?

>> I don't really get that. If you could make skipping a row trigger an
>> error, then that would create a bunch of covert channel attacks.
>
> Apparently I didn't explain it correctly. Skipping a row doesn't
> trigger an error. An example would perhaps help here to clarify:
>
> CREATE POLICY p1 ON t1 FOR DELETE
> USING (true)
> WITH CHECK (inserted_by = current_user);
>
> What would happen above is that, in a DELETE case, you're allowed to
> *try* and delete any record in the table, but if you try to delete a
> record which isn't yours, we throw an error. Currently the only option,
> if you want to prevent users from deleteing records which are not
> theirs, is to have:
>
> CREATE POLICY p1 ON t1 FOR DELETE
> USING (inserted_by = current_user)
>
> Which certainly has the effect that you can only delete records you own,
> but I can see use-cases where you'd like to know that someone tried to
> delete a record which isn't their own and that isn't something you can
> get directly today.

Well, you can use a trigger, I think. But the point is that right
now, if you try to delete a record that you don't own, it just says
DELETE 0. Maybe there was a record there that you can't see, and
maybe there wasn't.

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