I am fairly new to Postgres and I am trying to connect to my PostgreSQL database hosted on Heroku through Auth0's Database Connections. I am getting an error when I try to invoke the 'Get User' script within Auth0's database actions.

The error: no pg_hba.conf entry for host "xx.xxx.xx.x", user "xxx", database "xxx", no encryption

The script looks like this:

function loginByEmail(email, callback) {

  const postgres = require('pg');

  const conString = configuration.DATABASE_URL;
  postgres.connect(conString, function (err, client, done) {
    if (err) return callback(err);

    const query = 'SELECT id, nickname, email FROM organizations WHERE email = $1';
    client.query(query, [email], function (err, result) {
      // NOTE: always call `done()` here to close
      // the connection to the database
      done();

      if (err || result.rows.length === 0) return callback(err);

      const user = result.rows[0];

      return callback(null, {
        user_id: user.id,
        nickname: user.nickname,
        email: user.email
      });
    });
  });
}

configuration.DATABASE_URL: 'postgres://xxx:xxx@xxx?sslmode=require'

I appeneded sslmode=require to the end of my connection string to ensure I have a ssl connection to my database.

I have also tried changing sslmode=require to ssl=true which results in a different error: self signed certificate

I am unsure where to go from here so any help would be appreciated.

CodePudding user response：

You should first establish the client and specify the rejectUnauthorized flag, like this:

const client = new postgres.Client({
    connectionString: conString,
    ssl: { sslmode: 'require', rejectUnauthorized: false }
});

Then, instead of using your postgres to connect, use the client:

client.connect();
client.query(...);

This should solve the problem, and your connection will be encrypted. You won't, however, be protected against Man-In-The-Middle (MITM) attacks: