Re: Built-in connection pooling

On 20.04.2018 03:14, Tatsuo Ishii wrote:
>> On Fri, Apr 20, 2018 at 07:58:00AM +0900, Tatsuo Ishii wrote:
>>> Yeah. Since SCRAM auth is implemented, some connection poolers
>>> including Pgpool-II are struggling to adopt it.
>> Er, well. pgpool is also taking advantage of MD5 weaknesses... While
>> SCRAM fixes this class of problems, and channel binding actually makes
>> this harder for poolers to deal with.
> One of Pgpool-II developers Usama are working hard to re-implement
> SCRAM auth for upcoming Pgpool-II 4.0: i.e. storing passwords (of
> course in some encrypted form) in Pgpool-II.
>

Just want to notice that authentication is are where I have completely
no experience.
So any suggestions or help  in developing right authentication mechanism
for built-in connection pooling is welcome.

Right authentication of pooled session by shared backend is performed in
the same way as by normal (dedicated) Postgres backend.
Postmaster just transfer accepted socket to one of the workers
(backends) and it performs authentication in normal way.
It actually means that all sessions scheduled to the same worker should
access the same database under the same user.
Accepting connections to different databases/users right now is
supported by making it possible to create several session pools and
binding each session pool to its own port at which postmaster will
accept connections to this page pool.

As alternative approach I considered spawning separate "authentication"
process (or do it in postmaster), which will process startup package and
only after it schedule session to one of the workers. But such policy is
much more difficult to implement and it is unclear how to map
database/user pairs to worker backends.

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company