Unable to connect to Heroku postgres from outside of Heroku #278

thiago-soliveira · 2017-02-11T13:16:22Z

In order to connect to Heroku postgres from outside of Heroku we need to use SSL.

So, I'm getting the following message when trying to connect typeorm to the Heroku postgres.

"message": "no pg_hba.conf entry for host "xxx", user "xxx", database "xxx", SSL off"

I believe this is happening because the postgres driver is trying to connect without SSL. If I'm correct, how can I tell the sql driver to use SSL.

The text was updated successfully, but these errors were encountered:

pleerock · 2017-02-11T16:32:13Z

Solution is here. To do so in typeorm you need to provide option in special "extra" section of connection options:

createConnection(
    driver: {
        type: "postgres",
        host: "localhost",
        port: 5432,
        username: "root",
        password: "admin",
        database: "test",
        extra: {
             ssl: true
        }
    },);

AngelMunoz · 2017-11-22T22:49:55Z

hey sorry old issue, but how do you set this with .env file or env vars?

pleerock · 2017-11-24T14:23:08Z

You need to set

TYPEORM_DRIVER_EXTRA = {"ssl":true}

AngelMunoz · 2017-11-24T15:11:09Z

thanks it did work

pie6k · 2018-03-11T10:23:55Z

In my case:

