Обсуждение: current_role of caller of a DEFINER function

Hi. I was led to believe (by an hallucination...) that I could know
the current_role of the caller of a DEFINER function, but after actual
experimentation, turns out it shows the OWNER of the function, and not
the current_role of the caller.

I foolishly thought curent_role != current_user inside the DEFINER
function, but reading back the doc, it's clear current_role =
current_user = user, thus that was wishful thinking. Only session_user
is representative of the caller, and reliable (modulo SUPERUSER and
SET AUTHORIZATION, but that's a different story and kinda normal)

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.
2) Why isn't there a way to obtain the above? What harm would it be?

Obviously for #2, distinguishing current_role from current_user inside
DEFINER is a no-go, for backward compatibility. But could a new
variable be invented for that? What obvious technical reason I'm
missing would make that harmful or difficult?

As to the use-case, now that I have a queue mechanism for tasks to be
delegated to services, I need to make sure the poster of the task has
the right privileges, so I wanted to capture session_user and
current_role of the "poster", and the service would use that info,
reliably captured (inside the DEFINER function to post a task), using
ROLEs and GRANTs and other app-specific permission data.

LOGIN users have different persona in the system, so the current_role
matters, to determine whether the connection posting the task to be
processed asynchronously by a service is allowed or not. I'd think I'm
not the only one that would need something like this, no?

Thanks, --DD

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.
> 2) Why isn't there a way to obtain the above? What harm would it be?

Just te 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 first function

2. the owner of the first function (which is the user that called the
   second function)

Yours,
Laurenz Albe

On 2024-06-26 10:35 +0200, Dominique Devienne wrote:
> Hi. I was led to believe (by an hallucination...) that I could know
> the current_role of the caller of a DEFINER function, but after actual
> experimentation, turns out it shows the OWNER of the function, and not
> the current_role of the caller.
> 
> I foolishly thought curent_role != current_user inside the DEFINER
> function, but reading back the doc, it's clear current_role =
> current_user = user, thus that was wishful thinking. Only session_user
> is representative of the caller, and reliable (modulo SUPERUSER and
> SET AUTHORIZATION, but that's a different story and kinda normal)
> 
> 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.

Have you tried capturing current_user with a function parameter and
default value?
https://www.postgresql.org/message-id/f82f70fd-665f-6384-5e8a-987ab9e640d3%40technowledgy.de

-- 
Erik

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 = 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.

Thus you can use calling_user inside your function.

Best,

Wolfgang

On Wed, Jun 26, 2024 at 12:11 PM <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 = 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 PM Erik Wienhold <ewie@ewie.name> wrote:
> Have you tried capturing current_user with a function parameter and default value?
> https://www.postgresql.org/message-id/f82f70fd-665f-6384-5e8a-987ab9e640d3%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 AM Laurenz Albe <laurenz.albe@cybertec.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 function)

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

Only session_user
is representative of the caller, and reliable (modulo SUPERUSER and
SET AUTHORIZATION, but that's a different story and kinda normal)

On Wed, Jun 26, 2024 at 2:42 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Wednesday, June 26, 2024, Dominique Devienne <ddevienne@gmail.com> wrote:
>> Only session_user
>> is representative of the caller, and reliable (modulo SUPERUSER and
>> SET AUTHORIZATION, but that's a different story and kinda normal)
>
> Why can you not use session_user then?

Hi. As I already wrote above, the current_role matters in our security model.
The LOGIN user (i.e. session_user) is used only for authentication to
the DB and to connect.
All other security concerns are on other app-maintained (NOLOGIN)
roles, used for authorization. --DD