2012/5/24 Robert Haas <robertmhaas@gmail.com>:
> On Thu, May 24, 2012 at 12:00 PM, Kohei KaiGai <kaigai@kaigai.gr.jp> wrote:
>>> 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.
>
> You say "I agree", but it seems to me that you and Florian are in fact
> taking opposite positions.
>
Sorry, I misread what he described.
> FWIW, I'm inclined to think that you should NOT be able to create a
> row that references an invisible row. You might end up with that
> situation anyway, because we don't know what the semantics of the
> security policy are: rows might become visible or invisible after the
> fact, and we can't police that. But I think that if you take the
> opposite position that the select queries inside fkey triggers ought
> to be exempt from security policy, then you need to build some new
> mechanism to make that happen, which seems like extra work for no
> benefit.
>
I think it is fair enough for RI_FKey_check_ins and RI_FKey_check_upd;
RLS policy inside these trigger function will exhibit to create a row that
references invisible row.
However, it should not be applied on triggers being set on PK tables,
because it allows to modify or delete primary-key being referenced by
invisible foreign-key from the viewpoint of operators.
I think, it makes sense to have exceptional cases; to make sure
foreign-key constraint at the baseline.
Thanks,
--
KaiGai Kohei <kaigai@kaigai.gr.jp>