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