Re: [SQL] Foreign keys breaks tables permissions

Peter Eisentraut <peter_e@gmx.net> writes:
>> 1. Why is RI_FKey_check() using SELECT FOR UPDATE and not plain SELECT?
>> 2. What permissions should SELECT FOR UPDATE require?

> UPDATE seems reasonable. SELECT is no good because it would give read-only
> users the locking power of users with write access.

>> If the existing code is correct on both these points, then I think the
>> answer is that there is no bug: updating a table that has a foreign
>> key reference will require update rights on the master as well.

> I don't think that's acceptable.

I don't like it either, but if an FK check must use SELECT FOR UPDATE
then anyone who can trigger an FK check has the ability to create a
write-class lock on the referenced table.  Wrapping the FK check
in a SETUID trigger doesn't change that fundamental fact; it'll just
mean that the user triggering the check is now able to create a lock
that he doesn't have the privileges to create directly.

This is perhaps the least undesirable of the choices we have, but it's
still a security hole.
        regards, tom lane