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
