GCP Postgres refusing connection from App Engine nodejs-CodePudding

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

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.
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.
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.

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.

CodePudding user response：

What fixed it for me was the following:

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

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: { }
    },
  },
});