Thread: [HACKERS] SCRAM auth and Pgpool-II

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
singlesalt 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
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

On Thu, Jul 6, 2017 at 10:03 AM, Tatsuo Ishii <ishii@sraoss.co.jp> wrote:
> 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.

Couldn't you cache one single SASL exchange status for each
connection, meaning one PGconn saved for each? As the challenge sent
by the server and the response generated by the client are different
by design, I am afraid you would need to do that anyway in this
context (Isn't PG-pool using already the weaknesses of MD5 to make
things easier?). As the server decides first which authentication type
should happen before beginning the real message exchange, that should
not be difficult. It seems to me that you would need something more
modular than you have now if you want for example to handle
automatically connections to multiple servers that have different
password hashes stored for the same user. The latter may be an edge
case with pgpool though.
-- 
Michael

Michael,

> Couldn't you cache one single SASL exchange status for each
> connection, meaning one PGconn saved for each? As the challenge sent
> by the server and the response generated by the client are different
> by design, I am afraid you would need to do that anyway in this
> context (Isn't PG-pool using already the weaknesses of MD5 to make
> things easier?). As the server decides first which authentication type
> should happen before beginning the real message exchange, that should
> not be difficult. It seems to me that you would need something more
> modular than you have now if you want for example to handle
> automatically connections to multiple servers that have different
> password hashes stored for the same user. The latter may be an edge
> case with pgpool though.

Thank you for the quick response. I will study your suggestion along
with the SCRAM code in PostgreSQL whether it could be possible in
Pgpool-II.

Regarding your question on md5 auth handling in Pgpool-II, please look
into:

https://pgpool.net/mediawiki/index.php/FAQ#How_does_pgpool-II_handle_md5_authentication.3F

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

On Wed, Jul 5, 2017 at 9:32 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
> On Thu, Jul 6, 2017 at 10:03 AM, Tatsuo Ishii <ishii@sraoss.co.jp> wrote:
>> 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.
>
> Couldn't you cache one single SASL exchange status for each
> connection, meaning one PGconn saved for each? As the challenge sent
> by the server and the response generated by the client are different
> by design, I am afraid you would need to do that anyway in this
> context (Isn't PG-pool using already the weaknesses of MD5 to make
> things easier?).

I think it is.  From a security point of view, the fact that the same
salt is always used for the same username is a weakness of md5
authentication which SCRAM corrects.

IIUC, things will get even worse once channel binding is committed,
presumably for PostgreSQL 11.  The point of channel binding is to
guarantee that you are conducting the authentication exchange with the
target server, not some intermediate proxy that might be conducting a
hostile MITM attack.  pgpool may not be a hostile attack, but it is
acting as a MITM, and channel binding is going to figure that out and
fail the authentication.  So unless I'm misunderstanding, the solution
you are proposing figures to have a very limited shelf life.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Thu, Jul 06, 2017 at 10:03:37AM +0900, 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.

In the end, what poolers do is doing is indistinguishable, in security
terms, from a man-in-the-middle attack.  To the client, the thing with
which they're negotiating auth and doing queries is Pgpool-II, in a
manner similar to writing to a RAID volume rather than any individual
disk in it.

Are people actually running Pgpool on an untrusted network to the
PostgreSQL nodes?

Best,
David.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Sat, Jul 8, 2017 at 1:24 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Wed, Jul 5, 2017 at 9:32 PM, Michael Paquier
> <michael.paquier@gmail.com> wrote:
>> On Thu, Jul 6, 2017 at 10:03 AM, Tatsuo Ishii <ishii@sraoss.co.jp> wrote:
>>> 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.
>>
>> Couldn't you cache one single SASL exchange status for each
>> connection, meaning one PGconn saved for each? As the challenge sent
>> by the server and the response generated by the client are different
>> by design, I am afraid you would need to do that anyway in this
>> context (Isn't PG-pool using already the weaknesses of MD5 to make
>> things easier?).
>
> I think it is.  From a security point of view, the fact that the same
> salt is always used for the same username is a weakness of md5
> authentication which SCRAM corrects.

I recall vaguely Ishii-san mentioning me at PGcon that pgpool was
actually cheating, but my memories are a bit fuzzy for this part.

