Thread

1. Referencial integerity problem

   Mike Howard <mike@clove.com> — 2001-02-08T15:59:38Z

   Briefly, I create two tables, one having a column which references the
   other and which implements cascade deletes and updates.  I create a user
   who has modify access on one table, but only select on the referenced
   table.  This user is not allowed to insert a record into the referencing
   table - the error message refers to the referenced table.
   
   I don't think referential integrity should work this way.  Any thoughts?
   
   Details:
   
   create table foo (
     foo char(10)
   );
   revoke all on foo from public on foo;
   
   create table bar (
     foo char(10) references foo (foo) on delete cascade on update cascade,
     parm int
   );
   revoke all on bar from public on bar;
   
   create user lim ;
   
   grant select on foo to lim;
   
   grant insert on bar to lim;
   grant update on bar to lim;
   grant delete on bar to lim;
   grant select on bar to lim;
   
    bash$ psql -U lim test
   Password: 
   Welcome to psql, the PostgreSQL interactive terminal.
   
   Type:  \copyright for distribution terms
          \h for help with SQL commands
          \? for help on internal slash commands
          \g or terminate with semicolon to execute query
          \q to quit
   
   test=> select * from foo ;
       foo     
   ------------
    foo       
    bar       
   (2 rows)
   
   test=> insert into bar values ('foo', 1);
   ERROR:  foo: Permission denied.
   test=> 
   
   -- 
   Mike Howard <mike@clove.com>
   
   

2. Re: Referencial integerity problem

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2001-02-12T19:38:27Z

   Under 7.0 you needed update permission on the referenced table to
   grab the necessary locks.  Under 7.1 you won't need this anymore.
   
   On Thu, 8 Feb 2001, Mike Howard wrote:
   
   > Briefly, I create two tables, one having a column which references the
   > other and which implements cascade deletes and updates.  I create a user
   > who has modify access on one table, but only select on the referenced
   > table.  This user is not allowed to insert a record into the referencing
   > table - the error message refers to the referenced table.