Cassandra can not delete role or user which in the role or user list

Question

create ROLE testROLE with PASSWORD = 'test';
create ROLE testROLE with PASSWORD = 'test';

list ROLES shows the roles successfully.

DROP ROLE testROLE;
InvalidRequest: code=2200 [Invalid query] message="testrole doesn't exist"
DROP ROLE test1;
InvalidRequest: code=2200 [Invalid query] message="test1 doesn't exist"

Image of CQLSH output.

The role showed in the list but can not be deleted. How this can happen???

Benzyl

What is the output if you run DROP ROLE IF EXISTS testrole;? – Sunup

By chance, are you using Windows? – Cathee

@Cathee Linux 3.13.0-61-generic #100-Ubuntu SMP Wed Jul 29 11:21:34 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux – Benzyl

@Sunup nothing shows, after I execute that one, the roles list still keep the same. it's strange. – Benzyl

Answer 1

When not quoted, role names do not preserve case, as you can see from the list roles output. If your role names are case-sensitive, you'll need to enclose them in double quotes (like you would for keyspace or table names).

Did you edit the output you pasted? I ask because you shouldn't be able to run the same CREATE ROLE statement twice as shown. If so, was anything else elided? I suspect that you were able to successfully drop the role once, but not a second time (that's the expected behaviour & what I'm seeing).

Hefner

I also tried the testrole and testROLE. they show the same error info. – Benzyl

This exactly was the solution to my problem! :) – Iritis

Answer 2

From this: When migrating from Cassandra 2 to Cassandra 3, once all nodes have been migrated, one should delete the legacy tables system_auth.users, system_auth.credentials and system_auth.permissions.

If not done, Cassandra will write new roles to the new table, but try and read roles from the old one, which gives the behaviour described in the question.

Cavell