Re: DEFINER / INVOKER conundrum

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