Sequelize: Can't use SSL with Postgres

[mickhansen]

I wrote a local hack first, but a change in the master breaks that.
So i'll try to write a full fix with tests, but we'll need some kind of flag, since the postgres server you test on has to be set up for SSL.

[TrashOut]

Guuuuuuys, it's not working on Cloud9 this way.... please can you check it?

[cameront]

I'm not totally clear on the specifics of this bug, but we just ran into a problem conntecting to a heroku DB from outside heroku (therefore requiring ssl).

We had to change our connection parameters to tell sequelize to use native postgres bindings and turn ssl on:

{
  database: XXXX,
  username: YYYY,
  ...
  native: true,
  ssl: true
}

We only learned about the native binding thing from one of the answers to this question: http://stackoverflow.com/questions/10279965/authentication-error-when-connecting-to-heroku-postgresql-database

Hope that helps!

[mickhansen]

It's a problem with how the connection string is generated internally.
I wanted to fix this, problem is i never really figured out (not that i spent much time) how to create a unit test for it :)

[cameront]

I see. And maybe I'm still not understanding, but we are able to connect to a postgres database over ssl using sequelize with no code modifications, as long as "native":true and "ssl":true is specified in our config.

[mickhansen]

Yes works with native - Not with the javascript version though, where i believe i got it to work once

[nickarnold]

Hello all. We've run into this problem too, and have solved it rather easily. It does not require use of the native module as long as you're using a new (we're using 2.11.1) version of pg.

Basically, what happens is that the databaseConnectionUri method in /lib/dialects/postgres/query-generator.js doesn't look for the SSL config value when it builds out the URI that gets passed along to the pg module. The PG module looks for a query string in the URI, and if it exists checks the query string for an ssl key with a value of either true or 1.

So, the solution is to add an SSL check in the databaseConnectionUri template variable and then add SSL line inside the underscore template renderer directly below. I'll submit a PR for this probably later this evening.

...going to try and figure out tests before issuing the PR. :smile:

Note: If you're in a hurry and just need something to work immediately you can easily set ssl to true in the pg module, in a file called defaults.js. This quick-fix certainly isn't production-ready, but it'll work if you have a demo to give today and must use SSL.

Edited for grammer.

[mickhansen]

@nickarnold yeah thats the fix/hack i did myself, but i haven't figured out a unit test for it yet - why i never made the PR.

[mickhansen]

@nickarnold did you ever get around to doing a PR? :)

[joeylgutierrez]

@mickhansen I submitted @nickarnold 's change but without any unit test additions. I wasn't sure how to add that to the test db to write something that made any sense. I'm willing to write the tests and do the work but need a little direction

[mickhansen]

@joeylgutierrez Yeah not sure testing is feasible here, i'll look at your PR.

[amingilani]

Could you please reopen this issue. Trying to connect with heroku.

var sequelize = new Sequelize(process.env.DATABASE_URL, {
  dialect: 'postgres'
});

I get this

6:23:13 PM worker.1 |    name: 'SequelizeConnectionError',
6:23:13 PM worker.1 |    message: 'no pg_hba.conf entry for host "xxx.xxx.xxx.xxx", user "xxxxxxxxxxxxxxx", database "xxxxxxxxxxx", SSL off',
6:23:13 PM worker.1 |    parent:
6:23:13 PM worker.1 |     { [error: no pg_hba.conf entry for host "xxx.xxx.xxx.xxx", user "xxxxxxxxxxxxxxx", database "xxxxxxxxxxx", SSL off]
6:23:13 PM worker.1 |       name: 'error',
6:23:13 PM worker.1 |       length: 161,
6:23:13 PM worker.1 |       severity: 'FATAL',
6:23:13 PM worker.1 |       code: '28000',
6:23:13 PM worker.1 |       detail: undefined,
6:23:13 PM worker.1 |       hint: undefined,
6:23:13 PM worker.1 |       position: undefined,
6:23:13 PM worker.1 |       internalPosition: undefined,
6:23:13 PM worker.1 |       internalQuery: undefined,
6:23:13 PM worker.1 |       where: undefined,
6:23:13 PM worker.1 |       schema: undefined,
6:23:13 PM worker.1 |       table: undefined,
6:23:13 PM worker.1 |       column: undefined,
6:23:13 PM worker.1 |       dataType: undefined,
6:23:13 PM worker.1 |       constraint: undefined,
6:23:13 PM worker.1 |       file: 'auth.c',
6:23:13 PM worker.1 |       line: '474',
6:23:13 PM worker.1 |       routine: 'ClientAuthentication' },
6:23:13 PM worker.1 |    original:
6:23:13 PM worker.1 |     { [error: no pg_hba.conf entry for host "xxx.xxx.xxx.xxx", user "xxxxxxxxxxxxxxx", database "xxxxxxxxxxx", SSL off]
6:23:13 PM worker.1 |       name: 'error',
6:23:13 PM worker.1 |       length: 161,
6:23:13 PM worker.1 |       severity: 'FATAL',
6:23:13 PM worker.1 |       code: '28000',
6:23:13 PM worker.1 |       detail: undefined,
6:23:13 PM worker.1 |       hint: undefined,
6:23:13 PM worker.1 |       position: undefined,
6:23:13 PM worker.1 |       internalPosition: undefined,
6:23:13 PM worker.1 |       internalQuery: undefined,
6:23:13 PM worker.1 |       where: undefined,
6:23:13 PM worker.1 |       schema: undefined,
6:23:13 PM worker.1 |       table: undefined,
6:23:13 PM worker.1 |       column: undefined,
6:23:13 PM worker.1 |       dataType: undefined,
6:23:13 PM worker.1 |       constraint: undefined,
6:23:13 PM worker.1 |       file: 'auth.c',
6:23:13 PM worker.1 |       line: '474',
6:23:13 PM worker.1 |       routine: 'ClientAuthentication' } }

