In this post, we will explore how we successfully implemented Row-Level Security (RLS) in Apache Superset to create a multi-tenant dashboard that dynamically filters data based on the logged-in user’s company.
The dvdrental database, based on the Sakila dataset, is a well-known sample database used for learning SQL and database management. It represents a DVD rental store, with tables for customers, rentals, payments, and films. To transform this database into a multi-tenant system, multiple companies needed to use the same database while ensuring they could only access their own data.
To achieve this, a company_id column was added to key tables such as rental, payment, and customer. This allowed the system to associate customers and transactions with specific companies. The main objective was to create a single dashboard in Apache Superset that could be used by different users while dynamically filtering data based on their assigned company.
The key requirements were:
Admins should have access to all data across all companies.
Company Users should only see data relevant to their specific company.
Row-Level Security (RLS) should be implemented to automatically filter data based on the logged-in user's company_id, retrieved from their email.
With RLS in place, data was filtered at the query level, ensuring that a single dashboard could be used securely by multiple companies. Admins had a complete view of all data, while regular users could only see company-specific information. The company_id was linked to user emails, allowing for seamless access control without requiring manual input from users.
This approach provided a secure, scalable, and efficient multi-tenant dashboard, enabling different companies to operate within the same system without exposing their data to others.
Defining Roles For Users
The configuration process begins in the Superset User Interface (UI), where roles and users are created and assigned specific permissions.
In the Security section of Superset, roles can be defined with specific access levels. Each role determines what a user can do within the system, such as accessing datasets, viewing dashboards, and reading charts. By assigning permissions at the role level, different users can have varying levels of access. For example, an Admin role is configured to have full access to all data, while Company User roles are restricted to seeing only data associated with their company.
Once roles are created, users are added in the Superset UI and assigned these roles. When a user logs in, the system automatically applies the RLS filter based on their company_id. Superset achieves this by dynamically injecting a WHERE clause into queries executed by the user.
This dynamic filtering ensures that users have access only to the data they are authorized to see, making Superset an efficient tool for multi-tenant dashboards while maintaining strict data security and access control.
After applying Row-Level Security (RLS) to each user and assigning them access to their specific rows, the admin retains full access to all the data across companies. For example, if there are 1,000 records in the admin table, one user might have access to only 200 rows, while another user might see 250 rows, depending on their company's data.
Dashboards
This is clearly reflected in the dashboards. The admin dashboard displays the complete dataset, including the total sales from all companies.
For instance, in the pie chart, you can see that the total sales for DVD rentals amount to 1.95 million, representing data from all over the country.
USER 1
USER 2
However, when viewing the dashboards of individual users, the total sales figure is noticeably reduced. This happens because their dashboards only reflect company-specific data.
In Conclusion
By implementing RLS, we achieve effective tenant-based filtering, ensuring that data is securely and efficiently segmented for different users. This not only enhances security but also improves data management, making it a powerful tool for multi-tenant applications.
Top comments (1)
There are many applications offering charting as a library. Good to see superset growing as a viable alternative to tableau offering no-code approach to building production grade charts and dashboards.