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

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?

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,sequence_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.klaver@aklaver.com