Feedback-ID: i76984098:Fastmail
Message-ID: <fa68fc72-e109-452e-8642-2b99c613f870@aklaver.com>
Date: Wed, 30 Jul 2025 08:23:37 -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>,
 pgsql-general@lists.postgresql.org
References: <CAFCRh--AXoYUj8-WDuWpUcWXC0UNAL9gjbTp=1hU-NJhRyR0vQ@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAFCRh--AXoYUj8-WDuWpUcWXC0UNAL9gjbTp=1hU-NJhRyR0vQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/fa68fc72-e109-452e-8642-2b99c613f870%40aklaver.com>
Precedence: bulk

On 7/30/25 04:37, Dominique Devienne wrote:
> Hi. PostgreSQL v17 here.
> 
> I'm stumped by something, and would like pointers to sort something out.
> Inside my function, I do:
> ------------
>      EXECUTE format('SET LOCAL ROLE %I', schema_owner);
>      raise notice 'CURRENT_USER = %, can DELETE = %', CURRENT_USER,
> has_table_privilege('SchemaMapping', 'DELETE');
> 
>      DELETE FROM SchemaMapping
>       WHERE "schema" = schema_name;
> -----------
> 
> PQ: NOTICE: CURRENT_USER = Joe, can DELETE = t
> 
> ERROR: permission denied for table schemamapping CONTEXT: SQL
> statement "DELETE FROM SchemaMapping WHERE "schema" = schema_name"
> PL/pgSQL function foo(text,text) line 28 at SQL statement
> 
> The raise notice output is correct, and as expected. The DELETE error isn't.
> How can has_table_privilege() return true, yet the DELETE fail? I don't get it.
> 
> My setup ensures that the role I SET LOCAL ROLE to, has (indirectly)
> been granted DMLs on that table.
> 
> I also double-checked outside the routine, directly in code, for that privilege:
> 
>      c.ctx().setRole(schema_owner);
>      auto rset = pq::exec(c, "select
> has_table_privilege('SchemaMapping', 'DELETE')");
>      bool has_delete_mapping = rset.scalar<bool>();
>      BOOST_CHECK(has_delete_mapping);
> 
> Are there special consideration I'm unaware of, regarding SET ROLE
> inside routines?
> 
> (beside the fact you can't SET ROLE in a SECURITY DEFINER routime! As
> I discovered recently...).
> 
> I'd really appreciate some help here, as I don't understand what's going on.

What is the ROLE that defined the function?

What does "My setup ensures that the role I SET LOCAL ROLE to, has 
(indirectly) been granted DMLs on that table." in terms of actual GRANTs?


> 
> Thanks, --DD
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com