Debugging Cloud Build database connection

Question

My cloud build CI pipe is failing with very little debug information, but several factors point to a failing database connection. This is the last part of the build log:

...
Step #3 - "tests": webpack compiled successfully
Step #3 - "tests": [ info ]  running tests...
Step #3 - "tests": 
Step #3 - "tests":    connect ENOENT /cloudsql/w121-cms:europe-west1:w121/.s.PGSQL.5432
Step #3 - "tests":   
Step #3 - "tests": 
Finished Step #3 - "tests"
ERROR
ERROR: build step 3 "gcr.io/cloud-builders/npm" failed: step exited with non-zero status: 1

This is my build script:

steps:
  - id: install
    name: 'gcr.io/cloud-builders/npm'
    args: ['install']

  - id: lint
    name: 'gcr.io/cloud-builders/npm'
    args: ['run', 'lint']

  - id: proxy-install
    name: 'gcr.io/cloud-builders/npm'
    entrypoint: sh
    args:
      - -c
      - 'wget -O /workspace/cloud_sql_proxy https://dl.google.com/cloudsql/cloud_sql_proxy.linux.386 &&  chmod +x /workspace/cloud_sql_proxy'

  - id: tests
    name: 'gcr.io/cloud-builders/npm'
    timeout: 100s
    entrypoint: sh
    args:
      - -c
      - '(/workspace/cloud_sql_proxy $PROJECT_ID:europe-west1:w121 & sleep 2) && npm run ci:test'
    env:
      - 'NODE_ENV=test'
      - 'DB_CONNECTION=pg'
      - 'PG_HOST=/cloudsql/$PROJECT_ID:europe-west1:w121'
      - 'PG_PORT=5432'
      - 'PG_USER=postgres'
      - 'PG_DB_NAME=testing'
      - others ...
    secretEnv: ['PG_PASSWORD', 'APP_KEY']

availableSecrets:
  secretManager:
    - env: 'PG_PASSWORD'
      versionName: projects/$PROJECT_ID/secrets/PG_PASSWORD/versions/1
    - env: 'APP_KEY'
      versionName: projects/$PROJECT_ID/secrets/APP_KEY/versions/1

What I have checked:

Connection parameters

All of DB_CONNECTION, PG_HOST, PG_PORT and PG_USER are the same as in another working build script. From the log output we can see that the $PROJECT_ID in the host value resolves correctly. There definitely is a database called testing in that postgres instance.

Service worker permissions

Running this query:

gcloud projects get-iam-policy $PROJECTNUM

I get this confirmation that the build account can access secrets and the Cloud SQL service:

bindings:
- members:
  - serviceAccount:[email protected]
  role: roles/cloudbuild.builds.builder
- members:
  - serviceAccount:[email protected]
  role: roles/cloudbuild.serviceAgent
- members:
  - serviceAccount:[email protected]
  role: roles/cloudsql.client
- members:
  - serviceAccount:[email protected]
  - serviceAccount:[email protected]
  role: roles/secretmanager.secretAccessor

Network access

In my cd script I successfully run migrations on the same database instance using gcr.io/google-appengine/exec-wrapper and similar connection variables. Because I don't want to to add a build artifact with every pull request, I am here not building, pushing and running inside a container, but trying to connect via a proxy tunnel.

Can anyone spot a problem or have suggestions on how to debug this futher?

Thanks!

Answer 1

So! Finally got the CI pipe working. The trip-up was with the PG_HOST value. When I run the cloud proxy locally it reports:

Listening on /path/to/project/w121-cms:europe-west1:w121/.s.PGSQL.5432 for w121-cms:europe-west1:w121

But running it in the pipe it was:

Listening on 127.0.0.1:5432 for w121-cms:europe-west1:w121

The config step that worked was then:

  - id: proxy-install
    name: 'gcr.io/cloud-builders/npm'
    entrypoint: sh
    args:
      - -c
      - |
        wget -O /workspace/cloud_sql_proxy https://dl.google.com/cloudsql/cloud_sql_proxy.linux.386
        chmod +x /workspace/cloud_sql_proxy

  - id: tests
    name: 'gcr.io/cloud-builders/npm'
    timeout: 100s
    entrypoint: sh
    args:
      - -c
      - |
        /workspace/cloud_sql_proxy -dir=/workspace -instances=w121-cms:europe-west1:w121=tcp:5432 & sleep 2
        npm run ci:test
    env:
      - 'NODE_ENV=test'
      - 'DB_CONNECTION=pg'
      - 'PG_HOST=127.0.0.1'
      - 'PG_PORT=5432'
      - 'PG_USER=postgres'
      - 'PG_DB_NAME=testing'
    secretEnv: ['PG_PASSWORD', 'APP_KEY']