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

hkauchi(at)gmail(dot)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