Обсуждение: DEFINER / INVOKER conundrum

## Dominique Devienne (ddevienne@gmail.com):

> On the one hand, I want a INVOKER security function,
> to be able to capture the login and current ROLEs.

There's session_user ("the session user's name") which remains unchanged
on a SECURITY DEFINER function, and current_user ("the user name of the
current execution context") which changes according to the security
context set by SECURITY DEFINER/INVOKER.
-> https://www.postgresql.org/docs/current/functions-info.html

Regards,
Christoph

-- 
Spare Space.

> On 03/04/2023 13:18 CEST Dominique Devienne <ddevienne@gmail.com> wrote:
>
> My goal is to have clients connect to PostgreSQL,
> and call a function that return a JWT token.
>
> The JWT is supposed to capture the user (login role),
> and the current_role (which has meaning in our app),
> and sign it using a secret corresponding to a mid-tier
> service the client will connect to later.
>
> I've found https://github.com/michelp/pgjwt which seems
> perfect for my use case, but I'm struggling with something.
>
> On the one hand, I want a INVOKER security function,
> to be able to capture the login and current ROLEs.
>
> On the other hand, I want a DEFINER security function,
> to be able to access the secret to sign the JWT with.
>
> That secret will be in a table that regular users of our DB
> do NOT have access to, of course. But that the function
> doing the JWT signing does need access to, of course (again).
>
> I thought I'd have two layers of functions, one INVOKER
> that captures the ROLEs, which then calls the DEFINER one,
> passing the ROLEs captured, but since the INVOKER function
> must also be able to call the DEFINER function, what prevents
> the client from calling it directly, with different (spoofed) ROLEs?
>
> Is there a way out of that conundrum?

A single DEFINER function works if you capture current_user with a parameter
and default value.  Let's call it claimed_role.  Use pg_has_role[0] to check
that session_user has the privilege for claimed_role (in case the function is
called with an explicit value), otherwise raise an exception.

Connect as postgres:

    CREATE FUNCTION f(claimed_role text default current_user)
      RETURNS TABLE (claimed_role text, curr_user text, sess_user text)
      SECURITY DEFINER
      LANGUAGE sql
      $$ SELECT claimed_role, current_user, session_user $$;

Connect as alice:

    SELECT * FROM f();

     claimed_role | curr_user | sess_user
    --------------+-----------+-----------
     alice        | postgres  | alice
    (1 row)

[0] https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE

--
Erik

Erik Wienhold:
> A single DEFINER function works if you capture current_user with a parameter
> and default value.  Let's call it claimed_role.  Use pg_has_role[0] to check
> that session_user has the privilege for claimed_role (in case the function is
> called with an explicit value), otherwise raise an exception.
> 
> Connect as postgres:
> 
>     CREATE FUNCTION f(claimed_role text default current_user)
>       RETURNS TABLE (claimed_role text, curr_user text, sess_user text)
>       SECURITY DEFINER
>       LANGUAGE sql
>       $$ SELECT claimed_role, current_user, session_user $$;

For me, checking whether session_user has the privilege for claimed_role 
is not enough, so I add a DOMAIN to the mix:

CREATE DOMAIN current_user_only AS NAME CHECK (VALUE = CURRENT_USER);

CREATE FUNCTION f(calling_user current_user_only DEFAULT CURRENT_USER)
...
SECURITY DEFINER;

This works, because the domain check is evaluated in the calling context.

Best,

Wolfgang

> On 04/04/2023 07:55 CEST walther@technowledgy.de wrote:
>
> Erik Wienhold:
> > A single DEFINER function works if you capture current_user with a parameter
> > and default value.  Let's call it claimed_role.  Use pg_has_role[0] to check
> > that session_user has the privilege for claimed_role (in case the function is
> > called with an explicit value), otherwise raise an exception.
> >
> > Connect as postgres:
> >
> >     CREATE FUNCTION f(claimed_role text default current_user)
> >       RETURNS TABLE (claimed_role text, curr_user text, sess_user text)
> >       SECURITY DEFINER
> >       LANGUAGE sql
> >       $$ SELECT claimed_role, current_user, session_user $$;
>
> For me, checking whether session_user has the privilege for claimed_role
> is not enough, so I add a DOMAIN to the mix:
>
> CREATE DOMAIN current_user_only AS NAME CHECK (VALUE = CURRENT_USER);
>
> CREATE FUNCTION f(calling_user current_user_only DEFAULT CURRENT_USER)
> ...
> SECURITY DEFINER;
>
> This works, because the domain check is evaluated in the calling context.

Nice.  It's equivalent to my version without the domain if the client can
execute SET ROLE before calling f, thereby injecting any role for which
pg_has_role(session_user, calling_user, 'MEMBER') returns true.

Dominique did not say whether he controls the clients or not.

--
Erik