Re: [RFC] Interface of Row Level Security - Mailing list pgsql-hackers

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

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

best regards,
Florian Pflug