From: Craig James <craig_james@emolecules.com>
To: pgsql-admin@postgresql.org
Subject: Re: FOR SHARE permissions
Date: Mon, 13 Dec 2010 09:54:12 -0800
Message-ID: <4D065DC4.3080104@emolecules.com> (raw)
In-Reply-To: <AANLkTinbdL8RYpv=o+ThMHo23KL-vhDuAbEWx-3oncXH@mail.gmail.com>
References: <AANLkTinbdL8RYpv=o+ThMHo23KL-vhDuAbEWx-3oncXH@mail.gmail.com>
On 12/11/10 7:47 PM, David Underhill wrote:
> I have two tables. One has a foreign key referencing a serial
> field in the other table. I've given INSERT privilege to a role
> other than the owner, but I still can't insert into the table
> containing the foreign key unless I grant the /owner/ of the table
> UPDATE privilege on the table containing the referenced field.
>
> I don't quite understand why the /owner/ needs to have UPDATE
> permission in order for another distinct role (with INSERT
> privilege) to be able to insert a row in this case.
I don't know about the specifics of the Postgres implementation, but this makes sense from a security point of view.
When you insert into second table, you're effectively "locking" the referenced row in the referenced (first) table, making it so that the owner of that table can no long delete that row. You ARE updating that table. You're not inserting or deleting data from it, but you are changing what the owner can do to it. In other words, you're updating the owner's ability to delete from and update the referenced table.
Craig
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: craig_james@emolecules.com
Subject: Re: FOR SHARE permissions
In-Reply-To: <4D065DC4.3080104@emolecules.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox