Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

jeremy@xxxxxxxxxxxxxx wrote:

bryn@xxxxxxxxxxxx wrote:

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.

I don't believe this is correct.  psql is using libpq.  A hint to that is given in the section under the \c command:

When the command neither specifies nor reuses a particular parameter, the libpq default is used.

The libpq docs show more info on the connection parameters (https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS)

If not specified, both the user and the dbname default to the name of your operating system user.  So, this could work if your operating system user, the database name, and the database username are all postgres, but they could also all be bob.

As to the password requirement - this depends on the settings in pg_hba.conf.  You could set all connections to trust and then no one would need a password, but I wouldn't recommend that.

Thank you for this extra information, Jeremy. I'm afraid that what I wrote was insufficiently precise. I should have added these riders:

(1) My O/S env is in a brand new VM (it happens to be Parallels on my Big Sur Mac Book) that was created by a single button press that selected Ubuntu 20.04. (I followed that with what I hope is only benign customization for terminal colors and the like.)

(2) My PG regime is what I end up with in this VM immediately following the use of "apt install postgresql-11". (There's a good reason why I want that old version. I hope that its age isn't a distraction here.) Notably, the installation flow offers no opportunity to express choices.

(3) This gives me the PG software (largely owned by the O/S user "postgres", but with some programs owned by "root") and an already started cluster.

(4) I am able to start a plsql session, when my O/S user is "postgres" simply by typing the bare command "psql". Yes, I'm implicitly selecting various libpq default values—just as you described. But those defaults don't include a default for the password.

(5) When I start a session in this way, I see that I have this regime: a single role with the name "postgres" and the status "superuser"; and a single database non-template database also with the name "postgres", together with the usual "template1" and "template2". Further, this query (when connected to the "postgres" database):

select nspname
from pg_namespace n inner join pg_roles r on n.nspowner = r.oid
where r.rolname = 'postgres'

order by 1;

shows me this:

pg_toast
pg_temp_1
pg_toast_temp_1
pg_catalog
public
information_schema

When I asked how to refer to this clearly special cluster-role, David said:

Don't think it's documented but I like "bootstrap user"

and Ian said:

"bootstrap superuser" is also mentioned.

Meanwhile, in a separate thread, Adrian pointed me to the "initdb" doc (and command line help) where the term "database superuser" is used.

This means that I'm so far denied the possibility to use a single term that everybody agrees on. I may as well call it the "catalog owning role" here because at least that term is unambiguously descriptive.

I stated in my reply to Adrian that  I had formed this hypothesis (reworded slightly here).

When he environment is what I described at the start (which env. brings a "pg_hba.conf" file that requires password authentication by NOT specifying "trust"),

«

You can start a session without specifying the name of the cluster role as which to authorize, its password, and the name of the database to which to connect, ONLY when these things are true:

1. The within-cluster catalog-owning role has a certain name, say "pg_system" (or "bob").

2.  The O/S user that owns (most of) the O/S presence of the cluster and the software that accesses it has the identical name "pg_system" (or "bob").

3. The current O/S user when you make the attempt to connect is "pg_system" (or "bob").

»

I want to know if my hypothesis is correct. And, more importantly, I want to know where I can read a nicely written linear account of what *is* correct tha defines and then uses the official terms of art.