Is there a switch to put ssl on?

[mickhansen]

@amingilani did you read the issue at all? or look at docs, anyways you should use dialectOptions.ssl

[amingilani]

@mickhansen you're a beautiful person. By the way, nowhere in this issue does it say to use dialectOptions, and the dialectOptions in the docs don't show an ssl option.

I'm just going to leave this snippet for someone like me that runs into problems with Heroku:

var sequelize = new Sequelize(process.env.DATABASE_URL, {
  dialect: 'postgres',
  dialectOptions: {
    ssl: true
  }
});

[mickhansen]

@amingilani You're right about that. dialectOptions is just passed to the underlying library so we usually just refer to the indiviudal libraries :)

[2WheelCoder]

Thanks @amingilani. That was a big help with my Heroku setup.

[jonathanboswell]

@mickhansen Thanks for a very helpful ticket/thread here.

After installing the latest sequelize (3.23.2) and pg modules and setting up SSL and pg-native (and the libpq dependancies) I was still getting connection errors:

sequelize: {
    uri: 'postgres://{{user}}:{{password-with-chars-like-#-&}}@{{aws-rds-url}}}/{{database}}',
    options: {
        native: true,
        ssl: true
    }
}

The password had a "#" and a "&" in it which are valid characters for a postgres password and looks like they break the url.parse() in line 125 in the Sequelize constructor:

var urlParts = url.parse(arguments[0]);

After I removed those characters everything worked perfect.

Hope this helps anyone running into the same issue and don't know if this gets filed into the "stupid password" bucket or if you want me to create a new bug ticket.

Thanks for everything you do for Sequelize!

[mickhansen]

url.parse (which we use) would definitely choke on # and &.
You're welcome to open a ticket for password validations of sorts @jonathanboswell (by validation i mean throwing an error if containing special url characters).

[johnfdhartman]

For me the problem was caused by Sequelize parsing DATABASE_URL by including the / in the URI for the database parameter. E.g if my database url was postgres://username:password@host:5432/database, it was giving me ERROR: database "/database" does not exist. (This was after following @amingilani 's suggestion).

I fixed the parsing in my config.js as follows:

const URL = require('url'); 

const parseURL = (databaseURL) => {
  let URLObj = URL.parse(databaseURL);
  let [username, password] = URLObj.auth.split(':');
  return {
    username,
    password,
    host: URLObj.hostname,
    port: URLObj.port,
    database: URLObj.path.split('/')[1],
    dialect: 'postgres',
  };
};

//...
module.exports = {
  production: Object.assign(
    {dialectOptions: {ssl: true}, logging: console.log},
    parseURL(process.env.DATABASE_URL)
  ),
};

IIRC this doesn't work with the more recent versions of Node's URL library. This was for 6.26.

[salvadornava01]

@mickhansen you're a beautiful person. By the way, nowhere in this issue does it say to use dialectOptions, and the dialectOptions in the docs don't show an ssl option.

I'm just going to leave this snippet for someone like me that runs into problems with Heroku:

var sequelize = new Sequelize(process.env.DATABASE_URL, {
  dialect: 'postgres',
  dialectOptions: {
    ssl: true
  }
});

Thank you very very much!!

[tunjioye]

For Me, just setting ssl: true did not work.

I had to perform these 2 steps

1. Append ?sslmode=require to my POSTGRES DATABASE URI
2. Then make sure rejectUnauthorized: false is in my dialectOptions.ssl

