MIME-Version: 1.0
References: <CAFCRh--0j77ueun+Ak0vRe_vi__Jks_HZjD9QbD+THR8gAWvRQ@mail.gmail.com>
 <0b7d3b22-3e98-4eca-bc14-9430c48c0217@technowledgy.de> <CAFCRh-_FKBWVpBnD6ECOfM_bSgBfudDoPnBJM136O+pp5O27DQ@mail.gmail.com>
In-Reply-To: <CAFCRh-_FKBWVpBnD6ECOfM_bSgBfudDoPnBJM136O+pp5O27DQ@mail.gmail.com>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Tue, 3 Jun 2025 17:38:23 +0200
Message-ID: <CAFCRh-9r+3qoQvt-yVvkLqkmBN7eELoCut3tTLOvB6mUSFLyZA@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-9r%2B3qoQvt-yVvkLqkmBN7eELoCut3tTLOvB6mUSFLyZA%40mail.gmail.com>
Precedence: bulk

On Wed, Jun 26, 2024 at 2:25=E2=80=AFPM Dominique Devienne <ddevienne@gmail=
.com> wrote:
> On Wed, Jun 26, 2024 at 11:08=E2=80=AFAM Laurenz Albe <laurenz.albe@cyber=
tec.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.

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