Foreign Key Validation after Reference Table Ownership Change
От | Battuello, Louis |
---|---|
Тема | Foreign Key Validation after Reference Table Ownership Change |
Дата | |
Msg-id | 20180321080006.f495bf0537e129b62c3edf58e37f64ff.c1b3738b1c.mailapi@email02.godaddy.com обсуждение исходный текст |
Ответы |
Re: Foreign Key Validation after Reference Table Ownership Change
|
Список | pgsql-general |
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?
В списке pgsql-general по дате отправления: