Row Level Security (RLS)

Overview

Row Level Security (RLS) is a Preset feature that is used in tandem with Data Access Roles to enable you to exert a granular level of control over who can query and view specific data in selected datasets. 

Concept

What is Row Level Security (RLS)?
Row Level Security (RLS) rules limit which rows of your data can be accessed by specific groups of users. Functionally, this means that a WHERE clause is added to all queries on a specific set of data for a particular set of users. These users will only be able to view certain rows of your data. You can apply Row Level Security to all workspaces roles. 

You can either specify a group of users whose queries will be limited by the rule or you can specify a group of users whose queries won't be limited by the rule. 

What is the relationship between RLS and Data Access Roles?
Data Access Roles are used to define access based on datasets, schemas, or databases and to assign which users will be associated with those roles. Row Level Security (RLS) is used to add additional rules to limit which rows of the data are accessible to users.

For example, a "Sales Rep" data access role may have access to datasets relating to deals, prospective customers and sales achievement metrics. A Row Level Security rule may then be applied to the "Sales Rep" data access role to limit the rows visible to those associated with the specific Sales Rep region, such as Region = "Europe". 

In Summary
An RLS rule is used to define what rows within a dataset can be queried. After defining a RLS rule, it can be assigned to a Data Access Role. To create an RLS rule follow the steps below.

Workflow for Creating Row Level Security Rules

Types of Row Level Security Filters:

The two types of filters are regular and base.  A regular filter applies to all queries for the user belonging to that role. A base filter applies to all queries for users who are not added to the role.

Regular filters add WHERE clauses to queries if a user belongs to a role referenced in the rule.

Example:
I have a group of sales people who will only be seeing data for their region. I'd create a Regular filter which has the clause Region = 'North West' and I would assign that rule to the data access role which includes the sales people responsible for the north west territory.

Base filters add WHERE clauses to all queries except the roles defined in the rule, and can be used to define what users can see if no RLS rules within a rule group apply to them.

Example:
I want all users viewing our customer-facing dashboards, except our data team, to be limited to the last 3 months of data. I create a Base filter, assign my data team to it, and add a clause to limit the time range to the last 3 months.

Example Use Case:

Row level security can be used to create a permissions set up where my sales representatives all use the same dashboard but they each only see the data associated with their Region (either AMER, APAC, or EMEA). 

The dashboard we're going to be using for this example is the Vehicle Sales dashboard which is one of the dashboards that included as a part of your workspace by default.

Prerequisites:

• I've invited the sales team users to the workspace 
• The sales team users have logged into Preset at least once
• I've created the datasets that I want to use in the RLS rule

To start off, I will by create a data access role for all of these users and giving them access to the public.Vehicle Sales dataset. 

Create a Data Access Role:

1. In the "Settings" drop down on the top right hand corner of your workspace, select "Data Access Roles" 
2. Click the " + " in the top right hand part of the page to create the new role
3. Fill out the "Name" field for the form
   1. For this example, I will create one data access role called "Sales Team - All"
4. Add users to the "Users" field
5. For "Permissions" select "Dataset access on examples.Vehicle Sales"
   1. For more information on the permissions field see Data Access Roles 
6. Click "Save"

Next I will create a blank data access role for each of my regions. I will be creating three data access roles named:"Sales Team - AMER", "Sales Team - APAC" and "Sales Team - EMEA" and these are the data access roles which I will be applying the Row Level Security rule to.

Create a Blank Data Access Role:

1. In the "Settings" drop down on the top right hand corner of your workspace, select "Data Access Roles"
2. Click the " + " in the top right hand part of the page to create the new role
3. Only fill out the "Name" field for the form, leaving the "Users" and "Permissions" fields blank
   1.  For this example, I will be creating three separate data access roles named:"Sales Team - AMER", "Sales Team - APAC" and "Sales Team - EMEA" 
4. Click "Save"

I won't be adding the users to these roles until I create my Row Level Security rules and I will also keep the Permissions blank. I will then create three Row Level Security rules for each of my regions: "RLS Sales Team - AMER", "RLS Sales Team - APAC" and "RLS Sales Team - EMEA". I will select which dataset / schema / database these rules will apply to and I will write my clause which will limit the users to only view the relevant rows.

Create a Role Level Security Rule:

1. In the "Settings" drop down on the top right hand corner of your workspace, select "Row Level Security"
2. Click the " + Rule " in the top right hand part of the page to create the new role
3.  Add the following settings:
   1. Rule Name: "RLS Sales Team - EMEA" 
   2. Filter Type: Regular 
   3. Tables: public.Vehicle Sales
   4. Roles: "Sales Team - EMEA" 
   5. Group Key will be left blank
   6. Clause:  territory IN ('EMEA')
   7. Description: This RLS rule will restrict the access of the EMEA sales team to only view data from the EMEA region when accessing the Vehicle Sales Dataset
