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