Re: Permission denied on schema for all users on insert to table with fk

On Wed, Jul 25, 2018 at 11:32 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>