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