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

> adrian.klaver@aklaver.com wrote:
>
>> bryn@yugabyte.com wrote:
>>
>>> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> wrote:
>>>
>>>> bryn@yugabyte.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
tothis list where the issue was someone or something had changed a 'hidden' value in a env variable or conf file could
notconnect 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
withoutexiting 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
connectionsto 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
needto 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
immediatelyafter 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-provisioningneeds by using the pre-installed “security definer” procedures. Even to the extend that they could
easilyrestore 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
thatteam.) 

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
thesetasks. But the idea that it’s simply impossible to start a session as one of these roles except by doing that
appealsto my sense of what “hardening means. Another choice is to be stricter about “postgres” than about
“clstr$mgs”—justas 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
passwordthe the config files that we’ve been discussing would allow them to use ONLY “local”, “peer” authentication.