TL;DR
We are looking for a way for letting a service account inherit BQ read permissions from more than one other service account. Impersonation does only work with one.
The scenario
Our company follows a data mesh approach in which our product teams are responsible for integrating their data into BigQuery. The product owner is also considered the owner of the data. So it is the product owner to decides whom to give access to the data.
Working in an analytical team, we usually combine data from multiple source systems in our BigQuery queries. Our ETL processes run on a kubernetes cluster, each process is using a separate service account. This gives us fine-grained access control as access to data is limited for each process to those very objects that they really need. This design also helps us with debugging and cost control. On the other hand, this leads to an issue on source side:
The problem
Every time we design a new process, we need to ask the data owner for allowance. They already agreed that our product team / system at wholesome level may access their data, so this authorization process is quite cumbersome and confuses the data owner.
We'd prefer to have just one "proxy" service account for each source object that holds the neccessary BQ read permissions. The processes' service accounts would then be set up to inherit the rights from the proxy service account of those BQ sources they need to access.
Using impersonation does only help if it's just one source system, but our queries often use more than one.
Using Google Groups does not help
We discussed a solution in which we setup a google group for each source system we want to read from. BigQuery Data Reader role will then be assigned to this group. In turn, service accounts that require those rights will be added to the group. However, company policy does not allow for adding service accounts to google groups. Also, google groups cannot be managed (created) by our product teams themselves, so this approach lacks flexibility.
Implementing a coarse-grained approach
One approach is to use a more coarsed-grained access control, i.e. just using one service account for all ETL processes. We could add the process name as a label to the query to cover the debugging and cost control part. However, if possible, we'd prefer an approach in which the processes can only access as little data objects as possible.