Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rKhKe-00455m-Hs for pgsql-general@arkaria.postgresql.org; Tue, 02 Jan 2024 16:09:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rKhKb-00Gty5-9X for pgsql-general@arkaria.postgresql.org; Tue, 02 Jan 2024 16:09:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rKhKa-00Gtxx-Ub for pgsql-general@lists.postgresql.org; Tue, 02 Jan 2024 16:09:28 +0000 Received: from out1-smtp.messagingengine.com ([66.111.4.25]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rKhKY-00FB2h-0v for pgsql-general@lists.postgresql.org; Tue, 02 Jan 2024 16:09:28 +0000 Received: from compute1.internal (compute1.nyi.internal [10.202.2.41]) by mailout.nyi.internal (Postfix) with ESMTP id B36EA5C01C8; Tue, 2 Jan 2024 11:09:23 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute1.internal (MEProxy); Tue, 02 Jan 2024 11:09:23 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm1; t=1704211763; x=1704298163; bh=szwbBbNfIS0iRw8M3ENLfYv6hpG9SX4R4TFC781WfcE=; b= H0JaKNX43KUbb3GKZOKsUMdg2ZYpudTzYuE+uqaN6wJBiKr5cJBcVP0iaxlpB/zz /wnHph6+V/GPxecl7jvbqk27qHXY2eiPgr/5Rnk3kZshnFtCV0lc2l3NdRlHr8DI BRZKBruZxeloBgBt8lsNAcjvvgw64efQ+s+Laec3BynpmxRpe3fbUiJeic6ahjbW EGL66j4T4Wc/3nyaQXdZ7Vkm8myGqYbn9gxPsGkl5GfkMbDgbi79TahqH9c6n0Qg zt0roTWR9SDO0bbJMsKs7dDqxoraBzadQ30pMH+QRwibB4zfK0UgBwDHJ8XFkYmI b9crNejf6RLNDjWYp05URA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm2; t=1704211763; x= 1704298163; bh=szwbBbNfIS0iRw8M3ENLfYv6hpG9SX4R4TFC781WfcE=; b=I V+df4qlx2tc9CMwIL7azA8YDxGF4uJVOGN0f7I/T5vxHaCuK/qLbfMopAccZ8xZk TMzMy5jAmrZ8EWGCzdTGIuKHGO4OzSf55kvPUq5j09U1FLjO2qY+Ak1QbAOSbASs 8h9cSaKRoDc51eLMaVLB68FZM1E3u4mICyeIIeHUqwmOvGL7898iYosKZJNHNCh8 MlA3KOG1CtldaVvqpGy3mbVRSvB2wX0mlgeV/z10aRUJsqY4vzGGG+yv2Ebh3wV/ f/O+6dmZTDYskgmvTtnIQ+e/YwUvV+8/n8ZPWMybudBPZEvzMbsv5zi9Nl5tHH11 yIT1ONBXfymAAR3CAF/ww== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrvdegfedgudeiucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvfhfhjggtgfesthejredttddvjeenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucggtffrrghtthgvrhhnpeekfeehuddvjeeigfeifeejtdduudffledvfeelheef tdeiffeugfdvkeelgedtvdenucffohhmrghinhepphhoshhtghhrvghsqhhlrdhorhhgne cuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprggurhhi rghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Tue, 2 Jan 2024 11:09:23 -0500 (EST) Message-ID: Date: Tue, 2 Jan 2024 08:09:22 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: New SET privilege for pg_has_role() in v16+ Content-Language: en-US To: Dominique Devienne , pgsql-general@lists.postgresql.org References: From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 1/2/24 07:24, Dominique Devienne wrote: > Hi. And happy new year (for those using the Gregorian calendar). > > pg_has_role() from > https://www.postgresql.org/docs/current/functions-info.html > > added the 'SET' privilege in v16, and on top of the existing 'MEMBER' > and 'USAGE' ones: > > > MEMBER denotes direct or indirect membership in the role [...] > > USAGE denotes whether the privileges of the role are immediately > available without doing SET ROLE > > SET denotes whether it is possible to change to the role using the > SET ROLE command > > I'd like to know if possible why SET was added; the rationale for it. > Does it not imply that MEMBER and USAGE weren't enough somehow before? > > If `pg_has_role(..., 'MEMBER')` is true, isn't `pg_has_role(..., 'SET')` > implied? > If not, why? (and is that related to NOT INHERIT roles in the graph > between the two roles?) > > Asked differently I guess, when does being a MEMBER of a role (directly > or not), > NOT allow SET ROLE to that role? https://www.postgresql.org/docs/current/sql-set-role.html "Using this command, it is possible to either add privileges or restrict one's privileges. If the session user role has been granted memberships WITH INHERIT TRUE, it automatically has all the privileges of every such role. In this case, SET ROLE effectively drops all the privileges except for those which the target role directly possesses or inherits. On the other hand, if the session user role has been granted memberships WITH INHERIT FALSE, the privileges of the granted roles can't be accessed by default. However, if the role was granted WITH SET TRUE, the session user can use SET ROLE to drop the privileges assigned directly to the session user and instead acquire the privileges available to the named role. If the role was granted WITH INHERIT FALSE, SET FALSE then the privileges of that role cannot be exercised either with or without SET ROLE." > > We use ROLEs extensively in our PostgreSQL-based apps, > and I've read a lot about them, but at times I feel I'm missing something. > > Thanks, --DD -- Adrian Klaver adrian.klaver@aklaver.com