On Wednesday, March 21, 2018, Tom Lane <t...@sss.pgh.pa.us> wrote: > Louis Battuello <louis.battue...@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.
