Re: Foreign keys breaks tables permissions

Tom Lane writes:

> I looked at this some more because people were complaining that it
> was still broken in 7.0. AFAICT, it's got nothing to do with SETUID
> triggers or anything so hairy, it's just a question of what permissions
> we think ought to be required for which actions.

Since the foreign keys are implemented in semi-userspace the triggers will
either have to abide by the userspace privilege rules (not really good,
see below), circumvent the privilege system (e.g., not use SPI, but scan
the table yourself; probably no good), or be given special privileges,
i.e., setuid or similar.

In SQL land the privilege required for a foreign key *definition* is
REFERENCES, once you have it set up, no further privileges are required to
do the referencing. That makes some sense because changes to the FK table
never change the PK table, only the other way around.

> 1. Why is RI_FKey_check() using SELECT FOR UPDATE and not plain SELECT?

AFAIU this function checks upon changes to the FK table whether a PK
exists. In don't think you need to lock the PK table for that because once
you know the PK existed at some point during the insert/update you have
satisfied the requirement. If someone mangles the PK while you're still
running then any ignited delete or update on the FK table will block with
the normal lock mechanisms.

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

--
Peter Eisentraut Sernanders väg 10:115
peter_e(at)gmx(dot)net 75262 Uppsala
http://yi.org/peter-e/ Sweden