PostgreSQL BugTool Submission

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'
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