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 1owBkp-0004SZ-9p for pgsql-general@arkaria.postgresql.org; Sat, 19 Nov 2022 00:30:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1owBko-0003Lw-20 for pgsql-general@arkaria.postgresql.org; Sat, 19 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 1owBkn-0003Lk-Gj for pgsql-general@lists.postgresql.org; Sat, 19 Nov 2022 00:30:41 +0000 Received: from mail-qv1-xf2b.google.com ([2607:f8b0:4864:20::f2b]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1owBkj-00080s-Nx for pgsql-general@lists.postgresql.org; Sat, 19 Nov 2022 00:30:40 +0000 Received: by mail-qv1-xf2b.google.com with SMTP id c8so4440633qvn.10 for ; Fri, 18 Nov 2022 16:30:37 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:content-language:references:to:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=IxKoDxA/0JWrNNVTbeq7UX28yvhST9vEokbs+IIK5yY=; b=kgf2PbQRmv+7VxcH3J9wrUQ39FOA4egCAGUIMYsREKis8JcuW2mepyYc5dWc068b8L 2L1Ahg+u2ZChIDntLj8heZ2/t+O9hFnphQYaM+EmUpAuXH9WKpwb3uyp5vVGhaCh0EwF drHlKSHmiuBnNvraJ8OrPZjjq5qqTvQGwY/+cWapxTuhS7Z2V3F2mWcmpW5DhJNmzBXZ EpPzjoPsLJ9MXBI22DjPad/tvQsWAss+0uHhgGMp0f3sNGBhoEYnw1UqIzzqcXNoNbCo nRyqFYVyU9y7APSpu8z1otxWrvbySRizdj3iFV4MYTkJlftNA0cvELcO6MINXBCCQjsk klWA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:content-language:references:to:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=IxKoDxA/0JWrNNVTbeq7UX28yvhST9vEokbs+IIK5yY=; b=K3ik8nuxlovTJPC3HrGNXCkY/P4HZx8dhfM73Rljd+lG1PjBBzGpHs5k1zemTyzkh+ kC3DVo2F1HMZNuCEMXpbN5fC27TUi/5kd0PzZn203IIZo3aVPwvoXTtZqMbsF7PO/+xC RQ2uE4csy8AoqB5rc/8Zlq4Ii/sEAaWP8AQqNu8gvj84WLlantT+p4WrY++nCLlPv233 xyC63ISDUb2iF81bPSzbMbUmx2um7xRZPBEUEJxq+ULr+IoHeMIYGTylzJurfjI+umlD VEwsIEgBohp+esp++CWqGjtoYLKrpoSdlkJMZd6fQQo3RjPIObWSWqQ6ci+UaCS54keM VSwg== X-Gm-Message-State: ANoB5pn/F1sVLCZ6lEB4AoCO8dizRpHfcJOb0z4Eq1+cg3YxnPyaEJtI crdy5MzG3lFjr+hKnwgYSLU2TVPoIgS57A== X-Google-Smtp-Source: AA0mqf6OwCV7PhL/poIUjjtD55vGQ+DiBhYyZzzN5Kdp5XU8s7q03h3tsZ2QM2yicDuJxS3RDDZ7wg== X-Received: by 2002:a0c:f00b:0:b0:4c6:9348:51b6 with SMTP id z11-20020a0cf00b000000b004c6934851b6mr5224707qvk.70.1668817835796; Fri, 18 Nov 2022 16:30:35 -0800 (PST) Received: from [192.168.2.100] (pool-72-79-30-4.nwrknj.east.verizon.net. [72.79.30.4]) by smtp.gmail.com with ESMTPSA id o22-20020a05620a2a1600b006eeb3165554sm3527471qkp.19.2022.11.18.16.30.35 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 18 Nov 2022 16:30:35 -0800 (PST) Content-Type: multipart/alternative; boundary="------------wWhVc8gQiGFSzNocdRNq5Oa5" Message-ID: Date: Fri, 18 Nov 2022 19:30:34 -0500 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: pgsql-general@lists.postgresql.org References: <290EF7B8-D150-4AE1-8FFE-A38912CD1A8B@yugabyte.com> <20221118200835.sgva7b6wgg4w7py7@hjp.at> Content-Language: en-US From: Mladen Gogala In-Reply-To: <20221118200835.sgva7b6wgg4w7py7@hjp.at> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------wWhVc8gQiGFSzNocdRNq5Oa5 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 11/18/22 15:08, Peter J. Holzer wrote: > On 2022-11-17 11:36:15 -0800, Bryn Llewellyn wrote: >> The detail below leads to a simply stated question: >> >> Given that the bootstrap superuser must exist, is there ever a reason to create >> another role with "superuser"? >> >> My intuition tells me that the answer is a resounding "No!". > Is there ever a reason? Yes. Does that reason apply to you? I don't know. > Maybe, maybe not. > > >> — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — >> I wondered, at first, if it might be a good practice to create a >> second superuser, say "super" with "login", to alter the bootstrap >> superuser with "nologin", and then to use "super" on an "ordinary" >> daily basis for tasks that might need this. The thought was that this >> practice might protect the artifacts that the bootstrap superuser owns >> from damage. But this thought dissolved into thin air, before it was >> fully formed, on the realization that the unstoppable "super" could >> anyway do arbitrary damage to the bootstrap superuser's artifacts. > You could create additional superusers and restrict those to certain > databases and/or IP ranges. That probably won't stop an attacker (I can > think of at least one way to get around that and it's probably even > easier than I think) but it might prevent accidental damage. > > >> 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". > One important task that can AFAIK only be performed by superusers is the > creation of functions in untrusted languages like plpython3u and > plperlu. > > If your application uses functions in those languages you need a > superuser to install or upgrade it. > > hp > Yes, there is such a reason. On several servers that I have to work with, the user "postgres" is not a log-in user. In other words, I can't log in as "postgres", I can only log in as my own user and su to the user "postgres". All users must be authenticated by the LDAP system and the user "postgres" is not in the LDAP. Since I am lazy, I granted superuser privilege to my own user so that I don't have to switch to the user "postgres" every time I want to do something that affects everybody, like using "ALTER SYSTEM". I am also a member of the "dba" group on the Oracle servers, so that I can do "sqlplus / as sysdba" without switching to the user "oracle". It's the same thing. If I was to respond by a single word, the word would be "convenience". Or maybe "covfefe"? -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com --------------wWhVc8gQiGFSzNocdRNq5Oa5 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 11/18/22 15:08, Peter J. Holzer wrote:
On 2022-11-17 11:36:15 -0800, Bryn Llewellyn wrote:
The detail below leads to a simply stated question:

