Thread

1. Possible bug with permissions. (Table A has select , insert, update rights but has a reference to a second table that only has select rights)

   Wendel Leibe <wleibe@teamdev.com> — 2000-10-06T18:31:21Z

   I think I have found a bug with Postgres' permissions.  I have combed
   through the documentation, but I haven't been able to find an explanation
   for the following problem.
   
   First of all here is what I am running.
   Postgress version 7.0.2 ,
   OS  Linux Red Hat 6.2
   Kernel version 2.2.17
   
   
   I have the following SQL statements to create three tables in a database
   that I have previously created.
   
   
   INSERT INTO pg_group (groname, grosysid, grolist) values
   ('confUser',100,'{}');
   
   ALTER GROUP "confUser" Add User "conf-user";
   
   CREATE TABLE Object_Type (			# Create the Object_Type table.
   	Primary_Key serial ,
   	PRIMARY KEY (Primary_Key),
   	Name char (20) NOT NULL
   )
   ;
   
   GRANT select ON Object_Type TO GROUP "confUser";
   
   CREATE TABLE Objects (
   	Primary_Key serial,
   	PRIMARY KEY (Primary_Key),
   	Object_Type_Index int,
   	FOREIGN KEY (Object_Type_Index) REFERENCES Object_Type
   	  ON DELETE CASCADE,
   	Object_Instance int ,
   	Name char (50) NOT NULL,
   	Tag char (50),
       UNIQUE (Name,Object_Type_Index)
   )
   ;
   
   GRANT select ON Objects TO GROUP "confUser";
   
   CREATE TABLE Contact (
   	Primary_Key serial ,
   	PRIMARY KEY (Primary_Key),
   	User_Index int,
   	Greeting_Index int REFERENCES Objects,
   	First_Name char (12)  ,
   	Last_Name char (12)  ,
   	Company_Name char (30)  ,
   	Title char (30)  ,
   	Company_Type int REFERENCES Objects,
   	Assistant_Name char (30)  ,
   	On_Comments varchar (512)  ,
   	Off_Comments varchar (512)  ,
   	Not_Current CHAR DEFAULT 0 NOT NULL
   )
   ;
   
   GRANT select, insert, update ON Contact TO GROUP "confUser";
   
   
   Here is the problem.  If I try to insert a contact record as, "confUser."
   "psql" and my DBI driver for perl fails with a permission error on objects.
   If I give confUser permissions to write to the objects table the statement
   will execute.
   
   Here is a copy of the statement while logged on as conf-user and without
   write privleges on objects,"
   
   idg-wendel=> insert into contact (user_index, greeting_index, first_name,
   last_
   name, company_name, title, company_type) values (1, 3, 'Wendel', 'Leibe',
   'Team
   ', 'Software Engineer', 3);
   ERROR:  objects: Permission denied.
   idg-wendel=>
   
   
   BTW the objects table has more than 250 entries inside of it.  In other
   words, I am giving a valid primary_key for the references in the contact
   table in both cases so I don't know why it is failing.
   
   Is this a known bug or am I doing something wrong?
   
   Thanks,
   Wendel Leibe
   
   Software Engineer
   Austin, Texas
   
   
   
   

2. Re: Possible bug with permissions. (Table A has select , insert, update rights but has a reference to a second table that only has select rights)

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-10-10T02:56:12Z

   It's been discussed a fair bit in the mailing lists but I don't think it's
   made the documentation.  The triggers use SELECT FOR UPDATE to get the row
   locks but that forces the update permission need.  In 7.1 this should be
   gone (Peter E had a patch to remove the most common cases of this).
   
   Stephan Szabo
   sszabo@bigpanda.com
   
   On Fri, 6 Oct 2000, Wendel Leibe wrote:
   
   > I think I have found a bug with Postgres' permissions.  I have combed
   > through the documentation, but I haven't been able to find an explanation
   > for the following problem.
   > 
   > First of all here is what I am running.
   > Postgress version 7.0.2 ,
   > OS  Linux Red Hat 6.2
   > Kernel version 2.2.17
   > 
   
   
   

3. Re: Possible bug with permissions. (Table A has select , insert, update rights but has a reference to a second table that only has select rights)

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-10-10T03:56:37Z

   "Wendel Leibe" <wleibe@teamdev.com> writes:
   > Here is the problem.  If I try to insert a contact record as, "confUser."
   > "psql" and my DBI driver for perl fails with a permission error on objects.
   > If I give confUser permissions to write to the objects table the statement
   > will execute.
   
   Currently, REFERENCES foreign-key constraints are implemented with a
   trigger that does SELECT FOR UPDATE on the referenced table.  SELECT
   FOR UPDATE requires write privileges on the target table, so you get
   this behavior.  It's generally agreed that that's not the desired
   behavior, but I'm not sure what the state of play is on fixing it.
   You'll find plenty of info if you check the mail list archives.
   
   			regards, tom lane