On Wed, Jul 25, 2018 at 11:32 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 07/25/2018 06:40 AM, Leland Weathers wrote: > >> I just ran into an issue on 9.5.13 after creating a new schema with a set >> of tables in them, that no accounts (including schema / table owners) can >> insert into a table with a fk relation. A snippet of the error output in >> log files is as follows and looks like it is permissions related to the >> parent table:____ >> > > Is the FK to a table in another schema? > The tables are in the same schema. > > Can we see the schema definitions for the affected tables? >> >> >> > Here are the two tables schema as well as their permissions: > > system=# \d results.historyitem > > Table > "results.historyitem" > > Column | Type > | Modifiers > > ----------------+-----------------------------+------------- > --------------------------------------------------------------- > > id | integer | not null default > nextval(('results."historyitem_id_seq"'::text)::regclass) > > batchid | integer | not null > > datasourceid | integer | > > sequence_order | integer | not null > > description | text | > > causedfailure | boolean | > > timestamp | timestamp without time zone | > > modulename | text | > > modulebasename | text | > > Indexes: > > "pk_historyitem_id" PRIMARY KEY, btree (id) > > "ixfk_historyitem_batch" btree (batchid) > > Foreign-key constraints: > > "fk_historyitem_batch" FOREIGN KEY (batchid) REFERENCES > results.batch(id) ON DELETE CASCADE > > > > > > system=# \d results.batch > > Table "results.batch" > > Column | Type > | Modifiers > > ----------------------+-----------------------------+------- > --------------------------------------------------------------- > > id | integer | not null default > nextval(('results."batch_id_seq"'::text)::regclass) > > hostname | character varying(255) | > > assemblyversion | character varying(255) | > > commandlinearguments | text | > > starttime | timestamp without time zone | > > endtime | timestamp without time zone | > > exitcode | integer | > > windowsidentity | character varying(255) | > > threadcount | integer | > > Indexes: > > "pk_batch_id" PRIMARY KEY, btree (id) > > Referenced by: > > TABLE "results.historyitem" CONSTRAINT "fk_historyitem_batch" FOREIGN > KEY (batchid) REFERENCES results.batch(id) ON DELETE CASCADE > > TABLE "results.result" CONSTRAINT "fk_result_batch" FOREIGN KEY > (batchid) REFERENCES results.batch(id) ON DELETE CASCADE > > > > > > system=# \dp results.batch > > Access > privileges > > Schema | Name | Type | Access privileges | Column > privileges | Policies > > ---------+-------+-------+---------------------------------- > ----+-------------------+---------- > > results | batch | table | system_admin=arwdDxt/system_admin > +| | > > | | | system_reader=r/system_admin > +| | > > | | | system_batch_writer=arw/system_admin+| > | > > | | | gb=arwdDxt/system_admin > +| | > > | | | jb=arwdDxt/system_admin > | | > > (1 row) > > > > > > system=# \dp results.historyitem > > Access > privileges > > Schema | Name | Type | Access privileges | > Column privileges | Policies > > ---------+-------------+-------+---------------------------- > ----------+-------------------+---------- > > results | historyitem | table | system_admin=arwdDxt/system_admin > +| | > > | | | system_reader=r/system_admin > +| | > > | | | system_batch_writer=arw/system_admin+| > | > > | | | gb=arwdDxt/system_admin > +| | > > | | | jb=arwdDxt/system_admin > | | > > (1 row) > > > > > > system=# > > > > > > > And a more complete example of what we are seeing with multiple accounts. > This particular set is from an account that has their role set to that of > the database & schema owner which is different than the table owner role. > > > > system=# insert into results.batch (hostname, assemblyversion) VALUES > ('mycomp','0.0.0000.00000'); > > INSERT 0 1 > > system=# select lastval(); > > lastval > > --------- > > 6 > > (1 row) > > > > > > system=# INSERT INTO results.historyitem (batchid,datasourceid,sequence > _order) > > system-# VALUES > > system-# (6,20,1); > > ERROR: permission denied for schema results > > LINE 1: SELECT 1 FROM ONLY "results"."batch"... > > ^ > > QUERY: SELECT 1 FROM ONLY "results"."batch" x WHERE "id" > OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x > > system=# SELECT FROM results.batch WHERE id=6; > > -- > > (1 row) > > > > system# > > > >> __ __ >> >> “permission denied for schema <schemaname>",,,"SELECT 1 FROM ONLY >> <schemaname>.<tablename> x WHERE ""id"" OPERATOR(pg_catalog.=) $1 FOR KEY >> SHARE OF x",20____ >> >> __ __ >> >> __ __ >> >> The schema and tables are all owned by the same group role, and members >> of the owner role are also getting the error. So far, all users both owner >> and non-owner have been able to successfully execute the select statement >> used by trigger and get either a 1 back, or no rows when the correct id is >> entered. When run from the application, writes to this table are >> immediately after writes to the parent table so that the id can be returned >> for the child table writes. Writes to both parent/child tables are >> occurring with the same account. The following short snippet are a couple >> of the commands run by an account which is in the group role owning the >> database, schema and tables in question:____ >> >> __ __ >> >> <database>=# SELECT 1 FROM ONLY "<schema>"."<table>" x WHERE "id" >> OPERATOR(pg_catalog.=) 3 FOR____ >> >> KEY SHARE OF x;____ >> >> ?column?____ >> >> ----------____ >> >> 1____ >> >> (1 row)____ >> >> __ __ >> >> <database>=# INSERT INTO <schema>.sentryhistoryitem____ >> >> <database>-# (batchid,datasourceid,sequenc >> e_order,description,causedfailure,"timestamp",modulename, >> modulebasename)____ >> >> <database>-# VALUES____ >> >> <database>-# (3,20,1,'Found datasource [Id: 20, Name: >> ds1].',False,'07/24/2018 03:05:58.668','Datasource','Object')____ >> >> <database>-# ;____ >> >> ERROR: permission denied for schema <schema>____ >> >> LINE 1: SELECT 1 FROM ONLY "<schema>"."<table>"...____ >> >> ^____ >> >> QUERY: SELECT 1 FROM ONLY "<schema>"."<table>" x WHERE "id" >> OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x____ >> >> __ __ >> >> I’ve spent a bit of time searching on different sites trying to find >> pointers to this particular case and haven’t found any good ideas yet for >> next steps on troubleshooting or pointing at root cause. Any pointers to >> next steps would be appreciated. >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com >