@BTullis Can you please review permissions for the Product Analytics team? We should all have sql_lab access if we don't already.

Connie Chen
Irene Florez (iflorez)
Jennifer Wang (jiawang)
Kate Zimmerman
Maya Kampurath
Megan Neisler
Mikhail Popov
Morten Warncke-Wang
Neil Shah Quinn (Neil Quinn)
Shay Nowick

Thank you!

@BTullis Can you also review permissions for the Global Data & Insights team - I know my access has disappeared along with others - The following members should also have sql_lab access:
Christina Macholan
Tanja Andic
Yu-Ming Liou
Ntsako Maphophe
Caroline Myrick
Krishna Chaitanya Velaga
Jaime Anstee
Hiba Sha'ath
Isaac Looremeta
Hamid Ghani

@kzimmerman - I have added the sql_lab role to all of those users' accounts in Superset.

@JAnstee_WMF - I have added the sql_lab role to all of those user's account in Superset too, with the exception of @YLiou_WMF. I couldn't find a database record for his users account, so is it possible that he has never logged in to Superset?

I think it also makes sense to add:

• all members of the DE team as sql_lab users
• all SREs as Admin users

@odimitrijevic - do you have thoughts on a policy for this rights assignment in Superset?

What would be the ideal solution? Should SQL Lab access be allowed by default for all users, or is it best to assign rights based on team membership and requests?

We could map SREs into Superset Admin users automatically, based on their membership of the ops group in LDAP, but it will take a little work.
There's a lot of flexibility in the underlying authentication library: https://flask-appbuilder.readthedocs.io/en/latest/security.html#supported-authentication-types

all members of the DE team as sql_lab users

Maybe make all members of the DE team as Admin users?

In T328457#8578608, @Ottomata wrote:

all members of the DE team as sql_lab users

Maybe make all members of the DE team as Admin users?

Done. Eveyone from the team who has ever logged in, anyway.

Thanks @BTullis , I'll ask my team to check and we'll reach out if we need anything else!

Can I request a few engineers on the apps team to get the sql_lab role? Namely:
dbrant (myself)
sharvaniharan
cooltey
tsev
mazevedo
seddon

We occasionally have a need to run basic queries to verify our data integration.

In T328457#8634739, @Dbrant wrote:

Can I request a few engineers on the apps team to get the sql_lab role? Namely:
dbrant (myself)
sharvaniharan
cooltey
tsev
mazevedo
seddon

We occasionally have a need to run basic queries to verify our data integration.

Yes, I've now addded the sql_lab role to all of your accounts. Happy querying!

Hi @BTullis can you please add @JTannerWMF (jtanner) to the sql_lab role so that she can access queries and dashboards? Thank you.

I kindly ask granting sql_lab permissions to myself and the following WMDE colleagues of mine: @Aline_Bruenger_WMDE @Siko_WMDE. We'd like to query event data. Thank you!

+1 to defaulting to all, plus it can be useful to run sql queries against druid data cubes and it makes sense that everyone with superset access be able to work with those in that way in addition to charts

let's just default granting sql_lab access to all Superset accounts.

I agree in principle and I'll start looking at how best to configure this. However, I'd like to get approval from @odimitrijevic from a governance perspective, before implementing it.
In the meantime, I'll add to the sql_lab role of those accounts that have requested it in the last week or so. Apologies for any inconvenience caused by the delay.

I have added the sql_lab role to @JTannerWMF (jaz), @Aline_Bruenger_WMDE (alinebruenger), @Siko_WMDE (siko), @Jgiannelos (jgiannelos), @Urbanecm_WMF (urbanecm), and @Etonkovidova (etonkovidova) in superset production.

Thanks @BTullis i just verified i now can run queries in superset.

Hi @BTullis can you please grant myself (dani) the sql_lab role?

@DDeSouza done.

Thanks @Ottomata

Hi, could I (username spatton) please get access to the sql_lab role, too? Thanks!

@spatton - Thats' done for you now.

Hello, could you please give me the sql_lab role as well?

Also +1 to default but thanks @BTullis for handling these ad-hoc requests in the meantime. Adding to that, could you add all of Research staff/contractors if they're on Superset and don't already have it:

• Pablo Aragon (paragon)
• Nicholas Ifeajika (nickifeajika)
• Isaac Johnson (isaacj)
• Fabian Kaelin (fab)
• Miriam Redi (mirrys)
• Diego Saez-Trumper (dsaez)
• Mykola Trokhymovych (trokhymovych)
• Leila Zia (leizi)

Should already have it but including for completeness:

• Tanja Andic (tandic)
• Martin Gerlach (mgerlach)
• Caroline Myrick (cmyrick)

