From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Bryn Llewellyn <bryn@yugabyte.com>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Ian Lawrence Barwick <barwick@gmail.com>
Cc: pgsql-general list <pgsql-general@lists.postgresql.org>
Subject: Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all
Date: Thu, 27 Oct 2022 17:51:49 -0700
Message-ID: <e75abfa8-72af-701c-cf6f-5336a1a35c92@aklaver.com> (raw)
In-Reply-To: <B1B93EA7-7C3A-4E2B-8B01-61411892D61A@yugabyte.com>
References: <8A2C8B1B-470B-42F9-9694-C1EDDAC06266@yugabyte.com>
<CAKFQuwYHLCMpLgPbBC7idoomoKwrgrjGxOnd+D1CXAQtf3DHcg@mail.gmail.com>
<CAB8KJ=hvD3J-FCzP2Ley7nux9Kh5a8gsZCg1jV6X8rSJbCERCg@mail.gmail.com>
<5C47A318-F265-4A64-B306-C4019F499DB8@yugabyte.com>
<CAKFQuwavAV9D0Xh_ScTg4FTQkQvWsQhyVJYAOg9aVzbqa85ixg@mail.gmail.com>
<B1B93EA7-7C3A-4E2B-8B01-61411892D61A@yugabyte.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: adrian.klaver@aklaver.com, bryn@yugabyte.com, david.g.johnston@gmail.com, barwick@gmail.com
Subject: Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all
In-Reply-To: <e75abfa8-72af-701c-cf6f-5336a1a35c92@aklaver.com>
References: <8A2C8B1B-470B-42F9-9694-C1EDDAC06266@yugabyte.com> <CAKFQuwYHLCMpLgPbBC7idoomoKwrgrjGxOnd+D1CXAQtf3DHcg@mail.gmail.com> <CAB8KJ=hvD3J-FCzP2Ley7nux9Kh5a8gsZCg1jV6X8rSJbCERCg@mail.gmail.com> <5C47A318-F265-4A64-B306-C4019F499DB8@yugabyte.com> <CAKFQuwavAV9D0Xh_ScTg4FTQkQvWsQhyVJYAOg9aVzbqa85ixg@mail.gmail.com> <B1B93EA7-7C3A-4E2B-8B01-61411892D61A@yugabyte.com> <e75abfa8-72af-701c-cf6f-5336a1a35c92@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox