Connect to Postgres/Redshift over Socks proxy.

client.js

var pg = require('pg'),
    url = require('url'),
    SocksConnection = require('socksjs');

var db = url.parse(process.env.REDSHIFT_CONN_STRING),
    dbAuth = db.auth,
    dbUsername = dbAuth.split(':')[0],
    dbPassword = dbAuth.split(':')[1],
    dbName = db.pathname.replace('/', '');

var proxy = url.parse(process.env.QUOTAGUARDSTATIC_URL),
    auth = proxy.auth,
    username = auth.split(':')[0],
    pass = auth.split(':')[1];

var sock_options = {
    host: proxy.hostname,
    port: 1080,
    user: username,
    pass: pass
};

var remote_options = {
    host: db.hostname,
    port: db.port
};

var sockConn = new SocksConnection(remote_options, sock_options);

var config = {
    user: dbUsername,
    database: dbName,
    password: dbPassword,
    stream: sockConn
};

var client = new pg.Client(config);

// connect to our database
client.connect(function (err) {
  if (err) throw err;

  // execute a query on our database
  client.query('SELECT $1::text as name', ['john doe'], function (err, result) {
    if (err) throw err;

    // just print the result to the console
    console.log(result.rows[0]); // outputs: { name: 'apiary' }

    // disconnect the client
    client.end(function (err) {
      if (err) throw err;
    });
  });
});

I'm curious, where that stream option comes from?

Hi,

can you pls explain how to form process.env.REDSHIFT_CONN_STRING for node js app

As you see using https://nodejs.org/docs/latest/api/url.html for example postgres://user:password@hostname.com nothing special

I'm curious, where that stream option comes from?

https://github.com/brianc/node-postgres/blob/master/lib/connection.js#L22

Hi,

Thanks for reply

I am connecting to aws redshift db and I use Env variable like PGSSLMODE=require and that solved the connectivity issue.

By the way I am using "pg" NPM package for redshift in my Node app and my co-workers think that the client machine must have JAVA runtime or some JDBC/ODBC driver on the computer where Node app is running. Pleases let me know if that is right.

Not sure about that. I don't use java for long time. node-pg using C++ bindings.

not working for me 😕

Debugger attached.
Waiting for the debugger to disconnect...
<proj name>\node_modules\pg\lib\connection.js:38
    this.stream.setNoDelay(true)
                ^

TypeError: this.stream.setNoDelay is not a function
    at Connection.connect (<proj name>\node_modules\pg\lib\connection.js:38:17)
    at Client._connect (<proj name>\node_modules\pg\lib\client.js:104:11)
    at Client.connect (<proj name>\node_modules\pg\lib\client.js:152:12)
    at Object.<anonymous> (<proj name>\test2.js:40:8)
    at Module._compile (internal/modules/cjs/loader.js:1063:30)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:1092:10)
    at Module.load (internal/modules/cjs/loader.js:928:32)
    at Function.Module._load (internal/modules/cjs/loader.js:769:14)
    at Function.executeUserEntryPoint [as runMain] (internal/modules/run_main.js:72:12)
    at internal/main/run_main_module.js:17:47
Process exited with code 1

My packages

  "dependencies": {
    "pg": "^8.4.1",
    "socksjs": "^0.5.0"
  },

Hi, I am facing the same issue. Does someone has an answer for this? thank you.

TypeError: this.stream.setNoDelay is not a function
at Connection.connect (D:\WORK\Brejnholt\2020\ReturnSystem\node_modules\pg\lib\connection.js:38:17)
at Client._connect (D:\WORK\Brejnholt\2020\ReturnSystem\node_modules\pg\lib\client.js:113:11)
at Client.connect (D:\WORK\Brejnholt\2020\ReturnSystem\node_modules\pg\lib\client.js:161:12)
at BoundPool.newClient (D:\WORK\Brejnholt\2020\ReturnSystem\node_modules\pg-pool\index.js:227:12)
at BoundPool.connect (D:\WORK\Brejnholt\2020\ReturnSystem\node_modules\pg-pool\index.js:202:10)
at BoundPool.query (D:\WORK\Brejnholt\2020\ReturnSystem\node_modules\pg-pool\index.js:350:10)
at Object.runQuery (D:\WORK\Brejnholt\2020\ReturnSystem\database\pgdb.js:29:29)
at Object. (D:\WORK\Brejnholt\2020\ReturnSystem\server.js:29:6)
at Module._compile (internal/modules/cjs/loader.js:1063:30)

Example with with pg ~6.x not sure if this should be useful for anyone in these days.

@abtris Are you able to get a working example with the latest pg client up and running, up?

No, I don't have access to AWS Redshift anymore.

I was getting an error, TypeError: this.stream.setNoDelay is not a function using pg at version ~8.x, and was able to resolve using npm install pg@7.17.0. That's the last version of pg before a major update to streaming: https://github.com/brianc/node-postgres/blob/master/CHANGELOG.md#pg7170

How do we achieve this with the latest version of pg npm package ?

I have posted a working solution for pg@8 here: brianc/node-postgres#1035 (comment)