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

On Wed, May 20, 2015 at 3:42 PM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:
> Robert Haas wrote:
>> On Wed, May 20, 2015 at 11:27 AM, Marko Tiikkaja <marko@joh.to> wrote:
>> > 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.
>>
>> I don't think this will work, because the authentication dialogue is
>> structured a series of challenges and responses.
>
> After mulling over this a bit, I think that if we're to do something to
> improve things here we should redesign the protocol so that it considers
> poolers explicitely.  Right now I think a pooler is pretty limited in
> what it can do.  If we were to have messages specifically for poolers,
> life would be simpler: pooler authenticates to main server, client
> authenticates to pooler.  The pooler can change auth on the server
> connection to whatever the client has, and begin passthrough of protocol
> data; when client closes connection, pooler recycles connection and
> de-authenticates it with main server so that it can be reused for
> another client (re-auth).  Client by itself cannot "de-auth" to steal
> the connection under somebody else's name.

It might be a good idea to do something like this, but it's
significantly more complicated than a protocol-level SET SESSION
AUTHORIZATION.  Right now, you can never go backwards from an
authenticated state to an unauthenticated state, and there may be code
in the backend that relies on that in subtle ways.  The initial
bootstrap sequence is pretty complicated, and I'm pretty sure that any
naive attempt to redo that stuff is going to have unpleasant, probably
security-relevant bugs.

(In the current architecture, you also can't rebind to a new database;
I'm not sure if your proposal would change things from that side, but
if so, that adds a further level of complexity.)

I would urge, rather strongly, that we keep the first version of this
simple: let the pooler, via a protocol message, set the session
authorization in a fashion that prevents it from being changed back
except by another protocol message.  If we want to do something like
this after that, fine, but letting the pooler switch the authorization
in a non-subvertable way is a whole lot simpler than what you are
talking about.

> There's an issue that in order to authenticate a client, the pooler
> needs to have info from the server about auth data.  Last I checked
> pgbouncer, you had to copy a list of username/passwords from the server
> to a pgbouncer config file, which is ugly and dangerous (not to mention
> tedious and error-prone).  We could fix that sort of thing too, if we
> were to design something here with poolers in mind.

I think this is fundamentally backwards.  If the client is going to
authenticate directly to the pooler, then the pooler should be the
master source of the authentication information, and pooler should
just log into the server as superuser.  If you instead do what you're
proposing and teach the server to send its authentication secrets to
the pooler, you risk somebody evil using the same feature to extract
those secrets for malicious purposes (think: DBA who is about to be
fired).  You're basically adding a server "feature" whereby it
facilitates MITM attacks against itself.

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