Querystring in connection string

[elmigranto]

The problem

Postgres supports a bunch of stuff in connection strings:
https://www.postgresql.org/docs/9.5/static/libpq-connect.html#LIBPQ-CONNSTRING

However, ConnectionParameters, pg-connection-string and pg.Pool's config seem to only be aware about certain limited subset (like ssl, fallback_app_name and others). Doesn't seem practical to validate those in node, since server will already reject invalid config (I think).

Usecase

Instead of running a bunch of set X to Y after connecting to postgres on every client, I'd like to specify some session settings on connection string. In particular, I'd like to reproduce the following psql invocation from the node:

psql postgresql://?options=-c%20timezone%3DEurope/Moscow

Solution

Probably parse the whole query string and pass everything we found in it to the server. Not sure how connection flow works, and how to use those parsed params.

Looks like there are a bunch of places need to be change in order to achieve this. Not sure if these are all of them:

• pg-connection-string's parse function;
• pg.Client;
• pg.ConnectionParameters;
• pg.Pool's constructor
• anything else?

I would be happy to PR this, but I'm not sure where to start. Any tips?

As a sidenote, would be neat to support this stuff: psql options="-c\ timezone=Europe/Moscow".

[brianc]

I know what you're talking about, and I agree it would be good to be able to pass more parameters to postgres during startup rather than hard-coding the few we're sending.

One thing is I don't think connecting w/ a url is actually the best approach. Especially because pg.Pool has a constructor which takes config options for both the pool and for the client. The ideal approach is to have users parse their connection strings in their own applications & then always initialize both clients & the pool with a config object with all the properties.

Ideally there would be a separate npm module that would accept either a connection string and turn it into a config object with the connection string already parsed. Then if someone wants to use a connection string they could do so like:

var conString = process.env.PGCONNSTRING
var pgConString = require('pg-connection-string')
var options = pgConString(conString)
var pool = new pg.Pool(options);

That way it pulls the connection string parsing out of this library, where it can be better tested, cover more use cases, and evolve faster.

To maintain backwards compatibility we could consume this module from node-postgres itself and do something like if typeof config == 'string' config = pgConString(config) Basically centralizing that parsing out into a single place. Unfortunately node-postgres has grown with some pull requests that heap more and more functionality into connection string parsing, so its a bit tangled up right now. Does that make sense?

[elmigranto]

I'm pretty sure node-postrges uses pg-connection-string already. The problems are:

• parse populates result with only a subset of query string options;
• pg.Pool and pg.Client only use a subset of properties of passed options object.

[brianc]

yeah you're right I just checked on that - haha. Yeah, we need to make it easier to pass whatever options down to the client/pool/whatever and have them sent to the backend. Right now it's a "whitelist" kind of approach which is unfortunate.

[elmigranto]

I'd like to look into this, PR for pg-connection-string seems pretty straight-forward, but I am not sure what stuff to change inside pg. The other problem is that parsing of query string is something psql (or libpq?) does on its own, and I don't know how those parameters make its way to Postgres server.

I'll try to dig into this on my own some time, but would be nice if you (or someone else) could maybe explain connection flow here, if that's not too much hassle.

whitelist

That's the word I was looking for! :)

[brianc]

Sure...so how connection works is

pg authenticates with the database

after authentication pg sends a bunch of settings to establish the session and configure the application name and other stuff...

It does so by first getting the bundle of settings to send:
https://github.com/brianc/node-postgres/blob/master/lib/client.js#L212
And then sending these settings via the connection as key-value pairs
https://github.com/brianc/node-postgres/blob/master/lib/client.js#L65
https://github.com/brianc/node-postgres/blob/master/lib/connection.js#L143

So modifying the client.getStartupConf() to be smarter is a good place to smart - and then you gotta walk back the various ways configs can be passed to the client/pool and make sure they all accept more parameters....once the client is aware of the object containing the parameters to send to the backend, actually sending them is pretty straight forward.

---

As for the native connection we take the config object and turn it into a "keyword/value connection string" here:

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

That string is then passed unmodified to libpq where its converted into properties & sent to the postgres backend by libpq. The reason for doing a dns lookup in connection-parameters is because libpq is mostly non blocking but AFAIK it still does a blocking dns lookup if you connect with a hostname, which can cause big problems in node if you block the event loop in C, so we use node to lookup some DNS info before calling libpq to prevent this.

[vitaly-t]

@elmigranto @brianc

This is definitely the right thing to do, aligning connection parameters with what PostgreSQL supports. The driver is seriously lacking in that area at the moment. There are many parameters that PostgreSQL supports, and they all should be supported by node-postgres.