Syndesis: Google-sheets: unable to successfully refresh expired token

[mmelko]

This is a...

[ ] Feature request
[ ] Regression (a behavior that used to work and stopped working in a new release)
[x] Bug report  
[ ] Documentation issue or request

Description

After expiring acess token in integration I'm unable to reconnect from syndesis.

After clicking on "Reconnect" in google sheets connection created using oauth, I'm still getting in integration:

org.apache.camel.RuntimeCamelException: com.google.api.client.googleapis.json.GoogleJsonResponseException: 401 Unauthorized
    at org.apache.camel.component.google.sheets.GoogleSheetsProducer.doInvokeMethod(GoogleSheetsProducer.java:49)
    at org.apache.camel.util.component.AbstractApiProducer$1.run(AbstractApiProducer.java:86)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: com.google.api.client.googleapis.json.GoogleJsonResponseException: 401 Unauthorized
    at com.google.api.client.googleapis.json.GoogleJsonResponseException.from(GoogleJsonResponseException.java:146)
    at com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:113)
    at com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:40)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest$1.interceptResponse(AbstractGoogleClientRequest.java:321)
    at com.google.api.client.http.HttpRequest.execute(HttpRequest.java:1065)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:419)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:352)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.execute(AbstractGoogleClientRequest.java:469)
    at org.apache.camel.component.google.sheets.GoogleSheetsProducer.doInvokeMethod(GoogleSheetsProducer.java:47)
    ... 8 more

Steps to reproduce

1. Create google-sheet connector using OAuth (from Settings page)
2. create simple ingegration where google-sheet is involved
3. After token expiration: stop integration
4. In Connection->Google Sheets click on reconnect, you get "Successfully authorized Syndesis's access"
   5, Start integration again. Same exception in activities

[christophd]

It is mandatory to recreate the client ID on the Google Developers account before the Google Sheets connection in Syndesis is created. This is because the Google Developer API provides the refresh token only once. When the refresh token has already been delivered in a previous OAuth request all further requests for the refresh token will be ignored by Google. This is basically why the refresh is not working.

The reconnect is successful because the client ID itself is still valid.

I tested that with a new client ID on my Google Sheets Developer account and created a fresh connection within Syndesis with that client ID. My integration is now running for hours and successfully refreshing the access tokens. When I remove the connection settings and reuse the client ID in a new connection the refresh is not working anymore.

This is a known limitation according to what I have been told.

Thanks @zregvart for pointing to this

[christophd]

@TovaCohen is that behavior already covered in the documentation about OAuth settings in Syndesis?

[mmelko]

@christophd So if I understand this correctly, user would need to create new credentials dedicated to syndesis integration prior first api call to receive refresh token. If user wants to use existing credentials he shouldn't create connection using oauth flow and specify refresh token. I guess that make sense.

[TovaCohen]

Yes, this was documented for 7.2. For 7.3, this is the wording:

IMPORTANT: The Google client ID and Google client secret contain token refresh information that ensures that your connections to Google applications, and integrations that have connections to Google applications continuously work correctly. Consequently, you should not obtain new credentials. If you do, then you would need to recreate each Google connection, replace the old connections with new connections, and re-publish each integration that uses a Google connection.

For 7.3, there is one topic that provides instructions for registering Fuse Online as a Google client. This procedure is for Gmail, Google Calendar and Google Sheets. This important note is at the end of the registration procedure.

Let me know if you think the doc needs to say something different.

[zregvart]

Perhaps we can add also a caution on re-using (for instance configured before for another application) OAuth client ID and secret previously used without offline access access. The special case there is that Google will ignore any subsequent OAuth authorisations requesting offline access if the first request also didn't request offline access. In that case refreshing OAuth access token will fail and after, a timeout, the integration will no longer be able to connect to Google APIs. That's why, if unsure that the re-used OAuth client ID/secret was requested with offline access it is best to create new OAuth client configuration, i.e. new client ID/secret for Syndesis.

[TovaCohen]

Sorry @zregvart - I'm not understanding what you are suggesting we add to the doc. Maybe you could try writing the caution that you want to add, and then I might get it? When a Syndesis user registers Syndesis as a Google client to obtain a Google client ID and a Google client secret, and enables particular Google APIs, when/where would they specify or not specify anything about offline access?

[zregvart]

Perhaps a caution like:

Be careful when using the same Google client ID and secret already used by another, non-Syndesis, OAuth client. The offline access required for continued use by Syndesis must be requested on the first OAuth exchange. If any other non-Syndesis OAuth clients entered the OAuth exchange without requesting offline access, Syndesis will not be able to gain offline access on subsequent OAuth exchange. If unsure create new OAuth client ID and secret used just for Syndesis.

[TovaCohen]

Thanks Zoran!
It's not obvious to me where in the user doc this caution belongs. In the procedure for registering Syndesis as a Google client application, the user fills in an application name and a callback URL, both specific to Syndesis. So I'm wondering how or why a user would consider using OAuth credentials that they obtained when they registered some other client application. We are getting further and further away from that target citizen integrator audience.

I can think of two approaches:

• Create a new topic that provides background information about connections to Google. This would provide the information we already have in the Important Note about token refresh information. And then I would add a description of this possible situation, as Zoran described.
• Do not include this new caution. In the introduction to the procedure for registering Syndesis as a Google client application, we already say: "To connect to Gmail, Google Calendar, or Google Sheets, you must register your Syndesis environment as a Google client application." Let's just leave it at that.

Adding @gaughan so we get his opinion about whether to add this caution.
Here's a link to the 7.3 doc:
https://pnt-devops-rad-jenkins.rhev-ci-vms.eng.rdu2.redhat.com/job/ccs-mr-preview-prod/400/artifact/docs/connecting-fuse-online-to-applications-and-services/preview/index.html#register-with-google_google

Please let me know what to do. Thanks!

[zregvart]

I think it's a valid solution also not to include the new caution as most likely this issue will be experienced only by a developer trying out Google APIs. Like we had it here developing the connector. I think there is a very small chance that the issue will be encountered by users of Syndesis.

[gaughan]

My vote is to include it. It's possible the user has a test account on the shelf when they go to test syndesis first.

[TovaCohen]

Okay. I tweaked Zoran's wording, and I am planning to add the following at the end of the introduction to the procedure for registering Syndesis as a Google client.

CAUTION
Be careful if you choose to use the Google client ID and Google client secret that you are using for some other, non-Syndesis, OAuth client. Syndesis requires offline access that is requested on the first OAuth exchange. If another OAuth client already entered the OAuth exchange and did not request offline access, then Syndesis cannot obtain offline access on subsequent OAuth exchanges. If you are unsure whether offline access was requested on the first exchange, create a new Google client ID and secret for Syndesis.

[mmelko]

From my POV it's also worth mentioning that if user has refresh-token, he can create connection "standard way" (not using oauth flow). Then user can specify refresh-token and won't hit this.

please @zregvart @christophd confirm.

[zregvart]

@mmelko not sure how the user would get that refresh token though. In general I think we should deprecate/remove configuring oauth-capable connections via those elaborate forms.

[mmelko]

Well I've got it from google API playground. We are using refresh_token in our tests, otherwise we coudln't test it properly.

[zregvart]

Yeah, I wonder if testing the OAuth refresh through the UI is the correct approach to a functionality of the connector runtime.

[mmelko]

Well In my test I'm using refresh token before test run and then creating connector using oauth, but that's another discussion. In general I agree on what's been said here. Also it'd be probably good to have only one way how to create connection.