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@xxxxxxxxxxx
