Re: RFC: Non-user-resettable SET SESSION AUTHORISATION

On Wed, May 20, 2015 at 11:27 AM, Marko Tiikkaja <marko(at)joh(dot)to> wrote:
> On 5/20/15 5:21 PM, Robert Haas wrote:
>> On Tue, May 19, 2015 at 5:02 PM, Simon Riggs <simon(at)2ndquadrant(dot)com>
>> wrote:
>>> That's a reasonable argument. So +1 to protocol from me.
>>>
>>> To satisfy Tom, I think this would need to have two modes: one where the
>>> session can never be reset, for ultra security, and one where the session
>>> can be reset, which allows security and speed of pooling.
>>
>> I think the the second one is a lot more interesting, but I don't have
>> a problem with having the first one, too, if somebody wants it. We
>> can use one protocol message for both, with a 1-byte character field
>> used to indicate which mode the client is requesting.
>
> Now that we're on the topic of interesting things, would it make sense to
> add protocol support for a sort of a "re-authenticate"? So a pooler could
> first say "this user wants to log in from this host", then get back a
> message saying how to authenticate that user, which the pooler could then
> pass that on to the client. Once the client has passed its credentials, the
> pooler could (possibly in another backend) try to authenticate using those
> credentials, and only then set the session's authentication. This would
> allow for more transparent poolers while still, well, pooling connections.
>
> I haven't thought about this at all, so maybe it's a stupid idea (or the
> backends don't have all the information to do this), or whatever.

I don't think this will work, because the authentication dialogue is
structured a series of challenges and responses. For many
authentication methods, these are replay-resistant by design; if you
could watch a Kerberos authentication sequence and then, based on
having seen it, conduct an authentication dialog with somebody else
successfully, that would be a very serious security flaw; it would
amount to being able to steal the secret key by observing one
authentication dialog. Even md5 authentication is intended to be
replay-resistant, by using a different salt each time. Sure, the
pooler COULD reuse the same salt over and over and just look for a
matching response, but then md5 authentication via the pooler becomes
much less secure than md5 authentication that goes directly to the
server. That's bad.

I suspect you're asking about this because you are concerned about the
problem of authentication to the pooler being awkward and maybe
insecure. I suspect that the only real solution to that problem is
going to be to put the pooler into the database server itself, so that
you just have one piece of software. That doesn't mean we shouldn't
look for other methods of improving things between now and then, but I
think it's going to be a hard problem to solve.

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