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

On 10/27/22 17:20, Bryn Llewellyn wrote:
>> david(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>
>>> bryn(at)yugabyte(dot)com <mailto:bryn(at)yugabyte(dot)com> 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.

The above is not contributing to the below(pg_hba.conf) and would be
redundant any way as it just says OS user usr = Pg user usr and peer
means that anyway. The purpose of mapping would be to do something like
map OS user foo to PG user usr.

References:

https://www.postgresql.org/docs/current/auth-peer.html

"map

Allows for mapping between system and database user names. See
Section 21.2 for details.
"

Section 21.2

"The map-name is an arbitrary name that will be used to refer to this
mapping in pg_hba.conf."

This example below id for the ident auth method but the same syntax
applies to peer.

https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

# TYPE DATABASE USER ADDRESS METHOD
host all all 192.168.0.0/16 ident
map=omicron

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

As noted above your pg_ident.conf will not do anything for the above. It
will work though if you are logged in as OS user usr as it will connect
as PG user usr.

>
> (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.
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com