> IIUC, things will get even worse once channel binding is committed,
> presumably for PostgreSQL 11.  The point of channel binding is to
> guarantee that you are conducting the authentication exchange with the
> target server, not some intermediate proxy that might be conducting a
> hostile MITM attack.  pgpool may not be a hostile attack, but it is
> acting as a MITM, and channel binding is going to figure that out and
> fail the authentication.  So unless I'm misunderstanding, the solution
> you are proposing figures to have a very limited shelf life.

We may be misunderstanding each other then. The solution I am
proposing, or at least the solution that I imagine should be done but
my pgpool-fu is limited, is to keep around connection context and SSL
context to handle properly connections messages, and switch between
them. When using channel binding, the client needs the server
certificate for end-point and the TLS finish message which are both
stored in the SSL context after the handshake. So you need to cache
that for each server.

One problem is that pgpool does not use directly libpq but tries to
handle things by itself at protocol level, so it needs to replicate a
lot of existing APIs. Postgres-XC/XL use a connection pooler,
presumably XL uses even a background worker for this stuff since it
has been integrated with Postgres 9.3. And this stuff use libpq. This
makes life way easier to handle context data on a connection basis.
Those don't need to handle protocol-level messages either, which is
perhaps different from what pgpool needs.

In short I would think that pgpool needs first to un-cheat its
handling with MD5, which is likely here to simplify its code.
-- 
Michael

> I think it is.  From a security point of view, the fact that the same
> salt is always used for the same username is a weakness of md5
> authentication which SCRAM corrects.

In my understanding, md5 does not always use the same salt for the
same username. PostgreSQL keeps md5(password+username) in
pg_authid. When md5 auth is required, the backend sends a random
number (i.e. salt) to the client. The client replies back
md5(salt+md5(password+username)) to the backend. The backend does the
calculation (md5(salt+md5(password+username))). If the result matches
the value sent from the user, md5 authentication succeeds.

So the salt is differ in each session in md5.

The weakness in md5 is , IMO, each PostgreSQL installation always
keeps the same value (md5(password+username)).

> IIUC, things will get even worse once channel binding is committed,
> presumably for PostgreSQL 11.  The point of channel binding is to
> guarantee that you are conducting the authentication exchange with the
> target server, not some intermediate proxy that might be conducting a
> hostile MITM attack.  pgpool may not be a hostile attack, but it is
> acting as a MITM, and channel binding is going to figure that out and
> fail the authentication.  So unless I'm misunderstanding, the solution
> you are proposing figures to have a very limited shelf life.

Check...

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> I recall vaguely Ishii-san mentioning me at PGcon that pgpool was
> actually cheating, but my memories are a bit fuzzy for this part.

What I meant by "cheating" was, Pgpool-II behaves as if PostgreSQL
server in md5 auth.

For more details what Pgpool-II actually does in md5 auth, please see:

https://pgpool.net/mediawiki/index.php/FAQ#How_does_pgpool-II_handle_md5_authentication.3F

>> IIUC, things will get even worse once channel binding is committed,
>> presumably for PostgreSQL 11.  The point of channel binding is to
>> guarantee that you are conducting the authentication exchange with the
>> target server, not some intermediate proxy that might be conducting a
>> hostile MITM attack.  pgpool may not be a hostile attack, but it is
>> acting as a MITM, and channel binding is going to figure that out and
>> fail the authentication.  So unless I'm misunderstanding, the solution
>> you are proposing figures to have a very limited shelf life.
> 
> We may be misunderstanding each other then. The solution I am
> proposing, or at least the solution that I imagine should be done but
> my pgpool-fu is limited, is to keep around connection context and SSL
> context to handle properly connections messages, and switch between
> them. When using channel binding, the client needs the server
> certificate for end-point and the TLS finish message which are both
> stored in the SSL context after the handshake. So you need to cache
> that for each server.
> 
> One problem is that pgpool does not use directly libpq but tries to
> handle things by itself at protocol level, so it needs to replicate a
> lot of existing APIs. Postgres-XC/XL use a connection pooler,
> presumably XL uses even a background worker for this stuff since it
> has been integrated with Postgres 9.3. And this stuff use libpq. This
> makes life way easier to handle context data on a connection basis.
> Those don't need to handle protocol-level messages either, which is
> perhaps different from what pgpool needs.

Yeah, I wish I could use libpq in Pgpool-II. Unfortunately libpq does
not provide necessary features what Pgpool-II needs.

