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

> adrian(dot)klaver(at)aklaver(dot)com wrote:
>
>> bryn(at)yugabyte(dot)com wrote:
>>
>>> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com> wrote:
>>>
>>>> bryn(at)yugabyte(dot)com wrote:
>>>>
>>>> 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.
>>
>> This thinking extends, of course, to:
>>
>> psql -d postgres -U ‘postgres'
>>
>> having logged in as the O/S user "postgres". (And here, I can simply "set role" to "clstr$mgr" when I need to without exiting one session, logging in as a different O/S user, and then starting a new session.) But when I'm working interactively, I might well allow myself to type the bare minimum, on the fly, that gets the result.
>
> This implies that the only auth method you will be using is peer, is that correct? This also means that the only connections to the cluster will be done as local, is that correct?

I must stress that this is just an idea that I’m thinking about. I’m not committed to anything. At the very least, I’ll need to implement the complete convention-based multitenancy scheme that I sketched and try out some use cases.

The idea that informs this is that, maybe, sessions authorized as “postgres” or “clstr$mgr” would be needed only immediately after creating a new cluster to bootstrap the regime into place and to create, say, 100 empty databases.

Maybe, from time to time, it would be appropriate to patch the artifacts that implement the scheme. But that should be doable (with the usual discipline for making only compatible changes).

On a daily basis, the people who know the password for the “dNN$mgr” tenant database’s manager could meet all their role-provisioning needs by using the pre-installed “security definer” procedures. Even to the extend that they could easily restore it to the pristine state and start again. Or they could simply send an email to say they were done with it. And then the “clstr$mgr” guy would change the password and return it to the pool. (So another very rare task for that team.)

It might be too strict to force the “clstr$mgr” guys (and the “postgres” guys too) to “ssh” the to cluster’s host to do these tasks. But the idea that it’s simply impossible to start a session as one of these roles except by doing that appeals to my sense of what “hardening means. Another choice is to be stricter about “postgres” than about “clstr$mgs”—just as the doc talks about.

So, yes, if I still like it when it’s all working, then each of the “postgres” and “clstr$mgr” roles would have a NULL password the the config files that we’ve been discussing would allow them to use ONLY “local”, “peer” authentication.