Re: [RFC] Interface of Row Level Security

2012/5/25 Florian Pflug <fgp(at)phlo(dot)org>:
> On May24, 2012, at 19:25 , Robert Haas wrote:
>> 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.
>
> Right. I just realized, however, that there's another case which wasn't
> considered yet, which is how to handle the initial check during
> ALTER TABEL ADD CONSTRAINT. I'm thinking that it's fine to only consider
> visible rows in the parent table there too, but we should be checking
> all rows in the child table. The easiest way would be to restrict
> ALTER TABLE ADD CONSTRAINT to the table owner for tables with RLS
> (it seems that currently the REFERENCES privilege is sufficient), and
> make the table owner exempt from RLS on that table. The latter means you'd
> need at least two roles to use RLS, but anyone security-conscious enough
> to use RLS will probably not user the same role for DDL and DML operations
> anyway...
>
I think, the RLS policy should perform as a view with qualifiers.
It means database constraints have to be kept from the viewpoint of
any clients, so, any orphan foreign-keys or any rows violating check
constraint should not exist.

In case when a user insert a row with foreign-key, it is an exceptional
case. Even if he cannot insert a foreign-key that references invisible
primary-key, it never breaks database constraints from the viewpoint
of other folks.

My standpoint deals with database constraints as first class customer
that should be always kept in the lowest level, even though a part of
results would be filtered out due to RLS.
Isn't it a simple enough criteria?

>> 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.
>
> Hm, interesting angle. Continuing this thought, without any extra work,
> UNIQUE and EXCLUSION constraints *will* be enforced regardless of row
> visibility, because their implementation isn't SPI-based but instead
> detects conflicts while inserting tuples into the index.
>
> For being so obviously inconsistent in its treatment of UNIQUE and
> EXCLUSION constraints vs. FK constraints, this feels surprisingly
> right. So, to prevent design by accident, here's an attempt to explain
> that divergence.
>
> For UNIQUE and EXCLUSION constraints, the most conservative assumption
> possible is that all rows are visible, since that leads to the most
> rejections. With that assumption, no matter what the actual policy is,
> the data returned by a query will always satisfy the constraint. Plus,
> the constraint is still sensible because it neither rejects nor allows
> all rows. So that conservative assumption is the one we make, i.e. we
> ignore RLS visibility when checking those kinds of constraints.
>
> For FK constraints, OTOH, the most conservative assumption is that
> no rows are visible. But that is meaningless, since it will simply reject
> all possible rows. Having thus no chance of enforcing the constraint
> ourselves under all possible policies, the best we can do is to at least
> make it possible for the constraint to work correctly for as many policies
> as possible. Now, if we go with KaiGai's suggestion of skipping RLS
> while checking FK constraints, the only policy that the constraint will
> work correctly for is one which doesn't actually hide any parent rows.
> Whereas if we apply RLS checks while checking FK constraints, all policies
> which behave consistently for parent and child rows (i.e. don't hide the
> former but show the latter) will work correctly. We thus go with the
> second option, since the class of working policies is larger.
>
--
KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp>