Questions about user roles

CrateDB
1

Hello Team,

I have a few questions about user roles.

SET UP
Empty database (login as user crate)
Execute the following commands
CREATE USER admin;
CREATE USER testUser;
CREATE ROLE role_admin;
CREATE ROLE role_test;
Grant AL to role_admin;
Grant role_admin to admin;
Grant role_test to testuser;

Login as admin
Grant role_test to testuser;
Grant role_admin to admin;

Issue 1
If you execute the command
SELECT name, granted_roles, password, superuser FROM sys.users order by name;
The user testuser has the role role_test twice.
Why can a user have the same role twice?
Is there a way to remove all roles from a user without logging in with all users who have set the roles?

Issue 2
If the user admin has been deleted, how can the role_test, which was set by the user admin, be removed from the user testuser?

Issue 3
Why can the user admin give himself the role_admin?
Why can a user give themselves a role?

Can someone answer my questions?
Thank you for your answers.

2

Hi, thank you for your report!

Issue 1:

Why can a user have the same role twice?

I was able to reproduce this, it looks like a bug to me, this only happens if the role is being given by another user, I’ll create an issue with the core team.

Is there a way to remove all roles from a user without logging in with all users who have set the roles?

No, at least, with the current implementation, if the first issue gets confirmed as a bug and is fixed, I suppose this will not be an issue anymore, otherwise we could raise a feature request to make easier to drop all roles.

Issue 2

If the user admin has been deleted, how can the role_test, which was set by the user admin, be removed from the user testuser?

I think it currently cannot be done, or I can’t find a way to, its a result of the current implementation.

Issue 3

Why can the user admin give himself the role_admin?
Why can a user give themselves a role?

The same answer as issue 1-2.

Overall it all seems to stem from the fact that when we grant a role to a user, it seems that we check that tuple (role, grantor) exists within the user instead of just role.

3

I reported the issue, you can follow it here Same role can be granted twice on a user · Issue #18099 · crate/crate · GitHub

Thanks again for your report, things like this help us improve!

4

Thank you for the quick reply.
I will follow the issue and wait for the solution.

5

Hi there! Just a heads up, the issue was fixed and will most likely be available in the next hotfix release: 5.10.11. It is planned planned for 2025/07/14 if nothing goes wrong.