> In short I would think that pgpool needs first to un-cheat its
> handling with MD5, which is likely here to simplify its code.

See the link above why it's not possible.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

Michael,

* Michael Paquier (michael.paquier@gmail.com) wrote:
> On Sat, Jul 8, 2017 at 1:24 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> > IIUC, things will get even worse once channel binding is committed,
> > presumably for PostgreSQL 11.  The point of channel binding is to
> > guarantee that you are conducting the authentication exchange with the
> > target server, not some intermediate proxy that might be conducting a
> > hostile MITM attack.  pgpool may not be a hostile attack, but it is
> > acting as a MITM, and channel binding is going to figure that out and
> > fail the authentication.  So unless I'm misunderstanding, the solution
> > you are proposing figures to have a very limited shelf life.
>
> We may be misunderstanding each other then. The solution I am
> proposing, or at least the solution that I imagine should be done but
> my pgpool-fu is limited, is to keep around connection context and SSL
> context to handle properly connections messages, and switch between
> them. When using channel binding, the client needs the server
> certificate for end-point and the TLS finish message which are both
> stored in the SSL context after the handshake. So you need to cache
> that for each server.

I'm not sure what you mean here- the whole point of channel binding is
to prevent exactly the kind of MITM that pgPool would essentially be in
this case.  If you're suggesting that things be changed such that a
server provides information necessary to pgPool to pass along to the
client to make the client believe that it's talking to the server and
that there's no man-in-the-middle then the whole point of channel
binding goes out the window.  If what you're suggesting doesn't require
any help from either the client or the server to work then I fear we've
done something wrong in the implementation of channel binding (I've not
looked at it very closely).

I don't have any perfect answers for pgPool here, unfortunately.  One
approach would be to give it a list of usernames/passwords to use,
though that's hardly ideal.  Perhaps it would be possible for pgPool to
impersonate the server to the client and the client to the server if it
was able to query the database as a superuser on some other connection,
but I don't *think* so because of the way SCRAM works and because pgPool
wouldn't have access to the client's cleartext password.

Of course, if pgPool could convince the client to use 'password' auth
and get the cleartext password from the client then that would work to
authenticate against the server and there wouldn't be any channel
binding involved since the client is talking the basic cleartext
password protocol and not SCRAM.

> One problem is that pgpool does not use directly libpq but tries to
> handle things by itself at protocol level, so it needs to replicate a
> lot of existing APIs. Postgres-XC/XL use a connection pooler,
> presumably XL uses even a background worker for this stuff since it
> has been integrated with Postgres 9.3. And this stuff use libpq. This
> makes life way easier to handle context data on a connection basis.
> Those don't need to handle protocol-level messages either, which is
> perhaps different from what pgpool needs.

I don't really think that pgpool using or not using libpq makes any
real difference here.  What'll be an issue for pgpool is when clients
get updated libpq libraries that don't support password auth...

> In short I would think that pgpool needs first to un-cheat its
> handling with MD5, which is likely here to simplify its code.

This also doesn't seem particularly relevant to me, but then again, I've
never actually looked at the pgPool code myself.

Thanks!

Stephen

Tatsuo,

* Tatsuo Ishii (ishii@sraoss.co.jp) wrote:
> > I recall vaguely Ishii-san mentioning me at PGcon that pgpool was
> > actually cheating, but my memories are a bit fuzzy for this part.
>
> What I meant by "cheating" was, Pgpool-II behaves as if PostgreSQL
> server in md5 auth.
>
> For more details what Pgpool-II actually does in md5 auth, please see:
>
> https://pgpool.net/mediawiki/index.php/FAQ#How_does_pgpool-II_handle_md5_authentication.3F

I see.  In short, that's not going to be able to work with SCRAM.

We also certainly can't weaken or break SCRAM to address Pgpool's needs.
I'm afraid an alternative approach will need to be considered for Pgpool
to be able to do what it does today, as I outlined in my other email.

Thanks!

Stephen

>> For more details what Pgpool-II actually does in md5 auth, please see:
>> 
>> https://pgpool.net/mediawiki/index.php/FAQ#How_does_pgpool-II_handle_md5_authentication.3F
> 
> I see.  In short, that's not going to be able to work with SCRAM.

That's my understanding with SCRAM too.

> We also certainly can't weaken or break SCRAM to address Pgpool's needs.

