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.kla...@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.kla...@aklaver.com
