Customer access to BigQuery

[rikotsev]

Hello,

I have an application that is deployed in GCP. The application will use Google Identity Platform (CIAM) to allow end users to login, register, etc. 
The end users however also would need to be able to view data stored in BigQuery. The data stored in BigQuery will be filtered based on allow / deny policies for certain groups (as principals). 

However, the users of the application are not users in GCP and they cannot be assigned to the groups. Currently, the only 2 options I see to circumvent this are:
1) Using service accounts to access BigQuery - this does not allow sufficient granularity (on a per user basis)
2) Using an external IdP instead of Identity Platform and setting up a workforce federation (this doesn't seem right also and is quite expensive)

Any recommendations related to this?

[JadielMiles]

You can configure your application to use the OAuth 2.0 protocol to authenticate users through the Google Identity Platform. After successful authentication, you can request an access token, which can be used to make queries to BigQuery on behalf of the user. This will allow you to authorize each user individually.

[rikotsev]

I don't think I understand how this will help me in the situation. 

The Identity Platform is completely separated from the Google Cloud users. At least to my knowledge. A user in the identity platform will not appear as a principal. 

Furthermore, the users will log in to my application via username/password or their own IdP. It's very unlikely that they will have google accounts.

[lsolatorio]

Hi @rikotsev,,

Great to hear from you again.

As I understand what @JadielMiles is trying to say. He/she may be referring to the JSON Web Token (JWT) and Identity and Access Management (IAM). You can explore using JWT to authenticate users and set them with the appropriate BigQuery IAM roles and permissions for them to access your data.

Another secured option that you can consider is service account impersonation.

You start with an authenticated principal (your user account or a service account) and request short-lived credentials for a service account that has the authorization that your use case requires. The authenticated principal must have the necessary permissions to impersonate the service account.

---

I hope I was able to provide you with useful insights.

[rikotsev]

Hi @lsolatorio ,

Thank you for your reply. I actually have something that I think might work fine in the situation. I have two projects in GCP - project A and project B

Set-up:
 - Create a username/password provider in Google Identity Platform project A
 - Set-up Workload Identity Federation in project B - OIDC - the issuer will be project A

Application flow:
1) User logs into my application (hosted in Project A).
2) Upon login Google Identity Platform creates an Id Token with some custom claims. 
3) User wants to retrieve information from BigQuery in project B
4) My application (in project A) reaches to the Secure Token Service, provides the id token and based on the custom claims - receives an access token with a principal/subject and groups coming from my custom claims and impersonates a service account with access to BigQuery
5) My application calls the BigQuery API using the access token - since the principal and groups is coming from claims specific for the user - the BigQuery filters are able to determine if the principal should have access or not.

Let me know if this makes sense?

However, I am also considering Looker - and for Looker it seems impossible to use the above flow if I want to embed a dashboard.