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

Dave E Martin (xxiii(at)cyberdude(dot)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 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

On Wed, 30 Aug 2000 pgsql-bugs(at)postgresql(dot)org wrote:

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

Yes, this is a known problem.

> And, why is the trigger trying to select WITH UPDATE? (locking?)
Yes, if you don't grab a row lock, another transaction can come
along and delete the row you're referencing after you've checked
it, but before that transaction sees that you've added a referencing
row (which could lead to orphened children)

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

Right now, the suggested workaround is to do a trigger before updates
that prevents the unwanted writes in plpgsql. Not particularly good,
but functional until this gets fixed (and I don't know precisely when
that'll be)