How to properly setup Heroku Node.js Connection to Heroku-Postgres when sql_error

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

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.

CodePudding user response：

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);

CodePudding user response：

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?