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@xxxxxxxxxxx
