0

I'm trying to figure out how to set up my backend api (next.js/api) to the database (postgresql) that both are hosted by heroku.

Mediated by pg.pool, i set up with the following code.

const pool = new Pool(
    {
        connectionString: process.env.DATABASE_URL,
        // ssl: {
        //     rejectUnauthorized: false,
        //   }
    })

but returned by heroku with the following error:

sql_error_code = 28000 FATAL: no pg_hba.conf entry for host "122.180.247.11", user "u3idolso5k2v83", database "dc85788d13v9ej", SSL off

The error description is from: https://help.heroku.com/DR0TTWWD/seeing-fatal-no-pg_hba-conf-entry-errors-in-postgres

EDIT: meant to post this link, Is it ok to be setting rejectUnauthorized to false in production PostgreSQL connections?

The authentication failed because the connection didn't use SSL encryption: (SSL off). All Heroku Postgres production databases require using SSL connections to ensure that communications between applications and the database remain secure. If your client is not using SSL to connect to your database, you would see these errors even if you're using the right credentials to connect to it.

I find this strange, since heroku do provide ssl already to my server hoested by them by default, so its unexpected for such an error to occur at all?

The side step solution I've come across online is uncomment the ssl property in the connection...which works, but i feel uneasy with this one.

const pool = new Pool(
    {
        connectionString: process.env.DATABASE_URL,
         ssl: {
             rejectUnauthorized: false,
           }
    })

As mentioned briefly it is not safe from here: https://security.stackexchange.com/questions/229282/is-it-safe-to-set-rejectunauthorized-to-false-when-using-herokus-postgres-datab

I don't understand why this error occur at all, and how can it be fixed with proper security.

cozycoder
  • 309
  • 3
  • 9

2 Answers2

1

It's pretty standard for SSL certificates for Postgres servers to not be valid. Even official postgres clients don't validate the certificates. The library you are using defaults to validating certificates, but is very much in the minority.

When setting this up for https://www.atdatabases.org/docs/pg-options I made it not validate certificates by default to match the standard behaviour for Postgres.

This lets you create a connection pool for heroku using simply:

import createConnectionPool from '@databases/pg';

createConnectionPool(process.env.DATABASE_URL);
ForbesLindesay
  • 10,482
  • 3
  • 47
  • 74
1

As described in your linked-to answer, you can upgrade to one of Heroku's paid products which does support this. Or you can stop using Heroku. Or you can put up with the incredibly low risk that someone will MITM you.

I don't understand why this error occur at all,

What about it do you not understand? The explanation you linked to seems pretty clear. If you cannot formulate your uncertainty any more clearly than you have so far, how can anyone help you understand?

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • I don't mean to sound like I didn't put effort at all in processing this. As I'm still trying to gauge the trade offs. I just made a foolish assumption that things are free. I had a look at the pricing for those options, they are 300 dollar a month for the lowest tier. To me, it seems like a lot for anyone to get that security set up on Heroku. But, I think i just need better clarity why it is a low risk as the thread + you suggested. Is it the difficulty to do MITM here, or is it the probability of someone acting on the server? – cozycoder Nov 25 '21 at 02:37