How to Combine Template Variables with a user's Role?

allomorphy · January 2, 2022, 10:06pm

Hi there,

Is there a useful place of community contributed resources? I was thinking to place the following script where it might be of use to others. https://github.com/DIFFERENCECODE/grafana-provision-user

The script is used to automate the creation of new Organisations and Users in those Organisations.

I also have a question for some kind person in the know.
We want to define template variables for a dropdown to be populated by one or more users according to the following rules:

If the logged in user has the Role of Editor, then we we want the variable to contain all the users in the organisation.
If the logged in user has a Viewer Role, then only their name should be in the variable/dropdown.

My question that I could not find answered in the docs or on any forum::
Is it possible to build the query in the web interface for these variables from with the Grafana API or from the internal grafana.db? (I tried the -- Grafana -- datasource in the dashboard to define variables and alas no dice).
If we could use either of these to build these templated variables, that would be great. (Ideally not requiring development of a plugin).

If posting does not give us a way forward a direct method, then we will use the above script to replicate the Role information in influxdb. From influx, it should be straightforward to build a query according to the above rules (but an ugly solution).

My thanks and best wishes to all for 2022
Eric

mattabrams · January 7, 2022, 9:48pm

Hi @allomorphy !

I’ve started an awesome-grafana list on GH. I’d love it if you added your script to the list!

allomorphy · January 8, 2022, 11:41am

Awesome, Matt! These kinds of automation and utility scripts if well implemented and maintained are amazing. Support for the community is an issue in my experience so your initiative is much needed. Thank you.

I am wondering if I should first resolve that issue on the template variables for User and Org before adding to g-awesome
Still no response: https://github.com/grafana/grafana/discussions/43700
It would be much better not to “pollute" the script with this duplication into the database of Grafana.db information.

Would love your thoughts.

Thanks again for your initiative and interest.

My best wishes
Eric

mattabrams · January 10, 2022, 8:30pm

@allomorphy great looking script–looking forward to trying it out.

then only their name should be in the variable/dropdown.

Can you share that dashboard json? or at least the template variable config?

What if, in the script, you provision your dashboard as is (I’m assuming it is a query-based variable that is pulling data from influx?). But then add some flow control, so that for viewers you provision a slightly modified dashboard that uses a slightly modified query? Perhaps interpolating their name into it?

Might be way off here, but this is an interesting problem to figure out…

allomorphy · January 11, 2022, 8:47am

Thank you and dead right @mattabrams. That is the correct way forward and certainly if the template variable definition is not able to query internal Grafana data (like Roles).

I had considered this, but I failed a while ago provisioning dashboards and have not had the courage to try again. This looks like a time to do that (better than the ugly duplication into influx of Grafana data). I am assuming from your answer that Provisioning is able to customise per user. That is what we need! (Unlike the way that my script updates the dashboard with the API which is “per org”).

Here are one of a number of template variables defined in the dashboard. I cannot show the one in question here cause it is impossible and does not exist

Here is one of them:

        {
          "allValue": null,
          "current": {
            "selected": true,
            "text": "mMol",
            "value": "mMol"
          },
          "description": "Glucose Units",
          "error": null,
          "hide": 0,
          "includeAll": false,
          "label": "Glucose Units",
          "multi": false,
          "name": "glucose_units",
          "options": [
            {
              "selected": false,
              "text": "mg/dl",
              "value": "mg/dl"
            },
            {
              "selected": true,
              "text": "mMol",
              "value": "mMol"
            }
          ],
          "query": "mg/dl,mMol",
          "queryValue": "",
          "skipUrlSync": false,
          "type": "custom"
        },

Provisioning is much more elegant so I agree lets fix the architecture and then I will make the script more suitable.

Feel free to hop over to Grafana and see some public facing data.
I do not think on this public data we have defined annotations. When you define annotations, then you populate the measurement event table on the right of the graphs and are able to click on the desired event to go to that time slice. The real stuff is here: https://blogs.meterbolic.org

OT Whisper
Metabolic Health is the way out of COVID issues. Eventually they will get it. Grafana might be able to tell this story in a picture that changes the world. Help wanted!

I would love to help make certain things easier for others (and for myself!).

While we finish the provisioning script for release, I think the following zsh function is funky and might be interesting for CLI, for prototyping and perhaps even for production?

