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
>

Reply via email to