How to obtain an oauth access token to use the Google BigQuery Api

[ken-slabs]

I would like to expose a BigQuery stored procedure that returns data back to the caller based on parameters provided through an Apigee proxy. As I understand, an oAuth access token is required in order to call the Google BigQuery API.

I found an article that explains using a predefined service account to deploy the proxy with the necessary BigQuery permissions. Basically, it explained about using a ServiceCallOut policy to retrieve the access token but I am not sure how to properly setup the code in the policy to obtain the access token. Or is it the correct approach (I included the snippet in the ServiceCallOut policy below)

https://www.googlecloudcommunity.com/gc/Apigee/How-to-get-Bearer-token-for-Management-APIs-in-Apigee-X-using/m-p/184161

https://cloud.google.com/apigee/docs/api-platform/security/google-auth/overview

ServiceCallOut code

<?xml version="1.0" encoding="UTF-8" standalone="yes"?> SC-GetOAuthAccessToken false calloutResponse [https://accounts.google.com/o/oauth2/v2/auth](https://accounts.google.com/o/oauth2/v2/auth) [https://www.googleapis.com/auth/bigquery](https://www.googleapis.com/auth/bigquery) [https://www.googleapis.com/auth/cloud-platform](https://www.googleapis.com/auth/cloud-platform) [https://www.googleapis.com/auth/cloud-platform.read-only](https://www.googleapis.com/auth/cloud-platform.read-only)

[dchiesa1]

You’re close, but not quite there. For service-to-service calls, you will use a service account. From an Apigee proxy, you need to POST to https://oauth2.googleapis.com/token , and the payload must include a JWT.

Here’s a repo that shows how: https://github.com/DinoChiesa/Apigee-GCS-Get/tree/main/sharedflows/get-googleapis-token

That’s a re-usable sharedflow. You can use that to get a token that is usable with BQ. The Service Account must have at least BQ Data Viewer role. or something with similar permissions.

[ken-slabs]

Hi Dino,

Thanks for the Github instruction, it was very helpful. I am an Apigee newbie and had some difficulties figuring out how to import the proxy and sharedflow files but finally managed to get the proxy working. I saw the access token generated in debug mode and was able to use it with a BQ call - yay!

A quick question, where does the keyfile get stored? I didn’t see an option to delete the keyfile that was uploaded in the Apigee Admin UI

Thanks, Ken

[dchiesa1]

Thanks for the feedback. I’m glad my suggestions helped you out.

where does the keyfile get stored?

The data DOES get stored in the KVM, though the UI currently does not show the entries there. The UI currently shows just the KVM names themselves.

As for deleting the keyfile, You would need to perform DELETE operations on the items in the KVM, via the API. Like this:

DELETE https://apigee.googleapis.com/v1/organizations/:org/environments/:env/keyvaluemaps/:kvm/entries/:entryname
Authorization: Bearer :token

[ken-slabs]

Hi Dino,

I hope it is ok to continue to post here. I created a new proxy mimicking the “gcs-get” proxy, just removing some policies and endpoints not needed and focused more on calling the BQ Api.

A couple of questions:

• I am getting an error in the shared flow, see screenshot below. I saw that error even in the original “gcs-get” proxy

• I have setup the target endpoint with just two steps - it appears the steps in the gcs-get are related to gs, just wondering if these 2 steps are enough for calling the BQ Api?

Thanks, Ken

<?xml version="1.0" encoding="UTF-8" standalone="yes"?> Run BQ Queries FC-Get-GoogleApis-Token AM-GoogleApis-Auth [https://www.googleapis.com/bigquery/v2/projects/sl-stg-core-prj-a548/queries](https://www.googleapis.com/bigquery/v2/projects/sl-stg-core-prj-a548/queries)

[dchiesa1]

ok that seems frustrating

Unfortunately from the screenshot of the trace I cannot tell what is going wrong with the sharedflow.

Probably I have missed some error handling there. Something has gone wrong and it isn’t properly handled.

The “AssignMessage did not assign the message” is pretty generic and not particularly elucidating. I am guessing that there is an unresolved variable, and that would imply that the response payload back from the ServiceCallout is not of the form that is expected. But this is just a guess.

if you can share a trace session I could look more closely.

The most common problem with this shared flow is the ServiceAccount key is not loaded properly into the KVM, in my experience. So maybe check that.

[ken-slabs]

Hi Dino, I have the trace session but doesn’t appear to be an option to attach a file. What’s the best way to send you?

[dchiesa1]

Hmmm that’s unfortunate. I have the option to attach a file. It’s at the bottom of the text window as I type my message. Just above the “post reply” button. The prompt reads “browse files to attach”. You don’t have that?

[ken-slabs]

No, I don’t see that option. This is what I see. It’s over 4500 lines to paste into the text window. I also tried changing the ZIP extension to a PNG and paste but it’s smart, lol

[dchiesa1]

Ok that stinks. I don’t have another suggestion except to use an open google drive link or similar.

[ken-slabs]

Actually, I found the issue - i commented out the “target.url” assignment. The proxy is now working as expected - yay!

There

<?xml version="1.0" encoding="UTF-8" standalone="yes"?> request Bearer {gcp_bearer_token} {"query":"select * from aaa.table PA limit 10;"}

[dchiesa1]

Great!

what is objectmetadata.medialink? I guess that was a leftover from the example that was the source of this proxy eh?

anyway, I’m glad you got it working.

[ken-slabs]

yes, it was probably one of the variables used in your example for accessing GS.

Thanks again for getting me kickstarted!

[ashmitkolli]

@ken-slabs Hi, where are you passing the flow variables required for the generation of the JWT in the shared flow?

[ashmitkolli]

For other who are here,
All you gotta do is,
go here : https://github.com/DinoChiesa/Apigee-GCS-Get
Download the zip file, extract it, got to - Apigee-GCS-Get-main\Apigee-GCS-Get-main\sharedflows\get-googleapis-token\sharedflowbundle and create a zip file for sharedflowbundle which you can import into your shared flow and deploy.

I too have a usecase where google apigee needs to make an authorized api request to bigquery for fetching a dataset using a service account.

Moving on to the issue i’ve :
once we create a service account with keys, we get a JSON file with client id, keys etc that looks like this :
{
“type”: “service_account”,
“project_id”: “dddd”,
“private_key_id”: “ddddddd”,
“private_key”: “-----BEGIN PRIVATE KEY-----\nddddd\n-----END PRIVATE KEY-----\n”,
“client_email”: “ddd.iam.gserviceaccount.com”,
“client_id”: “122”,
“auth_uri”: “https://accounts.google.com/o/oauth2/auth”,
“token_uri”: “https://oauth2.googleapis.com/token”,
“auth_provider_x509_cert_url”: “https://www.googleapis.com/oauth2/v1/certs”,
“client_x509_cert_url”: “https://www.googleapis.com/robot/v1/metadata/dwwwdwd”
}

What do we do with this file?
i’m a bit confused.

i also have created the shared flow, deployed it and created a flow call out and assign message like you did in the target server side in the proxy meant to reach biq query.

i understood the functionality of most of the policies in the shared flow (AM-Extract-GCP-Token
Cache-Lookup-GCP-Token, Cache-Populate-GCP-Token, Generate-JWT-SelfSigned, JS-ShredCredentialsJson, KVM-Get-ServiceAccountJson, SC-PostToken ) except the JS-ShredCredentialsJson and Generate-JWT-SelfSigned.

What i have done is pasted the JSON file we get after creating a service account with keys onto JS-ShredCredentialsJson like below -

<?xml version="1.0" encoding="UTF-8" standalone="yes"?> { "type": "service_account", "project_id": "dddd", "private_key_id": "ddddddd", "private_key": "-----BEGIN PRIVATE KEY-----\nddddd\n-----END PRIVATE KEY-----\n", "client_email": "ddd.iam.gserviceaccount.com", "client_id": "122", "auth_uri": "[https://accounts.google.com/o/oauth2/auth](https://accounts.google.com/o/oauth2/auth)", "token_uri": "[https://oauth2.googleapis.com/token](https://oauth2.googleapis.com/token)", "auth_provider_x509_cert_url": "[https://www.googleapis.com/oauth2/v1/certs](https://www.googleapis.com/oauth2/v1/certs)", "client_x509_cert_url": "[https://www.googleapis.com/robot/v1/metadata/dwwwdwd](https://www.googleapis.com/robot/v1/metadata/dwwwdwd)" } private. jsc://shredJson.js

And i’m getting the following error :

Please Advice.
@ken-slabs @dchiesa1

[ken-slabs]

Hi @ashmitkolli , I don’t have the Apigee setup anymore since I was only using the Apigee evaluation version for a proof of concept; although I did export the code in case we are ready for the licensed version.

As far as I remember, there is an option for you to link the Api Proxy to a service account when you compile. I followed Dino’s example and pretty much got it working without much tweaking.

Good luck!

[ashmitkolli]

Thanks for getting back to me, Appreciate it!

could you share the file if you still have it on you?

I created shared flows, created a proxy with the shared flow created earlier and an assign message just like you’ve and deployed it using a service account.

when we create a service account, we are given the option create keys in GCP IAM. when we do, we’re given a JSON file that looks like below :

{
“type”: “service_account”,
“project_id”: “dddd”,
“private_key_id”: “ddddddd”,
“private_key”: “-----BEGIN PRIVATE KEY-----\nddddd\n-----END PRIVATE KEY-----\n”,
“client_email”: “ddd.iam.gserviceaccount.com”,
“client_id”: “122”,
“auth_uri”: “https://accounts.google.com/o/oauth2/auth”,
“token_uri”: “https://oauth2.googleapis.com/token”,
“auth_provider_x509_cert_url”: “https://www.googleapis.com/oauth2/v1/certs”,
“client_x509_cert_url”: “https://www.googleapis.com/robot/v1/metadata/dwwwdwd”
}

do you remember if you made any changes to the shared flow?
Specifically to JS-ShredCredentialsJson and Generate-JWT-SelfSigned policies in the shared flow or the javascript in the shared flow to input the above json? [ the reason why i’m asking is cause to generate a authorization/bearer token we need the client_id and client_secret right, where do we provide those details? ] or have you created a service account in GCP IAM with necessary permissions of BigQuery without any keys?

Without any modifications to JS-ShredCredentialsJson as well, it’s failing with the same error as the screenshot in the above message i sent.

[ken-slabs]

Did you follow the instruction here: https://github.com/DinoChiesa/Apigee-GCS-Get? there is a part to upload the json key file, I believe i had done and then needed to refer to the service account during the compilation.

I didn’t change much of the sharedflow except the part i commented out in the earlier thread.