Re: How should row-security affects ON UPDATE RESTRICT / CASCADE ?

2013/10/29 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Craig Ringer <craig(at)2ndquadrant(dot)com> writes:
>> During my testing of Kohei KaiGai's row-security patches I've been
>> looking into how foreign keys should be and are handled. There are some
>> interesting wrinkles around FK cascades, the rights under which FK
>> checks execute, and about the consistency effects of changing or
>> applying an RLS policy.
>
> As I recall, I've been saying since day one that row-level security cannot
> sensibly coexist with foreign-key constraints, and I've been told that the
> potential users of such a feature don't care. I'm glad to see somebody
> else complaining.
>
Not only RLS, it is not avoidable someone to estimate invisible records
using FK constraints, even if either of referencing or referenced records
were protected by column-level database privilege.
I don't remember how many times we had discussed about this topic.
Its conclusions was that access control itself is not capable to prevent
information leak (1bit; whether a particular key exists, or not) using FK
constraint, however, whole of its feature makes sense as long as user's
environment where RLS+PostgreSQL is installed allows such a small
fraction of information leak.
In case when user's environment does not allow to leak any bit, it is not
a reasonable solution, even though I don't know "reasonable solution"
in this prerequisites.
All of other commercial databases are standing on same assumption.
Even though their promotion white-paper might not say, their solution
of course have same weakness that may allow to leak something.

> Here's another example wherein there basically isn't a sensible solution:
> suppose you have delete rights on table A, and there is a table B
> with a foreign-key reference to A, and RLS says that there are rows in
> B that you can't see. You try to delete some row in A that is referenced
> by an invisible-to-you row in B. There are only two possible outcomes:
> the system refuses your request, and thereby exposes to you the fact that
> a referencing row exists; or the system allows the FK constraint to be
> violated.
>
My vote is, system should keep referencial integrity as if RLS policy is
not configured. It is more fundamental stuff than RLS policy per user
basis.

> As far as the points you're making go, I think we must say that RLS checks
> are not applied during FK trigger queries, ie the FK triggers can always
> see everything even though they don't run as superuser.
>
Existing my implementation does as above. If a record is referenced
by invisible records, its deletion shall fail in spite of the information
leakage.

> Otherwise you're
> going to end up with constraint violations, and as a database weenie
> I consider that unacceptable. This will mean that a poorly-chosen FK
> arrangement will allow some leakage of row-existence info, but I don't
> believe that that can be avoided anyway, per the above example.
>
OK, Let's drop table-level and column-level privileges also. They will be
able to leak existence of invisible records, even if user don't have privilege
to reference. :-)
Any tools have its expected usage and suitable situation to be applied.
A significant thing is to use a feature with understanding its purpose
and limitations. As everybody knows, we have no silver bullets for security,
but useful tool can help us, depending on situation.

Thanks,
--
KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp>