From: | Leland Weathers <leland(at)lcweathers(dot)net> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Permission denied on schema for all users on insert to table with fk |
Date: | 2018-07-25 13:40:29 |
Message-ID: | CA+QX6VWofLQLC9FP3mCeyczy7_w8m8mGGxKejiRcrBEqdNosyg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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,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.
From | Date | Subject | |
---|---|---|---|
Next Message | Fabio Pardi | 2018-07-25 13:48:32 | Re: DB Backup from WAL Slave |
Previous Message | basti | 2018-07-25 13:14:34 | Re: DB Backup from WAL Slave |