Don’t know why is_superuser was not set to true for acquired superusers. Now if we change it, may break existing tools/scripts/understanding of customers, so do we want to update current understanding of is_superuser column or introduce a new column or option instead?
> On Feb 29, 2024, at 11:30 AM, Štefan Miklošovič <stefan.mikloso...@gmail.com> > 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 >>>> >>>> >>>> >>
