Re: SET ROLE x NO RESET

On 12/30/23 11:16, Eric Hanson wrote:
> Hi,
> 
> What do you think of adding a NO RESET option to the SET ROLE command?
> 
> Right now Postgres can enforce data security with roles and RLS, but 
> role-per-end-user doesn't really scale:  Db connections are per-role, so 
> a connection pooler can't share connections across users.  We can work 
> around this with policies that use session variables and checks against 
> current_user, but it seems like role-per end user would be more 
> beautiful.  If SET ROLE had a NO RESET option, you could connect through 
> a connection pooler as a privileged user, but downgrade to the user's 
> role for the duration of the session.

+1

I agree this would be useful.

In the meantime, in case it helps, see

   https://github.com/pgaudit/set_user

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.
-------------

-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com