This should be simple, but I must be missing something obvious.
Running a change of table ownership on PostgreSQL 9.4.16. I changed the owner
of a reference table in another, yet, after granting references to the
referencing table owner, the key validation encounters an error.
create role user_1;
create schema test_schema_1;
alter schema test_schema_1 owner to user_1;
create table test_schema_1.reference_table (reference_id integer, primary key
(reference_id));
alter table test_schema_1.reference_table owner to user_1;
insert into test_schema_1.reference_table values (1);
create schema test_schema_2;
alter schema test_schema_2 owner to user_1;
create table test_schema_2.data_table (data_id integer, reference_id integer,
primary key (data_id));
alter table test_schema_2.data_table owner to user_1;
alter table test_schema_2.data_table add constraint data_table_fk1 foreign key
(reference_id) references test_schema_1.reference_table (reference_id);
insert into test_schema_2.data_table values (1,1);
INSERT 0 1
create role user_2;
alter table test_schema_1.reference_table owner to user_2;
grant references on test_schema_1.reference_table to user_1;
insert into test_schema_2.data_table values (2,1);
ERROR: permission denied for schema test_schema_1
LINE 1: SELECT 1 FROM ONLY "test_schema_1"."reference_table" x WHERE...
^
QUERY: SELECT 1 FROM ONLY "test_schema_1"."reference_table" x WHERE
"reference_id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
grant select on test_schema_1.reference_table to user_1;
insert into test_schema_2.data_table values (2,1);
ERROR: permission denied for schema test_schema_1
LINE 1: SELECT 1 FROM ONLY "test_schema_1"."reference_table" x WHERE...
^
QUERY: SELECT 1 FROM ONLY "test_schema_1"."reference_table" x WHERE
"reference_id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
User_1 owns both schemas and has references and select privileges on the
existing reference_table, yet the key validation still encounters an error.
What am I missing? What permission is being violated at the schema level?
