Add user impersonation for Google Spreadsheet access

[sashalikesplanes]

Some organizations do not allow for drive files (including Sheets) to be shared outside their organization. This is typically bypassed using a Service Account that has domain wide delegation, allowing it to impersonate a user within an organization with a limited scope. A file in question could be shared with that user, and the service account could access it by impersonating the user.

However Redash does not currently support adding an email which the service account should impersonate. I believe this feature could easily be added to query_running/google_spreadsheets.py by adding the following line delegated_creds = creds.create_delegated(self.configuration["impersonateAccountEmail") in _get_spreadsheet_service method, and updating the UI to include an additional field.

The impersonateAccountEmail configuration value would be optional, and if it is not set, it would fallback to the current behaviour.

What do you think of this proposal? I am currently implementing a simple hardcoded version without UI updates in my fork, but would be happy to clean it up into a proper PR for others to use.

[justinclift]

Hmmm, I'm pretty sure Redash at least used to be able to use a service account for Google Sheets.

Doing some searching online shows up some older info about it:

• Update documentation on how to create Google Service Account getredash/redash#3160

That page clearly mentions using a service account, but without any real detail.

The current page that points to though doesn't seem to mention service accounts:

• https://redash.io/help/open-source/admin-guide/google-developer-account-setup

So, it looks like something got dropped somewhere. Not sure if it's a documentation problem, or what...

[justinclift]

Actually no. The current page in the Redash knowledge base still has the service account info:

https://redash.io/help/data-sources/querying/google-sheets

Does that help? 😄

[sashalikesplanes]

Hey, thanks so much for the info. I have seen and used this feature already. I wish to expand it, by giving the service account the ability to impersonate a regular user account. In some enterprise settings you are unable to share a file with a service account, as a service account is not part of your Google Workspace. Hence you must use the impersonation strategy. This impersonation requires a slight modification to how the credentials are created in the spreadsheets query runner.

…

On Wed, 21 Jun 2023 at 07:37, Justin Clift ***@***.***> wrote: Actually no. The current page in the Redash knowledge base still has the service account info: https://redash.io/help/data-sources/querying/google-sheets Does that help? 😄 — Reply to this email directly, view it on GitHub <#63 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ATCHQBHTEU4X2UZHZTHH64DXMKCAJANCNFSM6AAAAAAZM6J74Q> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[justinclift]

Ahhh, that makes sense. Sounds like a potentially useful future addition.

We're still coming to grips with some of the more basic stuff with this code base though, so unless one of the more experienced Redash developers gets enthused and decides to pick it up... it'll probably be a while before this gets looked at with any seriousness. 😇

[sashalikesplanes]

Ah, I intended to have a go at implementing it myself! I will start with a non customized version where the email is hardcoded, to fulfill only my use case. I found the spot in the codebase for that already. Then I will seek advice from one of the more experienced developers, to understand which other parts I need to update to propagate this change to the UI and database. Im comfortable with the stack that redash is using, and at first glance the code base seems very well organized.

…

On Wed, 21 Jun 2023 at 09:41, Justin Clift ***@***.***> wrote: Ahhh, that makes sense. Sounds like a potentially useful future addition. We're still coming to grips with some of the more basic stuff with this code base though, so unless one of the more experienced Redash developers gets enthused and decides to pick it up... it'll probably be a while before this gets looked at with any seriousness. 😇 — Reply to this email directly, view it on GitHub <#63 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ATCHQBBYUMOXZ77HV3JYB5TXMKQSBANCNFSM6AAAAAAZM6J74Q> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[justinclift]

Awesome. Yeah, that sounds like a good approach. 😄