GCP Postgres refusing connection from App Engine nodejs

Question

I am following the tutorial for Strapi on CGP App Engine (nodejs- standard env) and unable to get the app to start because the connection is being refused Error: connect ECONNREFUSED 127.0.0.1:5432 by the GCP Postgres instance (Public IP) .

Why I'm confused

1. GCP Service Principle Persmissions: <project_name>@appspot.gserviceaccount.com has Cloud SQL Client for the App Engine default service account so this should apply to all App Engine Services.
2. I have other App Engine Services (python) connecting successfully to other Postgres Databases. This tells me I have the correct permissions, Cloud SQL Admin API enabled, and the correct username/password.
3. The code works locally (Docker) while linking the GCP Postgres database, but only with TCP routing, not a Unix Socket SQL proxy:

../../cloud_sql_proxy -instances=<project_name>:europe-west1:<sql_instance_name>=tcp:5432 & (sleep 5 && yarn strapi start)

I can login to the locally hosted Strapi app, add users, etc. and the changes are reflected in the GCP Postgres database.

4. The only difference between the local deployment (docker-compose.yml) and the App engine (app.yml) is how I set the environment variables.

#Dockerfile
FROM node:14-buster

RUN echo "deb [signed-by=/usr/share/keyrings/cloud.google.gpg] http://packages.cloud.google.com/apt cloud-sdk main" | tee -a /etc/apt/sources.list.d/google-cloud-sdk.list && curl https://packages.cloud.google.com/apt/doc/apt-key.gpg | apt-key --keyring /usr/share/keyrings/cloud.google.gpg  add - && apt-get update -y && apt-get install google-cloud-sdk -y
RUN wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy && chmod +x cloud_sql_proxy

#docker-compose.yml
version: "3.8"
services:
  dev:
    build: .
    ports:
      - "1337:1337"
    volumes:
      - .:/src
    command: ["yarn", "run", "start"]
    working_dir: /src
    environment:
      NODE_ENV: "production"
      DATABASE_NAME: '<database name>'
      DATABASE_USERNAME: '<username>'
      DATABASE_PASSWORD: '<password>'
      INSTANCE_CONNECTION_NAME: '<project_name>:europe-west1:<instance_name>'

# app.yml
runtime: nodejs14
instance_class: F2
service: strapi
env_variables:
  HOST: '0.0.0.0'
  NODE_ENV: 'local'
  DATABASE_NAME: '<database name>'
  DATABASE_USERNAME: '<username>'
  DATABASE_PASSWORD: '<password>'
  INSTANCE_CONNECTION_NAME: '<project_name>:europe-west1:<instance_name>'

beta_settings:
  cloud_sql_instances: '<project_name>:europe-west1:<instance_name>'

The code that defines the connection in the nodejs project, from the Strapi tutorial:

module.exports = ({ env }) => ({
  defaultConnection: 'default',
  connections: {
    default: {
      connector: 'bookshelf',
      settings: {
        client: 'postgres',
        socketPath: `/cloudsql/${env('INSTANCE_CONNECTION_NAME')}`,
        database: env('DATABASE_NAME'),
        username: env('DATABASE_USERNAME'),
        password: env('DATABASE_PASSWORD'),
      },
      options: { }
    },
  },
});

What have I missed? What else can I check? Someone please help me end this insanity.

Answer 1

What fixed it for me was the following:

1. Go to my App engine default service principal and give it the following roles (as described here)

• Cloud SQL Client
• Cloud SQL Editor
• Cloud SQL Admin

2. Change socketPath key to 'host' in the following default connection settings:

module.exports = ({ env }) => ({
  defaultConnection: 'default',
  connections: {
    default: {
      connector: 'bookshelf',
      settings: {
        client: 'postgres',
        ----> socketPath: `/cloudsql/${env('INSTANCE_CONNECTION_NAME')}`,
        database: env('DATABASE_NAME'),
        username: env('DATABASE_USERNAME'),
        password: env('DATABASE_PASSWORD'),
      },
      options: { }
    },
  },
});