PUT POST GET DELETE() {curl --silent -X $0 -H "Content-Type: application/json" http://admin:$GRAFANA_ADMIN_PASSWD@$GRAFANA_SERVER:3333/api/$@ | jq '.'}

It would be easy to make it possible to call either like this:
GET orgs/$ORGID/users
or with the api in the path
GET /api/orgs/$ORGID/users

Thank you Matt for your assistance and support. It makes all the difference

Eric

mattabrams · January 11, 2022, 9:50pm

The Grafana core squads have confirmed that template variables cannot return a dynamic response based on Grafana data like a user’s Role. Alas

spent some time fiddling with the script. So I’m wondering:

the script will always create a user, and it will always upload a new or revised dashboard to their chosen org. This dashboard is downloaded from a canonical source. But this also means that the provisioned user’s role is somewhat irrelevant to the uploaded dashboard and its restrictions / permissions based on Role.

this means, I think?: a provisioned dashboard that includes sensitive data must be hidden entirely from those with certain roles (if that is the goal).

I know, this lacks the elegance of having one dashboard to rule them all, but I don’t think there is a way to restrict access and handle permissions on the level of template variables.

Have you considered using dashboard-level or folder-level permissions instead?:

then you could provision two dashboards that are effectively the same, except for the locked-down admin dashboard, a variable’s query could be SELECT * FROM users, and then the public dashboard uses a query more like SELECT $user FROM users? You could then select viewing and editing privileges as needed?

allomorphy · February 1, 2022, 2:21pm

Hello again @mattabrams

Apologies. Life took over and I was hoping to get to this much faster
This is on our critical path and we do want to make something useful for the community, especially for people to just run a script and go.

Provisioning of dashboards and datasources is obviously the way to go and not using the API for this like the script does. We made progress with provisioning and also with creating a sqlite datasource so that we can query grafana.db when defining template variables.

It would be amazing to have some help form a volunteer in the community to push this effort further faster.

Thank you for your great support and the community
Best wishes
Eric

allomorphy · February 13, 2022, 12:25pm

Hi @mattabrams

Thanks again for your suggestions.

As an overview, the use case is a organisation like a consultant or a service provider or Medical Doctor with multiple clients or users (the Member of the Org). The Org need to have the option to see the data individually for each of its members. A Member only sees their own data.

Many ways to fry that fish of course and your idea of folder permissions, I can see working fine.

Somehow I am still stuck in template variables and their cool dropdown. Cause that is precisely what I want. In the case of the Member themselves, that drop down will have a single option, their own login.

Here is what works (using Template vars).

Install the data source Sqlite per standard UX Grafana and point it to grafana.db
Define a variable, in this case with the Query:
WITH RECURSIVE for(i) AS (VALUES(1) UNION ALL select user_id from org_user where org_id=${__org}) SELECT (SELECT email FROM user WHERE id=i ) FROM for;
Result is a dropdown with all the current Members of the active Org
Use then this new variable, say $client or in our case $subject inn your panel query

And here is what fails:

Sqlite does not seem to support further complexity like an IF or CASE statement in the form of:
SELECT CASE WHEN ( 5 > 4 ) THEN ( SELECT 'foo' ) ELSE ( SELECT 'bar' ) END;
Why is this needed:
It uses SQL code in this form to check if the logged in User is Editor or Admin for the Org and then executes the query that works in 2. above. And if not, it returns the logged in users name like with the Grafana global $’{__user.name}’

When we use the POST method in the script to update the dashboard from canonical json for other Orgs, then there is an error on reading the Sqlite datasources.

Failed to upgrade legacy queries Datasource ItL49wa7z was not found
Templating [subject]
Error updating options: Datasource ItL49wa7z was not found

In the dashboard json, I see

              "datasource": { 
                  "type": "frser-sqlite-datasource", 
                 "uid": "ItL49wa7z"  
               },

and for the influxdb datasource that we use for our data

            "datasource": {
              "type": "influxdb",
              "uid": "000000001"
            },

There is also a hazard warning icon on the Sqlite datasource tin the Grafana UX, so I suspect that this is a beta.

So I have for these two niggles lost interest in Sqlite and plan to upgrade to postgresql

With regard to the script, I believe that dashboards and datasources should better be provisioned in the standard Grafana way and not POSTED via the API, so this will also be attended to.

If anyone would like to help make something along these lines for general usefulness, then please mention this

My best wishes and thanks again for Grafana
Eric