Specifically set_session_auth(text): ------------- When set_session_auth(text) is called, the effective session and current user is switched to the rolename supplied, irrevocably. Unlike set_user() or set_user_u(), it does not affect logging nor allowed statements. If set_user.exit_on_error is "on" (the default), and any error occurs during execution, a FATAL error is thrown and the backend session exits.
This helps, but has the downside (of course) of being a compiled extension which limits its use on hosted services and such unless they decide to support it.
Would be really great if pooling could co-exist with per-user roles somehow, I'm not the best to weigh in on how, but it's bottlenecking the whole space of using roles per-user, and AFAICT this pattern would otherwise be totally feasible and awesome, with all the progress that's been made in this space.