Authorize Cloud SQL Admin API call from Cloud Function

Question

I'm trying to build small function (which later deployed to cloud function) to restore BAK file to cloud sql. This function will be trigerred by cron. I'm kind of lost when reading the docs about authorize this API: https://cloud.google.com/sql/docs/sqlserver/import-export/importing#importing_data_from_a_bak_file_in

Already create service account which include this role: Cloud SQL Admin, Storage Admin, Storage Object Admin, Storage Object Viewer and choose that Service Account from dropdown when creating Cloud Function but not work.

Also tried generating API keys after reading this: https://cloud.google.com/sql/docs/sqlserver/admin-api/how-tos/authorizing

So my POST url became this:

https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/import?key=generatedAPIKey

but still got an error:

  "error": {
    "code": 401,
    "message": "Request is missing required authentication credential. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project.",
    "errors": [
      {
        "message": "Login Required.",
        "domain": "global",
        "reason": "required",
        "location": "Authorization",
        "locationType": "header"
      }
    ],
    "status": "UNAUTHENTICATED"
  }
}

Do I need to use Oauth 2 for this? This is my code in Cloud Function:

import http.client
import mimetypes

def restore_bak(request):
    conn = http.client.HTTPSConnection("www.googleapis.com")
    payload = "{\r\n \"importContext\":\r\n   {\r\n      \"fileType\": \"BAK\",\r\n      \"uri\": \"gs://{bucket_name}/{backup_name}.bak\",\r\n      \"database\": \"{database_name}\"\r\n    }\r\n}\r\n"
    headers = {
      'Content-Type': 'application/json'
    }
    conn.request("POST", "/sql/v1beta4/projects/{project_id}/instances/{instance_name}/import", payload, headers)
    res = conn.getresponse()
    data = res.read()
    print(data.decode("utf-8"))
    return(data.decode("utf-8"))

kurtisvg kurtisvg · Accepted Answer · 2020-08-24T15:51:13

This looks like python, so I would recommend using the Discovery Client Library for Python. This library provides a convient wrapper around the SQL Admin API:

# Construct the service object for the interacting with the Cloud SQL Admin API.
service = discovery.build('sqladmin', 'v1beta4', http=http)

req = service.instances().list(project="PROJECT_ID")
resp = req.execute()
print json.dumps(resp, indent=2)

By default, this library uses the "Application Default Credentials (ADC)" strategy to obtain your credentials from the environment.

You can also manually authenticate your requests (for example, if you want to use asyncio) by creating an oauth2 token and setting it as a header in your request. The easiest way to do this is to use the google-auth package to get the ADC and set it as a header:

import google.auth
import google.auth.transport.requests

credentials, project_id = google.auth.default()
credentials.refresh(google.auth.transport.requests.Request())
headers = {
    "Authorization": "Bearer {}".format(credentials.token),
    "Content-Type": "application/json"
}

Authorize Cloud SQL Admin API call from Cloud Function

1 Answers