And I'll loop back about Yu-Ming Liou who will want access but we need to get basic analytics-privatedata-users access first.

Usernames taken from analytics-privatedata-users.

Thanks @Isaac :-) - I've added the sql_lab role to those users, as outlined below. Apologies that we haven't yet been able to work on updating the default permissions for all users.

In T328457#9009641, @Isaac wrote:

...Could you add all of Research staff/contractors if they're on Superset and don't already have it:

Should already have it but including for completeness:

And I'll loop back about Yu-Ming Liou who will want access but we need to get basic analytics-privatedata-users access first.

Usernames taken from analytics-privatedata-users.

So if you let me know when Yu-Ming Liou and Nicholas Ifeajika have logged in successfully, I'll add the role to their accounts too.

Thanks @BTullis! I'll loop back for Yu-Ming and Nicholas when needed and we have them setup with the other perrmissions etc.

Did I do the right think when posting here above asking for the sql_lab role?

In T328457#9009729, @KSiebert wrote:

Did I do the right think when posting here above asking for the

sql_lab
``` role ?

Hi @KSiebert - Yes you did. Apologies that I failed to notice your first message.
I've added the role to your account now, so you should be able to access SQL Lab in Superset.

@BTullis a few more (I was able to create a chart that some people now want to see)!

• @Maryana: https://wikitech.wikimedia.org/wiki/User:Maryana
• @NHillard-WMF: https://wikitech.wikimedia.org/wiki/User:Nathillard

In T328457#9010006, @Isaac wrote:

@BTullis a few more (I was able to create a chart that some people now want to see)!

• @Maryana: https://wikitech.wikimedia.org/wiki/User:Maryana
• @NHillard-WMF: https://wikitech.wikimedia.org/wiki/User:Nathillard

Those two accounts have also had the role added now. 👍

I was able to create a chart that some people now want to see

Excellent!

Those two accounts have also had the role added now. 👍

Thanks!

Oh, this is a little less elegant than I first thought. The reason being that we use AUTH_REMOTE_USER and have it enabled with CAS via an Apache proxy.
Here's a link to the code for how FAB deals with this type of authentication:

Essentially, unlike all of the other available methods (LDAP, OAuth, OID, DB) it doesn't have a built in method for looking up roles and assigning keys.

It looks like we have two options:

1. Create a custom role and manually assign it the union of permissions of Alpha and sql_lab from here. I can then make sure that this is the AUTH_USER_REGISTRATION_ROLE instead of what it currently is, which is Alpha
2. Implement OpenID connect and use this instead of the Apache/CAS proxy. This has been done by several other community users (see here and here and here) but it would require patching our version of superset with a new security manager, which we would have to write and maintain in python. We would then be able to get a list of groups of which the user is amember and use them in an AUTH_ROLE_MAPPING to assign roles based on these groups.

Option 1 would be a quicker and easier way to solve this particular problem, but might need manual rights updates when new versions are released.

Option 2 is more elegant, but would require patching our build of superset with a new security manager and expanding the configuration a bit.

I think I'm inclined to go with option 1 for now. It's the quicker and easier option.

Ideally, I'd like to get us to Superset 2.1 soon, running under Kubernetes on the dse-k8s cluster and using fresh docker images built by the new PipelineLib system under GitLab-CI. Maybe we could even even de-couple the MariaDB requirement from analytics_meta (which runs on an-coord100[1-2]] as well, if we're lucky.

Therefore, I think that is we start patching our Superset 1.5 in support of adding OpenID Connect support now, then this might make it more tricky to upgrade later on. We would have to replicate this patching when we migrate our Superset build from the legacy to the new build system. Any changes we make for option 1 are likely to be 100% reversible when we migrate to Superset 2.1 as well.

@Gehel, @odimitrijevic - What do either of you think? Would you be happy with this approach of adding a custom role, or would you rather that we take the time to customize our superset codebase to support more flexible group assignment?

I've now created a 'WMF Analyst' role in both the production and test instances of Superset.

The easiest way for me to apply this role to all users will be via MariaDB directly, I believe.
I looked at whether there was a superset fab command to apply a new role to users in bulk, but it appears that there isn't.

(venv) superset@an-tool1010:/home/btullis$ superset fab --help
Loaded your LOCAL configuration at [/etc/superset/superset_config.py]
Usage: superset fab [OPTIONS] COMMAND [ARGS]...

  FAB flask group commands

Options:
  --help  Show this message and exit.

Commands:
  babel-compile       Babel, Compiles all translations
  babel-extract       Babel, Extracts and updates all messages marked for...
  collect-static      Copies flask-appbuilder static files to your projects...
  create-addon        Create a Skeleton AddOn (needs internet connection to...
  create-admin        Creates an admin user
  create-app          Create a Skeleton application (needs internet...
  create-db           Create all your database objects (SQLAlchemy...
  create-permissions  Creates all permissions and add them to the ADMIN...
  create-user         Create a user
  export-roles        Exports roles with permissions and view menus to JSON...
  import-roles        Imports roles with permissions and view menus from...
  list-users          List all users on the database
  list-views          List all registered views
  reset-password      Resets a user's password
  security-cleanup    Cleanup unused permissions from views and roles.
  security-converge   Converges security deletes...
  version             Flask-AppBuilder package version

The user interface doesn't provide a way to add a role in bulk either, so the simplest way will be to execute a single UPDATE command in MariaDB to change all Alpha users to WMF Analyst users.

MariaDB [superset_production]> select * from ab_role;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | Admin       |
|  3 | Alpha       |
|  4 | Gamma       |
|  5 | granter     |
|  2 | Public      |
|  6 | sql_lab     |
|  8 | WMF Analyst |
+----+-------------+
7 rows in set (0.000 sec)

MariaDB [superset_production]> select count(*) from ab_user_role where role_id=3;
+----------+
| count(*) |
+----------+
|      411 |
+----------+
1 row in set (0.001 sec)

MariaDB [superset_production]>

Change 950157 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/puppet@production] Update the default user role in Superset to be 'WMF Analyst'

https://gerrit.wikimedia.org/r/950157

I've created a patch to change the default user role to 'WMF Analyst' and I'll aim to merge this next week and switch all existing 'Alpha' users to 'WMF Analyst' users.
Once this is done, we can also remove the manually applied sql_lab roles, although this is only to be extra neat and tidy.

Change 950157 merged by Btullis:

[operations/puppet@production] Update the default user role in Superset to be 'WMF Analyst'

https://gerrit.wikimedia.org/r/950157

I have updated all roles on the staging database and logged in. Everything appears to work correctly.

MariaDB [superset_staging]> select * from ab_role;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | Admin       |
|  3 | Alpha       |
|  4 | Gamma       |
|  5 | granter     |
|  2 | Public      |
|  6 | sql_lab     |
|  8 | WMF Analyst |
+----+-------------+
7 rows in set (0.002 sec)

MariaDB [superset_staging]> select count(*) from ab_user_role where role_id=3;
+----------+
| count(*) |
+----------+
|      366 |
+----------+
1 row in set (0.001 sec)

MariaDB [superset_staging]> select count(*) from ab_user_role where role_id=8;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.001 sec)

MariaDB [superset_staging]> update ab_user_role set role_id=8 where role_id=3;
Query OK, 366 rows affected (0.014 sec)
Rows matched: 366  Changed: 366  Warnings: 0

Updated the production Superset database.

MariaDB [superset_staging]> use superset_production;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [superset_production]> select * from ab_role;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | Admin       |
|  3 | Alpha       |
|  4 | Gamma       |
|  5 | granter     |
|  2 | Public      |
|  6 | sql_lab     |
|  8 | WMF Analyst |
+----+-------------+
7 rows in set (0.000 sec)

MariaDB [superset_production]> select count(*) from ab_user_role where role_id=3;
+----------+
| count(*) |
+----------+
|      411 |
+----------+
1 row in set (0.001 sec)

MariaDB [superset_production]> select count(*) from ab_user_role where role_id=8;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.001 sec)

MariaDB [superset_production]> update ab_user_role set role_id=8 where role_id=3;
Query OK, 411 rows affected (0.014 sec)
Rows matched: 411  Changed: 411  Warnings: 0

MariaDB [superset_production]>

HI @BTullis following up on the discussion from T344257#9114255. I'm still encountering issues running queries in Superset.

I went through the comparing the permissions of the sql_lab role and the WMF Analyst role and I found one discrepancy that was not mentioned in the standard roles page. This is the permission can sql json on Superset

@OSefu-WMF - Would you be able to try again for me please to see if you're able to execute queries in SQL Lab, with this additional permission? Apologies for the delay in updating this for you.

@BTullis - Looks like that fixed it! Thanks.

@BTullis: Sorry if you are not the right person for this but it seems like we are having a related permissions issue in T362533 where @Aitolkyn is unable to use SQL Lab and I suspect that this is related. If not, please let me know. Thanks!

In T328457#9714365, @ssingh wrote:

@BTullis: Sorry if you are not the right person for this but it seems like we are having a related permissions issue in T362533 where @Aitolkyn is unable to use SQL Lab and I suspect that this is related. If not, please let me know. Thanks!

Sorry for the noise, it was not a permissions issue. Please ignore :)