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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
