How should row-security affects ON UPDATE RESTRICT / CASCADE ?

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