BigQuery에 앱 연결하기

#!/usr/bin/env bash
set -euo pipefail

# ---- FILL THESE IN ----
GCP_PROJECT="YOUR_GCP_PROJECT_ID"
WORKSPACE_ID="YOUR_WORKSPACE_ID"
ALLOWED_DATASETS=("DATASET_1" "DATASET_2")

# ---- Defaults ----
POOL_ID="lovable"
PROVIDER_ID="lovable-gateway"
SA_NAME="lovable-bigquery"
LOCATION="global"
GATEWAY_SA_EMAIL="connector-gateway@lovable-core-prod.iam.gserviceaccount.com"

# ---- Validate placeholders ----
for VAR in GCP_PROJECT WORKSPACE_ID; do
  if [[ -z "${!VAR}" || "${!VAR}" == YOUR_* ]]; then
    echo "ERROR: set ${VAR} at the top of this script" >&2
    exit 1
  fi
done

if [[ ${#ALLOWED_DATASETS[@]} -eq 0 ]]; then
  echo "ERROR: set ALLOWED_DATASETS at the top of this script" >&2
  exit 1
fi

for DS in "${ALLOWED_DATASETS[@]}"; do
  if [[ -z "${DS}" || "${DS}" == DATASET_* ]]; then
    echo "ERROR: replace the placeholder entries in ALLOWED_DATASETS" >&2
    exit 1
  fi
done

if ! command -v python3 &>/dev/null; then
  echo "ERROR: python3 is required to update dataset access" >&2
  exit 1
fi

# ---- Derived ----
SA_EMAIL="${SA_NAME}@${GCP_PROJECT}.iam.gserviceaccount.com"
SUBJECT_TOKEN_AUDIENCE="https://connector-gateway.lovable.dev/workspaces/${WORKSPACE_ID}"

WORK_DIR=$(mktemp -d)
trap 'rm -rf "${WORK_DIR}"' EXIT

# Enable APIs first. Cloud Resource Manager is not enabled by default on
# a new project, and the gcloud projects commands below depend on it.
gcloud services enable \
  iam.googleapis.com sts.googleapis.com \
  iamcredentials.googleapis.com bigquery.googleapis.com \
  cloudresourcemanager.googleapis.com \
  --project="${GCP_PROJECT}"

PROJECT_NUMBER=$(gcloud projects describe "${GCP_PROJECT}" --format="value(projectNumber)")
POOL_RESOURCE="projects/${PROJECT_NUMBER}/locations/${LOCATION}/workloadIdentityPools/${POOL_ID}"

# Create the workload identity pool
if ! gcloud iam workload-identity-pools describe "${POOL_ID}" \
    --location="${LOCATION}" --project="${GCP_PROJECT}" &>/dev/null; then
  gcloud iam workload-identity-pools create "${POOL_ID}" \
    --location="${LOCATION}" \
    --project="${GCP_PROJECT}" \
    --display-name="Lovable WIF Pool"
fi

# Create the OIDC provider, restricted to Lovable’s connector gateway.
# An existing provider that already matches is left alone. One with
# different settings is only overwritten after you confirm, because the
# binding below trusts every identity this pool admits.
EXPECTED_CONDITION="assertion.email == '${GATEWAY_SA_EMAIL}'"

if gcloud iam workload-identity-pools providers describe "${PROVIDER_ID}" \
    --location="${LOCATION}" \
    --workload-identity-pool="${POOL_ID}" \
    --project="${GCP_PROJECT}" &>/dev/null; then
  CURRENT_CONDITION=$(gcloud iam workload-identity-pools providers describe "${PROVIDER_ID}" \
    --location="${LOCATION}" \
    --workload-identity-pool="${POOL_ID}" \
    --project="${GCP_PROJECT}" \
    --format="value(attributeCondition)")

  CURRENT_AUDIENCE=$(gcloud iam workload-identity-pools providers describe "${PROVIDER_ID}" \
    --location="${LOCATION}" \
    --workload-identity-pool="${POOL_ID}" \
    --project="${GCP_PROJECT}" \
    --format="value(oidc.allowedAudiences)")

  # The impersonation binding below matches on attribute.email, so the
  # provider has to map it.
  CURRENT_EMAIL_MAPPING=$(gcloud iam workload-identity-pools providers describe "${PROVIDER_ID}" \
    --location="${LOCATION}" \
    --workload-identity-pool="${POOL_ID}" \
    --project="${GCP_PROJECT}" \
    --format="value(attributeMapping['attribute.email'])")

  if [[ "${CURRENT_CONDITION}" == "${EXPECTED_CONDITION}" \
     && "${CURRENT_AUDIENCE}" == "${SUBJECT_TOKEN_AUDIENCE}" \
     && "${CURRENT_EMAIL_MAPPING}" == "assertion.email" ]]; then
    echo "Provider '${PROVIDER_ID}' already matches this workspace."
  else
    echo ""
    echo "Provider '${PROVIDER_ID}' already exists with different settings:"
    echo "  audience       now:  ${CURRENT_AUDIENCE:-(none)}"
    echo "                 new:  ${SUBJECT_TOKEN_AUDIENCE}"
    echo "  condition      now:  ${CURRENT_CONDITION:-(none)}"
    echo "                 new:  ${EXPECTED_CONDITION}"
    echo "  attribute.email now: ${CURRENT_EMAIL_MAPPING:-(unmapped)}"
    echo "                 new:  assertion.email"
    echo ""
    echo "Overwriting replaces both values. Any other audience on this provider is dropped."
    CONFIRM=""
    read -r -p "Overwrite it? (y/N) " CONFIRM < /dev/tty || CONFIRM="n"
    case "${CONFIRM}" in
      [Yy]|[Yy][Ee][Ss]) ;;
      *) echo "Aborted."; exit 1 ;;
    esac

    gcloud iam workload-identity-pools providers update-oidc "${PROVIDER_ID}" \
      --location="${LOCATION}" \
      --workload-identity-pool="${POOL_ID}" \
      --project="${GCP_PROJECT}" \
      --issuer-uri="https://accounts.google.com" \
      --allowed-audiences="${SUBJECT_TOKEN_AUDIENCE}" \
      --attribute-mapping="google.subject=assertion.sub,attribute.email=assertion.email" \
      --attribute-condition="${EXPECTED_CONDITION}"
  fi
else
  gcloud iam workload-identity-pools providers create-oidc "${PROVIDER_ID}" \
    --location="${LOCATION}" \
    --workload-identity-pool="${POOL_ID}" \
    --project="${GCP_PROJECT}" \
    --issuer-uri="https://accounts.google.com" \
    --allowed-audiences="${SUBJECT_TOKEN_AUDIENCE}" \
    --attribute-mapping="google.subject=assertion.sub,attribute.email=assertion.email" \
    --attribute-condition="assertion.email == '${GATEWAY_SA_EMAIL}'"
fi

# Create the service account Lovable impersonates
if ! gcloud iam service-accounts describe "${SA_EMAIL}" \
    --project="${GCP_PROJECT}" &>/dev/null; then
  gcloud iam service-accounts create "${SA_NAME}" \
    --project="${GCP_PROJECT}" \
    --display-name="Lovable BigQuery Reader"
fi

# Wait for the service account to become visible to IAM. Reads are
# eventually consistent, so a new service account can take 60s or more
# to appear. Both messages derive from PROPAGATION_TIMEOUT so they
# cannot drift from the time the loop actually spends waiting.
PROPAGATION_TIMEOUT=120
echo "Waiting up to ${PROPAGATION_TIMEOUT}s for service account to propagate..."
PROPAGATION_DEADLINE=$((SECONDS + PROPAGATION_TIMEOUT))
until gcloud iam service-accounts describe "${SA_EMAIL}" \
    --project="${GCP_PROJECT}" &>/dev/null; do
  if (( SECONDS >= PROPAGATION_DEADLINE )); then
    echo "ERROR: ${SA_EMAIL} did not become available after ${PROPAGATION_TIMEOUT}s" >&2
    exit 1
  fi
  sleep 2
done

# ---- Confirm before granting data access ----
echo ""
echo "Service account ${SA_EMAIL} will be granted:"
echo "  roles/bigquery.jobUser     on project ${GCP_PROJECT}"
echo "  roles/bigquery.dataViewer  on datasets: ${ALLOWED_DATASETS[*]}"
echo ""
REPLY=""
read -r -p "Continue? (Y/n) " REPLY < /dev/tty || REPLY="n"
case "${REPLY}" in
  ""|[Yy]|[Yy][Ee][Ss]) ;;
  *) echo "Aborted."; exit 1 ;;
