Обсуждение: 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.klaver@aklaver.com

On 1/2/24 08:21, Dominique Devienne wrote:
> On Tue, Jan 2, 2024 at 5:11 PM David G. Johnston 
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
> 
>     On Tue, Jan 2, 2024 at 8:25 AM Dominique Devienne
>     <ddevienne@gmail.com <mailto:ddevienne@gmail.com>> wrote:
> 
>         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:
> 
>     Membership no longer does anything by itself. 
> 
> 
> OK! That's news to me, I must go back to the v16 (?) release notes and 
> learn more about this.
> 
>     Both inherit and set capabilities are now individually controlled
>     permissions related to membership.
> 
> 
> Hmmm, what drove this change? (I guess I'm getting back to the rationale 
> from earlier).
> The previous model was not granular enough?
> And the new one is as granular as it gets?
> 
>     It is indeed possible, but not useful, to grant membership but then
>     disallow both set and inherit permissions.
> 
> 
> OK. Yet another thing I'll need to study.
> 
> As I wrote earlier, we use ROLEs extensively, some INHERIT and others 
> NOT INHERIT,
> to map an existing C/C++ enforce security model in mid-tier services, to 
> a ROLE/GRANT-based
> one enforced by PostgreSQL itself, thus understanding why these changes 
> were made in v16 matters to me a lot.

If you want the rationale see:

https://rhaas.blogspot.com/2023/01/surviving-without-superuser-coming-to.html

> 
> Thanks, --DD

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 1/2/24 08:15, Dominique Devienne wrote:
> On Tue, Jan 2, 2024 at 5:09 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 1/2/24 07:24, Dominique Devienne wrote:
>      > pg_has_role() from
>      > https://www.postgresql.org/docs/current/functions-info.html
>     <https://www.postgresql.org/docs/current/functions-info.html>
>      > <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:
> 
>     https://www.postgresql.org/docs/current/sql-set-role.html
>     <https://www.postgresql.org/docs/current/sql-set-role.html>
> 
>     "[...], if the role was granted WITH SET TRUE [...]"
> 
> 
> That seems to be the important part of your RFTM answer, which is 
> apparently new in v16.

There also seems to an override:

https://www.postgresql.org/docs/16/runtime-config-client.html#GUC-CREATEROLE-SELF-GRANT

"createrole_self_grant (string)

     If a user who has CREATEROLE but not SUPERUSER creates a role, and 
if this is set to a non-empty value, the newly-created role will be 
granted to the creating user with the options specified. The value must 
be set, inherit, or a comma-separated list of these. The default value 
is an empty string, which disables the feature.

     The purpose of this option is to allow a CREATEROLE user who is not 
a superuser to automatically inherit, or automatically gain the ability 
to SET ROLE to, any created users. Since a CREATEROLE user is always 
implicitly granted ADMIN OPTION on created roles, that user could always 
execute a GRANT statement that would achieve the same effect as this 
setting. However, it can be convenient for usability reasons if the 
grant happens automatically. A superuser automatically inherits the 
privileges of every role and can always SET ROLE to any role, and this 
setting can be used to produce a similar behavior for CREATEROLE users 
for users which they create.
"

-- 
Adrian Klaver
adrian.klaver@aklaver.com