Right, that's not what I want too.

> I'm afraid an alternative approach will need to be considered for Pgpool
> to be able to do what it does today, as I outlined in my other email.

Thank your for your comment. Probably now is the time to give up
to support SCRAM in Pgpool-II.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

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

Hi Alvaro,

>     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.

That's ambitious. Thank you for the info!

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

On Sat, Jul 8, 2017 at 9:30 AM, Stephen Frost <sfrost@snowman.net> wrote:
> Michael,
>
> * Michael Paquier (michael.paquier@gmail.com) wrote:
>> On Sat, Jul 8, 2017 at 1:24 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>> > IIUC, things will get even worse once channel binding is committed,
>> > presumably for PostgreSQL 11.  The point of channel binding is to
>> > guarantee that you are conducting the authentication exchange with the
>> > target server, not some intermediate proxy that might be conducting a
>> > hostile MITM attack.  pgpool may not be a hostile attack, but it is
>> > acting as a MITM, and channel binding is going to figure that out and
>> > fail the authentication.  So unless I'm misunderstanding, the solution
>> > you are proposing figures to have a very limited shelf life.
>>
>> We may be misunderstanding each other then. The solution I am
>> proposing, or at least the solution that I imagine should be done but
>> my pgpool-fu is limited, is to keep around connection context and SSL
>> context to handle properly connections messages, and switch between
>> them. When using channel binding, the client needs the server
>> certificate for end-point and the TLS finish message which are both
>> stored in the SSL context after the handshake. So you need to cache
>> that for each server.
>
> I'm not sure what you mean here- the whole point of channel binding is
> to prevent exactly the kind of MITM that pgPool would essentially be in
> this case.  If you're suggesting that things be changed such that a
> server provides information necessary to pgPool to pass along to the
> client to make the client believe that it's talking to the server and
> that there's no man-in-the-middle then the whole point of channel
> binding goes out the window.  If what you're suggesting doesn't require
> any help from either the client or the server to work then I fear we've
> done something wrong in the implementation of channel binding (I've not
> looked at it very closely).

Sorry if my last reply sounded confusing. I have re-read the thread,
my replies and what pgpool does and I can see now what can be
confusing in my words.

What I am suggesting here is that in order to handle properly SCRAM
with channel binding, pgpool has to provide a different handling for
client <-> pgpool and pgpool <-> Postgres. In short, I don't have a
better answer than having pgpool impersonate the server and request
for a password in cleartext through an encrypted connection between
pgpool and the client if pgpool does not know about it, and then let
pgpool do by itself the SCRAM authentication on a per-connection basis
with each Postgres instances. When using channel binding, what would
matter is the TLS finish (for tls-unique) or the hash server
certificate between Postgres and pgpool, not between the client and
pgpool. But that's actually the point you are raising here:

> I don't have any perfect answers for pgPool here, unfortunately.  One
> approach would be to give it a list of usernames/passwords to use,
> though that's hardly ideal.  Perhaps it would be possible for pgPool to
> impersonate the server to the client and the client to the server if it
> was able to query the database as a superuser on some other connection,
> but I don't *think* so because of the way SCRAM works and because pgPool
> wouldn't have access to the client's cleartext password.
>
> Of course, if pgPool could convince the client to use 'password' auth
> and get the cleartext password from the client then that would work to
> authenticate against the server and there wouldn't be any channel
> binding involved since the client is talking the basic cleartext
> password protocol and not SCRAM.

Yes, that's what I am coming at. The only way to defeat a password
sent to pgpool in cleartext would be to force the client to do things
through an encrypted connection.

>> One problem is that pgpool does not use directly libpq but tries to
>> handle things by itself at protocol level, so it needs to replicate a
>> lot of existing APIs. Postgres-XC/XL use a connection pooler,
>> presumably XL uses even a background worker for this stuff since it
>> has been integrated with Postgres 9.3. And this stuff use libpq. This
>> makes life way easier to handle context data on a connection basis.
>> Those don't need to handle protocol-level messages either, which is
>> perhaps different from what pgpool needs.
>
> I don't really think that pgpool using or not using libpq makes any
> real difference here.  What'll be an issue for pgpool is when clients
> get updated libpq libraries that don't support password auth...

There is no need to duplicate the protocol level handling, so life is
simplified for pgpool. But that cannot happen as Ishii-san said
upthread as pgpool needs to control things at a higher level.
-- 
Michael

