MIME-Version: 1.0
References: <CAFCRh--AXoYUj8-WDuWpUcWXC0UNAL9gjbTp=1hU-NJhRyR0vQ@mail.gmail.com>
 <fa68fc72-e109-452e-8642-2b99c613f870@aklaver.com>
In-Reply-To: <fa68fc72-e109-452e-8642-2b99c613f870@aklaver.com>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Wed, 30 Jul 2025 17:47:14 +0200
Message-ID: <CAFCRh-9AzsOBd6cPFsgmbw=Mf3nN5tHj9YYQQHZG0XqxDSMK=Q@mail.gmail.com>
Subject: Re: SET LOCAL ROLE inside SECURITY INVOKER (LANGUAGE plpgsql) function
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAFCRh-9AzsOBd6cPFsgmbw%3DMf3nN5tHj9YYQQHZG0XqxDSMK%3DQ%40mail.gmail.com>
Precedence: bulk

On Wed, Jul 30, 2025 at 5:23=E2=80=AFPM Adrian Klaver <adrian.klaver@aklave=
r.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 func=
tion?

> 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 T=
RUE,
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 ye=
t.

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.