Allowing users to only view data related to them in Apache Superset

Question

I have some information related to different vendors in my database and I want to allow each registered vendor (representative person) to view slices/dashboards which contains only data related to them. One possible solution could be to create separate views for each vendor as well as separate roles for each vendor. But it feels like a bad idea if you have 100+ vendors (as is my case); and it's not a flexible or scalable solution. Is there some way to automatically filter a given view for each user? For example, we have a "general profit by product" bar chart, and user X can see only products of vendor X

Nagel

Answer 1

What you're looking for is multi-tenancy support, and this is not currently supported out-of-the-box in Superset.

There is however an open PR for one possible solution: https://github.com/apache/incubator-superset/pull/3729

One option could be to re-use and/or adapt that code for your use-case.

Another option might be to look into JINJA_CONTEXT_ADDONS [https://github.com/apache/incubator-superset/blob/master/docs/installation.rst#sql-lab] and see whether you might be able to pass additional context to your query (e.g. your vendor_id) and restrict the scope of your query using that parameter.

Anthodium

Answer 2

One easy way of solving this problem is by using pre-defined JINJA parameters. Two parameters that can be used are '{{current_username() }}' and {{current_user_id() }}

First you need to ensure that you can use JINJA templates - In superset_config.py add the following

FEATURE_FLAGS = {
    "ENABLE_TEMPLATE_PROCESSING": True,
}

Restart

Now if you go to the SQL LAB and type the following -

 SELECT '{{ current_username() }}',{{ current_user_id() }};

You should get an output

?column?	?column?__1
PayalC	5

Now all you have to do is append one of the two following sql snippet in all your queries.

select ........ from ...... where ......  vendorid={{ current_user_id() }}

select ........ from ...... where ......  vendorname='{{ current_username() }}'

vendorid={{ current_user_id() }} and/or vendorname='{{ current_username() }}' will restrict the user to view only her data.

You could also make it more flexible by creating a table which has a mapping of user to vendorid. That table can be your added to all the queries and you could map multiple vendors to a single user or even all vendors to a single user for a super admin.

Sculptress

Answer 3

Superset config has the below two configurations(DB_CONNECTION_MUTATOR, SQL_QUERY_MUTATOR), which can allow for multi-tenancy to an extent.

A callable that allows altering the database conneciton URL and params on the fly, at runtime. This allows for things like impersonation or arbitrary logic. For instance you can wire different users to use different connection parameters, or pass their email address as the username. The function receives the connection uri object, connection params, the username, and returns the mutated uri and params objects. Example:

def DB_CONNECTION_MUTATOR(uri, params, username, security_manager, source):
       user = security_manager.find_user(username=username)
       if user and user.email:
           uri.username = user.email
       return uri, params

Note that the returned uri and params are passed directly to sqlalchemy's as such create_engine(url, **params)

DB_CONNECTION_MUTATOR = None

A function that intercepts the SQL to be executed and can alter it. The use case is can be around adding some sort of comment header with information such as the username and worker node information

def SQL_QUERY_MUTATOR(sql, username, security_manager):
      dttm = datetime.now().isoformat()
      return f"-- [SQL LAB] {username} {dttm}\n{sql}"
SQL_QUERY_MUTATOR = None

Headachy

Can the DB_CONNECTION_MUTATOR be used to make the same superset instance connect to different db hosts, depending on, say, the username or email id of the user? I am aiming to achieve multi-tenancy at the superset level by pointing different tenants to their own database hosts. – Heavenward

Technically yes, by overriding the DB_CONNECTION_MUTATOR and the security manager. However, it is rarely used for this use case. Better option would be to will be sync users from different tenants to superset instance and use JWT. Or enable single sign on if your org has it. – Headachy