BUG #14613: Referencing foreign key needs privileges of table owner?

From: hkauchi(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14613: Referencing foreign key needs privileges of table owner?
Date: 2017-04-06 02:09:41
Message-ID: 20170406020941.25934.30410@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14613
Logged by: Hisahiro Kauchi
Email address: hkauchi(at)gmail(dot)com
PostgreSQL version: 9.5.4
Operating system: Linux
Description:

Situation:

User1 is a table owner of table1, table2.
User2 is an operator of table1, table2.
Table2 references table1;

-------------------------------
create role user1;
create role user2 login;

create table table1(id int primary key);
create table table2(id int primary key, table1_id integer references
table1(id));
alter table table1 owner to user1;
alter table table2 owner to user2;
grant select,insert,update,delete on table1 to user2;
grant select,insert,update,delete on table2 to user2;

revoke all on schema public from public; // To prevent creation of
objects.
grant usage on schema public to user2;
-------------------------------

Problem:

User2 cannot insert to table2;

-------------------------------
-bash-4.1$ psql -U user2 test
psql (9.5.4)
Type "help" for help.

test=# insert into table1 values(1);
INSERT 0 1
test=# insert into table2(id, table1_id) values(1,1);
ERROR: permission denied for schema public
LINE 1: SELECT 1 FROM ONLY "public"."table1" x WHERE "id" OPERATOR(p...
^
QUERY: SELECT 1 FROM ONLY "public"."table1" x WHERE "id"
OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
-------------------------------

When user1 has USAGE privilege on schema public, there is no problem.

-------------------------------
-bash-4.1$ psql test
psql (9.5.4)
Type "help" for help.

test=# grant usage on schema public to user1;
GRANT
test=# \q
-bash-4.1$ psql -U user2 test
psql (9.5.4)
Type "help" for help.

test=> insert into table2(id, table1_id) values(1,1);
INSERT 0 1
-------------------------------

Is this a bug?

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2017-04-06 03:00:36 Re: BUG #14613: Referencing foreign key needs privileges of table owner?
Previous Message Andrew Gierth 2017-04-05 19:49:37 Re: Core dump when attempting to use plv8