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

* Simon Riggs (simon@2ndQuadrant.com) wrote:
> On 19 May 2015 at 16:49, Robert Haas <robertmhaas@gmail.com> wrote:
>
> > On Tue, May 19, 2015 at 3:00 PM, Simon Riggs <simon@2ndquadrant.com>
> > wrote:
> > > As long as the cookie is randomly generated for each use, then I don't
> > see a
> > > practical problem with that approach.
> >
> > If the client sets the cookie via an SQL command, that command would
> > be written to the log, and displayed in pg_stat_activity.  A malicious
> > user might be able to get it from one of those places.
> >
> > A malicious user might also be able to just guess it.  I don't really
> > want to create a situation where any weakess in pgpool's random number
> > generation becomes a privilege-escalation attack.
> >
> > A protocol extension avoids all of that trouble, and can be target for
> > 9.6 just like any other approach we might come up with.  I actually
> > suspect the protocol extension will be FAR easier to fully secure, and
> > thus less work, not more.
>
> 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.

For my 2c, I continue to agree with a protocol-based approach, but I
don't think having two modes would actually satisfy concerns regarding
the security- we're still going to have to fix any issues which are
security related that come up from having the "session able to be reset"
mode.

That said, we know connection poolers are already using SET SESSION AUTH
(which is clearly far worse than what we're proposing to do here..) and
clearly we support SET ROLE, so any issues with those methods really
should be getting addressed anyway.  Perhaps we can continue to beg off
in the SET SESSION AUTH case by hiding behind "you're a superuser" or
"you're using it wrong" but that doesn't actually make anyone more
secure and we clearly need to address the SET ROLE case, as that is
absolutely expected to work correctly.

As for the discussion regarding having a connection pooler built-in-
that is absolutely something we need to do, in my view, because any
external connection pooler isn't going to offer the same set of
capabilities that core does and we continue to fight with the concerns
around changing the wireline protocol which hamstrings our progress in
this area.  That isn't to say it's all roses if we just built it in,
because clearly it's not and there's work to be done there, but a
connection pooler which is tied closely to core and which is upgraded
and deployed with it could be much more easily changed and improved.  On
the other hand, I'd really like to see improvement to our protocol too
and perhaps this is a way to get those, though it hasn't been happening
so far, unfortunately.
Thanks!
    Stephen