Dave E Martin ([EMAIL PROTECTED]) 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 is logged 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 select permission 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 a future version 8) (as opposed to select * from
pg_trigger, et al.; it would also be nice if pg_dump could reproduce referential
integrity constraints directly, instead of as triggers.)
Sample Code
No file was uploaded with this report