Given that the bootstrap superuser must exist, is there ever a reason to create
another role with "superuser"?

My intuition tells me that the answer is a resounding "No!".
Is there ever a reason? Yes. Does that reason apply to you? I don't know.
Maybe, maybe not.


— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — 
I wondered, at first, if it might be a good practice to create a
second superuser, say "super" with "login", to alter the bootstrap
superuser with "nologin", and then to use "super" on an "ordinary"
daily basis for tasks that might need this. The thought was that this
practice might protect the artifacts that the bootstrap superuser owns
from damage. But this thought dissolved into thin air, before it was
fully formed, on the realization that the unstoppable "super" could
anyway do arbitrary damage to the bootstrap superuser's artifacts.
You could create additional superusers and restrict those to certain
databases and/or IP ranges. That probably won't stop an attacker (I can
think of at least one way to get around that and it's probably even
easier than I think) but it might prevent accidental damage.


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".
One important task that can AFAIK only be performed by superusers is the
creation of functions in untrusted languages like plpython3u and
plperlu.

If your application uses functions in those languages you need a
superuser to install or upgrade it.

        hp

Yes, there is such a reason. On several servers that I have to work with, the user "postgres" is not a log-in user. In other words, I can't log in as "postgres", I can only log in as my own user and su to the user "postgres". All users must be authenticated by the LDAP system and the user "postgres" is not in the LDAP. Since I am lazy, I granted superuser privilege to my own user so that I don't have to switch to the user "postgres" every time I want to do something that affects everybody, like using "ALTER SYSTEM". I am also a member of the "dba" group on the Oracle servers, so that I can do "sqlplus / as sysdba" without switching to the user "oracle". It's the same thing. If I was to respond by a single word, the word would be "convenience". Or maybe "covfefe"?

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
--------------wWhVc8gQiGFSzNocdRNq5Oa5--