Re: Seeking practice recommendation: is there ever a use case to have two or more superusers?

> hjp-pgsql(at)hjp(dot)at wrote:
>
> bryn(at)yugabyte(dot)com 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.

Thanks, Peter. I experimented with the notion of restricting a superuser to certain databases. My set-up has this in the “hba_file”:

# TYPE DATABASE USER METHOD [auth-options]
# ---- -------- -------------- ------ ----------------------------
local all postgres peer

So that gave me an easy way to test the notion. (This is a sandbox cluster in a VM. So I can do arbitrary destructive tests and recover in a heartbeat.) Before the test, this command at the O/S prompt (as the O/S user “postgres”):

psql -d d0 -U postgres

succeeded and took me where it specified. I changed the “hba_file” thus:

# TYPE DATABASE USER METHOD [auth-options]
# ---- -------- -------------- ------ ----------------------------
local postgres postgres peer

It had the effect that you promised. This command at the O/S prompt (as the O/S user “postgres”):

psql -d postgres -U postgres

succeeded. But this attempt:

psql -d d0 -U postgres

now failed with this error:

...no pg_hba.conf entry for host "[local]", user "postgres", database "d0"...

as promised by the doc. But this seems to achieve very little. Having authorized using the first command, I then did "drop database d0". I had no reason to think that it might fail. And it didn't. I see that it's careless to say that a superuser is *unstoppable*. But it seems that, as long as a superuser can start as session at all, then it can certainly do untold harm.

The experiment, then, doesn't seem to change the thinking. In other words, only a highly trusted person should know the password(s) needed to start a superuser session. This might well be done is a "four eyes" partnership, as such tasks often are, to increase the level of trust and responsibility that the organization relies upon. Given this, I cannot see how it would help increase the trust given to such people to have more than one superuser to connect as.

I don't see that the point about maintenance changes for functions written in untrusted languages changes anything. You already have the inevitable bootstrap superuser and, if the organization chooses, robust "four eyes" practices to govern how people use sessions that authorize as this role. You don't need a second superuser just because this kind of task is to be done a long time after the initial cluster creation and configuration.