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
