Paul Jungwirth <pj@illuminatedcomputing.com> writes:
>> Also I don't understand why you wrote “You need the permission on both
>> tables”: Only the owner of a table can add constraints to it
> Ah, this piece was really helpful for me in making it click. Thanks so
> much! I added a couple new paragraphs to my post with a link back to
> this thread. I feel like it all makes sense now! :-)
> FYI "You need this permission on both tables" is what the docs say
> (https://www.postgresql.org/docs/9.6/static/sql-grant.html):
>>> To create a foreign key constraint, it is necessary to have this
>>> privilege on both the referencing and referenced columns.
> Maybe it would be worth clarifying there that you need to *own* the
> referencing table, and you need REFERENCES on the referenced table?
Hmm ... interesting. A bit of excavating in tablecmds.c shows that
in order to do ADD FOREIGN KEY, you need to be owner of the table
the constraint is being attached to (checked by ATSimplePermissions,
which is used for AT_AddConstraint by ATPrepCmd), *and* you need
REFERENCES on both tables, or at least on the columns involved in
the proposed FK constraint (checked by checkFkeyPermissions, which
is invoked against each of the tables by ATAddForeignKeyConstraint).
So yeah, this seems a little bit redundant. In principle, a table owner
could revoke her own REFERENCES permissions on the table and thereby
disable creation of FKs leading out of it, but it'd be pretty unusual
to want to do so.
Moreover, this definition seems neither intuitive (REFERENCES doesn't
seem like it should be symmetric) nor compliant with the SQL standard.
In SQL:2011 section 11.8 <referential constraint definition> I read
Access Rules
1) The applicable privileges for the owner of T shall include REFERENCES
for each referenced column.
(T is the referenced table.) I see nothing suggesting that the command
requires REFERENCES privilege on the referencing table. Now this is a
little garbled, because surely they meant the owner of the referencing
table (who is issuing the command) not the owner of the referenced table,
but I think the intent is clear enough.
In short, it seems like this statement in the docs is correctly describing
our code's behavior, but said behavior is wrong and should be changed.
I'd propose fixing it like that in HEAD; I'm not sure if the back branches
should also be changed.
regards, tom lane