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 9:19 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 07/26/2018 06:57 AM, Leland Weathers wrote:



    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)

So if I am following neither of these roles have permissions on the tables. Is that correct?

If you try the INSERT as system_admin, jb or gb does it work?


Thanks that was the right direction and I feel stupid now and the issue is resolved. The system_admin account (the table owner) did not have usage permission on the schema - re-reading some SO articles, it was there in the comments and I had missed it. All the users had permissions but even superuser can't insert without the table owner having schema permissions.


    INSERT INTO results.historyitem
    (batchid,datasourceid,sequence_order) VALUES (6,20,1);


    --     Adrian Klaver
    adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@aklaver.com>




--
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