Thread: Foreign keys and permissions oddity

Is there some justification for this behavior that I should know already? It
seemed awfully strange when some folkds here stumbled on it:

$ create user a;
$ create user b;
$ commit;
$ \c - a
$ create table foo (id integer primary key);
$ revoke all on foo from a;
$ grant all on foo to b;
$ commit;
$ \c - b
$ create table bar (foo_id integer references foo (id));
$ commit;
$ insert into bar values (1);
ERROR:  permission denied for relation foo
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."foo" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x"

The key point seems to be that the owner of the referenced table has no
permissions on the table, although the referencing user does.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

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

On Sat, Aug 07, 2010 at 08:34:12AM +0100, Richard Huxton wrote:
> 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).

Yeah, that appears to be what's happening, based on the code. It's certainly
confusing to look at, and I'm not sure it couldn't be described a bug. I'll
continue to ponder that.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com