PostgreSQL 7.0RC1 access control bug with references

From: Dan Franklin <dfranklin(at)stove(dot)infoplease(dot)com> (by way of Dan Franklin <dfranklin(at)infoplease(dot)com>)
To: pgsql-bugs(at)postgresql(dot)org
Cc: dfranklin(at)infoplease(dot)com, tech(at)infoplease(dot)com
Subject: PostgreSQL 7.0RC1 access control bug with references
Date: 2000-04-27 17:26:39
Message-ID: 4.3.1.2.20000427132451.00af8290@mail.infoplease.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Your name : Dan Franklin
Your email address : dfranklin(at)infoplease(dot)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)

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2000-04-27 19:33:32 Re: Bug report
Previous Message Martin Renters 2000-04-27 16:04:11 bug report