Re: [RFC] Interface of Row Level Security

2012/5/24 Florian Pflug <fgp@phlo.org>:
> On May24, 2012, at 16:19 , Kohei KaiGai wrote:
>> So, the proposed interface might be revised as follows:
>>  ALTER TABLE <tblname> ADD SECURITY POLICY
>>      <func_name>(<args>, ...) [FOR SELECT |
>>                                                         INSERT |
>>                                                         [BEFORE|AFTER] UPDATE |
>>                                                         DELETE];
>>
>> In case of INSERT or AFTER UPDATE, I assume the check shall be applied
>> on the tail of before-row triggers.
>
> I'd go with just "SELECT, UPDATE, DELETE", and leave the INSERT and BEFORE
> UPDATE case to regular triggers, for two reasons
>
> First, it's conceptually much simpler, since the policy always just adds
> an implicit WHERE clause, period. This of course assumes that DELETE and
> (BEFORE) UPDATE simply skips rows for which the policy function returns false,
> instead of reporting 'permission denied' or something. But that's the most
> reasonable behaviour anyway, I think, because otherwise you'd make batch
> UPDATEs and DELETEs pretty much unusable, 'cause there'd always be the risk
> of tripping over some invisible row and getting and error.
>
I definitely agree with starting a new feature from simple implementation.

Although I'm inclined to the approach to replace references to tables with
security policy by sub-queries with security barrier flag, instead of adding
qualifiers of where clause to avoid the leaky-view scenario, it will make its
implementation mush simpler.

> Another issue, BTW, are FOREIGN KEY constraints. Should you be allowed to
> created references to rows which are invisible to you, or should FOREIGN KEY
> constraints be exempt from security policies? I'd say they shouldn't be, i.e.
> the policy WHERE clause should be added to constraint checking queries like
> usual. But maybe I'm missing some reason why that'd be undesirable…
>
I agree. The row level security policy should not be applied during FK checks
(or other internal stuff; to be harmless). At the previous discussion, it was
issued that iteration of FK/PK proving enables malicious one to estimate
existence of invisible tuple and its key value, although they cannot see the
actual values. It is well documented limitation, thus, user should not use row-
level security (or should not use natural key) if they cannot accept
this limitation.

Thanks,
--
KaiGai Kohei <kaigai@kaigai.gr.jp>