Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pjZdl-00026L-Uc for pgsql-general@arkaria.postgresql.org; Tue, 04 Apr 2023 05:55:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pjZdk-0006VY-Nn for pgsql-general@arkaria.postgresql.org; Tue, 04 Apr 2023 05:55:32 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pjZdk-0006VP-Dn for pgsql-general@lists.postgresql.org; Tue, 04 Apr 2023 05:55:32 +0000 Received: from dd25110.kasserver.com ([85.13.146.49]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pjZdh-0004JL-Nt for pgsql-general@lists.postgresql.org; Tue, 04 Apr 2023 05:55:31 +0000 Received: from [192.168.0.24] (ip-037-201-153-193.um10.pools.vodafone-ip.de [37.201.153.193]) by dd25110.kasserver.com (Postfix) with ESMTPSA id 63AFEE1A011F; Tue, 4 Apr 2023 07:55:26 +0200 (CEST) Message-ID: Date: Tue, 4 Apr 2023 07:55:25 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:102.0) Gecko/20100101 Thunderbird/102.5.0 From: walther@technowledgy.de Subject: Re: DEFINER / INVOKER conundrum To: Erik Wienhold , Dominique Devienne , pgsql-general@lists.postgresql.org References: <478369375.553946.1680533719442@office.mailbox.org> In-Reply-To: <478369375.553946.1680533719442@office.mailbox.org> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Spamd-Bar: -- List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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