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?
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.
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.
--------- 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.
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.
I don't have time to experiment right now - but at a high level one would
think user_2 would need usage and create on schema_1 in order to be the
owner of an object in schema_1. There is no possible way for it to
actually create its own object there without such permissions - that a
superuser can do so leaves a possibility for a non-superuser dump/restore
problem.
Whether the system allows you too or not I'd advise the owner of objects
within a schema either own the schema too or have usage and create
permission thereon.
David J.
Agreed. It would certainly make sense that user_2 have usage on the schema in order to operate against the table owned by user_2. I just found it confusing that the discrepancy would cause an issue for user_1, which had all necessary privileges on the schema and references on the reference table. Why would an issue with user_2’s ACL manifest itself with a 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’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?
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 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?
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 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
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.
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.
Exactly. The cause of my mistake was changing the REFERENCED table ownership to a role without granting usage on the schema, too. However, with the error occurring when acting as user_1, I wasn’t clear on where the privilege mismatch occurred and which role’s privilege required correction.