Use user credentials for datasource connection

[scherepanov]

Can we add option - use user credentials for Vertica datasource connection?

That will nicely address all issues with user permissions. Existing approach - having single Vertica account that is hardcoded in connection and used by all graphana users - have a lot of security problems.

It can be one-time use connection, created before query executed, and closed after. Or, you can create connection when user login, and keep it open until session closed or expired.

That would be a very nice option to have.

[verticacrossman]

Hi, Reviewed your request and had a few comments based on how I interpret it. Hopefully it will lead to clarification of request or acceptance of current methods.

The Grafana plugin was designed using Grafana's best practices and follows the same basic connectivity methods as the PostgreSQL, Snowflakes, MySQL, and other data source plugins follow. That is, when defining a data source using the plugin there is a single user/pwd pair defined and that pair is used in the connection to the database. That user typically would be one specifically created for use with the plugin and Grafana, and all privileges and grants would be handled in the database side to ensure correct visibility. In the Vertica plugin we recommend a user with the SYSMONITOR role, which allows it to see all system tables but no user tables unless granted. And you could of course create multiple Vertica data sources, maybe one for system tables and one for user tables. All roles, grants, and privs dictated on the Vertica database end. In Grafana Enterprise and Cloud there is an additional tab on each datasource where you can set permissions, which Grafana users/teams are allowed to use the data source, for added security.

Our plugin uses the Vertica SQL Go driver in the back end and the Grafana gui is handled by the React front end. If your proposal is to add some sort of delegate user in the data source it's not clear what benefit it would have. It would have ot be added in the front end UI, and only Grafana admins can create/modify datasources. So adding a field for -user in the front end would provide nothing different than just changing the user/pwd. Ultimately it would be saved in the data source so effectively hard coded until the next time it was changed.

In general I think any type of alternate user schema would have to filter down to all places where the plugin connects to Vertica (query builder, keep alive test, raw sql query, etc.).

If we misinterpreted your request please add more details. And don't forget, this is open source and we invite outside contributions to better the project, So feel free to build a mock up of the desired behaviour with details of it's benefits for review.

Regards,.

[scherepanov]

Hi,

Thanks for answering. Yes, you correctly understand my request, as well as architecture of whole thing.

I have a complicated Vertica environment with multiple security domains (around 100), each with it's own schema/tables, and separate and independent permission for each of them. Graphana datasource has hardcoded credentials set by Graphana admin. Yes you can create separate datasource for each security domain, with it's own credentials. Then you can start granting datasources to individual users and roles.

Then it starts fun part. For organisation with thousand(s) employees you need to replicate all users and team permissions inside Graphana, to give access to each datasource.

Another fun part is when employee need to run query that join data from tables in several security domains. That is a very typical activity. As tables will be accessible by different Graphana datasources, it is plain not possible, unless you will create new datasource that include all security domain, and start managing permissions on it separately.

That leads to restricted Graphana implementation. I have a single datasource, with hardcoded credentials, corresponding to single largest security domain. It is pretty much wide open security-wise. Anybody who wants to access tables outside single largest security domain, being rejected.

I am not sure that Enterprise will address security properly. I do not know how you can support users who need to run join on tables in several security domains, i.e. in several non-overlapping datasources. You will need new datasource that includes all tables user need to join. Managing permission on new datasource would be non-trivial, I doubt it can be supported in Enterprise version.

A very straightforward solution would be to run user queries under his own credentials. That nicely address all issues, as user cannot see anything that is not granted on database level.

[verticacrossman]

Thanks for the details. Assuming "multiple security domains" could mean multi tenant environment or multiple databases, key element for this discussion is different groups of users each with their own Vertica realm they work and play in.

"For organisation with thousand(s) employees you need to replicate all users and team permissions inside Graphana, to give access to each datasource."

I haven't extensively played with Grafana folders using team permissions, but it seems to possibly be a good way of controlling what teams have access to dashboards that are stored under folders. So instead of adding users to each data source you could: create a team for each security domain add desired users in that domain to the team create folder(s) for each domain/data source move each data source's dashboards to their respective folder add team permissions as desired to each folder

From some simple testing I am able to add a user to multiple teams, and add multiple teams with varying permissions to a folder. So I have one set of users, one set of teams, and ability to give them desired access to my various domains/folders without user or team replication. And only the Grafana admins can do these setup operations.

"Another fun part is when employee need to run query that join data from tables in several security domains. That is a very typical activity. "

This joining tables across data sources is definitely a tough one. Almost seems like it would require a federated query engine with a Grafana data source plugin (example Presto, unfortunately it doesn't have a Vertica connector) to get the complexity out of Grafana and closer to the database.

"A very straightforward solution would be to run user queries under his own credentials. "

This wasn't completely clear to me. Does "under his own credentials" mean Grafana users have same names as Vertica users, so if Bob Smith logs into Grafana and opens any dash with any data source, that his Bob Smith username would be passed to the database as the connect user (single sign on as he can't provide password on the fly in a dash with 20 panels)? Which means all data source targets would have to have Bob Smith with whatever correct grants are. That seems like it might be a maintenance nightmare as well.

