Paul Jungwirth <pj@xxxxxxxxxxxxxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