put what you get from heroku config:get DATABASE_URL to your .env file (heroku will automatically add that to process.env
connect with:

createConnection({
    url: process.env.DATABASE_URL,
    type: 'postgres',
    entities: [YOUR ENTITIES GO HERE],
    synchronize: true,
    extra: {
      ssl: true,
    },
  });

N-CP · 2018-06-27T02:03:09Z

I m trying to connect postgresql heroku to oracle sql developer. But i m getting the below error

message": "no pg_hba.conf entry for host "xxx", user "xxx", database "xxx", SSL off"

what could be the solution?
can anybody help me in this pls??

jmaicaaan · 2018-07-20T06:39:02Z

@N-CP, what is your ormconfig looks like?

a1300 · 2019-01-17T18:58:52Z

For me it worked with:

createConnection({
    url: process.env.DATABASE_URL,
    type: 'postgres',
    entities: [YOUR ENTITIES GO HERE],
    synchronize: true,
+   ssl: true,
  });

yaroslav-ilin · 2019-03-31T19:43:50Z

I believe the original issue and the solutions (both JSON & env) has to be documented.

Would such pull request for docs website be accepted?

Kononnable · 2019-04-06T10:55:02Z

SSL options are already documented (https://github.com/typeorm/typeorm/blob/master/docs/connection-options.md). However if you find a good place to remind about that(common problem?) PR will be accepted.

AllanPamplona-zz · 2019-04-08T18:57:58Z

Also PGSSLMODE=require solves this problem.

rakeshta · 2019-09-25T02:26:54Z

For me it worked with:

createConnection({
    url: process.env.DATABASE_URL,
    type: 'postgres',
    entities: [YOUR ENTITIES GO HERE],
    synchronize: true,
+   ssl: true,
  });

worked for me too.

idudinov · 2019-11-26T06:04:32Z

hi there!
still don't able to connect to remote Heroku Postgres server from local machine, my config looks like:

const config: PostgresConnectionOptions = {
    ...baseConfig, // here're common settings
    url: process.env.DATABASE_URL, // got from DATABASE_URL config var in Heroku
    ssl: true, // double check
    extra: {
        ssl: true,
    },
};

also tried to set up PGSSLMODE=require as env var.

TypeORM in package.json:
"typeorm": "0.2.20"

getting the error:

Error: self signed certificate
    at TLSSocket.onConnectSecure (_tls_wrap.js:1321:34)
    at TLSSocket.emit (events.js:210:5)
    at TLSSocket._finishInit (_tls_wrap.js:794:8)
    at TLSWrap.ssl.onhandshakedone (_tls_wrap.js:608:12) {
  code: 'DEPTH_ZERO_SELF_SIGNED_CERT'
}

not sure what certificate should I use, or can I bypass it somehow?

thanks in advance!

idudinov · 2019-11-26T09:31:16Z

jeez it fails on Heroku as well!

UPDATE: sorry, false alarm! it's actually node-postgres issue: brianc/node-postgres#2009

JakeSidSmith · 2020-04-16T00:25:15Z

@idudinov

getting the error:

Error: self signed certificate
    at TLSSocket.onConnectSecure (_tls_wrap.js:1321:34)
    at TLSSocket.emit (events.js:210:5)
    at TLSSocket._finishInit (_tls_wrap.js:794:8)
    at TLSWrap.ssl.onhandshakedone (_tls_wrap.js:608:12) {
  code: 'DEPTH_ZERO_SELF_SIGNED_CERT'
}

not sure what certificate should I use, or can I bypass it somehow?

thanks in advance!

I've just run into this issue and it seems that node-postgres have added an option to disable rejecting unauthorized connections. I don't think this is safe to run in production though. I'm just using it to run my migrations.

I believe the ideal solution is to get a new SSL certificate, but for now I have:

  ssl: true,
  extra: {
    ssl: {
      rejectUnauthorized: false,
    },
  },

aliskhanoff · 2020-06-27T13:14:50Z

@idudinov
getting the error:
Error: self signed certificate
    at TLSSocket.onConnectSecure (_tls_wrap.js:1321:34)
    at TLSSocket.emit (events.js:210:5)
    at TLSSocket._finishInit (_tls_wrap.js:794:8)
    at TLSWrap.ssl.onhandshakedone (_tls_wrap.js:608:12) {
  code: 'DEPTH_ZERO_SELF_SIGNED_CERT'
}
not sure what certificate should I use, or can I bypass it somehow?
thanks in advance!
I've just run into this issue and it seems that node-postgres have added an option to disable rejecting unauthorized connections. I don't think this is safe to run in production though. I'm just using it to run my migrations.

I believe the ideal solution is to get a new SSL certificate, but for now I have:
  ssl: true,
  extra: {
    ssl: {
      rejectUnauthorized: false,
    },
  },

it works for me. Thanks!

italodeandra · 2020-07-03T20:27:31Z

What fixed for me while using Postgres from Heroku was only adding the following environment variable:

PGSSLMODE=no-verify

Haple · 2020-08-21T01:36:17Z

What worked for me:

{
...
    "password": ...,
    "database": ...,
    "ssl": {
        "rejectUnauthorized": false,
    },
...
}

kvarela · 2020-10-31T04:49:56Z

What worked for me:

{
...
    "password": ...,
    "database": ...,
    "ssl": {
        "rejectUnauthorized": false,
    },
...
}

Yes, this for me also, AND I had to REMOVE:

{
  extra: {
    ssl: true
  }
}

What a PITA

When running migrations on DOAP I got the following error: Error during migration run: error: no pg_hba.conf entry for host "134.209.211.49", user "db", database "db", SSL off. This should fix it. See typeorm/typeorm#278

altschuler · 2021-01-04T00:11:58Z

If, like me, you added ?ssl=true to the url, then the rejectUnauthorized wont work. You need to either remove the ssl param from the URL or set it to ssl=no-verify in which case you can remove the ssl options from the typeorm config altogether. Found the answer here brianc/node-postgres#2009 (comment)

agilatakishiyev · 2021-01-13T18:12:10Z

@idudinov
getting the error:
Error: self signed certificate
    at TLSSocket.onConnectSecure (_tls_wrap.js:1321:34)
    at TLSSocket.emit (events.js:210:5)
    at TLSSocket._finishInit (_tls_wrap.js:794:8)
    at TLSWrap.ssl.onhandshakedone (_tls_wrap.js:608:12) {
  code: 'DEPTH_ZERO_SELF_SIGNED_CERT'
}
not sure what certificate should I use, or can I bypass it somehow?
thanks in advance!
I've just run into this issue and it seems that node-postgres have added an option to disable rejecting unauthorized connections. I don't think this is safe to run in production though. I'm just using it to run my migrations.

I believe the ideal solution is to get a new SSL certificate, but for now I have:
  ssl: true,
  extra: {
    ssl: {
      rejectUnauthorized: false,
    },
  },

thanks a lot it worked for me too
i was using typeorm and nest.js and i was getting this error while connecting a database located in digitalocean

benjaminudoh10 · 2021-05-06T13:40:19Z

@idudinov
getting the error:
Error: self signed certificate
    at TLSSocket.onConnectSecure (_tls_wrap.js:1321:34)
    at TLSSocket.emit (events.js:210:5)
    at TLSSocket._finishInit (_tls_wrap.js:794:8)
    at TLSWrap.ssl.onhandshakedone (_tls_wrap.js:608:12) {
  code: 'DEPTH_ZERO_SELF_SIGNED_CERT'
}
not sure what certificate should I use, or can I bypass it somehow?
thanks in advance!
I've just run into this issue and it seems that node-postgres have added an option to disable rejecting unauthorized connections. I don't think this is safe to run in production though. I'm just using it to run my migrations.

I believe the ideal solution is to get a new SSL certificate, but for now I have:
  ssl: true,
  extra: {
    ssl: {
      rejectUnauthorized: false,
    },
  },

Thank you so much for this. I have searched everywhere. All the solutions on this thread did not work for me except this.

Thanks again.

JakeSidSmith · 2021-05-07T13:33:04Z

@idudinov
getting the error:
Error: self signed certificate
    at TLSSocket.onConnectSecure (_tls_wrap.js:1321:34)
    at TLSSocket.emit (events.js:210:5)
    at TLSSocket._finishInit (_tls_wrap.js:794:8)
    at TLSWrap.ssl.onhandshakedone (_tls_wrap.js:608:12) {
  code: 'DEPTH_ZERO_SELF_SIGNED_CERT'
}
not sure what certificate should I use, or can I bypass it somehow?
thanks in advance!
I've just run into this issue and it seems that node-postgres have added an option to disable rejecting unauthorized connections. I don't think this is safe to run in production though. I'm just using it to run my migrations.

I believe the ideal solution is to get a new SSL certificate, but for now I have:
  ssl: true,
  extra: {
    ssl: {
      rejectUnauthorized: false,
    },
  },

Just as a follow up, since Heroku enforced SSL for all postgres connections the above is the solution they recommend.

So I guess it is safe for production. 😊

…/typeorm#278

revskill10 · 2021-10-30T08:28:02Z

Guys, the issue is in the PGSSLMODE=no-verify combined with

extra: {
    ssl: {
      rejectUnauthorized: false,
    },
  },

lenybernard · 2024-03-22T08:58:52Z

I resolve my issue by setting NODE_TLS_REJECT_UNAUTHORIZED=0 to heroku env vars (see https://stackoverflow.com/a/45088585).

This was not a typeorm issue, it's because heroku use self signed certificates.

pleerock added the question label Feb 11, 2017

thiago-soliveira closed this as completed Feb 13, 2017

pleerock mentioned this issue Apr 5, 2017

Unable to connect to RDS MySQL from Heroku #384

Closed

u1-liquid mentioned this issue May 23, 2019

Support PostgreSQL Extra Connection options misskey-dev/misskey#4970

Merged

kidsil mentioned this issue Feb 15, 2021

Error connecting to PostgreSQL with SSL w3tecch/express-typescript-boilerplate#228

Open

andreGarvin added a commit to andreGarvin/openkey that referenced this issue May 9, 2021

Update to fix the issue, this github issue explains the issue typeorm…

68333f7

…/typeorm#278

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Unable to connect to Heroku postgres from outside of Heroku #278

Unable to connect to Heroku postgres from outside of Heroku #278

thiago-soliveira commented Feb 11, 2017

pleerock commented Feb 11, 2017

AngelMunoz commented Nov 22, 2017

pleerock commented Nov 24, 2017

AngelMunoz commented Nov 24, 2017

pie6k commented Mar 11, 2018

N-CP commented Jun 27, 2018

jmaicaaan commented Jul 20, 2018

a1300 commented Jan 17, 2019

yaroslav-ilin commented Mar 31, 2019

Kononnable commented Apr 6, 2019

AllanPamplona-zz commented Apr 8, 2019

rakeshta commented Sep 25, 2019

idudinov commented Nov 26, 2019 •

edited

idudinov commented Nov 26, 2019 •

edited

JakeSidSmith commented Apr 16, 2020

aliskhanoff commented Jun 27, 2020

italodeandra commented Jul 3, 2020

Haple commented Aug 21, 2020

kvarela commented Oct 31, 2020

altschuler commented Jan 4, 2021

agilatakishiyev commented Jan 13, 2021

benjaminudoh10 commented May 6, 2021

JakeSidSmith commented May 7, 2021

revskill10 commented Oct 30, 2021

lenybernard commented Mar 22, 2024

Unable to connect to Heroku postgres from outside of Heroku #278

Unable to connect to Heroku postgres from outside of Heroku #278

Comments

thiago-soliveira commented Feb 11, 2017

pleerock commented Feb 11, 2017

AngelMunoz commented Nov 22, 2017

pleerock commented Nov 24, 2017

AngelMunoz commented Nov 24, 2017

pie6k commented Mar 11, 2018

N-CP commented Jun 27, 2018

jmaicaaan commented Jul 20, 2018

a1300 commented Jan 17, 2019

yaroslav-ilin commented Mar 31, 2019

Kononnable commented Apr 6, 2019

AllanPamplona-zz commented Apr 8, 2019

rakeshta commented Sep 25, 2019

idudinov commented Nov 26, 2019 • edited

idudinov commented Nov 26, 2019 • edited

JakeSidSmith commented Apr 16, 2020

aliskhanoff commented Jun 27, 2020

italodeandra commented Jul 3, 2020

Haple commented Aug 21, 2020

kvarela commented Oct 31, 2020

altschuler commented Jan 4, 2021

agilatakishiyev commented Jan 13, 2021

benjaminudoh10 commented May 6, 2021

JakeSidSmith commented May 7, 2021

revskill10 commented Oct 30, 2021

lenybernard commented Mar 22, 2024

idudinov commented Nov 26, 2019 •

edited

idudinov commented Nov 26, 2019 •

edited