CODEWITHSUNDEEP
continuous-integrationgoogle-cloud-sqlgoogle-cloud-build
Jannie Theunissen
Jannie Theunissen

Reputation: 30114

CI pipe failing to connect to test database

When I try to run my app's test suite in a CI pipe using Google Cloud Build, the process fails during the tests step with a database authentication error:

error: password authentication failed for user "postgres"

I am pretty confident about the correctness of the PG_PASSWORD secret:

Here is my Cloud Build CI config:

steps:
  - id: install
    name: 'node:22'
    entrypoint: npm
    args: ['install']

  - id: proxy-install
    name: 'node:22'
    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: 'node:22'
    timeout: 600s
    entrypoint: sh
    args:
      - -c
      - |
        /workspace/cloud_sql_proxy -term_timeout=100s -dir=/workspace -instances=w121-cms:europe-west1:w121=tcp:5432 & sleep 2
        npm run ci:test
    env:
      - 'TZ=UTC'
      - 'HOST=0.0.0.0'
      - 'LOG_LEVEL=info'
      - 'APP_NAME=Journey'
      - 'SESSION_DRIVER=memory'
      - 'NODE_ENV=test'
      - 'DB_HOST=127.0.0.1'
      - 'DB_PORT=5432'
      - 'DB_USER=postgres'
      - 'DB_DATABASE=testing'
      - 'DB_PASSWORD=$$PG_PASSWORD'
      - '[email protected]'
      - 'MAILGUN_API_KEY=redactedredacted'
      - 'MAILGUN_DOMAIN=mailgun.example.com'
      - 'BIBLE_API_KEY=redactedredacted'
      - 'CLOUDINARY_API_KEY=tmp'
      - 'CLOUDINARY_SECRET=tmp'
      - 'CLOUDINARY_CLOUD_NAME=tmp'
      - 'CLOUDINARY_PRESET=tmp'
    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

Here is the relevant log trace:

Step #2 - "tests": c[ info ] booting application to run tests...
Step #2 - "tests": 2025/02/19 14:22:39 New connection for "w121-cms:europe-west1:w121"
Step #2 - "tests": 2025/02/19 14:22:39 refreshing ephemeral certificate for instance w121-cms:europe-west1:w121
Step #2 - "tests": 2025/02/19 14:22:39 Scheduling refresh of ephemeral certificate in 54m59s
Step #2 - "tests": 2025/02/19 14:22:40 New connection for "w121-cms:europe-west1:w121"
Step #2 - "tests": 2025/02/19 14:22:40 Instance w121-cms:europe-west1:w121 closed connection
Step #2 - "tests": 2025/02/19 14:22:40 Instance w121-cms:europe-west1:w121 closed connection
Step #2 - "tests": 
Step #2 - "tests":    error:
Step #2 - "tests": password
Step #2 - "tests": authentication
Step #2 - "tests": failed
Step #2 - "tests": for
Step #2 - "tests": user
Step #2 - "tests": "postgres"

Upvotes: 2

Views: 78

Answers (1)

Jonathan Hess
Jonathan Hess

Reputation: 356

Jonathan from the Cloud SQL Connectors team here. It looks you have configured your Cloud SQL Proxy correctly. However, perhaps the Cloud Build task configuration is not getting the password into the correct environment variable.

You may need to configure your Cloud Build tasks so that the secret manager directly sets the DB_PASSWORD environment variable, like this example from the Cloud SQL: Connect to Cloud Build documentation.

Try out this updated configuration:

steps:
  - id: install
    name: 'node:22'
    entrypoint: npm
    args: ['install']

  - id: proxy-install
    name: 'node:22'
    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: 'node:22'
    timeout: 600s
    entrypoint: sh
    args:
      - -c
      - |
        /workspace/cloud_sql_proxy -term_timeout=100s -dir=/workspace -instances=w121-cms:europe-west1:w121=tcp:5432 & sleep 2
        npm run ci:test
    env:
      - 'TZ=UTC'
      - 'HOST=0.0.0.0'
      - 'LOG_LEVEL=info'
      - 'APP_NAME=Journey'
      - 'SESSION_DRIVER=memory'
      - 'NODE_ENV=test'
      - 'DB_HOST=127.0.0.1'
      - 'DB_PORT=5432'
      - 'DB_USER=postgres'
      - 'DB_DATABASE=testing' # Remove DB_PASSWORD env here.
      - '[email protected]'
      - 'MAILGUN_API_KEY=redactedredacted'
      - 'MAILGUN_DOMAIN=mailgun.example.com'
      - 'BIBLE_API_KEY=redactedredacted'
      - 'CLOUDINARY_API_KEY=tmp'
      - 'CLOUDINARY_SECRET=tmp'
      - 'CLOUDINARY_CLOUD_NAME=tmp'
      - 'CLOUDINARY_PRESET=tmp'
    secretEnv: 
      - 'DB_PASSWORD' # Reference DB_PASSWORD env secret to this task
      - 'PG_PASSWORD'
      - 'APP_KEY'

availableSecrets:
  secretManager:
    - env: 'PG_PASSWORD'
      versionName: projects/$PROJECT_ID/secrets/PG_PASSWORD/versions/1
    - env: 'DB_PASSWORD' # Set DB_PASSWORD env directly from the secret
      versionName: projects/$PROJECT_ID/secrets/PG_PASSWORD/versions/1
    - env: 'APP_KEY'
      versionName: projects/$PROJECT_ID/secrets/APP_KEY/versions/1

Upvotes: 1

Related Questions