Thread: Permission denied on schema for all users on insert to table with fk
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:
“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,
<database>-# VALUES
<database>-# (3,20,1,'Found datasource [Id: 20, Name: ds1].',False,'07/24/2018 03:05:58.668','Datasource','
<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.
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
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_se
q"'::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_ad
min +| | | | | 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_ad
min +| | | | | 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,causedfail ure,"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
On 07/26/2018 03:52 AM, Leland Weathers wrote: > > > > 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) What does \dn+ results show? Before you mentioned a trigger. I am not seeing that in the schema you sent. Is there one and if so what is it's definition and that of its associated function? What does show?: select session_user, current_user; INSERT INTO results.historyitem (batchid,datasourceid,sequence_order) VALUES (6,20,1); -- Adrian Klaver adrian.klaver@aklaver.com
On 07/26/2018 03:52 AM, Leland Weathers wrote:
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)
What does \dn+ results show?
system=# \dn+ results
List of schemas
Name | Owner | Access privileges | Description
---------+-------+------------
results | dba | dba=UC/dba +|
| | system_reader=U/dba +|
| | system_batch_writer=U/dba+|
| | gb=U/dba |
(1 row)
system=#
Before you mentioned a trigger. I am not seeing that in the schema you sent. Is there one and if so what is it's definition and that of its associated function?
What does show?:
select session_user, current_user;
INSERT INTO results.historyitem (batchid,datasourceid,sequence_order) VALUES (6,20,1);
--
Adrian Klaver
adrian.klaver@aklaver.com
On 07/26/2018 06:57 AM, Leland Weathers wrote: > > > Before you mentioned a trigger. I am not seeing that in the schema > you sent. Is there one and if so what is it's definition and that of > its associated function? > > > I was referring to the "built-in" PostgreSQL system trigger for > validating fk constraints are met. The trigger that uses the "SELECT 1 > FROM ONLY..." query. That particular query which the logs say I don't > have permissions to execute is not part of my schema/code. > > > What does show?: > > select session_user, current_user; > > > For this particular example, the session_user is: lw, current_user is > dba (database and schema owner role) So if I am following neither of these roles have permissions on the tables. Is that correct? If you try the INSERT as system_admin, jb or gb does it work? > > > INSERT INTO results.historyitem > (batchid,datasourceid,sequence_order) VALUES (6,20,1); > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > -- Adrian Klaver adrian.klaver@aklaver.com
On 07/26/2018 06:57 AM, Leland Weathers wrote:
Before you mentioned a trigger. I am not seeing that in the schema
you sent. Is there one and if so what is it's definition and that of
its associated function?
I was referring to the "built-in" PostgreSQL system trigger for validating fk constraints are met. The trigger that uses the "SELECT 1 FROM ONLY..." query. That particular query which the logs say I don't have permissions to execute is not part of my schema/code.
What does show?:
select session_user, current_user;
For this particular example, the session_user is: lw, current_user is dba (database and schema owner role)
So if I am following neither of these roles have permissions on the tables. Is that correct?
If you try the INSERT as system_admin, jb or gb does it work?
INSERT INTO results.historyitem
(batchid,datasourceid,sequence_order) VALUES (6,20,1);
-- Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
--
Adrian Klaver
adrian.klaver@aklaver.com