Re: Postgres Permissions Article

Paul A Jungwirth <pj@illuminatedcomputing.com>

From: Paul Jungwirth <pj@illuminatedcomputing.com>
To: pgsql-general@postgresql.org
Date: 2017-03-30T20:19:52Z
Lists: pgsql-hackers, pgsql-general
> 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?

In any case, thanks again to you all for your help figuring this out!

Paul



Commits

  1. For foreign keys, check REFERENCES privilege only on the referenced table.