On Wednesday, March 21, 2018, Tom Lane <
tgl@sss.pgh.pa.us> wrote:
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.)
Unless you mis-spoke and meant "referenced table" I'm confused because:
alter schema test_schema_1 owner to user_1;
[...]
alter table test_schema_2.data_table owner to user_1;
test_schema_1.data_table is the referencing table and is owned by user_1 as is test_schema_1 (which houses the referenced table reference_table)
Haven't tried to reproduce from the provided script but taking it at face value the error about there being a schema permission error is unexpected given that.
It would be useful to have the error report the user with the permission problem and not just the target object.
From the observed behavior basically one needs references permission to create a foreign key constraint but doesn't need select permissions on the pk/referenced table because the table itself will validate the constraint on the supplied data.
And altering an owner of a table to one lacking usage and create permissions on the schema is possible but unadvisible.
David J.
