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

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