I hope I’m articulating myself properly since we’re new to memsql as a product.
We’ve identified the following behaviour and I’m not sure how to label it nor how to tackle it:
We’ve implemented rls and created 5 new roles, it seem only the first 3 roles we apply stick while the 4th, 5th, and assuming anything after that doesn’t return any results.
So if I have the roles A, B, C, D and E - which ever 3 I choose to apply first will return results, if I apply a 4th role no results what so ever gets returned.
Use cases:
Role A -> I get a table back
Role BC -> I get a table back
Role EAD -> I get a table back
If I introduce more than 3 roles, ANY 3 roles, I don’t get anything back.
After further investigation we found that the problem is related to the CURRENT_SECURITY_ROLES().
When you execute select CURRENT_SECURITY_ROLES() you get a list of all the roles of the current user which is 95 characters length.
When we are using it inside a query for filtering rows using RLS, the returned value of this function is trimmed down to 64 characters:
SELECT `sec`.`ACCESS_ROLES` AS `ACCESS_ROLES` ,roles.current_roles, SECURITY_LISTS_INTERSECT( roles.current_roles,`sec`.`ACCESS_ROLES`)
FROM (`cv_r_all_parents` as `parents`
JOIN `cv_r_security_base` as `sec`
ON ((`sec`.`cv_id` = `parents`.`parent_id`) AND (`sec`.`hierarchy` = `parents`.`hierarchy`)))
join (select CURRENT_SECURITY_ROLES() as current_roles) roles on 1 = 1
limit 5;