LIKE THIS EXAMPLE BELOW

const sequelize = new Sequelize(`${process.env.DATABASE_URI}?sslmode=require`, {
  url: process.env.DATABASE_URI,
  dialect: 'postgres',
  logging: false,
  dialectOptions: {
    ssl: {
      rejectUnauthorized: false, // very important
    }
  }
}

For More Information, here is an Article on Heroku DevCenter about it
https://devcenter.heroku.com/articles/heroku-postgresql#heroku-postgres-ssl

[radcapitalist]

@tunjioye Did you see documentation somewhere saying that require: true is a valid value inside of dialectOptions.ssl? Because this is the only place I've seen it, and I don't think it does anything. For example, setting require: false in no way makes SSL optional. node-postgres does not seem to support the equivalent of sslmode = allow.

[tunjioye]

@tunjioye Did you see documentation somewhere saying that require: true is a valid value inside of dialectOptions.ssl? Because this is the only place I've seen it, and I don't think it does anything. For example, setting require: false in no way makes SSL optional. node-postgres does not seem to support the equivalent of sslmode = allow.

You are right @radcapitalist require: true is not needed inside the dialectOptions.ssl

[kasvith]

I'm having this issue with cli tool with --url option

[joao-p-pinto-findmore]

I keep getting the following error even though my queries still work. Is this expected when using rejectUnauthorized: false?

[DATABASE] [9-1] sql_error_code = 28000 FATAL: no pg_hba.conf entry for host "xx.xxx.xxx.xx", user "xxxxxxxxxxxxxx", database "xxxxxxxxxxxxxx", SSL off

This is my current configuration:

sequelize: {
  databaseUrl: `${DATABASE_URL}?sslmode=require`,
  options: {
    native: true,
    dialect: 'postgres',
    dialectOptions: {
      rejectUnauthorized: false,
    },
  }
}

I'm currently running the following setup:

"pg": "^6.1.0",
"pg-hstore": "^2.3.2",
"pg-native": "^1.10.0",
"sequelize": "^3.33.0",

I'm also running Node.js 10.24.0 and Postgres 13.2 (using the Heroku Postgres add-on).

[tunjioye]

@joao-p-pinto-findmore update your sequelize options to this.

sequelize: {
  databaseUrl: `${DATABASE_URL}?sslmode=require`,
  options: {
    native: true,
    dialect: 'postgres',
    dialectOptions: {
      ssl: {
        rejectUnauthorized: false, // very important
      },
    },
  }
}

Notice the dialectOptions.ssl.rejectUnauthorized

You can read this comment for more https://github.com/sequelize/sequelize/issues/956#issuecomment-778149933

[yoagauthier]

@joao-p-pinto-findmore

We've had several issues similar to you in the past few days on Heroku servers too.

• We had no pg_hba.conf entry was issues on Postgres v13.2, not on databases v12 or lower we also have on Heroku
• We also had DB connection that were initiated by pg but never resolved on some other servers

Their support pointed us at 2 recent changes :

1. SSL is now mandatory for postgres databases
2. Postgres 13 is now default for all databases

We struggled to find why the suggested rejectUnauthorized: false was no working, until we notice that pg recently introduced changes on how it's handling the SSL connection. Upgrading to pg 8.0 solved some of our problems.

But Postgres v13.2 databases still struggled with no pg_hba.conf entry errors. So we tried to setup the PGSSLMODE config var to no-verify as suggested in Heroku's doc, and it worked !

Not sure what is the underlying issue, I'm guessing some kind of order of precedence between configs vars, URL params and parameters passed in dialectOptions in sequelize or pg.

Hope this helps :-)

[tunjioye]

@joao-p-pinto-findmore

IN ADDITION
if you are deploying to Heroku, you can read this article
https://devcenter.heroku.com/articles/heroku-postgresql#heroku-postgres-ssl
https://devcenter.heroku.com/articles/heroku-postgresql#connecting-in-node-js

[kasvith]

If you have this issue for sequelize-cli in the Heroku environment(in-app obviously you can set SSL with options) can use the following method to execute migrations without an issue

PGSSLMODE=no-verify npx sequelize-cli --url $DATABASE_URL db:migrate

[crispinamuriel]

Hello, adding these lines

    dialect: 'postgres',
    dialectOptions: {
      ssl: true
    }

Inside my 'new Sequilize' configuration object helped me get my Heroku app to start working again thanks!

const db = new Sequelize(
  process.env.DATABASE_URL || `postgres://localhost:5432/${databaseName}`,
  {
    logging: false,
    dialect: 'postgres',
    dialectOptions: {
      ssl: true
    }
  }
)