Regards,

[scherepanov]

It is a single database, for single tenant, with multiple teams.

Yes, it is possible to assign datasource permissions to users/teams in Graphana, exactly as you described. My point here - nobody is going to do it, taking into account complexity of security, number of users/teams involved, number of schemas/tables involved. That will be a lot of work to setup, audit and maintain.

Federated query engine would be very awkward, as it will have huge performance overhead, especially on very large data set.

"A very straightforward solution would be to run user queries under his own credentials. ". Yes, single datasource for everything and all dashboards. Of course providing password to many datasources would not be a working solution.

Single datasource for all dashboards, run under user credentials, would nicely address all problems with security. User will be able to see all data he is authorised on database side, and nothing more. No need for any security on Graphana side. No need to audit Graphana security.

That may sounds like extreme, but this approach definitely have it's usage.

Thanks to this discussion, I now clearly understand what is problem here. Additional security layer in Graphana is definitely a problem, it needs to be managed, audited, it has limitations etc. Single datasource for all dashboards, run under user credentials, would delegate all security to database. That is exactly desired outcome for my use case.

[verticacrossman]

Ok, thanks for final clarification. Definitely not an easy one given the Grafana plugin frontend and backend architecture. There are also some connectivity limitations (currently supports password or TLS) posed by the vertica sql go driver used in the backend. Will kick this around a bit and take a look to see what if any options might be possible.

[verticacrossman]

One last question. Do you have complete control over both the Grafana user names/pwds and the Vetrtica user names/pwds. IE no allow of users to change passwords, no password expirations, etc. that could get name/pwd pairs out of sync between the two systems? Seems like that might be an issue with any "id/pwd passthrough" feature like we're discussing.

[scherepanov]

Credentials are verified against LDAP for both Vertica and Graphana. Both Vertica and Graphana do not have local accounts. (Hint: this is a good practice!)

[verticacrossman]

Great. That's the direction I was heading, in a round about way. May make it easier to come up with a solution if accounts for both are centralized.

[verticacrossman]

Hi,

We've been doing a lot of research related to this request. Below are the findings and possible solution which would require a sql go driver enhancement before we could implement anything in the plugin.

It seems that Grafana supports SSO using SAML, miniOrange, and several other methods. It also has various authentication plugins (Google, Gihub, GitLab) available. But all are for the top level Grafana login. There are also some ways to allow anoymous user access to dashboards. But in all these configurations the plugins still require a user name and password to be supplied in the data source definition.

Using the Volkov Labs Environment Variables plugin it appears Grafana doesn't expose the user id of the logged in user, or of course the password, so there doesn't seem to be a way to pick those up to pass to the plugin. Also note it's a blocker for any solution to Github issue 31 re auditing.

In the Grafana docs I found this page https://grafana.com/docs/grafana/latest/developers/plugins/add-authentication-for-data-source-plugins/

The noted data source proxy seems to be http/https specific, but the section of Authenticate using a backend plugin looks promising. Extract below:

Forward OAuth identity for the logged-in user If your data source uses the same OAuth provider as Grafana itself, for example using Generic OAuth Authentication, your data source plugin can reuse the access token for the logged-in Grafana user.

To allow Grafana to pass the access token to the plugin, update the data source configuration and set the jsonData.oauthPassThru property to true. The DataSourceHttpSettings provides a toggle, the Forward OAuth Identity option, for this. You can also build an appropriate toggle to set jsonData.oauthPassThru in your data source configuration page UI.

When configured, Grafana will pass the user’s token to the plugin in an Authorization header, available on the QueryDataRequest object on the QueryData request in your backend data source.

In the Vertica docs http://doc-build1.verticacorp.com/12.0.2-hotfix/HTML/Content/Authoring/Security/ClientAuth/OAuth/ConfiguringOAuth.htm

We've added Oauth 2 support which is a requirement for the Grafana oauthpassthru. But currently it's only been added to the JDBC and ODBC drivers. Currently the SQL Go client driver used in the Grafana plugin backend doesn't support oauth.

So it seems if the SQL GO driver gets oauth support then we might be able to enhance the Grafana plugin to use the oauthpassthu. If you review the Grafana page noted and the oauthpassthru behaviour is what you are looking for, then I'd recommend you submit an issue on the Github page for the SQL GO driver explaining you'd like OAuth support added to the driver and cite your issues 30 and 31 in the Grafana plugin as the reason for the need. Customer submitted issues carry more weight than internal requests, thus my suggestion for you to submit vs me.

Regards,