`google-drive-to-sqlite auth` command

[simonw]

Authentication will be tricky. For the moment I'll go with the simplest thing possible, but I may need to build a google-drive-to-sqlite auth command just to get things up and running.

Originally posted by @simonw in https://github.com/simonw/google-drive-to-sqlite/issues/1#issuecomment-1041023730

[simonw]

This is what worked in my notebook:

google_client_id = '99025868001-1rdn5dj68t4m60ommr5kb151p038fn6v.apps.googleusercontent.com'
# Is it safe to bake the secret into a distributed CLI app?
google_client_secret = '...'

scope = "https://www.googleapis.com/auth/drive.readonly"
auth_url = "https://accounts.google.com/o/oauth2/v2/auth?access_type=offline&client_id={}&redirect_uri=urn:ietf:wg:oauth:2.0:oob&response_type={}&scope={}".format(
    google_client_id, "code", scope
)
print(auth_url)

The user then clicks on that URL, grants permissions and copies a code from the resulting page.

copied_code = '4/1A...'

import httpx
response = httpx.post("https://www.googleapis.com/oauth2/v4/token", data={
    "code": copied_code,
    "client_id": google_client_id,
    "client_secret": google_client_secret,
    "redirect_uri": "urn:ietf:wg:oauth:2.0:oob",
    "grant_type": "authorization_code",
})
tokens = response.json()

# tokens now has a `"access_token"` key that can be used directly, and a `"refresh_token"`
# that can be exchanged for another access token later on

[simonw]

Documentation for "installed applications": https://developers.google.com/identity/protocols/oauth2#installed

The process results in a client ID and, in some cases, a client secret, which you embed in the source code of your application. (In this context, the client secret is obviously not treated as a secret.)

So it's OK to ship the secret in the app - that's good.

[simonw]

Created a dedicated google-drive-to-sqlite project in the Google API dashboard: https://console.developers.google.com/home/dashboard?project=fluted-anthem-341502

[simonw]

Created a new OAuth client ID:

[simonw]

Now that I have the OAuth consent screen created, I'm setting up the Client ID:

These don't need to be kept private:

• client_id: 148933860554-98i3hter1bsn24sa6fcq1tcrhcrujrnl.apps.googleusercontent.com
• client_secret: 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

[simonw]

(I hope Google don't block my secret because it shows up in a public GitHub repository)

[simonw]

This should show the OAuth consent screen:

https://accounts.google.com/o/oauth2/v2/auth?access_type=offline&client_id=148933860554-98i3hter1bsn24sa6fcq1tcrhcrujrnl.apps.googleusercontent.com&redirect_uri=urn:ietf:wg:oauth:2.0:oob&response_type=code&scope=https://www.googleapis.com/auth/drive.readonly

Indeed it does:

[simonw]

I'm going to have the google-drive-to-sqlite auth command do the following:

1. Show you the URL and ask you to click it
2. Switch to "paste in your token here"
3. Write the results to auth.json in a google-drive-to-sqlite key.

And a -a/--auth option for picking a different file.

[simonw]

Wrote this up as a TIL: https://til.simonwillison.net/googlecloud/google-oauth-cli-application