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?

>
> __ __
>
> “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(dot)klaver(at)aklaver(dot)com