Thread

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

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2000-08-30T06:15:30Z

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

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

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-08-30T14:17:40Z

   On Wed, 30 Aug 2000 pgsql-bugs@postgresql.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)