esac

# Allow the service account to run query jobs
gcloud projects add-iam-policy-binding "${GCP_PROJECT}" \
  --member="serviceAccount:${SA_EMAIL}" \
  --role="roles/bigquery.jobUser" --condition=None --quiet

# Grant read access on each dataset. BigQuery has no gcloud command for
# dataset-level IAM, so this updates the dataset access list directly.
for DS in "${ALLOWED_DATASETS[@]}"; do
  echo "Granting read access on ${GCP_PROJECT}:${DS}..."
  DS_JSON="${WORK_DIR}/${DS}.json"

  bq --project_id="${GCP_PROJECT}" show --format=prettyjson \
    "${GCP_PROJECT}:${DS}" > "${DS_JSON}"

  python3 -c '
import json, sys
path, service_account = sys.argv[1], sys.argv[2]
with open(path) as f:
    dataset = json.load(f)
# READER in the access list is the equivalent of roles/bigquery.dataViewer.
access = dataset.setdefault("access", [])
already_granted = any(
    entry.get("userByEmail") == service_account
    and entry.get("role") in ("READER", "roles/bigquery.dataViewer")
    for entry in access
)
if not already_granted:
    access.append({"role": "READER", "userByEmail": service_account})
with open(path, "w") as f:
    json.dump(dataset, f)
' "${DS_JSON}" "${SA_EMAIL}"

  bq --project_id="${GCP_PROJECT}" update --source "${DS_JSON}" "${GCP_PROJECT}:${DS}"
done

# Allow the pool to impersonate the service account
gcloud iam service-accounts add-iam-policy-binding "${SA_EMAIL}" \
  --project="${GCP_PROJECT}" \
  --role="roles/iam.workloadIdentityUser" \
  --member="principalSet://iam.googleapis.com/${POOL_RESOURCE}/attribute.email/${GATEWAY_SA_EMAIL}" \
  --condition=None --quiet

AUDIENCE="//iam.googleapis.com/${POOL_RESOURCE}/providers/${PROVIDER_ID}"

echo ""
echo "=== Paste these into the BigQuery connector in Lovable ==="
echo ""
echo "  WIF Audience:          ${AUDIENCE}"
echo "  Service Account Email: ${SA_EMAIL}"
echo ""
echo "NOTE: IAM bindings can take up to 5 minutes to propagate."