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.

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. 😄

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