Re: Foreign keys and permissions oddity

On 07/08/10 01:13, Joshua Tolley wrote:
> Is there some justification for this behavior that I should know already? It
> seemed awfully strange when some folkds here stumbled on it:
[snip]
> The key point seems to be that the owner of the referenced table has no
> permissions on the table, although the referencing user does.

Presumably the underlying trigger functions are executing as the owner
of the table. This would make sense in the (more common) case that you
want to reference a table you don't necessarily have full read access
for (e.g. member-id vs the whole row including address/phone).

You should be able to track the table's OID from pg_class through to
tgrelid on pg_trigger and then tdfoid to the relevant OIDs in pg_proc.
The functions are all named as RI_FKey_xxx.

Hmm - not sure if they execute as the table owner or the creator of the
constraint. You could justify either, but of course they're frequently
the same (as in your case).

--
Richard Huxton
Archonet Ltd