public inbox for [email protected]
help / color / mirror / Atom feedRe: current_role of caller of a DEFINER function
2+ messages / 1 participants
[nested] [flat]
* Re: current_role of caller of a DEFINER function
@ 2024-06-26 12:25 Dominique Devienne <[email protected]>
2025-06-03 15:38 ` Re: current_role of caller of a DEFINER function Dominique Devienne <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Dominique Devienne @ 2024-06-26 12:25 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
On Wed, Jun 26, 2024 at 12:11 PM <[email protected]> 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 <[email protected]> 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 <[email protected]> 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: current_role of caller of a DEFINER function
2024-06-26 12:25 Re: current_role of caller of a DEFINER function Dominique Devienne <[email protected]>
@ 2025-06-03 15:38 ` Dominique Devienne <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Dominique Devienne @ 2025-06-03 15:38 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
On Wed, Jun 26, 2024 at 2:25 PM Dominique Devienne <[email protected]> wrote:
> On Wed, Jun 26, 2024 at 11:08 AM Laurenz Albe <[email protected]> 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.
Hi. Resurrecting this thread, 1 year later, to follow up on Laurenz
question, and ask another of my own.
It just so happens that I now have two layers of SECURITY DEFINER
functions. The 1st (inner) layer is to encapsulate sensitive DDLs (and
audit them), is one "DBA" schema that belong to a "DBA" role, that
knows (almost) nothing of the application and is only concerned with
pure-PostgreSQL stuff, and the 2nd (outer) layer more application
specific, in another "App Admin" schema (that belongs to different
ROLE yet).
I'm using the DOMAIN type discussed here, on the outer layer, to
capture the CURRENT_ROLE, i.e. the caller of that outer SECURITY
DEFINER FUNCTION. This works great. But I also want to pass it down to
the lower/inner layer, for auditing/logging purposes only. Initially I
tried using the same DOMAIN type/value, but then I'd violate the CHECK
constraints. The initial value was captured before entering the outer
SECURITY DEFINER function, and when copying the value to pass it to
the inner SECURITY DEFINER function, that value no longer matches the
"new" CURRENT_ROLE from within the SECURITY DEFINER context.
The work-around is to take it as text (or name) instead of the DOMAIN
type, but that feels unsatisfactory, since then the caller could pass
an arbitrary value, not something that comes from my DOMAIN type,
which enforces the fact its value is the CURRENT_ROLE.
So my question is whether my inner-procs can take another type, that
can only be created from my DOMAIN type? I.e. I'd want to enforce the
value I'm getting comes from my DOMAIN type, and only that type. Is
that possible? Thanks, --DD
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-06-03 15:38 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-26 12:25 Re: current_role of caller of a DEFINER function Dominique Devienne <[email protected]>
2025-06-03 15:38 ` Dominique Devienne <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox