Thread: PostgreSQL BugTool Submission

James Aspnes (aspnes@cs.yale.edu) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
checking foreign keys requires write access

Long Description
In version 7.0.2, create tables A and B where B has a foreign
key reference to A.  Grant user X insert access on B but only
select access on A.  Attempts by X to insert into B will fail
with an access error on A, even though X is allowed to read A
(and thus verify that the foreign key constraint is not violated.)[B

Sample Code


No file was uploaded with this report

On Tue, 22 Aug 2000 pgsql-bugs@postgresql.org wrote:

> James Aspnes (aspnes@cs.yale.edu) reports a bug with a severity of 2
> The lower the number the more severe it is.
>
> Short Description
> checking foreign keys requires write access
>
> Long Description
> In version 7.0.2, create tables A and B where B has a foreign
> key reference to A.  Grant user X insert access on B but only
> select access on A.  Attempts by X to insert into B will fail
> with an access error on A, even though X is allowed to read A
> (and thus verify that the foreign key constraint is not violated.)[B

Yes.  This is a known issue due to getting row level locks
on rows in A with SELECT ... FOR UPDATE which requires update
permission (and presumably select as well).  For more information,
check out the -hackers archive over the last couple of months.

The current workaround is somewhat painful, and involves giving
update permission and using a trigger to actually prevent unwanted
changes.