Re: SCRAM auth and Pgpool-II

On 06/07/17 04:03, Tatsuo Ishii wrote:
> Hi PostgreSQL hackers,
>
> I would like to hear ideas how Pgpool-II can deal with SCRAM auth
> which will be in PostgreSQL 10.
>
> For those who are not familiar with Pgpool-II[1], it is an external
> OSS project to provide some additional features to PostgreSQL,
> including load balancing and automatic failover. Pgpool-II works as a
> proxy between PostgreSQL client and PostgreSQL server(s).
>
> When a client wants to connects to PostgreSQL and SCRAM auth is
> enabled, it sends user name to server. Then the server sends
> information including a salt to the client. The client computes a
> "ClientProof" using the salt and other information, and sends it to
> the server[2].
>
> For Pgpool-II, things would go as follows:
>
> 1) clients sends user name to Pgpool-II.
> 2) Pgpool-II forwards it to PostgreSQL servers.
> 3) Each PostgreSQL server sends their own salt to Pgpool-II.
> 4) Pgpool-II is confused because there are multiple salts and each has
> different values. The client only accepts single salt obviously.
>
> So my question is, is there any solution or workaround for the problem
> #4. Someone at PGCon 2017 suggested that the problem could be avoided
> if the auth method between the client and Pgpool-II is "trust" (which
> means no auth). But this does not seem to be a best solution for me
> because it would weaken the security.
>
> I suspect this problem may not be specific to Pgpool-II. Any middle
> ware which handles multiple PostgreSQL servers could have the similar
> problem.
>
> Any suggestion would be appreciated. Thanks in advance.
>
> [1] https://pgpool.net
> [2] https://tools.ietf.org/html/rfc5802#section-3

Hi Tatsuo.

There's definitely an important concern here that should be
addressed: how poolers/proxies/middleware/etc can deal with SCRAM,
specifically in the context of channel binding.

If there is to be a single connection from client to PostgreSQL
server, intercepted by pgpool to perform the magic foo, then channel
binding is, indeed, designed to defeat this. If, however, pgpool or the
middleware manages two separate connections (client<->pool and
pool<->PG) then there is some light here.

One SCRAM feature not implemented as of today is the authzid
(authorization identity: see
https://tools.ietf.org/html/rfc5802#page-10, SCRAM attribute "a" and
https://tools.ietf.org/html/rfc5801). Authzid is basically "I want to
authenticate as user X and once authenticated, consider I'm user Y".
With authzid, a pool/proxy may have a common user name with its own
SCRAM credentials to authenticate with the backend PostgreSQL, and pass
the authzid with the real username (the one provided on the
client<->pool connection).

This would require:

a) That authzid is implemented in PostgreSQL.
b) A mechanism in PG to name which user(s) Y are allowed to be
authenticated by user X. This is similar, but not identical, to the
current SET ROLE.

From a SCRAM protocol perspective, this is very simple, just an
extra attribute. Part b) may need more discussion.

I believe this could be of help to your case and other middleware.

Álvaro

--

Álvaro Hernández Tortosa

-----------
<8K>data