Thread

1. FOREIGN KEY check needs UPDATE permission

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2000-11-16T08:02:57Z

   Viljar Tulit (viljar@cyber.ee) reports a bug with a severity of 2
   The lower the number the more severe it is.
   
   Short Description
   FOREIGN KEY check needs UPDATE permission
   
   Long Description
   Lets you have 2 table. First SELECT only. Second with ALL privileges. And from second, there is REFERENCE to first. 
   Now if you INSERT something to second, it fails with no permission error in first, SELECT only table.
   FOREIGN KEY check needs UPDATE permission for table, where key lives.
   
   Sample Code
   Exampel database:
   
   CREATE USER yks NOCREATEDB NOCREATEUSER;
   CREATE USER kaks NOCREATEDB NOCREATEUSER;
   CREATE DATABASE bug;
   \c bug
   /* create table, where kaks is RO access privileges
      and yks is all privileges */
   CREATE SEQUENCE xxx_seq;
   GRANT SELECT ON xxx_seq TO kaks;
   GRANT SELECT,UPDATE ON xxx_seq TO yks;
   CREATE TABLE xxx(
           nimi text,
           id int4 DEFAULT nextval('xxx_seq') NOT NULL,
           PRIMARY KEY(id));
   GRANT SELECT ON xxx TO kaks;
   GRANT ALL ON xxx TO yks;
   
   /* create table, where yks is all privileges */
   CREATE SEQUENCE yyy_seq;
   GRANT SELECT,UPDATE ON yyy_seq TO kaks;
   CREATE TABLE yyy(
           andmed text,
           tekitas int4 REFERENCES xxx(id),
           id int4 DEFAULT nextval('yyy_seq') NOT NULL,
           PRIMARY KEY(id));
   GRANT ALL ON yyy TO kaks;
   
   
   
   now bug: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
   
   
   psql -Upostgres bug
   bug=# \z
   Access permissions for database "bug"
    Relation |    Access permissions     
   ----------+---------------------------
    xxx      | {"=","yks=arwR","kaks=r"}
    xxx_seq  | {"=","yks=rw","kaks=r"}
    yyy      | {"=","kaks=arwR"}
    yyy_seq  | {"=","kaks=rw"}
   (4 rows)
   
   bug=# \c - yks
   You are now connected as new user yks.
   bug=> INSERT INTO xxx (nimi) values ('viljar');
   INSERT 31552 1
   bug=> \c - kaks
   You are now connected as new user kaks.
   bug=> INSERT INTO yyy (andmed,tekitas) values ('buug',1);
   ERROR:  xxx: Permission denied.
   
   
   
   No file was uploaded with this report
   
   
   

2. Re: FOREIGN KEY check needs UPDATE permission

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-11-16T17:50:27Z

   Known issue with no real workaround apart from giving update
   permissions.  Should be fixed for 7.1 if Peter Eisentraut's patch
   is in.