On Thu, Jul 26, 2018 at 8:31 AM, Adrian Klaver <adrian.kla...@aklaver.com> 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.kla...@aklaver.com >