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

On Tue, Jan 2, 2024 at 8:25 AM Dominique Devienne <ddevienne(at)gmail(dot)com>
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
> 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?
>
> 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.
>
>
Membership no longer does anything by itself. Both inherit and set
capabilities are now individually controlled permissions related to
membership. It is indeed possible, but not useful, to grant membership but
then disallow both set and inherit permissions.

David J.