[pgpool-hackers: 2679] Proposal to add local authentication along with local user-database store in pgpool-II

[Muhammad Usama]

Hi,

Since PostgreSQL10 has recently added a support for SCRAM authentication
with future plans including its extension of channel binding. And because
of the nature of SCRAM and other more secure authentication methods like
ssl-certificate-authentication, Pgpool-II is not able to allow these
auth-methods because of its current authentication system design.
As almost all modern authentication methods are designed to guard against
man-in-middle kind of attacks and middleware applications like Pgpool-II
tries to exploit this very vulnerability to provide seamless authentication
to users by forwarding the credentials provided by clients application to
the backend servers. But fortunately or unfortunately with the modern auth
protocols it is becoming almost next to impossible and it is a need of time
to rethink the authentication system of Pgpool-II.

My proposal is to add a configurable feature in the Pgpool-II 3.8 to allow
it to have its own user-password database which it can use to authenticate
the clients connecting to Pgpool-II and also use the same to authenticate
the user with PostgreSQL backend.

Similar concept is also used by pgbouncer in form of authentication-file
which contains the user-password pairs and pgbouncer use it to authenticate
the connections with PostgreSQL backend and also the clients connecting to
pgbouncer.
https://pgbouncer.github.io/config.html#authentication-file-format

Also Pgpool-II already uses the password file for md5 authentication so
this enhancement would not be a radical change to the existing users. And
we can also provide the utility application with Pgpool-II to generate the
pgpool-auth file from pg_shadow table to make this configuration hassle
free. ( similar to mkauth.py included with pgbouncer)

I think adding this feature will allow us make Pgpool-II more usable and
secure and with this we will be able to support SCRAM and SSL-AUTH
authentication methods in Pgpool-II. And it will also solve the problems
like the one reported in the http://www.pgpool.net/mantisbt/view.php?id=374

Finally this email just outlines the overview of the feature and once if we
agree to go in the direction we can discuss it in more details like the
file-format, user-password management for Pgpool-II and data-encryption on
that file.



Thoughts and comments are most welcome

Thanks
Best Regards
Muhammad Usama
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20180118/5604e276/attachment.html>