Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SSL Query Parameter for Postgres #852

Closed
gregbty opened this issue May 30, 2015 · 20 comments
Closed

SSL Query Parameter for Postgres #852

gregbty opened this issue May 30, 2015 · 20 comments

Comments

@gregbty
Copy link

@gregbty gregbty commented May 30, 2015

It seems like #516 has surfaced again. When I use a config object it seems to work fine with the ssl option, however this doesn't work with a connection string:

Works:

connection: {
  host: "host",
  port: "port",
  user: "user",
  password: "password",
  database: "database",
  ssl: true
 }

Does not work:

connection: "postgres://user:password@host:port/database?ssl=true"
@tgriesser tgriesser added the bug label Jun 1, 2015
@tgriesser tgriesser closed this in 90105e0 Jun 1, 2015
tgriesser added a commit that referenced this issue Jun 1, 2015
@kimmobrunfeldt
Copy link

@kimmobrunfeldt kimmobrunfeldt commented Jun 1, 2015

I'm experiencing this issue too:

Using following config:
{ client: 'pg',
  connection: 'postgres://<gibberish>:<gibberish>@<hostgibberish>.eu-west-1.compute.amazonaws.com:5432/<gibberish>?charset=utf-8&ssl=true',
  pool: { min: 2, max: 10 },
  debug: false,
  migrations:
   { directory: '/tmp/build_7902ce01745a86676a8158ae8b05c065/migrations',
     tableName: 'migrations' } }
Using environment: production
Knex:Error Pool2 - error: no pg_hba.conf entry for host "50.147.231.214", user "<user>", database "<database>", SSL off
Error: Pool was destroyed
    at Pool._destroyPool (/tmp/build_7902ce01745a86676a8158ae8b05c065/node_modules/knex/node_modules/pool2/lib/pool.js:485:16)
    at Pool.<anonymous> (/tmp/build_7902ce01745a86676a8158ae8b05c065/node_modules/knex/node_modules/pool2/lib/pool.js:408:18)
    at Pool.<anonymous> (/tmp/build_7902ce01745a86676a8158ae8b05c065/node_modules/knex/node_modules/pool2/lib/pool.js:442:17)
    at tryCatcher (/tmp/build_7902ce01745a86676a8158ae8b05c065/node_modules/bluebird/js/main/util.js:24:31)
    at Promise.errorAdapter (/tmp/build_7902ce01745a86676a8158ae8b05c065/node_modules/bluebird/js/main/nodeify.js:35:34)
    at Promise._settlePromiseAt (/tmp/build_7902ce01745a86676a8158ae8b05c065/node_modules/bluebird/js/main/promise.js:528:21)
    at Promise._settlePromises (/tmp/build_7902ce01745a86676a8158ae8b05c065/node_modules/bluebird/js/main/promise.js:646:14)
    at Async._drainQueue (/tmp/build_7902ce01745a86676a8158ae8b05c065/node_modules/bluebird/js/main/async.js:177:16)
    at Async._drainQueues (/tmp/build_7902ce01745a86676a8158ae8b05c065/node_modules/bluebird/js/main/async.js:187:10)
    at Immediate.Async.drainQueues [as _onImmediate] (/tmp/build_7902ce01745a86676a8158ae8b05c065/node_modules/bluebird/js/main/async.js:15:14)
    at processImmediate [as _immediateCallback] (timers.js:358:17)
@nickpoorman
Copy link

@nickpoorman nickpoorman commented Jun 10, 2015

I'm having this issue as well on v0.8.6.

@idan
Copy link

@idan idan commented Jul 7, 2015

I'm experiencing this on v0.8.6 as well.

@nmccready
Copy link

@nmccready nmccready commented Aug 6, 2015

I see this is fixed here on master. https://github.com/tgriesser/knex/blob/master/test/tape/parse-connection.js#L47-L56

When is 0.8.7 being cut?

@tjwebb
Copy link

@tjwebb tjwebb commented Aug 31, 2015

@tgriesser please release a patch version for this. knex is currently unusable with Postgres over SSL (this would include all Heroku databases, for example)

