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