Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all
(David and Ian, I'm resending this because, I see that I managed to omit "pgsql-general@lists.postgresql.org" from the addressee list. So, of course, it didn't show up in the "pgsql-general" archive.)
barwick@gmail.com wrote:david.g.johnston@gmail.com:bryn@yugabyte.com wrote:
The descriptive designation "the role that owns the SQL part of the implementation of PostgreSQL" is too much of a mouthful for daily use.
Don't think it's documented but I like "bootstrap user" which I've seen bandied about here a bit. It isn't that special but if the bootstrap user name and o/s user name are not the same name then you've broken an almost universal convention that exists to make stuff like logging in with peer authentication work better.
"bootstrap superuser" is also mentioned a few times in the [upcoming] docs, see e.g.
https://www.postgresql.org/docs/devel/sql-grant.html
This recent commit: e530be2c5ce77475d56ccf8f4e0c4872b666ad5f [1] might also be of interest to anyone considering the "special-ness" of this role.
[1] https://git.postgresql.org/gitweb/?p%3Dpostgresql.git;a%3Dcommit;h%3De530be2c5ce77475d56ccf8f4e0c4872b666ad5f
Thanks. David and Ian. I'll take this:
If GRANTED BY is specified, the grant is recorded as having been done by the specified role. A user can only attribute a grant to another role if they possess the privileges of that role. The role recorded as the grantor must have ADMIN OPTION on the target role, unless it is the bootstrap superuser. When a grant is recorded as having a grantor other than the bootstrap superuser, it depends on the grantor continuing to possess ADMIN OPTION on the role; so, if ADMIN OPTION is revoked, dependent grants must be revoked as well.
from an upcoming version of the "grant" statement doc, to be a sufficient establishment of the canonical status of the term of art that I sought. I'll adopt the term "bootstrap superuser" (and not plain "bootstrap user") and I'll assume that everybody on this list (at least anybody who might answer my questions) shares the same, and immediate, understanding of the term—which implies this:
This invariant must hold if an "ordinary" within-cluster superuser is to qualify as the cluster's "bootstrap superuser":
the name of the bootstrap superuser's within-cluster role
AND
the name of the O/S user that owns lots of (but not all*) the software files that define the PostgreSQL RDBMS, together with the various files that represent what users create
are identical.
[*] I see that, in my Ubuntu installation, critical programs like "postgres" itself, "initdb", "pg_ctl", "pg_dump" and so on are owned by "root".
The fact that the "bootstrap superuser" term of art denotes a matching pair of two principals (an O/S user and a within-cluster role) means that some sentences will require extra verbiage to identify which half of the pair the sentence treats. I'm open to suggestions. But I'll start with these these I'm corrected: the "bootstrap (regular) OS-user" and the "bootstrap within cluster superuser role". Sadly, the fact that "super" is baked into the term of art makes it difficult to name the O/S half of the phenomenon.
I can now characterize what I'd observed more clearly, thus: only a bootstrap super user (as defined above) can start a session without mentioning the name of the database to which to connect and the name of the within-cluster role to connect as—and without supplying a password. And it can do this only from as O/S session where the effective O/S user is the bootstrap superuser.
It seems, too, that one would be stupid to call the bootstrap superuser anything other than "postgres". Notice that this implies that the typical macOS regime (where my bootstrap super user is called "Bllewell" and has to be double-quoted in SQL, and yours is called "sagrawal") is, indeed, stupidly unconventional.
All this implies a little test. Here, I'll save typing by saying that my bootstrap superuser is called "postgres". I created a second database in a freshly created cluster called "x". And then, from the O/S, I tried this:
psql -d x
That worked fine. But, having said this, it would seem that it would be so very unconventional (given that you've already agreed to call your bootstrap superuser "postgres", not to make a database called "postgres" available too. (I tested that by dropping my "postgres" database. (This is the freedom that a VM with a nice snapshot together with the ability to start afresh with "initdb" brings.) Now, the bare "psql" causes the error "FATAL: database "postgres" does not exist".
В списке pgsql-general по дате отправления:
Предыдущее
От: RonДата:
Сообщение: Re: Index corruption revealed after upgrade to 11.17, could date back to at least 11.12
Следующее
От: Bryn LlewellynДата:
Сообщение: Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all