On 10/27/22 17:20, Bryn Llewellyn wrote:
david.g.johnston@xxxxxxxxx <mailto:david.g.johnston@xxxxxxxxx> wrote:
bryn@xxxxxxxxxxxx <mailto: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.
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@xxxxxxxxxxx
