Re: Seeking practice recommendation: is there ever a use case to have two or more superusers? - Mailing list pgsql-general

> adrian.klaver@aklaver.com wrote:
>
>> bryn@yugabyte.com wrote:
>>
>> Consider this wording. It also uses “good practice”.
>>
>> «
>> It is good practice to limit the number of superuser roles that exist in a cluster to exactly one: the inevitable
bootstrapsuperuser. This recognizes the fact that, once the initial configuration of a cluster has been done
immediatelyafter its creation (which configuration is done while still in self-imposed single-user mode), there are
thenvery few, and infrequent, tasks that require the power of the superuser role. 
>> »
>>
>> Nobody supports it!
>
> ...why the "Nobody supports it!" statement for a recommendation that only appeared at the same time? I for one have a
poorrecord 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
thatdoing so brings. Rather, you should realize that there are hardly any tasks that cannot be carried out by an
appropriatelyconfigured role with "nosuperuser”. 


The essential content of each (what I wrote in my opening post and what stands between « ... » above) is the same:
allowmaximum 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,
becomecontroversial. 

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
responsiblefor creating the cluster gets their personal user credentials installed into the cluster as superuser and
fromthat point on never uses postgres.   


That’s actionable advice. I mentioned that I had implemented that scheme and then, later, abandoned it. I can easily
re-implementit. 

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
criticalhere—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.
(Havingsaid 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
sampleof drop targets.) This seems to be a Very Good Thing. But the fact that this is the behavior makes me wonder what
harmcan be done by a session that authorizes as the bootstrap superuser that cannot be done by a session that
authorizesas a regular superuser. I'll try to find out.