Restrict an API key to selected data
Create a database role and issue a key that can read one table.
Restrict an API key to selected data
Use a database role when a service needs selected data, not every table.
Before you begin
- Set
RAWTREE_API_KEYto an administrator API key. - Use the
analyticsdatabase. 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. - Use the
eventsandprivate_eventstables, withactionanduser_idfields. Create both tables with the table API.
1. Create a role
Create a role for the service.
CREATE ROLE api_events_reader2. Grant table access
Grant SELECT on the event table.
GRANT SELECT ON analytics.events TO api_events_readerAdd a column list when the key needs access to selected columns only.
3. Create a role key
Create an API key that uses the database 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":"api-events-reader","database_roles":["api_events_reader"]}'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.
4. Query the granted table
Use the returned key to query the granted table.
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 action, user_id FROM events"}'5. Query an ungranted table
Check that the key cannot read the private table.
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 * FROM private_events"}'The request returns HTTP 400 with this response.
{
"error": "rawtree_error",
"message": "Access denied. You don't have permission to run this query.",
"hint": "Check the database user and role grants for the requested operation, database, table, and columns."
}Check the result
Confirm that the role key reads events and cannot read private_events.
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 role after you delete its keys.
DROP ROLE api_events_reader