referential integrity requires write permission to a table which only needs to be read

Dave E Martin (xxiii@cyberdude.com) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
referential integrity requires write permission to a table which only needs to be read

Long Description
I have two tables:
create table NAS_MAKE
(
    id int8 not null primary key,
...
);

create table NAS
(
    id int8 not null primary key,
    nas_make_id int8 not null
        references nas_make (id) match full deferrable initially deferred,
...
);

with these permissions:

grant select,insert,update on nas to group radius_writer;
grant select on nas_make to group radius_writer;

An attempt by a user in group radius_writer to insert a new row into NAS gets the following:

ERROR:  nas_make: Permission denied.

the postgres debug output shows the following:

 StartTransactionCommand
000829.23:35:14.455  [2542] query: INSERT INTO NAS (...) values (...)
000829.23:35:14.455  [2542] ProcessQuery
000829.23:35:14.458  [2542] CommitTransactionCommand
000829.23:35:14.459  [2542] ERROR:  nas_make: Permission denied.
000829.23:35:14.459  [2542] AbortCurrentTransaction

Wrapping it in a transaction with an update shows:

000829.23:17:55.068  [2542] StartTransactionCommand
000829.23:17:55.068  [2542] query: UPDATE EPG_UNIQUE_IDS SET table_name='NAS',next_id='35' WHERE
upper(table_name)=upper('NAS');
000829.23:17:55.070  [2542] ProcessQuery
000829.23:17:55.075  [2542] CommitTransactionCommand
000829.23:18:08.413  [2542] StartTransactionCommand
000829.23:18:08.413  [2542] query: INSERT INTO NAS (...) values (...)
000829.23:18:08.414  [2542] ProcessQuery
000829.23:18:08.417  [2542] CommitTransactionCommand
000829.23:18:46.444  [2542] StartTransactionCommand
000829.23:18:46.444  [2542] query: commit;
000829.23:18:46.444  [2542] ProcessUtility: commit;
000829.23:18:46.444  [2542] CommitTransactionCommand
000829.23:18:46.446  [2542] query: SELECT oid FROM "nas_make" WHERE "id" = $1 FOR UPDATE OF "nas_make"
000829.23:18:46.450  [2542] ERROR:  nas_make: Permission denied.
000829.23:18:46.450  [2542] AbortCurrentTransaction

Two questions:
why is the trigger (i'm presuming its the trigger) query only logged in the more complicated case (although its error
islogged in both cases) 

And, why is the trigger trying to select WITH UPDATE? (locking?)

In any case, this is requiring us to grant update permission to this user group, and we really only want them to have
selectpermission to the table in question. 

(p.s. we actually consider this somewhere between major annoyance and minor annoyance)

(p.p.s. Have the startup messages in the debug output include the postgres version.  We are running 7.0.2)

(p.p.p.s. a psql statement of some sort to show triggers in a human readable/user friendly form would be appreciated in
afuture version 8) (as opposed to select * from pg_trigger, et al.; it would also be nice if pg_dump could reproduce
referentialintegrity constraints directly, instead of as triggers.) 

Sample Code


No file was uploaded with this report