Re: Permission denied on schema for all users on insert to table withfk

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