Lock a role to a workload
Assign a default workload to a role and prevent query overrides.
Lock a role to a workload
Settings profiles connect access control to scheduling. Give a role a default
workload, then use CONST
when that role must not choose another one.
Before you begin
- Set
RAWTREE_API_KEYto an administrator API key. - Use
analytics. If it does not exist, create it with thePOST /v1/databasesendpoint. - Pass
?database=analyticson each query. If you omit it, the Query API uses the API key's stored default database.
1. Create a role
Create a role for the service.
CREATE ROLE analytics_analyst2. Create a workload
Create a workload for the role.
CREATE WORKLOAD analyst_workload ON CLUSTER 'default'3. Set the role's default workload
Set a default workload and prevent the role from changing it.
CREATE SETTINGS PROFILE analytics_analyst_profile SETTINGS workload = 'analyst_workload' CONST TO analytics_analyst4. Create a role key
Create an API key that uses the role.
curl -X POST 'https://api.rawtree.com/v1/keys?database=analytics' \
--header "Authorization: Bearer $RAWTREE_API_KEY" \
--header "Content-Type: application/json" \
-d '{"name":"analytics_analyst","database_roles":["analytics_analyst"]}'The response contains the new key's ID and token.
{
"id": "<key-id>",
"token": "rt_..."
}Save the token value as READER_API_KEY and the id value as READER_KEY_ID.
5. Query with the role key
Run a query with the returned key.
curl -X POST 'https://api.rawtree.com/v1/query?database=analytics' \
--header "Authorization: Bearer $READER_API_KEY" \
--header "Content-Type: application/json" \
-d '{"sql":"SELECT 1"}'6. Test the workload constraint
Try to select another workload.
curl -X POST 'https://api.rawtree.com/v1/query?database=analytics' \
--header "Authorization: Bearer $READER_API_KEY" \
--header "Content-Type: application/json" \
-d '{"sql":"SELECT 1 SETTINGS workload = '\''batch'\''"}'The API returns HTTP 400 with this response.
{
"error": "rawtree_error",
"message": "Code: 452. DB::Exception: Setting workload should not be changed. (SETTING_CONSTRAINT_VIOLATION) (version 26.7.3.88 (official build))",
"hint": "Check your SQL syntax."
}READONLY and CONST constrain the individual setting. They do not grant
table access or replace a role grant.
Check the result
The query without a workload uses the profile default. The batch override
fails because the profile constrains
workload with CONST.
Cleanup
If the service no longer needs the role key, delete it.
curl -X DELETE "https://api.rawtree.com/v1/keys/$READER_KEY_ID" \
--header "Authorization: Bearer $RAWTREE_API_KEY"Drop the profile before you drop the role.
DROP SETTINGS PROFILE analytics_analyst_profileDrop the role after you delete its keys.
DROP ROLE analytics_analystDrop the workload after you drop the profile.
DROP WORKLOAD analyst_workload ON CLUSTER 'default'