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.
It seems clear that if a user tries to INSERT a tuple into a table that they would not be able to see if they tried to read it, the insert should fail with a permission denied error, at least by default or as an easy option. That stops users probing for keys by looking for unique constraint errors, i.e. key proving. This works for inserts directly into a table, but gets complicated with foreign keys. If the user tries to insert a row into table A and table A has a FK to table B, if the user cannot see the referenced value in B then they should not be able to insert the row into A, instead getting the same error as if the tuple in B really didn't exist. Otherwise, again, they can probe for the existence of keys by using foreign key relationships. Problem is, that won't necessarily happen, because the FK check is run with the rights of the table owner. So you may find that you can't insert a row that references a foreign row you can see; the FK constraint check will fail even though you can clearly see the row. Similarly, you _can_ insert a row that references a row you cannot see. It gets even weirder when you're a superuser because you're exempt from RLS checks when you query a table directly but when you add a FK constraint the check is run with the table owner's rights - so it might fail even with the rows obviously visible to you. Take the following fairly nonsensical session, which is based on the RLS test suite: test=# \dt rls_regress_schema.* List of relations Schema | Name | Type | Owner --------------------+----------+-------+------------------- rls_regress_schema | category | table | rls_regress_user0 rls_regress_schema | document | table | rls_regress_user0 test=# select * from rls_regress_schema.category; cid | cname -----+----------------- 11 | novel 22 | science fiction 33 | technology 44 | manga (4 rows) test=# INSERT INTO rls_regress_schema.document (did, cid, dlevel, dtitle) VALUES (9, 22, 0, 'blah'); ERROR: insert or update on table "document" violates foreign key constraint "document_cid_fkey" DETAIL: Key (cid)=(22) is not present in table "category". Um. WTF? As Kohei KaiGai pointed out when I asked him about it, this is because the foreign key check trigger runs as the table owner, in this case rls_regress_user0. rls_regress_user0 is set up so it can't see any rows in the 'category' table even though it's the owner, so no foreign key pointing to this table can ever succeed. I don't think this is a usable situation, but I don't have any easy answers. We can't run the trigger as the current_user because it might not then have the required GRANTs for table-level access, and because it could cause malicious functions to run in the security context of the invoking user. Yet running it in the context of the owning user seems to get rid of half the point of RLS by making it incredibly hard to use it with foreign keys sanely. Thoughts? -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers