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

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(dot)klaver(at)aklaver(dot)com