A simple way to obtain results restricted to logged user (aka: Row Level Security)

Development
1

After some tweaking I found a way (probably not the best) to achieve this in Redash

The idea is to use username or its groups to filter data, so each user sees only the data marked for him. The same query and datasource could serve for all users.

Filtering data could be done easily with simple conditions ( i.e select * from data where ….. and allowed_groups in (redash_user_groups) )

I tried some different options at client or backend, and finally the backend one seems best.

For example, this could be a simple query to start trying to check values at execution time. (The values we want to be substituted should have some fixed specific names, not easy to be used some other way)

Select
'__REDASH_USER'        as redash_connected_user, 
'__REDASH_USER_GROUPS' as redash_user_groups, 
from dual

The aim is to substitute __REDASH_USER with the actual user who retrieves data and __REDASH_USER_GROUPS with his redash groups at execution time.

After some tests the easier way to do it is at backend, specifically at app/redash/handlers/query_results.py Adding some lines at run_query

    (...)
    query_text = data_source.query_runner.apply_auto_limit(
        query.text, should_apply_auto_limit
    )

    # RLS  Substitute username or groups.  Turn cache or previous results off with max_age=0

    if '__REDASH_USER' in query_text or '__REDASH_USER_GROUPS' in query_text:
        if hasattr(current_user, 'email'):
            query_text = query_text.replace('__REDASH_USER_GROUPS',','.join(str(v) for v in current_user.group_ids))
            query_text = query_text.replace('__REDASH_USER',current_user.email)
        else:
            query_text = query_text.replace('__REDASH_USER_GROUPS',' -1 ')
            query_text = query_text.replace('__REDASH_USER','guest@example.com')
        max_age=0

      # END RLS

    if query.missing_params:
   (...)

That way, at query execution time, __REDASH_USER would be substituted with user’s mail and __REDASH_USER_GROUPS would be substituted with a list of comma separated values of the redash groups ids the user belongs to.

Then, the result is

REDASH_CONNECTED_USER  REDASH_USER_GROUPS
somebody@mydomain.com           1,2

So we can use these values in any query we want to filter data depending on users or groups (preferably)

One potential problem could be cached or previous results, but seems that setting max_age=0 when substitution is done no results are stored or cached (at least on my tests, probably needs deeper testing)

It is important to note that public or using the api_key way of retrieving data have no username associated, so this substitution just returns ‘guest@example.com’ and -1 as group.

(In #3723 there is a similar discussion with other terms)