Use OAuth to authenticate against BigQuery

[YannBrrd]

Hi,

Is it possible to use OAuth to authentitcate against BigQuery, using end user identity ?

Thanks.
Yann

[tswast]

With "default credentials", you can use https://cloud.google.com/sdk/gcloud/reference/auth/application-default/login, but you'll need to provide a project ID in the connection string.

Note: You'll encounter a warning from the google-auth library when using this method. It can safely be ignored, but if you want to avoid it, it's possible to create a key file associated with your user credentials. I started a PR to make such a utility to do this in pydata/pydata-google-auth#28 (Once you create a key file, set the GOOGLE_APPLICATION_DEFAULT environment variable)

[tswast]

Obviously, this use case could be better. We should consider using the pydata-google-auth package here which will fall back to browser login when no other credentials are found.

[YannBrrd]

OK. I'll dig this thanks.

In facrt, I'm targeting an update in SuperSet, using OAuth, and providing list of BgTable tables available for the connected user in a complete organisation.

Seems a bit tricky for me, but I sense there is a way.

[tswast]

For a shared SuperSet instance, it's a little trickier than I described. #28 is an attempt at implementing it, but it used super-user permissions with service account, which isn't ideal.

Does SuperSet have a means of providing a connection string per user?

[YannBrrd]

Need to check where the create_engine is called. I guess it would be better if they did...

[choprashweta]

Can there be support to directly pass a credentials object here - https://github.com/mxmzdlv/pybigquery/blob/master/pybigquery/sqlalchemy_bigquery.py#L327, directly using the credentials object returned by the OAuth authentication process to generate the client. Alternatively if the provided credentials path would accept not just service account credentials but also OAuth2 Client credentials that would work too.

[vinceatbluelabs]

Hi @tswast--if you can provide direction on what would be a reasonable path to allow this, @choprashweta or I can prepare a PR.

[tswast]

The fact that the connection args have to be parsed from a URL string makes this a little more difficult. The closest comparison I can think of is how the Simba ODBC driver is configured. It has an optional refresh_token argument for OAuth authentication.

If specified in the URL, the refresh_token can be passed to the google.oauth2.credentials.Credentials constructor. No access token is necessary.

credentials = google.oauth2.credentials.Credentials(None, refresh_token=refresh_token)

[tswast]

To support non-"offline" credentials, it may also be desirable to add an access_token parameter. Though note that any access_token will expire within an hour.

[codegeekjk]

@YannBrrd @tswast are any workaround available for superset ? superset is using this library for connecting to Bigquery.
Currently we want impersonate user via Superset so only users which have access to particular dataset can query it.

We are using service account json for connection and in current scenario user can query all the available datasets.

[codegeekjk]

anyone can provide me some pointers for the implementation so i can work on this.