PostgreSQL BugTool Submission

NAGY Andras (nagya@inf.elte.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
auser having only read access to bar and read/write to foo will be able to do insertions in foo.  However, it is not
thecase. 

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'
CREATE
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;
CHANGE
grant select on bar to nagya;
CHANGE
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)
CREATE
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