This also happens to be a blocking issue for the Waterline PostgreSQL adapter, which we migrated over to use knex.js: https://github.com/waterlinejs/postgresql-adapter

@tgriesser
Copy link
Member

@tgriesser tgriesser commented Aug 31, 2015

Sorry abt that. Been MIA for quite a bit, I'll cut one tomorrow and start jumping into a lot of the backlogged tickets.

@tgriesser tgriesser reopened this Aug 31, 2015
@tgriesser
Copy link
Member

@tgriesser tgriesser commented Aug 31, 2015

This also happens to be a blocking issue for the Waterline PostgreSQL adapter, which we migrated over to use knex.js: https://github.com/waterlinejs/postgresql-adapter

Oh wow that's awesome!

@tjwebb
Copy link

@tjwebb tjwebb commented Aug 31, 2015

Been MIA for quite a bit, I'll cut one tomorrow and start jumping into a lot of the backlogged tickets.

@tgriesser sweet, thanks man.

@kimmobrunfeldt
Copy link

@kimmobrunfeldt kimmobrunfeldt commented Aug 31, 2015

Workaround which worked for me: set environment variable PGSSLMODE=require. It forces postgres driver to use SSL and it resolved the issue at least in my case. Still waiting for the official patch though.

@nickpoorman
Copy link

@nickpoorman nickpoorman commented Oct 22, 2015

Is anyone ever going to fix this???????

@rhys-vdw
Copy link
Member

@rhys-vdw rhys-vdw commented Oct 26, 2015

Is anyone ever going to fix this???????

@nickpoorman You're welcome to provide a fix.

@codeclown
Copy link
Contributor

@codeclown codeclown commented Jan 10, 2016

I assume this is because Knex parses connection strings instead of passing them to the underlying library untouched, no?

src/util-parse-connection.js
src/index.js

Just bumped into this issue when using a valid connection string with charset option for mysql. I tested it and confirmed that the query parameters never arrived to node-mysql.

@zacharynevin
Copy link

@zacharynevin zacharynevin commented Feb 13, 2016

I'm having this issue as well

@bennmapes
Copy link

@bennmapes bennmapes commented Jun 29, 2016

I had the same issue, it appears the knex doesn't set the ssl default in pg.

I was able to fix this by adding

var pg = require('pg');
pg.defaults.ssl = true;

before using knex. Now I don't get that error when accessing/seeding the database.

@gilbert
Copy link

@gilbert gilbert commented Aug 12, 2016

The workaround by @bennmapes works great. The best place to put it is in your knexfile.js, since knex requires that file before migrating, etc.

vitorbaptista added a commit to opentrials/api that referenced this issue Oct 25, 2016
This is needed by Heroku
(https://devcenter.heroku.com/articles/heroku-postgresql#heroku-postgres-ssl),
but KnexJS doesn't have support for it. We needed to do a small hack to make it
work while knex/knex#852 isn't fixed.
@robinvdvleuten
Copy link

@robinvdvleuten robinvdvleuten commented Jun 20, 2017

Is this still an issue? It seems to be working with a parameter string

@elhigu
Copy link
Member

@elhigu elhigu commented Jun 26, 2017

@robinvdvleuten does it work also with connection object by passing { ..., ssl: true } ?

@knex knex deleted a comment from whalelephant May 14, 2019
@elhigu
Copy link
Member

@elhigu elhigu commented May 14, 2019

removed some a bit off-topic discussion

@trubachev
Copy link

@trubachev trubachev commented Aug 7, 2019

my workaround is to parse connections string to object and add/override sss option. NPM package to parse pg-connection-string

const dbConfig = {
  client: "postgresql",
  connection: {
    ssl: isProduction || isStaging,
    ...parse(CONFIG.DATABASE_URL),
  },
}
@Duchynko
Copy link

@Duchynko Duchynko commented Jun 22, 2020

@robinvdvleuten does it work also with connection object by passing { ..., ssl: true } ?

This still doesn't work

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.