BUG #14613: Referencing foreign key needs privileges of table owner?
The following bug has been logged on the website:
Bug reference: 14613
Logged by: Hisahiro Kauchi
Email address: hkauchi@gmail.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?
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
hkauchi@gmail.com writes:
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.
You realize that that's much more draconian than needed to prevent
creation of objects? In particular, you've left user1 unable to
reference the table it owns, which hardly seems like a useful
arrangement.
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
This is not a bug. That foreign key check query is executed as the owner
of table1. If it were not, you'd probably be complaining about some
other permissions problem.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On 2017/04/06 12:00, Tom Lane wrote:
revoke all on schema public from public; // To prevent creation of
objects.You realize that that's much more draconian than needed to prevent
creation of objects? In particular, you've left user1 unable to
reference the table it owns, which hardly seems like a useful
arrangement.
Yes, I had done too much... :(
But I thought user2 had enough permissions, so I asked a question.
This is not a bug. That foreign key check query is executed as the owner
of table1. If it were not, you'd probably be complaining about some
other permissions problem.
I understood.
Thanks!
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs