Re: CASE CLOSED... Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
От | Adrian Klaver |
---|---|
Тема | Re: CASE CLOSED... Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should |
Дата | |
Msg-id | 6f13d004-2c48-1801-db3b-b41050193dee@aklaver.com обсуждение исходный текст |
Ответ на | CASE CLOSED... Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should (Bryn Llewellyn <bryn@yugabyte.com>) |
Ответы |
Re: CASE CLOSED... Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
|
Список | 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
В списке pgsql-general по дате отправления:
Предыдущее
От: Andreas 'ads' ScherbaumДата:
Сообщение: PGSQL Phriday #002: PostgreSQL Backup and Restore
Следующее
От: Bryn LlewellynДата:
Сообщение: Re: CASE CLOSED... Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should