PostgreSQL 7.0RC1 access control bug with references - Mailing list pgsql-bugs

==================================================================
                         POSTGRESQL BUG REPORT TEMPLATE
==================================================================

Your name        :    Dan Franklin
Your email address    :    dfranklin@infoplease.com


System Configuration
---------------------
   Architecture (example: Intel Pentium)      : Intel Pentium III stepping 03

   Operating System (example: Linux 2.0.26 ELF)     : Linux 2.2.14-5.0

   PostgreSQL version (example: PostgreSQL-6.5.1): PostgreSQL-7.0RC1

   Compiler used (example:  gcc 2.8.0)        : egcs-2.91.66


Please enter a FULL description of your problem:
------------------------------------------------

   If a table T has a column FK that references another table K,
   and a user has full access to table T but only select access to table K,
   then inserting a row into T gets "Permission denied".

   Since inserting a row into T never requires K to be modified, only
   select access on the modified table should be required.


Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

The following test case is a shell script that creates a 'reftest'
database and a user of that database, creates a primary/foreign-key
relationship, grants limited access to the tables, and then tries to
do the insert.  Two sets of tables are created to make it easy to see
the permissions issue.

------------------------------------------------------------------------------
## This test case illustrates that if one table has a foreign key
## referencing another table, then any user wishing to insert rows into
## the foreign-key table must have read-write access on the primary-key
## table, rather than just read.

# Show version
psql --version

## Database for test case
createdb reftest

# Create two primary tables and two tables referencing them,
# with different permissions.
psql reftest <<EOF

create table color1 (
     id serial,
     value varchar(20) unique
);

insert into color1(value) values('red');
insert into color1(value) values('blue');

create table color2 (
     id serial,
     value varchar(20) unique
);

insert into color2(value) values('red');
insert into color2(value) values('blue');

create table crayon1 (
     id serial,
     name text,
     color varchar(20) references color1(value)
);

create table crayon2 (
     id serial,
     name text,
     color varchar(2) references color2(value)
);

create user webuser nocreatedb nocreateuser;

grant select,update,delete on color1 to webuser;
grant select on color2 to webuser;

grant all on crayon1,crayon2 to webuser;
-- Show permissions
\z

EOF

psql -e -U webuser reftest <<--EOF
-- The first one works
insert into crayon1(name,color) values('C1', 'red');
select * from crayon1;

-- The 2nd one gets "Permission denied"
insert into crayon2(name,color) values('C2', 'red');
select * from crayon2;
--EOF

---------------------------------END SCRIPT ---------------------------------

The output from the last part of the script (the 2nd psql invocation) is:

   insert into crayon1(name,color) values('C1', 'red');
   INSERT 24320 1
   select * from crayon1;
    id | name | color
   ----+------+-------
     1 | C1   | red
   (1 row)

   insert into crayon2(name,color) values('C2', 'red');
   ERROR:  color2: Permission denied.
   select * from crayon2;
    id | name | color
   ----+------+-------
   (0 rows)

The "Permission denied" is a bug; granting select on color2 should be
sufficient to allow inserting into crayon2.

If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

   (Nope)