Viljar Tulit (viljar@cyber.ee) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
FOREIGN KEY check needs UPDATE permission
Long Description
Lets you have 2 table. First SELECT only. Second with ALL privileges. And from second, there is REFERENCE to first.
Now if you INSERT something to second, it fails with no permission error in first, SELECT only table.
FOREIGN KEY check needs UPDATE permission for table, where key lives.
Sample Code
Exampel database:
CREATE USER yks NOCREATEDB NOCREATEUSER;
CREATE USER kaks NOCREATEDB NOCREATEUSER;
CREATE DATABASE bug;
\c bug
/* create table, where kaks is RO access privileges
and yks is all privileges */
CREATE SEQUENCE xxx_seq;
GRANT SELECT ON xxx_seq TO kaks;
GRANT SELECT,UPDATE ON xxx_seq TO yks;
CREATE TABLE xxx(
nimi text,
id int4 DEFAULT nextval('xxx_seq') NOT NULL,
PRIMARY KEY(id));
GRANT SELECT ON xxx TO kaks;
GRANT ALL ON xxx TO yks;
/* create table, where yks is all privileges */
CREATE SEQUENCE yyy_seq;
GRANT SELECT,UPDATE ON yyy_seq TO kaks;
CREATE TABLE yyy(
andmed text,
tekitas int4 REFERENCES xxx(id),
id int4 DEFAULT nextval('yyy_seq') NOT NULL,
PRIMARY KEY(id));
GRANT ALL ON yyy TO kaks;
now bug:Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
psql -Upostgres bug
bug=# \z
Access permissions for database "bug"
Relation | Access permissions
----------+---------------------------
xxx | {"=","yks=arwR","kaks=r"}
xxx_seq | {"=","yks=rw","kaks=r"}
yyy | {"=","kaks=arwR"}
yyy_seq | {"=","kaks=rw"}
(4 rows)
bug=# \c - yks
You are now connected as new user yks.
bug=> INSERT INTO xxx (nimi) values ('viljar');
INSERT 31552 1
bug=> \c - kaks
You are now connected as new user kaks.
bug=> INSERT INTO yyy (andmed,tekitas) values ('buug',1);
ERROR: xxx: Permission denied.
No file was uploaded with this report