> On 04/04/2023 07:55 CEST walther@xxxxxxxxxxxxxxx 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
