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
>

Reply via email to