| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | Louis Battuello <louis(dot)battuello(at)etasseo(dot)com> |
| Cc: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Foreign Key Validation after Reference Table Ownership Change |
| Date: | 2018-03-21 17:52:41 |
| Message-ID: | 24104.1521654761@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
Louis Battuello <louis(dot)battuello(at)etasseo(dot)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.)
regards, tom lane
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Stuart McGraw | 2018-03-21 17:59:37 | Re: postgresql-10.3 on unbuntu-17.10 - how?? |
| Previous Message | Adrian Klaver | 2018-03-21 17:51:41 | Re: Foreign Key Validation after Reference Table Ownership Change |