Re: CASE CLOSED... Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should

On 10/30/22 21:01, Bryn Llewellyn wrote:

See comments inline.

> *INTRODUCTION
>
> *Thanks to all who've helped me on this topic. Forgive me if I left out
> anybody on the "To" list.
>
> I suppose that I should have explained my use case more carefully. I did
> sketch it earlier on. But, not surprisingly, this got lost in the noise.
> I was afraid of being accused of writing too much, and so I kept my
> account short. Maybe too much so. Anyway, I've written it up more fully
> at the end. Feel free to ignore that account.
>
> Very briefly, I find the notion appealing that you can authorize a
> client session as "postgres" (using this actual role name to denote the
> cluster's bootstrap superuser) by authorizing an O/S session on the

Unless you are using a different package manager, say Postgres.app:

https://postgresapp.com/

User your system user name

> machine that hosts the cluster's data and the software that manages it
> without needing a (second) password because being able to log in as the
> right O/S user is considered enough of a check. I'll call this O/S user
> "postgres", too, recognizing the common convention and to save myself
> some typing. This allows the possibility to set the password for the
> "postgres" cluster-role to NULL so that you MUST use the O/S prompt to
> start a session as this role. In other words, make it such that "local",
> "peer" authentication is the ONLY way to start a session as the
> "postgres" role". (This would echo a very popular, highly
> recommended, practice  with Oracle Database and its corresponding SYS
> database user.)
>
> "Local", "peer" authentication is actually essential when you install PG
> on Ubuntu because the "apt install postgresql-11" flow (at least) offers
> no opportunity for user input and finishes up with an already-started
> cluster that has password authentication turned on (using the "md5"
> method). But the password is a secret. So the only way to make progress
> its to start with this:

It is not a secret, it does not exist. In other words it is never set as
that is left for the DBA to do.

>
> psql -c " alter role postgres with password 'x' ";
>
> "Local", "peer" authentication is also a useful backdoor (even when a
> NOT NULL role password is defined) for the case that a human being
> forgets the password that allows starting a session as the "postgres"
> role from a client machine.

Actually on the server machine as 'local' is a socket connection.

>
> As an extension of this thinking, I've resolved to adopt the practice
> recommendation from the doc always to use a dedicated, slightly junior,
> role for provisioning databases and roles. I want to call this role
> "clstr$mgr". And, yes, I do want that dollar sign in place. I explain
> why below. The practice goes hand-in-hand with keeping the password that
> allows starting a session as the "postgres" role a very closely guarded
> secret. This means that the people who know the password that allows
> starting a session as the "clstr$mgr" role will NOT know the password
> that allows starting a session as the "postgres" role.

Good idea.

> *THE SOLUTION
>
> *I now have an end-to-end solution where I can, for example, "ssh" to
> the cluster's host machine as the O/S user "clstr_mgr" and simply type
> "psql" at the O/S prompt, as soon as I'm in, to take me to a session
> where this:
>
> select current_database()||' > '||session_user as "Where/who ami I";
>
> shows this:
>
>    Where/who ami I
> ----------------------
>  postgres > clstr$mgr
>
> I actually have no requirement to elide the database name or the role
> name. The only thing I *require* is not to need a second password. But
> Peter showed me how—so why not follow his plan? It's a nice, albeit
> small, usability benefit. Here's how I got there.
>
> 1. Create the database role
> ---------------------------
>
> create role clstr$mgr with
>   nosuperuser
>   createrole
>   createdb
>   noreplication
>   nobypassrls
>   connection limit -1
>   login password null;
>
> 2. Create the partner O/S user
> ------------------------------
>
> I use "sudo" from any starting place that allows this. I (with another
> hat on) have to be allowed to do this, also, for, e.g, "systemctl start
> postgresql" and its cousins.
>
> sudo adduser clstr_mgr
>
> Then (as per Peter) I put this in the ".bashrc" for the O/S user
> "clstr_mgr":
>
> export  PGDATABASE='postgres'
> export      PGUSER='clstr$mgr'
>
> 3. Set up the config files
> --------------------------
>
> Here's (the relevant extract from) my "pg_hba.conf" file:
>
> # TYPE  DATABASE  USER            METHOD  [auth-options]
> # ----  --------  --------------  ------  ----------------------------
>   local all       postgres        peer
>   local all       "clstr$mgr"     peer    map=bllewell
>   local all       all             peer
>
> And here's my "pg_ident.conf" file in its entirety:
>
> # MAPNAME   SYSTEM-USERNAME  PG-USERNAME
> # --------  ---------------  -----------
>   bllewell  clstr_mgr        "clstr$mgr"

Thumbs up.

>
> Regard my name, "bllewell", as just a placeholder for something more
> suitable if I ever use this for real.
>
> And that's it!
>
> Of course, these two longer forms work too. This:
>
> psql -h localhost -p 5432 -d postgres -U 'clstr$mgr'
>
> But this DOES require the role's password. So I should really say that
> it works only when I set a NOT NULL password for the role—and so it
> doesn't suit my purpose.
>
> This, on the other hand:
>
> psql -d postgres -U 'clstr$mgr'
>
> calls for "local", "peer" authentication as so it does NOT require a
> password. That would be enough for me. But, naturally, and now that it's
> working. I prefer the Peter-inspired bare "psql".

Personally, I use longer forms like above as a form of explicit is
better then implicit. There are no end of posts to this list where the
issue was someone or something had changed a 'hidden' value in a env
variable or conf file could not connect or connected to wrong cluster
and/or database.

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