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.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com> wrote:
>>
>>> bryn@yugabyte.com <mailto:bryn@yugabyte.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.klaver@aklaver.com