GCP permissions: access scopes and custom IAM service account roles

Question

I have a Kotlin app that uses custom service account and needs to query a BigQuery table backed by a Google Spreadsheet. Querying the table requires a "https://www.googleapis.com/auth/drive" access scope, but as I understand custom service accounts can't have access scopes attached to them. Whats the best way forward? Can I add some set of permissions to custom SA that would simulate the access scope?

The error I'm getting is:

INFO - com.google.cloud.bigquery.BigQueryException: Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.
    at com.google.cloud.bigquery.spi.v2.HttpBigQueryRpc.translate(HttpBigQueryRpc.java:115)
    at com.google.cloud.bigquery.spi.v2.HttpBigQueryRpc.queryRpc(HttpBigQueryRpc.java:652)
    at com.google.cloud.bigquery.BigQueryImpl$35.call(BigQueryImpl.java:1282)
    at com.google.cloud.bigquery.BigQueryImpl$35.call(BigQueryImpl.java:1279)
    at com.google.api.gax.retrying.DirectRetryingExecutor.submit(DirectRetryingExecutor.java:105)
    at com.google.cloud.bigquery.BigQueryRetryHelper.run(BigQueryRetryHelper.java:64)
    at com.google.cloud.bigquery.BigQueryRetryHelper.runWithRetries(BigQueryRetryHelper.java:38)
    at com.google.cloud.bigquery.BigQueryImpl.queryRpc(BigQueryImpl.java:1278)
    at com.google.cloud.bigquery.BigQueryImpl.query(BigQueryImpl.java:1266)
    at com..extractor.BigQueryDataExtractor.extractData(BigQueryDataExtractor.kt:90)
    at com.extractor.commands..call(.kt:62)
    at com..extractor.commands..call(.kt:9)
    at picocli.CommandLine.executeUserObject(CommandLine.java:1953)
    at picocli.CommandLine.access$1300(CommandLine.java:145)
    at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2358)
    at picocli.CommandLine$RunLast.handle(CommandLine.java:2352)
    at picocli.CommandLine$RunLast.handle(CommandLine.java:2314)
    at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2179)
    at picocli.CommandLine$RunLast.execute(CommandLine.java:2316)
    at picocli.CommandLine.execute(CommandLine.java:2078)
    at com...extractor..run(.kt:25)
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:791)
    at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:775)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:345)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1343)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1332)
    at com...extractor..main(.kt:38)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:49)
    at org.springframework.boot.loader.Launcher.launch(Launcher.java:108)
    at org.springframework.boot.loader.Launcher.launch(Launcher.java:58)
    at org.springframework.boot.loader.JarLauncher.main(JarLauncher.java:88)
Caused by: com.google.api.client.googleapis.json.GoogleJsonResponseException: 403 Forbidden
POST https://www.googleapis.com/bigquery/v2/projects//queries
{
  "code" : 403,
  "errors" : [ {
    "domain" : "global",
    "message" : "Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.",
    "reason" : "accessDenied"
  } ],
  "message" : "Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.",
  "status" : "PERMISSION_DENIED"
}```


EDIT: I've managed to pass extra scopes to the credentials created by Spring Boot, so solved my problem, but would still be interested in hearing if there was a way to solve the problem without using scopes

you can simply add bigquery permissions to the custom service account https://cloud.google.com/bigquery/docs/access-control#bq-permissions — Mehdi Benmoha, Feb 15 '22 at 09:51
No Drive permissions listed there? How should I know what permissions to add? — sumek, Feb 15 '22 at 10:10
oh sorry that's on the Bigquery side, I misunderstood, have a look at this: https://stackoverflow.com/questions/48672690/querying-federated-tables-google-drive-via-bigquery-python-api?rq=1 — Mehdi Benmoha, Feb 15 '22 at 11:11
Did you add the Bigquery service account or the custom service account email to the authorized users in Drive ? — Mehdi Benmoha, Feb 15 '22 at 11:12
I've added the custom service account email. "Shared the spreadsheet" with the email — sumek, Feb 15 '22 at 11:38
Is there a way to use default credentials but with customised scopes? — sumek, Feb 15 '22 at 11:56
@guillaumeblaquiere I just came across your medium posts: https://medium.com/google-cloud/google-oauth-credential-going-deeper-the-hard-way-f403cf3edf9d I'll definitely be reading it soon — sumek, Feb 15 '22 at 12:35
If you don't use federated table, I don't understand the relation with Drive API and BigQuery. Can you share a minimal piece of code to make that point clearer? — guillaume blaquiere, Feb 15 '22 at 12:38
My understanding is that this is due to the BQ table being connected to Google Spreadsheet — sumek, Feb 15 '22 at 13:28
In other case I was running the same process using VM SA, not custom SA. In that case i was able to add the scope to the VM SA and I also shared the Google spreadsheet with the SA email account — sumek, Feb 15 '22 at 13:35
@guillaumeblaquiere I've found a decent solution to the problem in a different way as described here: https://stackoverflow.com/questions/71126447/spring-cloud-gcp-using-default-credentials-provider-but-with-customised-scopes/71128189#71128189 — sumek, Feb 15 '22 at 14:59
@sumek, As you have found a solution, please post your answer to help to contribute with the community. — ewertonvsilva, Feb 16 '22 at 15:41
I've solved the underlying problem using other approach. Posted the answer in the other question that I have also asked — sumek, Feb 16 '22 at 17:02

score 0 · Answer 1 · answered Feb 16 '22 at 17:04

0

You can assign access scopes to the client when creating it. In my case it was a case of setting a property in the Spring Boot configuration file as described here: https://docs.spring.io/spring-cloud-gcp/docs/current/reference/html/#scopes

answered Feb 16 '22 at 17:04

sumek

26,495
13
56
75

GCP permissions: access scopes and custom IAM service account roles

1 Answers1

Linked