On Thu, Jul 26, 2018 at 8:31 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
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?
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)
INSERT INTO results.historyitem (batchid,datasourceid,sequence_order) VALUES (6,20,1);
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
