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

PostgreSQL BugTool Submission

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: PostgreSQL BugTool Submission
Date: 2000-08-23 18:54:42
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
NAGY Andras (nagya(at)inf(dot)elte(dot)hu) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
Foreign keys referencing read-only tables fail

Long Description
Consider two tables, foo(barid) and bar(id, str), where foo has a foreign key referencing bar(id).  It is expected that a user having only read access to bar and read/write to foo will be able to do insertions in foo.  However, it is not the case.

Postgresql 7.0.2 (from debian woody) on debian gnu/linux 2.2 (potato).

Sample Code
create table bar(id int primary key, str text);
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'bar_pkey' for table 'bar'
insert into bar values(0, 'zero');
INSERT 139693 1
insert into bar values(1, 'one');
INSERT 139694 1
insert into bar values(2, 'two');
INSERT 139695 1
revoke all on bar from public;
grant select on bar to nagya;
select * from bar;
 id | str  
  0 | zero
  1 | one
  2 | two
(3 rows)

create table foo (barid int references bar(id));
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
insert into foo values(0);
ERROR:  bar: Permission denied.
insert into foo values(5);
ERROR:  bar: Permission denied.

postgres log for the insert command:
000823.20:52:18.170 [22793] StartTransactionCommand
000823.20:52:18.170 [22793] query: insert into foo values(0);
000823.20:52:18.172 [22793] ProcessQuery
000823.20:52:18.177 [22793] query: SELECT oid FROM "bar" WHERE "id" = $1 FOR UPDATE OF "bar"
000823.20:52:18.186 [22793] ERROR:  bar: Permission denied.
000823.20:52:18.187 [22793] AbortCurrentTransaction

No file was uploaded with this report


pgsql-bugs by date

Next:From: pgsql-bugsDate: 2000-08-23 21:53:13
Subject: PostgreSQL BugTool Submission
Previous:From: pgsql-bugsDate: 2000-08-23 16:59:55
Subject: PostgreSQL BugTool Submission

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