Not using ssl and sslmode parameters with Heroku / Migrations folder

paulmiller3000 commented 4 years ago

I'm submitting a...

[ ] Bug report
[ ] Feature request
[x] Question

Current behavior

When deploying to Heroku, I receive the following error:

[ERROR] AssertionError [ERR_ASSERTION]: ifError got unwanted exception: no pg_hba.conf entry for host "{redacted}", user "{redacted}", database "{redacted}", SSL off
    at module.exports (/app/node_modules/db-migrate/lib/commands/helper/assert.js:9:14)

Procfile:

release: db-migrate up
web: node server.js

Below is my database.json file. The environment in question is "production."

{
  "development": {
    "driver": "pg",
    "user": {"ENV": "DATABASE_USER"},
    "password": {"ENV": "DATABASE_PASSWORD"},
    "host": {"ENV": "DATABASE_HOST"},
    "database": {"ENV": "DATABASE_NAME"},
    "schema": {"ENV": "DATABASE_SCHEMA"}
  },

  "staging": {
    "driver": "pg",
    "user": {"ENV": "DATABASE_USER"},
    "password": {"ENV": "DATABASE_PASSWORD"},
    "host": {"ENV": "DATABASE_HOST"},
    "database": {"ENV": "DATABASE_NAME"},
    "schema": {"ENV": "DATABASE_SCHEMA"}
  },

  "production": {
    "driver": "pg",
    "url": {"ENV": "HEROKU_POSTGRESQL_BLACK_URL"},
    "ssl": true,
    "sslmode": "require"
  }
}

I hard-coded the ssl and sslmode values after first trying them as environment variables.

I checked and the proper database name and user are being read from the URL.

Expected behavior

db-migrate attempts to connect using ssl.

Environment


db-migrate version: 0.11.6
db-migrate driver with versions: db-migrate-pg 1.0.0

Additional information:
- Node version: 12.3.1
- Platform:  Windows 10

paulmiller3000 commented 4 years ago

Update: I'm making some progress. First, I updated my Procfile as follows:

release: db-migrate up -v -e $ENVIRONMENT
web: node server.js

I saw in the output that it was using development settings. For some reason, it wasn't recognizing the ENVIRONMENT config var. So, I set it to use NODE_ENV, which was set via Heroku CLI when I first set up the server.

That resulted in the following:

[INFO] Detected and using the projects local version of db-migrate. '/app/node_modules/db-migrate/index.js' [INFO] Using production settings: { driver: 'postgres', user: '{redacted}', password: '**', database: '{redacted}', host: '{redacted}', port: '5432', sslmode: 'require', ssl: true, native: true } [INFO] require: db-migrate-pg [INFO] connecting Cannot find module 'pg-native'

So, I ran npm install pg-native. I also updated my database.json production node as follows:

  "production": {    
    "driver": "pg",    
    "url": {"ENV": "HEROKU_POSTGRESQL_BLACK_URL"},    
    "addIfNotExists": {
      "sslmode": "require"
    },
    "overwrite": {
      "native": true
    }

Now this is where I'm at (almost there!):

[INFO] connected [SQL] show server_version_num [SQL] SHOW search_path [SQL] SET search_path TO "$user","public" [SQL] SELECT table_name FROM information_schema.tables WHERE table_name = 'migrations' AND table_schema = 'public' [INFO] creating table: migrations [SQL] CREATE TABLE IF NOT EXISTS "migrations" ("id" SERIAL PRIMARY KEY NOT NULL, "name" VARCHAR (255) NOT NULL, "run_on" TIMESTAMP NOT NULL) [INFO] migration table created [INFO] loading migrations from dir /app/migrations [ERROR] TypeError: Cannot read property '1' of null at Class.parseName (/app/node_modules/db-migrate/lib/skeleton.js:163:17) at Class.init (/app/node_modules/db-migrate/lib/migration.js:20:24) at Class.prototype. [as init] (/app/node_modules/db-migrate/lib/class.js:46:26) at new Class (/app/node_modules/db-migrate/lib/class.js:59:17) at /app/node_modules/db-migrate/lib/migration.js:339:14 at Array.map () at /app/node_modules/db-migrate/lib/migration.js:338:35 at FSReqCallback.oncomplete (fs.js:154:23)

It should be noted all migrations run correctly in our local environments, as well as on the free version of PostgreSQL on our Heroku Staging environment. Finally, they ran correctly in Production until we upgraded the PostgresSQL add-on.

So, I'm not clear on why it's failing on the very first migration. Here's the function from that migration:

exports.up = function(db) {
  return db.createTable('user', 
  {
    id: { 
      type: 'int',
      unsigned: true,
      notNull: true,
      primaryKey: true,
      autoIncrement: true
    },
    email: {
      type: 'string',
      notNull: true,
      length: 200
    },
    bcp_profileid: 'int',
    bcp_guid: {
      type: 'string',
      length: 50
    },
    bcp_admin: {
      type: 'boolean',
      notNull: true,
      defaultValue: false
    },
    first_name: 'string',
    last_name: 'string',
    date_created: {
      type: 'datetime',
      notNull: true,
      defaultValue: new String('CURRENT_TIMESTAMP')
    },
    last_login: 'datetime'
  });
};

Still, it feels great to be making progress. Will continue to research.

paulmiller3000 commented 4 years ago

SUCCESS! This last issue was my fault: I'd added a file named migrations-config.js to the migrations folder as a troubleshooting step I'd found somewhere along the way. That's a BIG no-no: ONLY migrations should go in the migrations folder (obviously).

Thanks to this user here: https://stackoverflow.com/a/35160097/1575022

Hopefully this helps someone else along the way.

desmondmc commented 3 years ago

@paulmiller3000 you just saved my life. I was so stuck on this, I don't even want to imagine how much time i would have wasted if I hadn't have found this issue.

Thanks so much! 🤗

paulmiller3000 commented 3 years ago

@desmondmc Glad I could help!

desmondmc commented 3 years ago

I'll add my issues, just in case it's helpful to future readers.

The problem started happening to me after updating from a hobby heroku postgres db to standard.
It seems that i was never actually connecting with ssl and it was just working before because the heroku hobby postgres allows that.
As @paulmiller3000 pointed out db-migrate kept using my dev config for some reason, even though i was passing -e prod to the db-migrate command (heroku run db-migrate up --config database.json -e prod --app my-app). It kept saying something about "prod not valid avoid using ';'"
Nothing i could do would force db-migrate to use my production config. In the end the only thing that worked was changing my config file to use NODE_ENV to choose the environment:

{
  "defaultEnv": {"ENV": "NODE_ENV"},
  "dev": {
    "driver": "pg",
    "host": "localhost",
    "database": "hevy"
  },
  "production": {
    "driver": "pg",
    "url": { "ENV": "HEROKU_POSTGRESQL_SILVER_URL" },
    "addIfNotExists": {
      "sslmode": "require"
    },
    "overwrite": {
      "native": true
    }
  }
}

jasper-lyons commented 3 years ago

* As @paulmiller3000 pointed out db-migrate kept using my `dev` config for some reason, even though i was passing `-e prod` to the db-migrate command (`heroku run db-migrate up --config database.json -e prod --app my-app`). It kept saying something about "prod not valid avoid using ';'"

Just solved this issue myself. FYI, the heroku command will eat your -e param so db-migrate never receives is. If you instead do heroku run bash and then run your commands from the bash cli, it should work (I had success with this).

db-migrate / node-db-migrate