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 1rKhr5-0047SE-SW for pgsql-general@arkaria.postgresql.org; Tue, 02 Jan 2024 16:43:03 +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 1rKhr4-00HSIe-Jv for pgsql-general@arkaria.postgresql.org; Tue, 02 Jan 2024 16:43:02 +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 1rKhr4-00HSIW-8j for pgsql-general@lists.postgresql.org; Tue, 02 Jan 2024 16:43:02 +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 1rKhr1-00FBKK-7S for pgsql-general@lists.postgresql.org; Tue, 02 Jan 2024 16:43:01 +0000 Received: from compute4.internal (compute4.nyi.internal [10.202.2.44]) by mailout.nyi.internal (Postfix) with ESMTP id 281ED5C00E7; Tue, 2 Jan 2024 11:42:58 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute4.internal (MEProxy); Tue, 02 Jan 2024 11:42:58 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc: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=1704213778; x=1704300178; bh=X3vJGKvxQWTvgKo0lNkGOxqrLWS8SAz7Kx9AcKU5XMg=; b= DMH02CIDlKj6uck2r9PDq40JuOLgeFom2rsxBCAv3IRJ1o/EMRvo8lLQSprsKtyM Hr8FO2jn9XzLam9VznPPWVcEVWCTbmlv6Rb6uTgj2znp1YTROdOQcP1ZJECNJ/R+ yIWHfaHJCElkAMgXDXPCH1aE+lNjN/GeHfU5J73FWkI9hooHvijZCpe05GsT8WM1 tOWLXgPHnyVmthf3fiICdIi6Y741dxh6e9HdeU8nUl+w3WQeRDJvRaF22Jcehula ZyKYSO36NkoFgiUc0YMt7TKq2fstrTng8UvZsqnslTsB6YnGip797zEiMeTNiq42 NG7ssHpwwk4pOvRQqqGf4w== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc: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=1704213778; x= 1704300178; bh=X3vJGKvxQWTvgKo0lNkGOxqrLWS8SAz7Kx9AcKU5XMg=; b=b Tg8lZocVc1rAD0cHGWpfDjBiTaDcL2zp1DW7F1DY0nwOew4KrEaNiS4uriHwNTb8 2wNTWI8JfgLxPKRUIdxRaTd4KJK9zskRbj5BCo19O4fEvUDpNub3qdxkUtDIcgQx 6larJl98+yvdnDJK3NedDRRCagBDikwZtsXQW4tGmU8Wn2L0vSEnGfSOBMDYtb3C lSVHarSz7foDXHeYKove0Td8Gqh0Tj1ahYNqWv49Sp8tFpUEHDVEVJ+1vl6gyZxE 174ywBAzRtYZ5sYmxf1DaWE5IUARzDoJDX10LhGSH3sx7Vdg23cezM6Qcb+oXD2x EMPutM+u6G4spCRBw4uMg== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrvdegfedgvdefucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvvehfhfgjtgfgsehtkeertddtvdejnecuhfhrohhmpeetughr ihgrnhcumfhlrghvvghruceorggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtg homheqnecuggftrfgrthhtvghrnhepgfdufeekhfevfeelveeiueevhedvuddukeduvddv lefhueeuieejtdeuvdevvdeunecuffhomhgrihhnpehpohhsthhgrhgvshhqlhdrohhrgh enucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrughr ihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Tue, 2 Jan 2024 11:42:57 -0500 (EST) Message-ID: <8dd13827-bb84-467c-ba47-d23de63ad8d5@aklaver.com> Date: Tue, 2 Jan 2024 08:42:56 -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 Cc: pgsql-general@lists.postgresql.org References: From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 1/2/24 08:15, Dominique Devienne wrote: > On Tue, Jan 2, 2024 at 5:09 PM Adrian Klaver > wrote: > > On 1/2/24 07:24, Dominique Devienne wrote: > > 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: > > https://www.postgresql.org/docs/current/sql-set-role.html > > > "[...], if the role was granted WITH SET TRUE [...]" > > > That seems to be the important part of your RFTM answer, which is > apparently new in v16. There also seems to an override: https://www.postgresql.org/docs/16/runtime-config-client.html#GUC-CREATEROLE-SELF-GRANT "createrole_self_grant (string) If a user who has CREATEROLE but not SUPERUSER creates a role, and if this is set to a non-empty value, the newly-created role will be granted to the creating user with the options specified. The value must be set, inherit, or a comma-separated list of these. The default value is an empty string, which disables the feature. The purpose of this option is to allow a CREATEROLE user who is not a superuser to automatically inherit, or automatically gain the ability to SET ROLE to, any created users. Since a CREATEROLE user is always implicitly granted ADMIN OPTION on created roles, that user could always execute a GRANT statement that would achieve the same effect as this setting. However, it can be convenient for usability reasons if the grant happens automatically. A superuser automatically inherits the privileges of every role and can always SET ROLE to any role, and this setting can be used to produce a similar behavior for CREATEROLE users for users which they create. " -- Adrian Klaver adrian.klaver@aklaver.com