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>]

david.g.johnston@xxxxxxxxx wrote:

bryn@xxxxxxxxxxxx wrote:

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)

No, it does not.  It denotes only the PostgreSQL role.  "service user" is probably a better term for the O/S side of things.  Though, frankly, aside from trying to distinguish things when talking about logging in, the necessity to even care about the O/S user is fairly minimal.

[about your "usr" example] just create a database named "usr" and you won't get the "database usr not found" error message anymore and the login will succeed.

Thank you very much David. The scales have now finally fallen from my eyes. I know now that in order to be able to start a client session from the O/S of the machine where the PG software and cluster live, without needing to supply a password even when "pg_hba.conf" asks for password authentication, it's sufficient to do this (using my "usr" example):

(0) Simply leave the regime in place where the catalog-owning role is called "postgres" and the cluster's data files and other config files are owned by postgres.

(1) create a new database role thus (where "password null" is just so that I can prove a point here):

create role usr with login password null;

(2) Add this line under the existing final comment in the shipped copy of "pg_ident.conf" thus:

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
usr             usr                     usr          # Added by Bryn

(It seems that I could set the first field of this line to "dog"—but I won't test that.

(3) Add this line between the existing two in the shipped copy of "pg_hba.conf" thus:

local   all             postgres                                peer # See the essay at the start.
local   all             usr                                     peer # Added by Bryn
local   all             all                                     peer

(My copy of this file specifies "md5" and not "trust".)

I'd've thought that "all" would mean any O/S user existing, or yet-to-be-created. But the comment in the shipped "pg_hba.conf" says this:

# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
...
# Database administrative login by Unix domain socket
local   all             postgres                                peer

(So two terms for the one notion just a couple of lines apart!) I'll do the empirical test presently. Anyway, with these conditions met, I can "su usr" and then start a session like this:

psql -d postgres

Yes, your point about what artifacts exist the moment after "initdb" finishes is taken. So I finished my test by (after authorizing as "postgres") creating a database "usr" and granting "connect" on it to "usr".) Then I could create a new session from the O/S prompt when "whoami" shows "user" with the bare "psql"—just as I could the moment after the PG install finished from the O/S prompt when "whoami" shows "postgres".

I did think that I'd tried all this at the outset. But clearly I must've missed one of those steps or done a typo.