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 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: 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. Further, if the "postgres" cluster-role's password is deliberately set to NULL, then there's no second password to keep safe—but you can, nevertheless, start a session as the "postgres" cluster-role from the O/S prompt where the cluster is hosted by authorizing as the "postgres" O/S user. I'll think more about this. But it seems that it might be a useful "hardening" notion in the general business of security practice to adopt this regime intentionally. 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. Of course, because what's sauce for the goose is sauce for the gander, I want to show that it's possible to implement the same practice for "clstr$mgr" sessions as for "postgres" sessions. In other words, to make it possible to start a session as the "clstr$mgr" role ONLY by using "local", "peer" authentication—but, obviously, via its own dedicated O/S user. However, Linux (at least) simply disallows O/S users that have a dollar sign in the name. That's where the idea of using a mapping from the O/S user "clstr_mgr" to the cluster role "clstr$mgr" comes from. (So if Linux had a different philosophy for user names, like it already has for file names, then I wouldn't be talking about mapping.) 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" 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". — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — MY ACTUAL USE CASE I want to implement a robust convention for multitenancy. It depends critically on a "local role" notion that is defined as follows. A local role can at least connect, and maybe do other things, to exactly one database. This convention allows each database to seem to be its own private world where I can, therefore, choose the names for my local roles without considering collision with the names of other local roles. My scheme is naïve. But it works. I enforce the rule that database names are short and sweet: "d0", "d1", and so on up to any integer following the "d". I use the "comment" statement to express the purpose of the database without trying to reflect this in the name. I could have made the database names pure integers. But I don't want to be burdened with double quoting the corresponding identifiers. Once I've authorized a session, of course, the name of the database doesn't matter. Next, I want to use role "nicknames" that you can choose freely as long as the nickname has only Lower-case latin letters, digits or underscores—with the familiar extra rule about how it can start. Notatbly, the nickname must not contain a dollar-sign. The real role name is then exemplified thus: "d42$mgr", "d42$client", "d42$what_ever", ... Here the first two names are special (and are set up by the provisioning of the tenant database itself). The others are all provisioned by "security definer" procedures that "dNN$mgr" has "execute" on. They use "current_database() to confine the scope of the roles they create by granting "connect" (and so on) only to that. Of course, the arguments to these procedures expect the nickname. And they generate the actual name behind the scenes. The role-provisioning procs are in a dedicated schema "mgr" that is brought by "template1". And they're owned by "clstr$mgr". I've seen the need, so far, for just one special role-provisioning proc that's owned by "postgres". This is needed for setting parameters that must be done by a superuser. Once a new tenant database (as I call it) has been provisioned by a session that authorized as "clstr$mgr", then such a session is no longer needed (except, maybe, later to drop the database). Of course, the whole regime has to be set up in a big bootstrap while the cluster is still new and (effectively) single-user. Some of this needs a "postgres" session. And some needs a "clstr$mgr" session. It doesn't harm usability to require that this bootstrapping (just like cluster creation itself) is done by working at the O/S prompt. The dollar-sign helps the convention because it makes the rule that governs the legality of a role nickname easy to state. And it doesn't matter if this is a theoretical portability problem because the scheme is oriented specifically to how multitenancy works in PG. Finally, the role provisioning procs grant each newly-created role to "clstr$admin". And then new local roles are granted to "dNN$mgr" so that, according to rank in the hierarchy, "clstr$mgr" can "set role" to ANY local role in ANY tenant database. And the "dNN#mgr" role for some tenant database "dNN" can "set role" to any local role in the tenant that it manages. The reason for liking the name "clstr$mgr" is obvious now: the more general form is "<scope>$<nickname>". If it weren't for the existing convention, I'd call the cluster bootstrap superuser "clstr$super". (I'm still tempted. I can see, now, how ro do this—thanks to everybody's help. But I fear that that this might be wroo unconventional to be wise.) <note> I could give up my dollar-sign idea for my naming convention and, instead, use (say) double-underscore as the separator for the two components of the "scope-nickname" template. Then "clstr__mgr" would be legal both as a role name and as an O/S user name. But this idea appeals to me less, aesthetically, than using the dollar-sign. </note>
