Feedback-ID: i76984098:Fastmail
Message-ID: <662792ed-810d-46f1-a0c3-d4b55e5469fc@aklaver.com>
Date: Wed, 30 Jul 2025 12:42:39 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql)
 function
From: Adrian Klaver <adrian.klaver@aklaver.com>
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>
 <508f71c4-f1b1-4685-921d-bec8b361be10@aklaver.com>
Content-Language: en-US
In-Reply-To: <508f71c4-f1b1-4685-921d-bec8b361be10@aklaver.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/662792ed-810d-46f1-a0c3-d4b55e5469fc%40aklaver.com>
Precedence: bulk

On 7/30/25 09:21, Adrian Klaver wrote:
> 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?
> 

If the above is not the issue, then a simple test case:

grant db_user to app_user with set true, inherit true;

-- As db_user

create table fnc_set_role_test(id integer, fld1 varchar);
insert into fnc_set_role_test values (1, 'test');

CREATE OR REPLACE FUNCTION public.role_set()
  RETURNS void
  LANGUAGE plpgsql
AS $function$

BEGIN
     EXECUTE format('SET LOCAL ROLE %I', 'db_user');
     raise notice 'CURRENT_USER = %, can DELETE = %', CURRENT_USER,
     has_table_privilege('fnc_set_role_test', 'DELETE');
     DELETE FROM fnc_set_role_test;
END;
$function$
;

-- As app_user
\c - app_user

select * from fnc_set_role_test ;

select role_set();
NOTICE:  CURRENT_USER = db_user, can DELETE = t
  role_set
----------

(1 row)

  select * from fnc_set_role_test ;
  id | fld1
----+------
(0 rows)


My suspicion is that there is a missing piece in your chain of roles.


-- 
Adrian Klaver
adrian.klaver@aklaver.com