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.klaver@aklaver.com