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

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.klaver@aklaver.com