Skip site navigation (1) Skip section navigation (2)

Re: FOR SHARE permissions

From: Craig James <craig_james(at)emolecules(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: FOR SHARE permissions
Date: 2010-12-13 17:54:12
Message-ID: 4D065DC4.3080104@emolecules.com (view raw or flat)
Thread:
Lists: pgsql-admin
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

In response to

pgsql-admin by date

Next:From: Guillaume LelargeDate: 2010-12-13 18:16:20
Subject: Re: adminpack installed?
Previous:From: Fred ParkinsonDate: 2010-12-13 17:28:28
Subject: adminpack installed?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group