2

This is background on the same error

The difference in this situation is that the pg client is constructed via db-migrate. In the process of upgrading to Node 14, in order to resolve compiler warnings, npm packages were updated as follows:

    "db-migrate": "^0.11.12",
    "db-migrate-pg": "^1.2.2",
    "pg": "^8.5.1",

which caused deployment to heroku to now fail with this error:

psql: FATAL:  no pg_hba.conf entry for host "...", user "...", database "...", SSL off

From the background, the postgres client (pg) must be changed to establish its connection using ssl = { rejectUnauthorized: false }. However, the client is created not directly but via db-migrate.

We are not using the documented command-line form of db-migrate, but instead have a wrapper migration script. This script does not invoke the command-line db-migrate but instead calls DBMigrate.getInstance(true, options);, where those options are programmatically generated per-environment. Thus we circumvent the documented file-based configuration of db-migrate.

Per the db-migrate docs, DATABASE_URL was intended for use with heroku but that assumes a command-line invocation of db-migrate will honor the environment variable and then ignore (non .rc-) file-based configuration. Since we have the wrapper script, we instead depend on DATABASE_URL via another documented db-migrate mechanism:

Note that if the settings for an environment are represented by a single string that string will be parsed as a database URL.

Thus we use this programmatic configuration generated in our wrapper script:

const options = {
  env: "your_env_name_here",
  config: {
    your_env_name_here: process.env.DATABASE_URL
  },
};
const dbmigrate = DBMigrate.getInstance(true, options);

How do I specify the required ssl configuration through db-migrate using DATABASE_URL?

Tim Heilman
  • 340
  • 2
  • 11

1 Answers1

1

It turns out that allowing db-migrate to parse our heroku-provided DATABASE_URL was itself interfering with heroku's infrastructural choices requiring ssl = { rejectUnauthorized: false } to begin with, which cannot be provided by that URL alone. The pg-connection-string library will do this for you, and the code snippet below translates that parsed URL to db-migrate's long-form but including the necessary heroku ssl configuration.

const parse = require('pg-connection-string').parse;
const r = parse(process.env.DATABASE_URL);
const options = {
  env: "your_env_name_here",
  config: {
    your_env_name_here: {
      driver: "pg",
      user: r.user,
      password: r.password,
      host: r.host,
      database: r.database,
      port: r.port,
      ssl: { rejectUnauthorized: false },
    },
  },
};
const dbmigrate = DBMigrate.getInstance(true, options);

This re-enabled our heroku deploy using Node 14 and updated npm libraries related to postgres and db-migrate.

Tim Heilman
  • 340
  • 2
  • 11