Connect to Cloud SQL (PostgreSQL) instance with Sequelize

[sfalk]

I am simply not able to establish a connection to my Cloud SQL instance (PostreSQL). For deployment, I have created a cloudbuild.yamlbut no matter what I try, I'm not able to connect.

For now, I am just creating a .env during a build step. I am aware that this is not safe. I'm intending to fix this later on.

steps:
  - name: 'gcr.io/cloud-builders/docker'
    entrypoint: 'bash'
    args:
      - '-c'
      - |
        echo "ENVIRONMENT=development" > .env
        echo "DB_HOST=12.34.56.78 >> .env
        echo "DB_NAME=my-database-dev" >> .env
        echo "DB_USERNAME=postgres" >> .env
        echo "DB_PASSWORD=password" >> .env
        echo "DB_DIALECT=postgres" >> .env
        echo "CLOUD_SQL_INSTANCE="$_CLOUD_SQL_INSTANCE >> .env

  # Step to install dependencies
  - name: 'gcr.io/cloud-builders/npm'
    args: [ 'install' ]
  # Step to apply Sequelize migrations
  - name: 'gcr.io/cloud-builders/npm'
    args: [ 'run', 'db:migrate' ]

As I am calling gcloud builds submit . I am getting to the following error-response. Note, that I am doing this with my own account, not a Service Account. I am owner of the project but I added Cloud SQL Admin for good measure to my roles for now.

{
  "error": {
    "code": 401,
    "message": "Request is missing required authentication credential. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project.",
    "errors": [
      {
        "message": "Login Required.",
        "domain": "global",
        "reason": "required",
        "location": "Authorization",
        "locationType": "header"
      }
    ],
    "status": "UNAUTHENTICATED",
    "details": [
      {
        "@type": "type.googleapis.com/google.rpc.ErrorInfo",
        "reason": "CREDENTIALS_MISSING",
        "domain": "googleapis.com",
        "metadata": {
          "method": "google.cloud.sql.v1beta4.SqlConnectService.GetConnectSettings",
          "service": "sqladmin.googleapis.com"
        }
      }
    ]
  }
}

I don't understand why I get this error and how to fix this.

Additionally, I'd like to highlight that I am not available to connect to the database using psql. Perhaps, this is expected, but in trying to get any connection going, I tried to rune psql -h postgresql://user:password@public-ip/database-name but only got a timeout from that.

Last but not least, since I am using Sequelize, this is how I am initializing it:

const connector = new Connector();
const clientOpts = connector.getOptions({
  instanceConnectionName: process.env.CLOUD_SQL_INSTANCE,
  ipType: IpAddressTypes.PUBLIC,
  authType: process.env.DB_PASSWORD ? AuthTypes.PASSWORD : AuthTypes.IAM,
});
dbConfig = {
  username: process.env.DB_USERNAME,
  password: process.env.DB_PASSWORD,
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  dialect: process.env.DB_DIALECT,
  dialectOptions: clientOpts,
};
export const sequelize = new Sequelize({
  ...dbConfig,
  models: [/* ... */],
});

// Test connection
try {
  console.log('[sequelize.ts] Testing connection ..');
  sequelize
    .authenticate()
    .catch((err) => console.error('Unable to connect to the database:', err))
    .then();
} catch (err) {
  console.error('Unable to connect to the database:', err);
}

Can somebody please guide me though this?

[robertcarlos]

Hi @sfalk,

Welcome to Google Cloud Community!

There might be certain steps that you missed out in connecting to Cloud SQL. Based on this documentation on connecting from Cloud Build using Node.js:

• Make sure your Cloud Build service account has the IAM roles and permissions required to connect to the Cloud SQL instance. The Cloud Build service account is listed on the Google Cloud console IAM page as the Principal [YOUR-PROJECT-NUMBER]@cloudbuild.gserviceaccount.com.

  To view this service account in the Google Cloud console, select the Include Google-provided role grants checkbox.

  Your Cloud Build service account needs one of the following IAM roles:

  • Cloud SQL Client (preferred)
  • Cloud SQL Admin
  Or, you can manually assign the following IAM permissions:
  • cloudsql.instances.connect
  • cloudsql.instances.get

  If the Cloud Build service account belongs to a different project than the Cloud SQL instance, then the Cloud SQL Admin API and IAM permissions need to be added for both projects.

• "DATABASE_PORT=${_DATABASE_PORT}" is not defined in your cloudbuild.yaml. Your code should look like this:
  

  env:
    - "DATABASE_NAME=${_DATABASE_NAME}"
    - "DATABASE_USER=${_DATABASE_USER}"
    - "DATABASE_HOST=127.0.0.1"
    - "DATABASE_PORT=${_DATABASE_PORT}" //usually this is 5432
    - "DATABASE_TYPE=${_DATABASE_TYPE}""DATABASE_TYPE=${_DATABASE_TYPE}"

You may also check the best practices like using Cloud SQL Auth Proxy when testing your application locally. Check this quickstart for using the Cloud SQL Auth Proxy and Cloud SQL Proxy via a docker container as well for your reference.

Hope this helps.