Using Airbyte on Google Compute Engine with configuration in Google Cloud SQL

[joseph-maddox]

Documenting this here in case it helps future-me or someone else in the community. (Also, a shout-out to the engineers on Airbyte office-hours who gave me some helpful pointers and ideas. 👏)

If you want to run Airbyte on a GCE VM with its configs in an external Cloud SQL Postgres instance, you'll need to add a Google Cloud SQL Proxy container to the docker-compose environment to make your config database accessible to Airbyte. Running the proxy on the host doesn't appear to be enough (probably due to some esoterica of Docker networking).

Replace the db service in docker-compose.yaml with the following:

  cloudsql:
    image: gcr.io/cloud-sql-connectors/cloud-sql-proxy:2.6.0
    container_name: airbyte-cloudsql-proxy
    # replace your-gcp-project:us-central1:airbyte, below with the ID of your Cloud SQL instance
    command: --address 0.0.0.0 --credentials-file /secrets/airbyte-sa.json your-gcp-project:us-central1:airbyte?port=5432
    volumes:
      - ./airbyte-sa.json:/secrets/airbyte-sa.json
    networks:
      - airbyte_internal
    ports:
      - 5432:5432
    restart: always

Update the .env to reflect the following:

DATABASE_USER=airbyte
DATABASE_PASSWORD=your-password-here
DATABASE_HOST=cloudsql
DATABASE_PORT=5432
DATABASE_DB=airbyte
DATABASE_URL=jdbc:postgresql://cloudsql:5432/airbyte

This setup assumes that a service account JSON key (for a service account with the cloudsql.client role) is stored in a file named airbyte-sa.json in the root Airbyte folder (with docker-compose.yaml, run-ab-platform.sh, etc.)

[joseph-maddox]

And if you want to make all those changes using a bash script (in my case, using a remote-exec provisioner in Terraform), this ought to do the trick:

#!/usr/bin/env bash

find_replace() {
  FIND=$(printf '%s\n' "$1" | sed -e 's/[\/$.*[\\^&]/\\&/g')
  REPLACE=$(printf '%s\n' "$2" | sed -e 's/[\/&]/\\&/g')
  sudo sed -i "s/$FIND/$REPLACE/" "$3"
}

##### Updating Airbyte Config
echo 'Updating Airbyte Config...'
CLOUD_SQL_PROXY_VER='2.6.0'
AIRBYTE_CONFIG=/usr/local/airbyte/.env
AIRBYTE_DB_INSTANCE=$(gcloud secrets versions access 'projects/<my-project-id>/secrets/airbyte-db-instance/versions/latest')
AIRBYTE_PASSWORD=$(gcloud secrets versions access 'projects/<my-project-id>/secrets/airbyte-db-password/versions/latest')
AIRBYTE_SA_KEY=$(gcloud secrets versions access 'projects/<my-project-id>/secrets/airbyte-sa-key/versions/latest')
AIRBYTE_SQL_CMD="--address 0.0.0.0 --credentials-file /secrets/airbyte-sa.json $AIRBYTE_DB_INSTANCE?port=5432"
sudo echo "$AIRBYTE_SA_KEY" > "/usr/local/airbyte/airbyte-sa.json"
sudo yq e 'del(.services.db)' docker-compose.yaml -i
sudo yq e ".services.cloudsql.image = \"gcr.io/cloud-sql-connectors/cloud-sql-proxy:$CLOUD_SQL_PROXY_VER\"" docker-compose.yaml -i
sudo yq e ".services.cloudsql.container_name = \"airbyte-cloudsql-proxy\"" docker-compose.yaml -i
sudo yq e ".services.cloudsql.command = \"$AIRBYTE_SQL_CMD\"" docker-compose.yaml -i
sudo yq e ".services.cloudsql.volumes |= [\"./airbyte-sa.json:/secrets/airbyte-sa.json\"]" docker-compose.yaml -i
sudo yq e ".services.cloudsql.networks |= [\"airbyte_internal\"]" docker-compose.yaml -i
sudo yq e ".services.cloudsql.ports |= [\"5432:5432\"]" docker-compose.yaml -i
sudo yq e ".services.cloudsql.restart = \"always\"" docker-compose.yaml -i
find_replace \
  "DATABASE_USER=docker" \
  "DATABASE_USER=airbyte" $AIRBYTE_CONFIG
find_replace \
  "DATABASE_PASSWORD=docker" \
  "DATABASE_PASSWORD=$AIRBYTE_PASSWORD" $AIRBYTE_CONFIG
find_replace \
  "DATABASE_HOST=db" \
  "DATABASE_HOST=cloudsql" $AIRBYTE_CONFIG
find_replace \
  "DATABASE_URL=jdbc:postgresql://db:5432/airbyte" \
  "DATABASE_URL=jdbc:postgresql://cloudsql:5432/airbyte" $AIRBYTE_CONFIG

[marcosmarxm]

Thanks a lot @joseph-maddox! @RealChrisSean check out this tutorial to use secure connection external database on Google Cloud.