On Sat, Jul 8, 2017 at 1:54 PM, Álvaro Hernández Tortosa <aht@8kdata.com> wrote:
>     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.

Thanks. You are putting is more simple words the concepts I am coming
at. One issue is how to send the password to pgpool, which needs to
have it in cleartext for the SASL exchange with each Postgres backend.

>     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 RFC paragraph is relevant as well:
https://tools.ietf.org/html/rfc4422#section-2. Nothing will happen in
PG10 regarding that part.

>     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.

A more granular SET SESSION AUTHORIZATION then?
--
Michael

> What I am suggesting here is that in order to handle properly SCRAM
> with channel binding, pgpool has to provide a different handling for
> client <-> pgpool and pgpool <-> Postgres. In short, I don't have a
> better answer than having pgpool impersonate the server and request
> for a password in cleartext through an encrypted connection between
> pgpool and the client if pgpool does not know about it, and then let
> pgpool do by itself the SCRAM authentication on a per-connection basis
> with each Postgres instances. When using channel binding, what would
> matter is the TLS finish (for tls-unique) or the hash server
> certificate between Postgres and pgpool, not between the client and
> pgpool. But that's actually the point you are raising here:

Using a clear text password would not be acceptable for users even
through an encrypted connection, I think.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

Greetings Tatsuo,

* Tatsuo Ishii (ishii@sraoss.co.jp) wrote:
> > What I am suggesting here is that in order to handle properly SCRAM
> > with channel binding, pgpool has to provide a different handling for
> > client <-> pgpool and pgpool <-> Postgres. In short, I don't have a
> > better answer than having pgpool impersonate the server and request
> > for a password in cleartext through an encrypted connection between
> > pgpool and the client if pgpool does not know about it, and then let
> > pgpool do by itself the SCRAM authentication on a per-connection basis
> > with each Postgres instances. When using channel binding, what would
> > matter is the TLS finish (for tls-unique) or the hash server
> > certificate between Postgres and pgpool, not between the client and
> > pgpool. But that's actually the point you are raising here:
>
> Using a clear text password would not be acceptable for users even
> through an encrypted connection, I think.

Really, I don't think users who are concerned with security should be
using the md5 method either.

What would be really nice for such cases is support for Kerberos and
delegated Kerberos credentials.  Having pgpool support that would remove
the need to deal with passwords at all.

Ditto for having postgres_fdw support same.  More often than not,
Kerberos deployments (via AD, generally) is what I find in the
enterprises that I work with and they're happy to see we have Kerberos
but it's disappointing when they can't use Kerberos with either
connection poolers or with FDWs.

Thanks!

Stephen

>> Using a clear text password would not be acceptable for users even
>> through an encrypted connection, I think.
> 
> Really, I don't think users who are concerned with security should be
> using the md5 method either.

The comment in pg_hba.conf.sample seem to prefer md5 over clear text
password.

# Note that "password" sends passwords in clear text; "md5" or
# "scram-sha-256" are preferred since they send encrypted passwords.

> What would be really nice for such cases is support for Kerberos and
> delegated Kerberos credentials.  Having pgpool support that would remove
> the need to deal with passwords at all.
> 
> Ditto for having postgres_fdw support same.  More often than not,
> Kerberos deployments (via AD, generally) is what I find in the
> enterprises that I work with and they're happy to see we have Kerberos
> but it's disappointing when they can't use Kerberos with either
> connection poolers or with FDWs.

I would add supporting Kerberos to the Pgpool-II todo list.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

On 07/13/17 20:09, Tatsuo Ishii wrote:

> The comment in pg_hba.conf.sample seem to prefer md5 over clear text
> password.
> 
> # Note that "password" sends passwords in clear text; "md5" or
> # "scram-sha-256" are preferred since they send encrypted passwords.

Should that be reworded to eliminate "md5"? I'd consider "scram-sha-256"
suitable over a clear channel, but I've never recommended "md5" for that.

-Chap

>> The comment in pg_hba.conf.sample seem to prefer md5 over clear text
>> password.
>> 
>> # Note that "password" sends passwords in clear text; "md5" or
>> # "scram-sha-256" are preferred since they send encrypted passwords.
> 
> Should that be reworded to eliminate "md5"? I'd consider "scram-sha-256"
> suitable over a clear channel, but I've never recommended "md5" for that.

