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?
> 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?
There's a fixed ROLE (called :SOWNER) that has USAGE on the SCHEMA
that owns the table.
And an explicit GRANT INSERT, UPDATE, DELETE ON TABLE SchemaMapping to it.
And that fixed :SOWNER ROLE is granted to the per-schema role I SET
LOCAL ROLE to, WITH INHERIT TRUE.
Lets call that latter role :OWNER1.
So inside the function, when I `SET LOCAL ROLE :OWNER1`
I expect current_role to become :OWNER1 (the raise notice corroborates that),
which activates the :SOWNER fixed role, since it was granted WITH INHERIT TRUE,
which thus give USAGE on the table SCHEMA, and DELETE on its TABLE.
Thus the DELETE DML should work...
That's what I'm expecting to happen. But it doesn't. And I don't see why yet.
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.
