Thread: 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?

USAGE

 

https://www.postgresql.org/docs/10/static/sql-grant.html

 

David J.

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.)