Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux