Connecting to PostgresSQL hosted online using NodeJS

Question

I'm currently trying to create a functional query from the database to post it into a created a csv file, however I am unable to connect to the PSQL host programmatically. So what I am trying to do is :-

1. Connect to DB and query results
2. Push results to an Excel File
3. Continue()
4. SFTP Results to myself on SFTP Server and place file in directory.

I am able to connect to the PostgresDB manually by the following in CLI:-

1. ssh username@xx.xx.xx.xx //Doesnt need password because my id_rsa key is stored on the Server
2. psql -U username -h LOCALHOST -p 5432 -d databasename pass- password (Manually input)

Furthermore, connecting through Visual Studio Code works as well however I need to connect to the server (Remote Connection) and then connect to the Database using a postgres Driver.

After investigating it, I figured I firstly need to connect using SSH to the server, then and only then I will be allowed to access the Database.

This is how I approached it through Code :-

Index.js

const serverConnectionParams = require('./src/config/serverConn');

function testConnectionServer() {
    try {
        serverConnectionParams.connectToServer();
    } catch (err) {
        console.error(err);
    }
}

testConnectionServer();

serverConn.js

const { Client } = require('ssh2');
const { readFileSync } = require('fs');
const databaseConnectionParams = require('./databaseConn');

function connectToServer() {
  const conn = new Client();
  conn.on('ready', () => {
    console.log('Client :: ready');
    conn.exec('uptime', (err, stream) => {
      if (err) throw err;
      
      databaseConnectionParams.auth();    *// This is the database connection param*

      stream.on('data', (data) => {
        console.log('STDOUT: ' + data);
      }).stderr.on('data', (data) => {
        console.log('STDERR: ' + data);
      });
    });
  }).connect({
    host: 'xx.xx.xx.xx',
    username: 'username',
    privateKey: readFileSync('src/key/id_rsa')
  });
}

exports.connectToServer = connectToServer;

databaseConn.js

const { readFileSync } = require('fs');
const envParam = require('./env.js');

const { Sequelize } = require('sequelize');


const sequelize = new Sequelize(envParam.database, envParam.username, envParam.password, {
    host: envParam.host,
    dialect: envParam.dialect,
    ssl: true,
    pool: {
        max: envParam.pool.max,
        min: envParam.pool.min,
        acquire: envParam.pool.aquire,
        idle: envParam.pool.idle
    }
});

async function auth() {
    try {
        console.log('trying to connect')
        sequelize.validate();
    } catch (error) {
        console.error('Unable to connect to the database:', error);
    }

}

exports.auth = auth;

env.js

const env = {
database: 'databasename',
username: 'username',
password: 'password',
host: 'ip@',
dialect: 'postgres',
pool: {
    max: 5,
    min: 0,
    aquire: 30000,
    idle: 10000
 }
};

module.exports = env; 

---

After running my node index.js I receive the following error statement :-

Client :: ready
trying to connect
STDOUT:  10:43:09 up  1:21,  1 user,  load average: 5.71, 6.03, 5.15

C:\Users\~\node_modules\sequelize\lib\dialects\postgres\connection-manager.js:184
                reject(new sequelizeErrors.ConnectionError(err));
                       ^

ConnectionError [SequelizeConnectionError]: no pg_hba.conf entry for host "xx.xx.xx.xx", user "username", database "databasename", SSL off
    at Client._connectionCallback 
{
  parent: error: no pg_hba.conf entry for host "xx.xx.xx.xx", user "username", database "databasename", SSL off
      at Parser.parseErrorMessage 
 {
    length: 154,
    severity: 'FATAL',
    code: '28000',
    detail: undefined,
    hint: undefined,
    position: undefined,
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'auth.c',
    line: '490',
    routine: 'ClientAuthentication'
  },
  original: error: no pg_hba.conf entry for host "x.x.x.x", user "username", database "password", SSL off
      at Parser.parseErrorMessage (C:\Users\~\node_modules\pg-protocol\dist\parser.js:287:98)
      at Parser.handlePacket (C:\Users\~\node_modules\pg-protocol\dist\parser.js:126:29)
      at Parser.parse (C:\Users\~\node_modules\pg-protocol\dist\parser.js:39:38)
      at Socket.<anonymous> (C:\Users\~\node_modules\pg-protocol\dist\index.js:11:42)
      at Socket.emit (node:events:394:28)
      at addChunk (node:internal/streams/readable:315:12)
      at readableAddChunk (node:internal/streams/readable:289:9)
      at Socket.Readable.push (node:internal/streams/readable:228:10)
      at TCP.onStreamRead (node:internal/stream_base_commons:199:23) {
    length: 154,
    severity: 'FATAL',
    code: '28000',
    detail: undefined,
    hint: undefined,
    position: undefined,
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'auth.c',
    line: '490',
    routine: 'ClientAuthentication'
  }
}

Investigating the Error Code: 28000

Found this link explaining the issue as an authentication attempt failure

https://help.heroku.com/DR0TTWWD/seeing-fatal-no-pg_hba-conf-entry-errors-in-postgres

Also found several solutions online regarding pg_hba.conf needs to use md5 and then restart postgress (Not tried, as i cannot restart the postgress service)

error: Ident authentication failed for user

Found another solution explaining it was an SSL issue (Tried it and it didnt work)

Node.js, PostgreSQL error: no pg_hba.conf entry for host

After using SSL it would change the error code to the following :-

 SequelizeConnectionError: self signed certificate

Found a solution to that here:-

SequelizeConnectionError: self signed certificate

After I put that it would give me a different error that rejectUnauthorized is depreciated and very old version (Cant seem to reproduce the error code as of the moment)

So my hands are tied at the moment, any help will be great!

I've also tried using different Javascript modules instead of sequelize however they all have the same authentication issue.

I also tried to pass my id_rsa key, however it wouldnt solve my issue at all.

My assumptions are even though I am passing the connToDatabase function inside the SSH connection, it is still searching for the ip@ in the incorrect location. (Ip@ of DB on the server is 192.168.31.4)

But when using that IP@ it will say ERR Connection Timed out

Another Assumption I have is that the Database has many restrictions from connecting and require further more params.

UPDATE:

I tried editting the pg_hba.conf file through remote access on VSC however it would give me error cannot read file.

Any help would be great!

Answer 1

I had completely forgotten that I posted this question.

The solution to it was pretty straight forward, after investigating it for a while I realized I was making a mistake in connection params.

For others who require help with such issues I will post the solution in a simple manner.

Basically, I needed first to SSH to the server and add a tunnel to my connection to connect to the database. Then and only then will my sequelize params for the database pass through because I have completely connected to the server and internal postgresql database.

So TLDR SSH -> Tunnel -> Sequelize

ssh(10.x.x.1, etc) -> addTunnel(localhost, etc) -> sequelize(databaseName, etc)

EDIT: Added Code for reference

const Ssh2Promise = require('ssh2-promise');
const { readFileSync } = require('fs');

async function connectToServer(callback) {
  const ssh = new Ssh2Promise({
    host: '10.x.x.1',
    username: 'usernameofssh',
    privateKey: readFileSync('src/key/id_rsa'), //This is only for RSA Fingerprint, if you do not have said fingerprint you can use "passphrase" with your password instead
  });

  const tunnel = await ssh.addTunnel({
    remoteAddr: '192.168.0.1', //This is the database connection ip@, once connected to it you can fetch from LOCALHOST. Incase its AWS it would be test.test-test.amazonaws.com
    remotePort: 1234, //Port for connection
    localPort: 1234,
  });

  //Don't forget to throw it in a try catch for feedback
  await auth(callback); //Send it wherever you need it to go
}