python - Cloud Storage 存储桶的 Cloud SQL 导入权限问题

Question

我正在编写一个 Cloud Function 来:

• Export Cloud SQL (postgresql) DB 到 Cloud Storage 存储桶中的文件
• Import它回到另一个 Cloud SQL 实例/数据库(仍然是 postgresql)

注意:我希望此代码每晚自行运行以将生产数据库复制到暂存环境，因此我计划使用 Cloud Scheduler 触发它。
如果您有更好/更简单的解决方案在 GCP 中解决这个问题，我会洗耳恭听 :)

这是我的代码(实际功能是文件底部的 clone_db):

from os import getenv
from datetime import datetime
from time import sleep

from googleapiclient import discovery
from googleapiclient.errors import HttpError
from oauth2client.client import GoogleCredentials
from google.cloud import storage

GS_BUCKET = getenv("GS_BUCKET")
GS_FOLDER = "sql-exports"
GS_EXPORT_PATH = f"gs://{GS_BUCKET}/{GS_FOLDER}"


def __sql_file_name(db: str, timestamp: datetime):
    return f"{db}-{timestamp.strftime('%Y-%m-%d')}.sql.gz"


def __sql_file_uri(db: str, timestamp: datetime):
    return f"{GS_EXPORT_PATH}/{__sql_file_name(db, timestamp)}"


def __export_source_db(service, project: str, timestamp: datetime, instance: str, db: str):
    context = {
        "exportContext": {
            "kind": "sql#exportContext",
            "fileType": "SQL",
            "uri": __sql_file_uri(db, timestamp),
            "databases": [db],
        }
    }

    return service.instances().export(project=project, instance=instance, body=context).execute()


def __import_target_db(service, project: str, timestamp: datetime, instance: str, db: str):
    context = {
        "importContext": {
            "kind": "sql#importContext",
            "fileType": "SQL",
            "uri": __sql_file_uri(db, timestamp),
            "database": db,
        }
    }

    return service.instances().import_(project=project, instance=instance, body=context).execute()


def __drop_db(service, project: str, instance: str, db: str):
    try:
        return service.databases().delete(project=project, instance=instance, database=db).execute()
    except HttpError as e:
        if e.resp.status == 404:
            return {"status": "DONE"}
        else:
            raise e


def __create_db(service, project: str, instance: str, db: str):
    database = {
        "name": db,
        "project": project,
        "instance": instance,
    }

    return service.databases().insert(project=project, instance=instance, body=database).execute()


def __update_export_permissions(file_name: str):
    client = storage.Client()
    file = client.get_bucket(GS_BUCKET).get_blob(f"{GS_FOLDER}/{file_name}")
    file.acl.user(getenv("TARGET_DB_SERVICE_ACCOUNT")).grant_read()
    file.acl.save()


def __delete_sql_file(file_name: str):
    client = storage.Client()
    bucket = client.get_bucket(GS_BUCKET)
    bucket.delete_blob(f"{GS_FOLDER}/{file_name}")


def __wait_for(operation_type, operation, service, project):
    if operation["status"] in ("PENDING", "RUNNING", "UNKNOWN"):
        print(f"{operation_type} operation in {operation['status']} status. Waiting for completion...")

        while operation['status'] != "DONE":
            sleep(1)
            operation = service.operations().get(project=project, operation=operation['name']).execute()

    print(f"{operation_type} operation completed!")


def clone_db(_):
    credentials = GoogleCredentials.get_application_default()
    service = discovery.build('sqladmin', 'v1beta4', credentials=credentials)

    # Project ID of the project that contains the instance to be exported.
    project = getenv('PROJECT_ID')

    # Cloud SQL instance ID. This does not include the project ID.
    source = {
        "instance": getenv("SOURCE_INSTANCE_ID"),
        "db": getenv("SOURCE_DB_NAME")
    }

    timestamp = datetime.utcnow()

    print(f"Exporting database {source['instance']}:{source['db']} to Cloud Storage...")
    operation = __export_source_db(service, project, timestamp, **source)

    __wait_for("Export", operation, service, project)

    print("Updating exported file permissions...")
    __update_export_permissions(__sql_file_name(source["db"], timestamp))
    print("Done.")

    target = {
        "instance": getenv("TARGET_INSTANCE_ID"),
        "db": getenv("TARGET_DB_NAME")
    }

    print(f"Dropping target database {target['instance']}:{target['db']}")
    operation = __drop_db(service, project, **target)
    __wait_for("Drop", operation, service, project)

    print(f"Creating database {target['instance']}:{target['db']}...")
    operation = __create_db(service, project, **target)
    __wait_for("Creation", operation, service, project)

    print(f"Importing data into {target['instance']}:{target['db']}...")
    operation = __import_target_db(service, project, timestamp, **target)
    __wait_for("Import", operation, service, project)

    print("Deleting exported SQL file")
    __delete_sql_file(__sql_file_name(source["db"], timestamp))
    print("Done.")

一切正常，直到我尝试将导出的数据导入到我的目标实例中。

当它调用 import_ 时，函数失败并出现以下错误:

Error: function crashed. Details:
<HttpError 403 when requesting https://www.googleapis.com/sql/v1beta4/projects/<project_id>/instances/<instance_id>/import?alt=json returned "The service account does not have the required permissions for the bucket.">

我已经在此处和网络上的许多其他问答中了解到此错误，但我不知道如何解决。
这是我所做的:

• Cloud Function 作为我的“Compute Engine 默认服务帐户”运行，它在 IAM 中设置了 Project Editor 角色
• objective-c loud SQL 实例的服务帐户作为 Storage Object Admin 添加到存储桶的权限中。我尝试了各种其他角色组合(遗留读取器/所有者、存储对象查看器……)都无济于事
• 正如您在函数代码中所见，我特别授予目标实例的服务帐户对导出文件的读取权限，它正确反射(reflect)在云存储中的对象权限上:

• 我已经尝试禁用此存储桶的对象级权限，并确保我上面第一点的权限设置正确，但它也不起作用

有趣的是，当我尝试从 GCP Cloud SQL 控制台在同一实例上手动导入同一文件时，一切正常。
完成后，我可以看到我导出的文件的权限已更新，将实例的服务帐户作为 Reader 包括在内，就像我最后在代码中尝试重现该行为一样。

那么我在这里错过了什么？
我应该为哪个服务帐户设置哪些权限才能使其正常工作？

Answer 1

问题出在您的代码上，与 Cloud SQL 无关。

当调用 _import_target_db 函数时，您正在查找 Cloud Storage 存储桶中不存在的文件。

进入细节:

您将数据库导出到您的存储桶，名称为:

gs://yourBucket/sql-exports/exportedDatabaseName-yyyy-mm-dd.sql.gz

但是，当您尝试导入它时，导入函数正在查找名为的文件:

gs://yourBucket/sql-exports/importDatabaseName-yyyy-mm-dd.sql.gz

此文件在您的存储桶中不存在，出于安全原因，将返回 403 Forbidden 错误。