BigQuery impersonate service account

[joshk0]

Tell us about the problem you're trying to solve

In the BigQuery connector, I would like to use Application Default Credentials (I think this is already supported) but then use those credentials to impersonate a different service account.

Example: https://github.com/salrashid123/gcp_impersonated_credentials/blob/main/java/src/main/java/com/test/TestApp.java#L28-L30

Describe the solution you’d like

BigQuery connector should continue to accept JSON credentials for authenticating but then fall back to ADC. Then, if the configuration field for setting a "Account to impersonate" is set, we should attempt to impersonate that account before attempting to access the table.

For accessing the target table, if this feature works, then the ADC credentials will not need any access to the target BigQuery, only the impersonated account does.

This is a crucial component for using Airbyte in a multi-tenant customer environment.

Describe the alternative you’ve considered or used

We have no alternative; our security posture as a business is strengthened by using as few hardcoded credentials as possible. Airbyte's BigQuery connector is not usable for us at this time until we can use our existing service accounts in a credentialless fashion.

Additional context

https://cloud.google.com/iam/docs/impersonating-service-accounts

Are you willing to submit a PR?

It'll probably be a very long time before I'd have time to, but theoretically I am willing to.

[marcelopio]

Just to link, I added ADC, but not as a fallback, because that would require that the worker had access to the host environment. So the way to get ADC now is passing the json generated by gcloud auth application-default login

#14784

[marcelopio]

I think to add the impersonation would be just to add a field with the account to be impersonated, and that is basically it, if you are ok with using the application-default login json. I can make a PR with that idea

[marcosmarxm]

Thanks @marcelopio the team will review your proposal this week.

[joshk0]

I would rather not have JSON anywhere in my authentication pipeline.

The power for ADC to be attached to workloads, cloud function invocation or compute instances by the provider implicitly, rather than with a data file that can escape the system is crucial to my company's security posture.

[marcelopio]

ADC will work on compute engines without any json provided, just not on developers machines

[joshk0]

Sure, that's fine, and JSON should be a valid option for dev machines. But I'd like to use better practices in prod.

[marcelopio]

Nice, then draft solution should be fine!

[joshk0]

Got it - I misunderstood your sentence back there then. Thanks!

[marcelopio]

My fault, I didn't explain the whole problem with ADC.

Google ADC implementation has a lot of steps and what everyone generally assumes is the use of GOOGLE_APPLICATION_CREDENTIALS. That won't work with Airbyte unfortunately, but if you are on Google environment that exposes the credentials via API it should work.

This service account might be a default service account provided by Compute Engine, Google Kubernetes Engine, App Engine, Cloud Run, or Cloud Functions.

It will also work on Cloud Shell

[grishick]

@marcelopio I took your PR, added a test for impersonation and pushed to this PR: #20788