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

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

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
>

"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