4. Click "Save"

Add Users to the Blank Data Access Role:

1.  In the "Settings" drop down on the top right hand corner of your workspace, select "Data Access Roles" 
2. Select one of your data access roles: "Sales Team - AMER", "Sales Team - APAC" or "Sales Team - EMEA"
3. Add the sales people to their respective regions.
4. Click Save

Result:

Now all of my EMEA territory sales people have been added to the data access roles: "Sales Team - All" and "Sales Team - EMEA" will have the clause:

WHERE territory IN ('EMEA')

applied to all chart queries using the public.Vehicle Sales dataset. They will only be able to view data which has 'EMEA' in the territory column. 

Example 2: Define Inclusive Regular RLS Rule

Create Data Access Role

The first step is to create a Data Access Role.

In the Toolbar, hover your cursor over Settings and then select Data Access Roles.

Select + Add new record and, in the Name field, enter Wii Platform. This should be a descriptive term for the new role.

For now, select Save and let's define an RLS rule.

Create RLS Regular Rule

As above, navigate to Row Level Security, add a new record, and define the following fields:

• Filter Type: Select Regular. This filter type adds WHERE clauses to queries if a user belongs to a role referenced in the rule.
• Tables: Select public.Video Game Sales.
• Roles: Select Wii Platform, as defined above. The rule will be applied to any role(s) selected here.
• Group Key: Enter platform. To view options, use SQL Editor to explore the dataset.
• Clause: Enter platform = 'Wii'. This is a condition, expressed in SQL, that defines the rule to apply to the query. In this case, the rule will limit the display of platform data to "Wii".

When done, select Save.

Objective

Create a base filter that excludes query access to all game platforms within the dataset — this will be applied to all users. We will then create a regular filter that only grants access to video games using the Wii platform — this will be applied to a Data Access Role. The end result is that users who are assigned to the role will be able to view a chart that only includes video games on the Wii platform.

Example 3: Define Exclusionary Base RLS Filter

To start, we will create a base filter that excludes access to all video game platforms within the dataset.

In the Toolbar, hover your cursor over Settings and then select Row Level Security.

Select + Add new record and complete the following fields:

• Filter Type: Select Base. This filter type applies to ALL queries.
• Tables: Select public.Video Game Sales.
• Roles: For this use case, leave blank (i.e., applies to everyone). Since this is a base type, the rule will not be applied to any roles that are defined in this field.
• Group Key: Enter platform. To view options, use SQL Editor to explore the dataset.
• Clause: Enter 1 = 0. This is a condition, expressed in SQL, that defines the filter to apply to the query. In this case, the false query "1 = 0" will always return no rows.

When done, select Save.

Now let's view the "Games" table chart to test out the base filter.

As expected, the Base RLS filter is preventing access to data within the Video Game Sales dataset.

Associate Role with a User

Lastly, we will associate the Wii Platform role with a user.

Navigate back to Data Access Roles and select Edit record for the Wii Platform role you created earlier.

In the Users field, select one or more users to which the role will be applied.

When using RLS in conjunction with Data Access Roles, keep the Permissions field blank.

Select Save.

RLS Rule Verification

Your Data Access Role screen should look similar to the following:

...and your Row Level Security screen should look as follows:

Now let's test the RLS regular filter by navigating to the "Games" table chart and running the query.

As you can see, the user can now only access records from the dataset where the platform is "Wii" (note the Platform column in the table).

Advanced Row Level Security using Username

Admins who have user access information stored directly in their database can use RLS to automatically control data access scalably.

To set this up, you will use a combination of Jinja macros, APIs, and the Regular RLS filter.

Before creating the new RLS rule, you will need to use the Preset API.

Mapping Preset Usernames to Emails

Using the Get Team Members API, you can regularly retrieve a list of email addresses and Preset usernames for your users. This can be retrieved by your ingestion engine and stored in your database integration layer. You can then join these Preset usernames to any existing data tables that contain user access by email.

Set up the Row Level Security Rule

Once the Preset username is stored in your tabes, you can set up the Regular RLS filter using the Preset Jinja maco. First, choose Regular as the Filter type and select the tables in your database that both contain the user access information and require filtering based on the user access

Add a Clause statement at the bottom for the WHERE clause that filters data access based on the user's login username.

[myusercolumn]='{{ current_username()}}'

Replace [myusercolumn] with the column in your table that contains the Preset username of who should be able to access this data.

You're all set! Your users now will only see data filtered to them when querying the tables you have selected.