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.
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.
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!
Thank you for the quick reply.
I will follow the issue and wait for the solution.
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.