On Fri, Jan 13, 2017 at 7:39 PM, Karl Czajkowski <karlcz@isi.edu> wrote:
> The concern was raised about the risk of the subsequent queries being
> able to reverse the "set role" to subvert such authorization. Others
> in the thread provided the common advice of parametric queries to
> prevent query injection, but I think you need more than that. I think
> one would like a stronger isolation between the connection
> setup/security config layer and the fancier (and possibly flawed)
> application layer. We struggle with this because all these mechanisms
> are multiplexed as commands in one SQL connection, instead of having
> an in-band versus out-of-band protocol layering available to the web
> application, so the more risky data-dependent SQL can flow in-band
> while the basic request-handler lifecycle could configure the security
> context out-of-band.
Wasn't there some previous discussion about the ability to have a
form of SET ROLE that would specify that some token would be needed
to do a successful RESET ROLE? (I don't remember whether people
were leaning toward providing the token on the SET ROLE statement,
or getting that as part of the return state.) If you made the
token a suitably secure random value, and made an incorrect token
on the RESET ROLE a FATAL error, it seems like it could be secure
enough. You still need to have the ability to authenticate the
client side connection in the connection pooler, and have the
connection pooler connect to the server with a login with rights to
do the appropriate SET ROLE (preferably without requiring superuser
rights).
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company