In the YBA Platform, only user-created YCQL keyspaces can currently be backed up and restored; system keyspaces and tables, including roles and permissions, are not included in backups. As a result, if you back up a universe and restore it to a new one, you’ll need to manually recreate the user roles and granted privileges.
To simplify the manual process of creating roles and privileges, today’s YugabyteDB tip includes a shell script that reads system data from the system_auth keyspace and generates DDL commands to recreate roles and grants.
system_auth keyspace are referenced:
system_auth.rolessystem_auth.role_permissions
Example:
I have a YCQL database which has the following user created roles:
cassandra@ycqlsh> SELECT * FROM system_auth.roles WHERE role NOT IN ('cassandra');
role | can_login | is_superuser | member_of | salted_hash
--------+-----------+--------------+--------------------+------------------------------------------------------------------------------
role4 | True | False | [] | $2a$12$WcH/iCE4KoXk8BRO124Jv.dPXssgfx3Hb.K8dXZbR1.ktC5qdLDra\x00\x7f\x00\x00
role10 | False | True | [] | null
role6 | False | False | [] | null
role3 | True | False | ['role6', 'role4'] | $2a$12$V18hZ6ewJVGKwj/9KdeXJ./AUd0031u4vDy.ywx/v9LuyXPPBglvS\x00\x7f\x00\x00
role2 | False | True | ['role4'] | null
(5 rows)
There are two user created keyspaces each with one table:
cassandra@ycqlsh> SELECT table_name FROM system_schema.tables WHERE keyspace_name IN ('k1', 'k2');
table_name
------------
t
t
(2 rows)
Various permissions have been granted to the roles above as can be seen here:
cassandra@ycqlsh> SELECT * FROM system_auth.role_permissions WHERE role NOT IN ('cassandra');
role | resource | permissions
--------+-----------+-----------------------------------------
role10 | data | ['ALTER']
role3 | data | ['CREATE', 'ALTER', 'MODIFY', 'SELECT']
role3 | data/k1/t | ['SELECT', 'MODIFY', 'ALTER', 'DROP']
role2 | data/k1 | ['CREATE']
role2 | data/k1/t | ['SELECT']
role2 | data/k2/t | ['SELECT']
(6 rows)
The following script can be used to generate DDL commands for recreating the above roles and granted privileges:
#!/bin/bash
shopt -s expand_aliases
# Script: ycql_extract_roles_and_grants.sh
# Description: This script will extract the data from the system_auth.roles and
# system_auth.role_permissions tables, and then generate DDL commands
# which can be kept as a backup, or executed on another YCL database
# to migrate roles and grants.
# Created: 11/10/2024
# Last update: 11/10/2024
# Reference: https://docs.yugabyte.com/preview/api/ycql/ddl_create_role/
# https://docs.yugabyte.com/preview/api/ycql/ddl_grant_role/
# https://docs.yugabyte.com/preview/api/ycql/ddl_grant_permission/
# Call Spec:
# ./ycql_extract_roles_and_grants.sh <<YB TServer Host IP>>
# Important Notes:
# 1. This script is for YCQL only.
# 2. The Database Node IP must be running the YB T-Server process
# 3. Specify the path to the ycqlsh CLI via the YB_PATH variable
# 4. Specify the YCQL user and password in the YB_USER and YB_PASSWORD variables
# 5. The password for a role cannot be migrated. The default password that will
# be used in the generated DDL is defined on the ROLE_PASSWORD variable.
# Set up the YCQLSH alias below per your environment
YB_PATH='/root/yugabyte-2.23.0.0/bin/ycqlsh'
# Provide YCQL super user and password
YB_USER='cassandra'
YB_PASSWORD='cassandra'
# Password to use for as default when creating a role
ROLE_PASSWORD="ChangeMe"
# Check if IP or hostname was provided
if [ -z "$1" ]
then
echo "Please provide an IP or hostname for a YB Database Node"
exit 1
else
YB_IP=$1
fi
# Setup alias for ycqlsh
alias y="$YB_PATH $YB_IP -u $YB_USER -p $YB_PASSWORD"
# Step 1 (Create Roles)
y -e "SELECT * FROM system_auth.roles WHERE role NOT IN ('cassandra');" | awk -v rp=$ROLE_PASSWORD -F '|' '(NF==5 && FNR>=4) {gsub(" ","", $1); gsub(" ","", $2); gsub(" ","", $3); print "CREATE ROLE " $1 " WITH SUPERUSER=" $2 " AND LOGIN=" $3 " AND PASSWORD=\047" rp "\047;"'}
# Step 2 (Role membership)
y -e "SELECT * FROM system_auth.roles WHERE role NOT IN ('cassandra');" | awk -F '|' '(NF==5 && FNR>=4) {gsub(" ","", $1); gsub(" ","", $4); gsub("\\[","", $4); gsub("\\]","", $4); gsub(/\047/, "", $4); if ($4 != "[]") split($4,a,","); for(i in a) print "GRANT " a[i] " TO " $1 ";"'}
# Step 3 (All Keyspaces)
y -e "SELECT * FROM system_auth.role_permissions WHERE role NOT IN ('cassandra') AND resource = 'data';" | awk -F '|' '(NF==3 && FNR>=4) {gsub(" ","", $1); gsub(" ","", $2); gsub(" ","", $3); gsub("\\[","", $3); gsub("\\]","", $3); gsub(/\047/, "", $3); if ($3 != "[]") split($3,a,","); for(i in a) print "GRANT " a[i] " ON ALL KEYSPACES TO " $1 ";"'}
# Step 4 (Targeted Tables and Keyspaces)
y -e "SELECT * FROM system_auth.role_permissions WHERE role NOT IN ('cassandra') AND resource NOT IN ('data');" | awk -F '|' '(NF==3 && FNR>=4) {gsub(" ","", $1); gsub(" ","", $2); gsub("data","", $2); sub("/","", $2); gsub("/",".", $2); gsub(" ","", $3); gsub("\\[","", $3); gsub("\\]","", $3); gsub(/\047/, "", $3); if ($3 != "[]") split($3,a,","); for(i in a) if ($2 ~ "\\.") { print "GRANT " a[i] " ON " $2 " TO " $1 ";"} else { print "GRANT " a[i] " ON KEYSPACE " $2 " TO " $1 ";"}'}
exit 0
Let’s give it a try!
[root@cloud-server-0 ~]# ./ycql_extract_roles_and_grants.sh 127.0.0.1 > my_ycql_roles.sql
[root@cloud-server-0 ~]# cat my_ycql_roles.sql
CREATE ROLE role4 WITH SUPERUSER=True AND LOGIN=False AND PASSWORD='ChangeMe';
CREATE ROLE role10 WITH SUPERUSER=False AND LOGIN=True AND PASSWORD='ChangeMe';
CREATE ROLE role6 WITH SUPERUSER=False AND LOGIN=False AND PASSWORD='ChangeMe';
CREATE ROLE role3 WITH SUPERUSER=True AND LOGIN=False AND PASSWORD='ChangeMe';
CREATE ROLE role2 WITH SUPERUSER=False AND LOGIN=True AND PASSWORD='ChangeMe';
GRANT role6 TO role3;
GRANT role4 TO role3;
GRANT role4 TO role2;
GRANT ALTER ON ALL KEYSPACES TO role10;
GRANT CREATE ON ALL KEYSPACES TO role3;
GRANT ALTER ON ALL KEYSPACES TO role3;
GRANT MODIFY ON ALL KEYSPACES TO role3;
GRANT SELECT ON ALL KEYSPACES TO role3;
GRANT SELECT ON k1.t TO role3;
GRANT MODIFY ON k1.t TO role3;
GRANT ALTER ON k1.t TO role3;
GRANT DROP ON k1.t TO role3;
GRANT CREATE ON KEYSPACE k1 TO role2;
GRANT SELECT ON k1.t TO role2;
GRANT SELECT ON k2.t TO role2;
Please note that passwords cannot be migrated for roles. The provided script assigns the same password to all roles, so users should update their passwords as soon as possible.
There is a way for the YCQL superuser to update a role’s password by directly modifying the system_auth.roles table. I’ll cover this approach in a separate YugabyteDB tip!
Be sure to run the script after adding new roles or modifying existing role privileges to ensure your backup is always up to date.
Related tip: Extract YCQL Types Into DDL Commands
Have Fun!