I don't think so unless clear text password is superior than md5.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

On 07/13/17 21:54, Tatsuo Ishii wrote:
>>> The comment in pg_hba.conf.sample seem to prefer md5 over clear text
>>> password.
>>>
>>> # Note that "password" sends passwords in clear text; "md5" or
>>> # "scram-sha-256" are preferred since they send encrypted passwords.
>>
>> Should that be reworded to eliminate "md5"? I'd consider "scram-sha-256"
>> suitable over a clear channel, but I've never recommended "md5" for that.
> 
> I don't think so unless clear text password is superior than md5.

Neither is suitable on an unencrypted channel (as has been repeatedly
observed back to 2005 at least [1], so I guess I'm not spilling the beans).
At last, scram-sha-256 is an option that is believable for that use.

So, allowing that neither "password" nor "md5" should ever be used on
an unencrypted channel, as long as the channel is encrypted they are both
protected (by the channel encryption) from eavesdropping, so they score
a tie on that dimension. For a tiebreaker, you could look at the
consequences of revealing rolpassword from pg_authid. On that dimension,
with "md5" you have revealed a password-equivalent, while with "password"
you have not [2], so on that dimension "password" indeed is superior to
"md5".

-Chap

[1]: https://www.postgresql.org/message-id/8764ygc7i6.fsf%40stark.xeocode.com
[2]:
https://www.postgresql.org/message-id/20050421190637.GF29028%40ns.snowman.net

On 07/13/2017 10:46 PM, Chapman Flack wrote:

> Neither is suitable on an unencrypted channel (as has been repeatedly

Please forgive my thinko about md5. I had overlooked the second
salted md5 used in the protocol, and that had to be some years ago
when I was sure I had looked for one in the code. But it's been there
since 2001, so I simply overlooked it somehow. Not sure how. I've had
an unnecessarily jaundiced view of "md5" auth for years as a result.

I feel much better now. Sorry for the noise.

-Chap

Greetings,

* Vladimir Borodin (root@simply.name) wrote:
> > 14 июля 2017 г., в 1:33, Stephen Frost <sfrost@snowman.net> написал(а):
> > What would be really nice for such cases is support for Kerberos and
> > delegated Kerberos credentials.  Having pgpool support that would remove
> > the need to deal with passwords at all.
>
> Since nearly all systems with some kind of load nowadays use connection poolers (pgpool-II or pgbouncer) between
applicationsand postgres, it is a pretty big pain to re-implement all authentication methods supported by postgres in
suchpoolers. Kerberos is cool but not the only thing that should be supported by FDWs or connection poolers. I.e. many
userswould want to have support for LDAP and SCRAM. And every time when there would be some changes in postgres auth
methods,exactly the same work (or even worse) should be done in many (at least two) other products widely used by
people.

Honestly, I disagree about the notion that LDAP support should be added
to anything or encouraged.  There's a reason that AD uses Kerberos and
not LDAP and Microsoft continues to (quite reasonably) make it more
difficult for individuals to perform LDAP auth in AD.

The auth methods in PG do not see a huge amount of churn over the years
and so I don't agree with the argument that it would be a problem for
other systems to support Kerberos or similar strong auth methods.

> It seems that postgres either should provide connection pooling feature in core or give external poolers a kind of
genericmechanism to transparently proxy auth requests/responses, so that authentication would be fully managed by
postgresand that would be the only place where changes in auth methods should be done. Yes, in this case connection
pooleractually behaves like man in the middle so it should be done very carefully but it seems that there is no other
way.

While this might be possible by having some kind of special trusted
connection between the pooler and PG, I actually don't particularly like
the notion of inventing a bunch of complicated logic and pain so that a
connection pooler can avoid implementing a proper authentication system.

Having PG support connection pooling itself, of course, would be nice
but I'm not aware of anyone currently working on it.

Thanks!

Stephen

On Sat, Jul 15, 2017 at 12:15 AM, Stephen Frost <sfrost@snowman.net> wrote:
> While this might be possible by having some kind of special trusted
> connection between the pooler and PG, I actually don't particularly like
> the notion of inventing a bunch of complicated logic and pain so that a
> connection pooler can avoid implementing a proper authentication system.

I strongly agree with that.
-- 
Michael