Feedback-ID: i76984098:Fastmail
Message-ID: <508f71c4-f1b1-4685-921d-bec8b361be10@aklaver.com>
Date: Wed, 30 Jul 2025 09:21:08 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql)
 function
To: Dominique Devienne <ddevienne@gmail.com>
Cc: pgsql-general@lists.postgresql.org
References: <CAFCRh--AXoYUj8-WDuWpUcWXC0UNAL9gjbTp=1hU-NJhRyR0vQ@mail.gmail.com>
 <fa68fc72-e109-452e-8642-2b99c613f870@aklaver.com>
 <CAFCRh-9AzsOBd6cPFsgmbw=Mf3nN5tHj9YYQQHZG0XqxDSMK=Q@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAFCRh-9AzsOBd6cPFsgmbw=Mf3nN5tHj9YYQQHZG0XqxDSMK=Q@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/508f71c4-f1b1-4685-921d-bec8b361be10%40aklaver.com>
Precedence: bulk

On 7/30/25 08:47, Dominique Devienne wrote:
> On Wed, Jul 30, 2025 at 5:23 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>> On 7/30/25 04:37, Dominique Devienne wrote:
>>> Are there special consideration I'm unaware of, regarding SET ROLE
>>> inside routines?
> 
>> What is the ROLE that defined the function?
> 
> A 3rd role. But does it matter? Given that this is in SECURITY INVOKER function?

My mistake, a BC(Before Coffee) issue.


> The function and the table belong to yet another role.
> And when we enter the function, we're yet another one (obviously with
> USAGE+EXECUTE, since could call it).
> But once we SET LOCAL ROLE, the effective permissions used should be
> for :OWNER1 and the inherited :SOWNER.

Could this be a search_path and/or naming issue, where the table 
SchemaMapping appears in more then one schema or different name case?

-- 
Adrian Klaver
adrian.klaver@aklaver.com