MIME-Version: 1.0
References: <CAFCRh--0j77ueun+Ak0vRe_vi__Jks_HZjD9QbD+THR8gAWvRQ@mail.gmail.com>
 <0b7d3b22-3e98-4eca-bc14-9430c48c0217@technowledgy.de>
In-Reply-To: <0b7d3b22-3e98-4eca-bc14-9430c48c0217@technowledgy.de>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Wed, 26 Jun 2024 14:25:30 +0200
Message-ID: <CAFCRh-_FKBWVpBnD6ECOfM_bSgBfudDoPnBJM136O+pp5O27DQ@mail.gmail.com>
Subject: Re: current_role of caller of a DEFINER function
To: walther@technowledgy.de
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-_FKBWVpBnD6ECOfM_bSgBfudDoPnBJM136O%2Bpp5O27DQ%40mail.gmail.com>
Precedence: bulk

On Wed, Jun 26, 2024 at 12:11=E2=80=AFPM <walther@technowledgy.de> wrote:
> Dominique Devienne:
> > 1) Is there any way to know the current_role of the caller of a
> > DEFINER function. I fear the answer is no, but better be sure from
> > experts here.
>
> You can do something like this:
>
> CREATE DOMAIN current_user_only AS text
>    CONSTRAINT current_user_only CHECK (VALUE =3D CURRENT_USER);
>
> CREATE FUNCTION func(
>    calling_user current_user_only DEFAULT CURRENT_USER
> ) ... SECURITY DEFINER;
>
> The default value will be evaluated in the context of the calling user,
> the constraint forces it to never be set explicitly to something else.

Fantastic Wolfgang. Thanks! (again...)

I just tested it in my unit test (unlike last time, see below...), and
it works perfectly.

I tried w/o param, to benefit from the default. OK.
I tried w/ an explicit param, that satisfies the constraint. OK.
I tired w/ an explicit param, that does NOT satisfy the constraint
(i.e. simulating a hack), and it is KO as expected:

ERROR:  value for domain captured_current_role violates check
constraint "equals_current_role"

On Wed, Jun 26, 2024 at 12:06=E2=80=AFPM Erik Wienhold <ewie@ewie.name> wro=
te:
> Have you tried capturing current_user with a function parameter and defau=
lt value?
> https://www.postgresql.org/message-id/f82f70fd-665f-6384-5e8a-987ab9e640d=
3%40technowledgy.de

I'm embarrassed to admit that you posted a link to a response to one
of my own threads/questions...
Which Wolfgang in fact had already answered then even. I had
completely forgotten about it, sorry.

On Wed, Jun 26, 2024 at 11:08=E2=80=AFAM Laurenz Albe <laurenz.albe@cyberte=
c.at> wrote:
> On Wed, 2024-06-26 at 10:35 +0200, Dominique Devienne wrote:
> > So I have two questions:
> > 1) Is there any way to know the current_role of the caller of a
> > DEFINER function. I fear the answer is no, but better be sure from
> > experts here.
>
> Just to be certain, let me ask a question back:
>
> If a SECURITY DEFINER function calls another SECURITY DEFINER function,
> which role would you like to get:
> 1. the invoker that called the 1st function
> 2. the owner of the 1st function (which is the user that called the 2nd f=
unction)

Honestly Laurenz, I didn't think about it, and it does not matter too
much in my case.
Because what matters to me is the initial entry-point, from caller to
DEFINER function,
to accurately capture the role, and then I can pass it on explicitly
myself if needed.
This is for more knowledgeable people to decide on.

I still think such a new variable would be useful,
and simpler than Wolfgang's DOMAIN solution,
but I do have a solution that works right now, and
not in a future version, assuming such a change ever occurs,
so this is already great for me.

Thank you all, --DD