Roles with passwords; SET ROLE ... WITH PASSWORD ?

At present, a role may have a password but unless it's a login role the
docs say that password doesn't get used.

I currently have an app where it'd be handy to be able to:

SET ROLE rolename WITH PASSWORD 'blah';

to switch to role `rolename' only if the password `blah' is correct for
that role. `rolename' might or might not be a login role, though in most
of the cases I'm looking at it'd make more sense for it to be a login role.

Why would this be useful? The app relies on PostgreSQL for basic user
management and authentication. User rights are controlled by role
assignments, and are enforced at the database level by appropriate
table, column and function permissions plus selective use of SECURITY
DEFINER functions and triggers. The app only uses role memberships to
decide what UI to hide to avoid confusing the user with permission
errors from the DB.

This works extremely well, _except_ that occasionally it's desirable to
override a user's rights by intervention of a supervisor user. Having a
way to do this by switching to a role by providing a password to confirm
access would be nice, especially if the user didn't have to be a member
of that role already.

Right now I'm having to spawn a new connection with the supplied
supervisor username & password, then do the work in that connection.
This works OK, but:

(a) The switch can't be done mid-transaction so that priveleges are held
for the minimum time possible. I'm looking at using `SET ROLE' to drop
down to lower rights in the supervisor connection instead, but this
could be clumsy when the main user has several roles significant for the
operation(s) being performed.

(b) The new connection doesn't hold the advisory locks the first
connection had, which is occasionally problematic.

(c) It's a PITA when working through an ORM like Hibernate, whereas a
`SET ROLE' would be trivial and convenient.

(d) I'd rather not spawn the extra backend, though I guess it doesn't
matter much with an event of this rarity.

Anyway ... I'm curious about whether `SET ROLE rolename WITH PASSWORD'
is something that's technically practical to implement in PostgreSQL and
what people think about the idea.

I don't see any obvious, gaping security issues with doing this, since
anybody who can 'SET ROLE ... WITH PASSWORD ...' should be able to log
in with those credentials too. However, there may be issues interacting
with external auth systems like ldap or kerberos. Thoughts?

Also: I'm currently thinking of writing a `SECURITY DEFINER' function
that tests a supplied password against a named role by direct access to
pg_shadow, and if the password matches invokes SET ROLE with that role.
Crazy?

--
Craig Ringer