How do I run the migration with SSL enabled?

[STRd6]

I'm trying to run a migration on a remote postgres host that requires SSL, passing --url=... on the command line. How do I enable SSL?

[dmcquay]

I have the same question, but I am using the config file. I added "ssl": true, but it still does not connect via SSL.

[ben-ng]

+1

[ryanwalters]

+1

[sdepold]

Hm I have a bit of hard times to answer this question, as I don't have an SSL enabled endpoint available.

[ryanwalters]

You can spin one up for free on Heroku.

[sdepold]

🆒 Will try to do that ASAP (which is probably one evening during this week)

[ryanwalters]

The SSL Heroku connection string looks something like:

postgres://username:[email protected]:5432/database?ssl=true

The ?ssl=true comes from Heroku's Docs. It is required when connecting remotely.

So back to sequelize, I try to pass this value into --url=..., including ?ssl=true and I get an error like:

Unable to connect to database: SequelizeConnectionError: no pg_hba.conf entry for host "12.34.56.78", user "username", database "database?ssl=true", SSL off

You'll notice that the database value that is being parsed includes the query parameter ?ssl=true and SSL is not being enabled.

Digging in a little bit, I see that urlStringToConfigHash ignores the query string.

It seems like two things might help here:

1. Remove query string from database

2. If ssl=true is being passed, enable it

   // Tried this just for testing, and it doesn't work. How do we enable SSL?
   if (urlParts.query) {
       result = _.assign(result, {
           ssl: true
       });
   }
   

Any thoughts?

[icemilo]

My PR has been merged, could you test if specifying dialectOptions will work for postgres?

[gofish]

⚠️ The dialectOptions PR #220 allows users to specify the requisite ssl options, fulfilling this issue as far as the ssl feature request component, but the --url parameter parsing is still broken. A flag named '--url' is reasonably expected to support valid URL forms, with or without query parameters. The database component is specified by the URI path, not including any part of the query.

It is unfortunate that node's url.parse incorrectly ambiguates the path component with the query component, and thus does not conform to RFC3986 terminology [1]. As such, urlStringToConfigHash should apparently be using the urlParts.pathname field, not urlParts.path, a fairly understandable mistake.

[1] "The path is terminated by the first question mark ("?") or number sign ("#") character, or by the end of the URI."

[joshuakarjala]

@gofish could you please give an example of how you solve this issue using dialectOptions for postgres.

[gofish]

@joshuakarjala You need to use a config file and specify "dialectOptions" with an "ssl" entry.

Example:

{
  "url": "postgres://localhost/database",
  "dialectOptions": {
    "ssl": {
      "ca": "path/to/ca.crt"
    }
}

The default config file is "config/config.json" (or config/config.js). Specify an alternative using --config .

[joshuakarjala]

Got it to work by doing:

{
   "use_env_variable":"DB_CONNECTION_STRING",
   "dialect":"postgres",
   "ssl":true,
   "dialectOptions":{
      "ssl":{
         "require":true
      }
   }
}

Still would be much appreciated if ?ssl=true as part of the URL was supported

[sushantdhiman]

Added @joshuakarjala comment into docs https://github.com/sequelize/cli/tree/master/docs#configuration-for-connecting-over-ssl

[metapink]

This seems to be related, but has slightly different information https://github.com/sequelize/cli/tree/master/docs#dialect-specific-options
It might also be incomplete however, https://stackoverflow.com/questions/43948920/how-to-connect-via-ssl-to-sequelize-db

[somratpro]

hey @joshuakarjala

I am going throw the same problem. I have created a file config/config.json in my project. and paste your code there. changed the env_variable to DATABASE_URL . but still heroku giving me the same error. can you help me? I am looking for help last 5 days.

error_log: {"error":true,"message":"no pg_hba.conf entry for host \"176.34.207.82\", user \"okqjgsiepkuary\", database \"d6s00lqc8oi37u\", SSL off"}

[RedJanvier]

Got it to work by doing:

{
   "use_env_variable":"DB_CONNECTION_STRING",
   "dialect":"postgres",
   "ssl":true,
   "dialectOptions":{
      "ssl":{
         "require":true
      }
   }
}

Still would be much appreciated if ?ssl=true as part of the URL was supported

Thank you! I had an issue that the certificate was self-signed which was resolved by adding rejectUnauthorized: false to the dialectOptions as follows:

{
   "use_env_variable":"DB_CONNECTION_STRING",
   "dialect":"postgres",
   "ssl":true,
   "dialectOptions":{
      "ssl":{
         "require":true
         "rejectUnauthorized": false
      }
   }
}

💡 Referrence: brianc/node-postgres#2009 (comment)

[decompil3d]

For anyone else who comes across this issue and, despite setting the ssl fields as above is still seeing problems, check if you have a url field specified either on the command line or in your .sequelizerc file. Sequelize seems to skip loading the config file if you've specified url. Just removing the url field from .sequelizerc and instead specifying it in your config file itself can make the difference to ensure that these SSL fields are actually respected.