Обсуждение: Foreign Key Validation after Reference Table Ownership Change
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?
What permission is being violated at the schema level?
--------- Original Message ---------Subject: Re: Foreign Key Validation after Reference Table Ownership Change
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: 3/21/18 11:23 am
To: "Battuello, Louis" <louis.battuello@etasseo.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
On Wednesday, March 21, 2018, Battuello, Louis <louis.battuello@etasseo.com> wrote:What permission is being violated at the schema level?
USAGEDavid J.
So, user_2 needs usage on the schema containing its newly owned reference table even though user_1 is performing the insert on a table in the other schema? Interesting. I though the validation was only dependent on user_1's ACL.
Agreed. It would certainly make sense that user_2 have usage on the schema in order to operate against the table owned byuser_2. I just found it confusing that the discrepancy would cause an issue for user_1, which had all necessary privilegeson the schema and references on the reference table. Why would an issue with user_2’s ACL manifest itself witha foreign key validation on insert by user_1 on a table owned by user_1?
"David G. Johnston" <david.g.johnston@gmail.com> writes: > On Wed, Mar 21, 2018 at 8:30 AM, Battuello, Louis < > louis.battuello@etasseo.com> wrote: >> So, user_2 needs usage on the schema containing its newly owned reference >> table even though user_1 is performing the insert on a table in the other >> schema? Interesting. I though the validation was only dependent on user_1's >> ACL. > It was the only thing that made sense, given the error, though I agree it > seems a bit odd. The point is you can't resolve a name like "schema_1.something" unless you have USAGE on schema_1. So the RI-checking query, which is run as the owner of the table, fails at parse time. regards, tom lane
> > The point is you can't resolve a name like "schema_1.something" unless > you have USAGE on schema_1. So the RI-checking query, which is run as > the owner of the table, fails at parse time. That certainly makes sense for user_2 that owns the reference table and is blocked by not having usage on the reference table’sschema. But, user_1 owns both schemas and has usage on both but no longer owns the reference table in one schema. Why is user_1’sinsert on the referencing table failing? Is the validation of the FK no longer done as user_1?
On 03/21/2018 10:48 AM, Louis Battuello wrote: > >> >> The point is you can't resolve a name like "schema_1.something" unless >> you have USAGE on schema_1. So the RI-checking query, which is run as >> the owner of the table, fails at parse time. > > That certainly makes sense for user_2 that owns the reference table and is blocked by not having usage on the referencetable’s schema. > > But, user_1 owns both schemas and has usage on both but no longer owns the reference table in one schema. Why is user_1’sinsert on the referencing table failing? Is the validation of the FK no longer done as user_1? > From Tom's post: " The point is you can't resolve a name like "schema_1.something" unless you have USAGE on schema_1. So the RI-checking query, which is run as ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ the owner of the table, fails at parse time." ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ It is not the user that is doing the INSERT that matters it is the user that owns the table that matters. -- Adrian Klaver adrian.klaver@aklaver.com
Louis Battuello <louis.battuello@etasseo.com> writes: >> The point is you can't resolve a name like "schema_1.something" unless >> you have USAGE on schema_1. So the RI-checking query, which is run as >> the owner of the table, fails at parse time. > That certainly makes sense for user_2 that owns the reference table and is blocked by not having usage on the referencetable’s schema. > But, user_1 owns both schemas and has usage on both but no longer owns the reference table in one schema. Why is user_1’sinsert on the referencing table failing? Is the validation of the FK no longer done as user_1? Exactly, it's done as the owner of the referencing table. (I don't recall whether that's uniformly true for all types of FK-enforcement queries, but evidently it's true for this case.) regards, tom lane
Louis Battuello <louis.battuello@etasseo.com> writes:
>> The point is you can't resolve a name like "schema_1.something" unless
>> you have USAGE on schema_1. So the RI-checking query, which is run as
>> the owner of the table, fails at parse time.
> That certainly makes sense for user_2 that owns the reference table and is blocked by not having usage on the reference table’s schema.
> But, user_1 owns both schemas and has usage on both but no longer owns the reference table in one schema. Why is user_1’s insert on the referencing table failing? Is the validation of the FK no longer done as user_1?
Exactly, it's done as the owner of the referencing table. (I don't recall
whether that's uniformly true for all types of FK-enforcement queries,
but evidently it's true for this case.)
On Mar 21, 2018, at 2:36 PM, David G. Johnston wrote:
And altering an owner of a table to one lacking usage and create permissions on the schema is possible but unadvisible.David J.