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 1pjLaj-0000D6-Co for pgsql-general@arkaria.postgresql.org; Mon, 03 Apr 2023 14:55:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pjLai-0003vD-5U for pgsql-general@arkaria.postgresql.org; Mon, 03 Apr 2023 14:55:28 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pjLah-0003v3-Qh for pgsql-general@lists.postgresql.org; Mon, 03 Apr 2023 14:55:27 +0000 Received: from mout-u-107.mailbox.org ([2001:67c:2050:101:465::107]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pjLae-000eIW-TI for pgsql-general@lists.postgresql.org; Mon, 03 Apr 2023 14:55:27 +0000 Received: from smtp2.mailbox.org (unknown [10.196.197.2]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-u-107.mailbox.org (Postfix) with ESMTPS id 4Pqv876pkFz9sTD; Mon, 3 Apr 2023 16:55:19 +0200 (CEST) Date: Mon, 3 Apr 2023 16:55:19 +0200 (CEST) From: Erik Wienhold To: Dominique Devienne , pgsql-general@lists.postgresql.org Message-ID: <478369375.553946.1680533719442@office.mailbox.org> In-Reply-To: References: Subject: Re: DEFINER / INVOKER conundrum MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Priority: 3 Importance: Normal List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 03/04/2023 13:18 CEST Dominique Devienne wrote: > > My goal is to have clients connect to PostgreSQL, > and call a function that return a JWT token. > > The JWT is supposed to capture the user (login role), > and the current_role (which has meaning in our app), > and sign it using a secret corresponding to a mid-tier > service the client will connect to later. > > I've found=C2=A0https://github.com/michelp/pgjwt which seems > perfect for my use case, but I'm struggling with something. > > On the one hand, I want a INVOKER security function, > to be able to capture the login and current ROLEs. > > On the other hand, I want a DEFINER security function, > to be able to access the secret to sign the JWT with. > > That secret will be in a table that regular users of our DB > do NOT have access to, of course. But that the function > doing the JWT signing does need access to, of course (again). > > I thought I'd have two layers of functions, one INVOKER > that captures the ROLEs, which then calls the DEFINER one, > passing the ROLEs captured, but since the INVOKER function > must also be able to call the DEFINER function, what prevents > the client from calling it directly, with different (spoofed) ROLEs? > > Is there a way out of that conundrum? A single DEFINER function works if you capture current_user with a paramete= r and default value. Let's call it claimed_role. Use pg_has_role[0] to chec= k 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: =09CREATE FUNCTION f(claimed_role text default current_user) =09 RETURNS TABLE (claimed_role text, curr_user text, sess_user text) =09 SECURITY DEFINER =09 LANGUAGE sql =09 $$ SELECT claimed_role, current_user, session_user $$; Connect as alice: =09SELECT * FROM f(); =09 claimed_role | curr_user | sess_user =09--------------+-----------+----------- =09 alice | postgres | alice =09(1 row) [0] https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-I= NFO-ACCESS-TABLE -- Erik