MIME-Version: 1.0
References: <CA+bysH_or91tt7r0gKLJtw5Wp+DEYwnaRJoTvLfKO33dcdQ_rQ@mail.gmail.com>
In-Reply-To: <CA+bysH_or91tt7r0gKLJtw5Wp+DEYwnaRJoTvLfKO33dcdQ_rQ@mail.gmail.com>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Mon, 24 Nov 2025 14:46:20 +0100
Message-ID: <CAFCRh-_evpaOaSNYqVNV7V2Fpibce4U4X11b3ZPj_HOqCPjseQ@mail.gmail.com>
Subject: Re: set role command
To: Calvin Guo <newoakllc2023@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAFCRh-_evpaOaSNYqVNV7V2Fpibce4U4X11b3ZPj_HOqCPjseQ%40mail.gmail.com>
Precedence: bulk

On Mon, Nov 24, 2025 at 9:15=E2=80=AFAM Calvin Guo <newoakllc2023@gmail.com=
> wrote:
> I really feel, once you "set role usera", you should behave like usera, y=
ou should NOT have the power say:
> hi, I can assume my super user power whenever I want. As this make the "s=
et 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) associate=
d to
that ROLE may not be "active", unless you INHERIT that ROLE. W/o INHERIT tr=
ue,
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_rol=
e.
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