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,