From: | Leland Weathers <leland(at)lcweathers(dot)net> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Permission denied on schema for all users on insert to table with fk |
Date: | 2018-07-26 15:28:24 |
Message-ID: | CAHeq4Yxd0h4m7DVvEv3iVVtsxgQBaOcjdE4XEDbQWzbnS=M+gg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Jul 26, 2018 at 9:19 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
From | Date | Subject | |
---|---|---|---|
Next Message | Dimitri Maziuk | 2018-07-26 17:54:19 | Re: logical replication snapshots |
Previous Message | Charles Clavadetscher | 2018-07-26 15:11:59 | RE: Read only to schema |