Search Postgresql Archives

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 Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux