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 1oxHBU-0008CE-4K for pgsql-general@arkaria.postgresql.org; Tue, 22 Nov 2022 00:30:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oxHBS-0007nf-Ut for pgsql-general@arkaria.postgresql.org; Tue, 22 Nov 2022 00:30:42 +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 1oxHBR-0007my-Kt for pgsql-general@lists.postgresql.org; Tue, 22 Nov 2022 00:30:42 +0000 Received: from out1-smtp.messagingengine.com ([66.111.4.25]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oxHBO-0002Dq-KL for pgsql-general@lists.postgresql.org; Tue, 22 Nov 2022 00:30:40 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailout.nyi.internal (Postfix) with ESMTP id 0B3995C01B1; Mon, 21 Nov 2022 19:30:38 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute2.internal (MEProxy); Mon, 21 Nov 2022 19:30:38 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-transfer-encoding:content-type:date:date:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to; s=fm3; t=1669077038; x= 1669163438; bh=HxBwnO2vL6BB1ZBEqxT92YzrWVYJ6xDYMZgFCTZ7HIY=; b=1 7Pd3sK9ViuKD9t6JTCc1YvEx9fsl9N4OyRxbYBrm7eqWAZen63h/L22dS9UgcpJn wu/T+iQX51zT4KPGHxFb4J2/kPP7iWKHnu6iV3JTp7oiqBBdnEBNgDL9iQZNeQSG oUU9OiGQeFrtz4BU38bmmMb4T5D9/HGZMszmbUxnCiz4i8PkskmuooJAgg6gvDeO xfkL07Bo2/JuYJHTsA09i6RX3SYbo9QLa7e5cjrqU0Ui523o0aPxlXVwTH+Udmo0 /NwBLpz9eS7Phi/9HnsRG1oUGw72UBnjTP/3RiAbn6Wmwv3Rr4qu4O0R6NicNGDV 5xJ5jjggjtr90CtziPNgQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t=1669077038; x= 1669163438; bh=HxBwnO2vL6BB1ZBEqxT92YzrWVYJ6xDYMZgFCTZ7HIY=; b=O SVSKfr+F0I17AGaDQDOBEWJOI1oJfKMpF0E883c6wU0MEOXtVfvyWkU//eJLx8VB qDpvricHpRBZp3VBPEhrt+/8HbCY665+8m2emPK0FoTSnD08g7NnByCGNnFxWDQS rM/TSKm/WeIciGXiEqhAdnHJ8NvIfn6QyiNCppLpcp6DNg36pmXLqVn3SrmmFMC/ np/o6+m8psheobeYUSdhOeEhVC9kvfWisVzAN9cmoSXEGeSsgZHazEzSUOM3riR6 1TK1xYW0fGelSaVr8VbQXXmaJHfMv9cMAeIBlYCqb5CMDeYTSJRF1fwRVPXQHhLT q/ewrbKin4iWQlpDb8vdA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvgedrheejgddvfecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpefkffggfgfuvfevfhfhjggtgfesthekredttdefjeenucfhrhhomheptegurhhi rghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtoh hmqeenucggtffrrghtthgvrhhnpefhgeevkeefleffvdfgvdefffdtueejheehueeifffh feeihfdvhffgtefgvdeugfenucffohhmrghinhepphhoshhtghhrvghsqhhlrdhorhhgne cuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprggurhhi rghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Mon, 21 Nov 2022 19:30:37 -0500 (EST) Message-ID: Date: Mon, 21 Nov 2022 16:30:36 -0800 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.5.0 Subject: Re: Seeking practice recommendation: is there ever a use case to have two or more superusers? To: Bryn Llewellyn Cc: "David G. Johnston" , pgsql-general list References: <290EF7B8-D150-4AE1-8FFE-A38912CD1A8B@yugabyte.com> <20221118200835.sgva7b6wgg4w7py7@hjp.at> <0B653B38-2D7D-45D6-B87F-DD07200EA676@yugabyte.com> <20221118221305.ku2ibj2bowahzn2y@hjp.at> <11d24370-18c6-f736-85db-e9ae1814bb64@gmail.com> <20221119085225.uivmxue2gef5utv2@hjp.at> <40623F5C-5B99-4AFD-9AA6-ABFFE6B17606@yugabyte.com> <6f5bb172-eea8-3456-60ab-0dd332b89d56@aklaver.com> <4380c01d-a182-8c13-f4e2-278ba8a3cf01@aklaver.com> <9B82247D-3F56-47C1-A918-18E2E8DEDFAB@yugabyte.com> <487898A1-46F5-4351-9BEA-D7170BDBC9D5@yugabyte.com> Content-Language: en-US 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 11/21/22 15:05, Bryn Llewellyn wrote: >> adrian.klaver@aklaver.com wrote: >> ...why the "Nobody supports it!" statement for a recommendation that only appeared at the same time? I for one have a poor record of mind reading and/or predicting the future:) > > Here’s what I wrote in the post that started this thread, archived at this URL: > > https://www.postgresql.org/message-id/290EF7B8-D150-4AE1-8FFE-A38912CD1A8B@yugabyte.com > >> The implication is clear: you should allow a cluster to have just a single superuser, the inevitable bootstrap superuser, and you should think very carefully indeed before ever starting a session as this role because of the risks that doing so brings. Rather, you should realize that there are hardly any tasks that cannot be carried out by an appropriately configured role with "nosuperuser”. > > > The essential content of each (what I wrote in my opening post and what stands between « ... » above) is the same: allow maximum one superuser. Each is a strawman. And, as such, carries its own implicit invitation for challenge or support. The outcome was all challenge and no support. I don’t know why observing that this was the outcome has, itself, become controversial. You must be reading a different thread. What I saw in the replies was people answering '...is there ever a use case to have two or more superusers?' with, maybe but in the end it is up to you to decide what works in your case. > > In fact, David Johnston did unequivocally challenge my strawman a couple of turns back, thus: > >> no one is supposed to login to the database cluster using the postgres role. Period. Upon initialization whomever is responsible for creating the cluster gets their personal user credentials installed into the cluster as superuser and from that point on never uses postgres. You left out the preface to the above, 'My policy would be that ...` And the equivocal additions later in the post: "I suppose the suggestion I would be willing to consider is: only have the postgres superuser, never grant superuser to login roles explicitly, instead if those persons require superuser grant them membership in the postgres role." and " So yes I, like everyone else, is going to end up forming their own generalities. Ideas that I cannot wholly discredit as bad, but that don't fit into my generality, get the "if the specific circumstances warrant it" treatment. My own presuppositions ultimately should get the same treatment by whomever is implementing such policies." > > > That’s actionable advice. I mentioned that I had implemented that scheme and then, later, abandoned it. I can easily re-implement it. > > Because PG allows a cluster to have as many superusers as you please, and because any one of these can create or drop another, any convention in this space needs some extra mechanisms to enforce it.. > > I believe that the fact that a superuser's ability to start a session can be limited by what the "hba_file" says is critical here—together with the fact that the ability to edit this file is governed by the regime of O/S users and file privileges. Maybe this is the key to the effectively tamper-proof implementation of the scheme that David recommends. (Having said this, there's always the "set role" backdoor.) > > There's also the caveat that a "drop" attempt by a superuser for a single object owned by the bootstrap superuser (say, the "pg_catalog.pg_terminate_backend()" function) in some database causes an error with the message "cannot drop function... because it is required by the database system". (At least, this is what my tests have shown with a smallish sample of drop targets.) This seems to be a Very Good Thing. But the fact that this is the behavior makes me wonder what harm can be done by a session that authorizes as the bootstrap superuser that cannot be done by a session that authorizes as a regular superuser. I'll try to find out. Superuser is superuser, there is no magic associated with the bootstrap superuser. FYI, the answer is won't make a difference. -- Adrian Klaver adrian.klaver@aklaver.com