- In the example you listed below, role2 doesn’t lose superuser status because it was created as a superuser (based on the point 2). The scenario you mentioned can happen if role2 was initially not created as a superuser. When we create a role as a superuser, we persist that info in roles table, i.e, a role doesn’t lose that state by removal of a grant. Whereas acquired superuser status is not persisted, it is calculated on they fly looking at each role in the hierarchy
- Regarding removal of inheriting superuser role needs broader discussion. There must be a reason why it was done and removing this feature may impact existing use cases. So I believe we need a separate discussion just for that. > On Feb 29, 2024, at 12:36 PM, Bowen Song via dev <dev@cassandra.apache.org> > wrote: > > I believe that opens the door to this kind of situations: > > create superuser role "role1" > create superuser role "role2" > add "role2" to members of "role1" > remove "role2" from the members of "role1" > "role2" now inexplicitly lost the superuser state > TBH, my preferred solution is making superuser roles not inheritable. If a > role has members, it cannot be made superuser; and if a role is superuser, no > members can be added to it. > > It doesn't make much sense to inherit from a superuser role, because it has > unrestricted permissions, which renders any permission explicitly set on the > child roles useless. This enforces the role to be made superuser explicitly, > which makes all the display or filtering issues related to the inheritance > goes away. > > On 29/02/2024 11:30, Štefan Miklošovič wrote: >> Why don't we just update the is_superuser column of a role when it >> effectively achieves a superuser status when it is granted some superuser >> role? Similarly, we would remove its superuser status when there are no >> superuser roles granted to it anymore. >> >> I think that at least for the second case (when a superuser role is revoked >> and there is none anymore), there would need to be some transaction because >> as it checks if there are any superuser roles or not to set it to false, >> somebody else might grant that superuser role to it again so we might end up >> with having is_superuser set to false while it might still have a superuser >> role granted. >> >> I am not sure if this is achievable and I am sorry if this was already >> answered / rejected elsewhere. >> >> On Thu, Feb 29, 2024 at 11:33 AM <shailajako...@icloud.com >> <mailto:shailajako...@icloud.com>> wrote: >>> Hi Maxwell, >>> >>> Currently system_auth.roles table doesn’t have acquired superuser info >>> available in columns to filter on it. Below is the system_auth.roles table >>> for the example I have listed in the previous email. If you notice, though >>> role1 and role11 acquired superuser status through grants, is_superuser >>> column is False for these roles and acquired superuser status is not >>> apparent directly from the columns of this table. member_of column shows >>> immediate parent/grant of a given role. But these grants can result in a >>> huge tree of roles hierarchy and there may be a role anywhere up in the >>> hierarchy which is a superuser. >>> >>> cassandra@cqlsh> select * from system_auth.roles; >>> >>> role | can_login | is_superuser | member_of | salted_hash >>> -----------+-----------+--------------+------------+-------------------------------------------------------------- >>> role2 | False | False | null | >>> null >>> role11 | False | False | {'role1'} | >>> null >>> super1 | False | True | null | >>> null >>> role1 | False | False | {'super1'} | >>> null >>> >>> >>> Thanks, >>> Shailaja >>> >>> >>>> On Feb 29, 2024, at 2:11 AM, guo Maxwell <cclive1...@gmail.com >>>> <mailto:cclive1...@gmail.com>> wrote: >>>> >>>> Hi , >>>> 1. can this cql "SELECT role from system_auth.roles where is_superuser = >>>> True ALLOW FILTERING ;" meet your needs if the user to execute the cql >>>> have the right to do so ？ >>>> 2. I think may be we can also add the ability to filter on list role/user >>>> grammar, for example : list user where super = True; >>>> >>>> >>>> >>>> Shailaja Koppu <s_ko...@apple.com <mailto:s_ko...@apple.com>> >>>> 于2024年2月28日周三 20:40写道： >>>>> Hi Team, >>>>> >>>>> Currently LIST ROLES command doesn’t indicate if a role has superuser >>>>> privilege, if acquired through a grant in roles hierarchy (LIST ROLES has >>>>> super column true only if the role is created with SUPERUSER=true). For >>>>> example, in the below example, super1 is a superuser, role1 acquired >>>>> superuser status through grant of super1 and role11 acquired superuser >>>>> status through grant of role1. LIST ROLES output has super column true >>>>> only for super1. >>>>> >>>>> >>>>> cassandra@cqlsh> create role super1 WITH SUPERUSER = true; >>>>> cassandra@cqlsh> create role role1; >>>>> cassandra@cqlsh> create role role11; >>>>> cassandra@cqlsh> create role role2; >>>>> cassandra@cqlsh> grant super1 to role1; >>>>> cassandra@cqlsh> grant role1 to role11; >>>>> cassandra@cqlsh> list roles; >>>>> >>>>> role | super | login | options | datacenters >>>>> -----------+-------+-------+---------+------------- >>>>> role1 | False | False | {} | ALL >>>>> role11 | False | False | {} | ALL >>>>> role2 | False | False | {} | ALL >>>>> super1 | True | False | {} | ALL >>>>> >>>>> >>>>> One way to check has a role acquired superuser status is by running LIST >>>>> ROLES of <rolename> and looking for at least one row with super column >>>>> true. This works fine to check superuser status of a given role. >>>>> >>>>> cassandra@cqlsh> list roles of role11; >>>>> >>>>> role | super | login | options | datacenters >>>>> --------+-------+-------+---------+------------- >>>>> role1 | False | False | {} | ALL >>>>> role11 | False | False | {} | ALL >>>>> super1 | True | False | {} | ALL >>>>> >>>>> >>>>> But if we need to get list of all roles having superuser status (acquired >>>>> through grant as well), there is no easy way to retrieve this from C*. >>>>> This can be a requirement for an external service interacting with C* and >>>>> performing their own checks (for example, Sidecar). So I am proposing a >>>>> new CQL command LIST SUPERUSERS, which lists all roles having superuser >>>>> status (acquired as well). We will ensure that the user running this >>>>> command has DESCRIBE permission on root roles resource, i.e, to run this >>>>> command user must be either a superuser or granted DESCRIBE permission on >>>>> ALL ROLES. Here is the Jira >>>>> <https://issues.apache.org/jira/browse/CASSANDRA-19417> and sample output >>>>> for the above example. >>>>> >>>>> cassandra@cqlsh> list superusers; >>>>> >>>>> role >>>>> ----------- >>>>> role1 >>>>> role11 >>>>> super1 >>>>> >>>>> Alternatives thought of so far, >>>>> - LIST ROLES SUPERUSERSONLY >>>>> - LIST ROLES superuseronly=true >>>>> - LIST USERS superuseronly=true command : I have a question here, is >>>>> LIST USERS command deprecated? I see this link saying that >>>>> https://docs.datastax.com/en/cql-oss/3.3/cql/cql_reference/cqlListUsers.html. >>>>> if LIST USERS and LISR ROLES commands are same, why don’t we just pick >>>>> one so we don’t have to maintain two different commands ? >>>>> - LIST ROLES command default i.e, without NORECURSIVE clause : to print >>>>> super column true for acquired superusers as well, but this may break >>>>> existing tools/scripts of customers as we are changing the default >>>>> behavior >>>>> >>>>> >>>>> I prefer LIST SUPERUSERS command because - This command looks neat and >>>>> simple and we don’t have to worry about handling/breaking other >>>>> options/columns supported by these existing commands. For example we >>>>> don’t have to worry about handling/breaking OF clause of LIST ROLES >>>>> command. And any new options we add to these commands in the future, >>>>> don’t have to worry about handling/breaking of SUPERUSERS option. Please >>>>> let me know your thoughts on this. >>>>> >>>>> >>>>> Thanks, >>>>> Shailaja >>>>> >>>>> >>>>> >>>
