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)
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 |