RFC: Non-user-resettable SET SESSION AUTHORISATION

Hi all

For some time I've wanted a way to "SET SESSION AUTHORISATION" or "SET
ROLE" in a way that cannot simply be RESET, so that a connection may be
handed to a less-trusted service or application to do some work with.

This is most useful for connection pools, where it's currently necessary to
have a per-user pool, to trust users not to do anything naughty, or to
filter the functions and commands they can run through a whitelist to stop
them trying to escalate rights to the pooler user.

In the short term I'd like to:

* Add a WITH COOKIE option to "SET SESSION AUTHORIZATION", which takes an
app-generated code (much like PREPARE TRANSACTION does).

* Make DISCARD ALL, RESET SESSION AUTHORIZATION, etc, also take WITH
COOKIE. If session authorization was originally set with a cookie value,
the same cookie value must be passed or an ERROR will be raised when RESET
is attempted.

* A second SET SESSION AUTHORIZATION without a prior RESET would be
rejected with an ERROR if the first SET used a cookie value.

This can be done without protocol-level changes and with no backward
compatibility impact to existing applications. Any objections?

These commands will appear in the logs if log_statement = 'all', but the
codes are transient cookie values, not passwords. They'll be visible in
pg_stat_activity but only to the privileged user. It'll probably be
necessary to clear the last command string when executing SET SESSION
AUTHORIZATION so the new user can't snoop the cookie value from a
concurrent connection, but that should be simple enough.

As is currently the case, poolers will still have to use a superuser
connection if they want to pool across users.

In the longer term I want to add a protocol-level equivalent that lets a
session switch session authorization or role, for efficiency and log-spam
reasons.

I'm also interested in a way to allow SET SESSION AUTHORIZATION to a list
of permitted roles when run as a non-superuser, for connection pool use.
SET ROLE might do, but it's more visible to apps, wheras SET SESSION
AUTHORIZATION really makes the connection appear to "become" the target
user.

That's later though - first,

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services