Multi-tenant access controls

If your database is a multi-tenant architecture, you can specify row-level policies over your tables in the ‘Access controls’ page located in your data source settings. You can define granular access controls across all of your users by using these row-level policies.

​

How do row-level policies work?

You are able to define row level policies for each of your tables using the same SQL dialect as your data source (i.e. BigQuery, Postgres, MySQL, etc.).

When Buster generates a query, we parse that query and deterministically apply your row-level filters as Common Table Expressions (CTEs). These filtered queries are built after the query has been validated and just before the query is sent to your database or warehouse. This ensures that User A only has access to User A’s data, User B to User B’s data, etc.

You are able to define dynamic variables that will be injected into your policy. These variables are the access variables you assign during user creation.

​

Access variables

Access variables are dynamic variables assigned during user creation that are used to filter the data that is returned to the user.

Here is an example of access variables for a user from a CRM database:

{
    "user_id": "8907dcd1-1919-474b-aba7-7f23a062d02e",
    "team_id": "c43a9bb6-ca17-49dd-b5bd-59250aae3e29",
}

These variables are injected into your row-level policies and are used to filter the data that is returned to the user.

​

How to write a row-level policy

Here are the steps to write a perfect row level policy:

1. All policies must start with SELECT * FROM table_name_here .

   We do not actually use a SELECT *. This serves as a placeholder for Buster.

2. Do not use aliases for columns or tables in the policy.

3. You are free to write any policy that is syntactically correct for the data source you are onboarding.

4. Wrap your access variables with the { } curly braces in the query.

​

Example of a row-level policy

If I had a table called customers and each user should only see the rows where their unique identifier matches the id column, then I would go about creating a user and writing the policy as follows:

1. Define the row level policy for the customers table:

   SELECT * FROM customers WHERE id = {customer_id}
   

2. Create your user:

   curl -X POST --location 'https://api.buster.so/api/v1/users' \
   --header 'Content-Type: application/json' \
   --header 'Authorization: Bearer <API-KEY>'
   --data '{
       "id": "8907dcd1-1919-474b-aba7-7f23a062d02e",
   		"project_id": "c43a9bb6-ca17-49dd-b5bd-59250aae3e29",
   		"name": "Buster Bluth",
   		"group": "Bluths Bananas",
   		"access_variables": {
   				"customer_id": "8907dcd1-1919-474b-aba7-7f23a062d02e"
   				# In this case, customer id is the same as the id
   		}
   }'
   

3. Your user can now ask questions and the row level policies will automatically be applied.

   See an example of a user asking a question related to the `customers` table

   1. User asks: “Can you show me all of my new customers from the last 3 months?”
   2. Buster generates:

      SELECT c.name, c.joined_at
      FROM customers c
      WHERE c.joined_at > current_date - INTERVAL '3 months';
      

   3. Buster applies the filter for the customers table:

      WITH customers AS (
      SELECT c.name, c.joined_at
      FROM customers c
      WHERE c.id = '8907dcd1-1919-474b-aba7-7f23a062d02e'
      )
      
      SELECT c.name, c.joined_at
      FROM customers c
      WHERE c.joined_at > current_date - INTERVAL '3 months';
      

   4. Buster responds with the user’s data, based on the row-level filter.