Connecting to Heroku Postgres from Auth0 results in: err no pg_hba.conf entry for host, no encryption

Question

I'm 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:

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) {
      done(); // Close the connection to the database
      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
      });
    });
  });

}

Connection String:

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

I appended 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.

Answer 1

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

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 your problem, and the connection will be encrypted. You won't, however, be protected against Man-In-The-Middle (MITM) attacks, as specified in documentation.