Re: set role command

On Mon, Nov 24, 2025 at 9:15 AM Calvin Guo <newoakllc2023(at)gmail(dot)com> wrote:
> I really feel, once you "set role usera", you should behave like usera, you should NOT have the power say:
> hi, I can assume my super user power whenever I want. As this make the "set role usera" pretty much useless.

SET ROLE is only about switching between USAGE and MEMBER of other ROLEs.
Since v16, one can control individual ROLE-to-ROLE GRANTs, for INHERIT'ance.
You can be GRANT'ed a ROLE, yet the permissions (ACLs on objects) associated to
that ROLE may not be "active", unless you INHERIT that ROLE. W/o INHERIT true,
you must explicit SET ROLE to that role, to activate that role and its
permissions.

I thought like you initially that after a SET ROLE, that restricted
which ROLE I could endorsed.
But no, of course. Restricting SET ROLE on ROLEs from the subgraph
makes no sense, when
you can RESET ROLE to "get back up" to your "root" role (your LOGIN
role, or SESSION_ROLE),
and then SET ROLE to a different subgraph role.

Any ROLE you are a MEMBER of, you can SET ROLE to, whatever the current_role.
But by playing with INHERIT'ance, you CAN control what's
active/effective, for a given current role.
One can always SET ROLE to some other ROLE, of course.

What really matters is who CONNECT'ed / logged-IN to the DB. I.e. who
is authenticated.
If you start as a SUPERUSER, then you can always become SUPERUSER again, as
Laurenz pointed out. Changing ROLE is only about activate this or that set of
permissions / privileges, on DB objects. Nothing more. --DD