Re: DEFINER / INVOKER conundrum

[walther]

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