Seeking practice recommendation: is there ever a use case to have two or more superusers? - Mailing list pgsql-general
| From | Bryn Llewellyn |
|---|---|
| Subject | Seeking practice recommendation: is there ever a use case to have two or more superusers? |
| Date | |
| Msg-id | 290EF7B8-D150-4AE1-8FFE-A38912CD1A8B@yugabyte.com Whole thread Raw |
| Responses |
Re: Seeking practice recommendation: is there ever a use case to have two or more superusers?
|
| List | pgsql-general |
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!".
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
*Detail*
reached the interim conclusion that the term "bootstrap superuser" is the generally accepted term of art for the superuser whose name you specify to "initdb" with its -U option when you create a new cluster. I said "interim" because the current PG doc has no glossary entry for this notion. (Sometimes, the explicit use of "initdb" is hidden behind a wrapper like "pg_createcluster". You use this after installing PG with "apt install postgresql" on Ubuntu. This detail is of no consequence for my question.)
The bootstrap superuser is uniquely special in that, in each database, it owns, for example, the "pg_catalog" and "information_schema" schemas and the objects in them. In other words, this role owns the SQL component of the implementation of the PG RDBMS and is essential to allow it to function.
However, you can create as many additional superusers as you please—and each is as unstoppable as all the others. 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.
Moreover, it seems that there are, anyway, no (or exceedingly few) tasks that ever need the power of a superuser. At the very least, there seems to be no defensible notion of "daily superuser tasks".
I noticed this in a recent post to this list by Christophe Petus:
The typical configuration is to not permit the PostgreSQL superuser to log in remotely. The database can be managed by a different, non-superuser role, including schema migrations.
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".
David Johnston strengthened this notion with his turn in the same thread:
You only need superuser once to configure the system in such a way, through role and grants and possibly default permissions, that from then on most everything an application user would want to do can be done by the role(s) you have created.
This thinking is reflected further in the "22.2. Role Attributes" section in the PG doc:
It is good practice to create a role that has the CREATEDB and CREATEROLE privileges, but is not a superuser, and then use this role for all routine management of databases and roles. This approach avoids the dangers of operating as a superuser for tasks that do not really require it.
*Yet more detail — only if you're interested*
I have a nicely working prototype that honors these principles. (I've mentioned it before.) It implements a "multitenancy by convention" scheme that avoids collisions of global names by following a naming convention that's enforced with "security definer" procedures for role provisioning.
Just as David described, I set up the scheme as a postlude to "initdb" by using a session authorized as the bootstrap superuser. This creates my "clstr$mgr" role with "createdb" and "createrole" and installs the role-provisioning procedures in a dedicated "mgr" schema in the customized "template1" database. (Both the "mgr" schema and the objects in it are owned by "clstr$mgr".) Once this set-up is done, no session needs ever agin to authorize as the bootstrap superuser—unless bug fixes or enhancements are needed to the implementation of the scheme itself. (But most of this patching could, anyway, be done by a session that authorizes as "clstr$mgr".) This patching would anyway require stopping all ordinary user sessions and doing the task in a self-imposed single-user mode. So it's most certainly natural to disallow authorizing a bootstrap superuser session remotely—just as Christophe said.
It's rare, even, to need to authorize as "clstr$mgr". This needed only to create and configure, or to drop, a tenant database. These operations are mechanical, and are therefore scripted. "Configure" here means creating a dedicated local manager role (local in the sense that it has "connect" only on the subject database). The local manager has no special role attributes and is empowered entirely by (uniquely) having "execute" on the role-provisioning "security definer" procedures.
pgsql-general by date: