Re: New SET privilege for pg_has_role() in v16+

On 1/2/24 07:24, Dominique Devienne wrote:
> Hi. And happy new year (for those using the Gregorian calendar).
>
> pg_has_role() from
> https://www.postgresql.org/docs/current/functions-info.html
> <https://www.postgresql.org/docs/current/functions-info.html>
> added the 'SET' privilege in v16, and on top of the existing 'MEMBER'
> and 'USAGE' ones:
>
> > MEMBER denotes direct or indirect membership in the role [...]
> > USAGE denotes whether the privileges of the role are immediately
> available without doing SET ROLE
> > SET denotes whether it is possible to change to the role using the
> SET ROLE command
>
> I'd like to know if possible why SET was added; the rationale for it.
> Does it not imply that MEMBER and USAGE weren't enough somehow before?
>
> If `pg_has_role(..., 'MEMBER')` is true, isn't `pg_has_role(..., 'SET')`
> implied?
> If not, why? (and is that related to NOT INHERIT roles in the graph
> between the two roles?)
>
> Asked differently I guess, when does being a MEMBER of a role (directly
> or not),
> NOT allow SET ROLE to that role?

https://www.postgresql.org/docs/current/sql-set-role.html

"Using this command, it is possible to either add privileges or restrict
one's privileges. If the session user role has been granted memberships
WITH INHERIT TRUE, it automatically has all the privileges of every such
role. In this case, SET ROLE effectively drops all the privileges except
for those which the target role directly possesses or inherits. On the
other hand, if the session user role has been granted memberships WITH
INHERIT FALSE, the privileges of the granted roles can't be accessed by
default. However, if the role was granted WITH SET TRUE, the session
user can use SET ROLE to drop the privileges assigned directly to the
session user and instead acquire the privileges available to the named
role. If the role was granted WITH INHERIT FALSE, SET FALSE then the
privileges of that role cannot be exercised either with or without SET
ROLE."

>
> We use ROLEs extensively in our PostgreSQL-based apps,
> and I've read a lot about them, but at times I feel I'm missing something.
>
> Thanks, --DD

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com