Re: How should row-security affects ON UPDATE RESTRICT / CASCADE ? - Mailing list pgsql-hackers

Tom Lane-2 wrote
> Craig Ringer <

> craig@

> > 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.
> 
> 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.  

Is there some way to enforce that the PK and FK hosting tables have
compatible RLS definitions?  The examples that come to mind are:

1) both tables have RLS filters on at least one of the FK relationship
columns so in a multi-tenant situation a given user is likely (hard to
enforce perfectly) to be restricted to at least checking only the subset of
rows in the PK belong to their tenant.

2) the PK table has no filter AND the FK table does not have an RLS filter
on any of the columns being used in the FK.  This covers shared lookup
tables.

I see no serious problem with DELETE FK-triggers but the ability to PK probe
by inserting into a FK table does seem to need limitation.  Of course the
normal direct insert RLS checks will help (and maybe totally) to cover #1
above.

The other question is whether such a hidden relationship constitutes a
mis-configuration of RLS.  This goes back to compatibility - is there some
algorithm that can be applied to FK constraints and the associated tables
that can measure compatibility and generate warnings when a constraint or
RLS definition is added or changed on those tables?  An error is probably to
severe; especially at first.

Lacking a use-case for when two incompatible tables need to have a FK-PK
relationship I'm more inclined to force the application of RLS across the
relationship constraint and consider these trigger errors to be symptoms of
a mis-configuration of the RLS policy that need to be fixed by the DBA.  In
the presence of a mis-configured policy the ability to provide security
guarantees is shot and the examples so far all prove that.  Table "B" should
have the PK record visible for corresponding visible FK records on table "A"
otherwise there would have been no way to insert the table "A" initially
which means there was a time when an (invalid) constraint was added that
broke the relationship and at that point an error should have been raised.

Hopefully this all sparks some thoughts from others much more familiar with
RLS than I.

David J.










--
View this message in context:
http://postgresql.1045698.n5.nabble.com/How-should-row-security-affects-ON-UPDATE-RESTRICT-CASCADE-tp5776229p5776273.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.