Node-postgres: Support RDS connection json in pg-connection-string `parse`

[stephenh]

Per @hjr3 moving this over from pg-connection-string's repo.

This is perhaps a little "too cute", but for applications/libraries that use Amazon's RDS for their postgres db, RDS/CloudFormation can auto-manage the database user/password as secrets in the AWS Secrets Manager.

See here, which is not terribly succinct, but basically CloudFormation auto-secrets a secret, and "attaches" it to the PG instance, so that applications can (assuming they're granted access) look up the secret to get the connection info.

Helpfully, the secret has both user/pass as well as connection information, i.e. it's basically all the connection info an application needs, encoded in JSON that looks like:

{
  "host":"whateverrds.amazon.com",
  "port":5432,
  "username":"...",
  "password":"...",
  "dbname":"..."
}

(Without newlines, just formatted for the issue.)

This value is typically passed to the application as an environment (i.e. read securely by the instance/container when it boots up), similar to how PG connection info is normally passed, but now as this JSON key/value pairs instead of the OG postgres:// connection string.

For my library, I'd like to easily support "runs with existing postgres://... connections" as well as "just pass the RDS connection JSON thingy", and have both just work.

Which I can handle manually, but it seems neat if parse(...) itself would recognize the ^ format (i.e. if the first char of the string is a { then assume "this is RDS-style JSON") and return the appropriate ConnectionInfo.

Granted, this adds some RDS-specific behavior to node-postgres/pg-connection-string, and fwiw I don't know why they didn't just use a postgres://... connection string instead of this JSON value :shrug:, but given how widespread RDS is, it seems potentially worth the ROI to implement this "pretend/great JSON is a connection string" once here, and have all node-postgres / RDS / RDS-managed-secret users be able to use it.

I can work on a PR if this sounds okay.

[vitaly-t]

You can just use the generic [connection-string] to pass in either proper connection string, or a URL parameter set to your JSON object. So you will check for that parameter first, and if set - parse it and use its values; otherwise use it as regular connection string.

P.S. It beats me why this library went to integrate the custom pg-connection-string module after everything, instead of just going for the generic [connection-string], which supports the URL Connection standard properly, plus supports multiple hosts, which is well overdue here.

[stephenh]

So you will check for that parameter first, and if set - parse it

The ask of this issue is that node-postgres would know about the RDS JSON config/convention as a first-class way to specify settings, and do that parsing for me.

[vitaly-t]

@stephenh And why would this library start accommodating features for one specific provider?

[stephenh]

@vitaly-t I already acknowledged in the initial issue that, yes, this is a specific provider, and provided my personal rationale that it is admittedly a very large provider, so seems like this could be useful for many? more users than just myself.

I'm not asserting "everyone does/should use RDS", just that I think it's valid to at least consider this sort of convenience for a potentially-large-but-yes-not-majority subset of node-postgres users.

These sort of trade offs are generally exactly what config options/conditional behavior/etc are for: to balance "flexibility for different use cases" vs. "too much complexity in the library".

So, I pitched my case/rationale, and will defer to the maintainers to decide.

[vitaly-t]

What you need to implement on your side in order to support it is so minor, just couple lines of code, that it does not justify adding such specifics into this library:

import {ConnectionString} from 'connection-string';

const cs = new ConnectionString('your connection string here');

// Let's say you can optionally pass in encoded JSON with the config,
// as "configJson" URL parameter, which then takes priority:
const configJson = cs.params && cs.params.configJson;

if (configJson) {
    const config = JSON.parse(configJson);

    // use the parsed config object to connect to the database
} else {
    // use the connection string directly as is,
    // or convert it into config object
}

[charmander]

I think this should go in another package, not pg.

[vitaly-t]

@charmander I added a practical hands-on solution for the question, and you marked it as spam?

WTF?

[charmander]

@vitaly-t connection-string is completely irrelevant to this problem, so please stop bringing it up in every issue, especially accompanied by misinformation.

[vitaly-t]

@charmander The issue is about parsing the connection string. How is it irrelevant to the connection-string?

stop bringing it up in every issue

I'm only bringing it up where it is relevant. What is your issue with that?

especially accompanied by misinformation

This is just rude.

[sehrope]

+1 to keeping something like this separate from the core pg module. It's not specific to pg and it's not even standardized by RDS for the specific fields included in the JSON (e.g. you can "username" to "user", "password" could be "auth"). You'd also be making other assumptions about the PGSSLMODE, CA, and a bunch of other fields as well.

Create a separate module, something like rds-pg-config, that takes a single JSON field and returns a pg compatible config object. That'd be a decent place to document the specific choices of field names for username, password, host, etc, and also the specific defaults that would apply to RDS. Could even pick a specific env var to look at by default.

Then you'd use it via:

const pg = require('pg');
const rdsPgConfig = require('rds-pg-config');

const pool = new pg.Pool({
    ...rdsPgConfig(process.env.SOME_ENV_VAR_GOES_HERE),
    // ...other pg options you want to override
    // ...pool options
});

[charmander]

The issue is about parsing the connection string. How is it irrelevant to the connection-string?

@vitaly-t The issue is about parsing connection parameters in a particular JSON schema and converting it to pg-compatible options. Encoding the JSON that contains all of the connection information to tack it onto postgres:// so your package can have a reason to be brought up before it gets converted right back to the original JSON is creative, but not helpful.

let config;

if (connectionString.startsWith('{')) {
    config = JSON.parse(connectionString);
    config.user = config.username;  // if the JSON has to match the example
    config.database = config.dbname;
} else {
    config = {connectionString};
}

Connection string parsing is the fallback path and unrelated.

[brianc]

I think this should go in another package, not pg.

+1 to keeping something like this separate from the core pg module.

yup! I agree w/ what these other folks said....this should live in it's own module. @stephenh perhaps it's something you'd like to make & contribute back the community if it's helpful for you!

[stephenh]

Cool, np thanks @brianc @charmander @sehrope

[brianc]

as an aside just in general I try to limit the amount of new features I add directly to pg, and somewhat even to the other modules in this repo just because it's quite a bit to maintain. I want to be diligent and always driving pg forward, so in order to do that I have to limit what stuff I take on, otherwise (as has happened to me in the past) I get overwhelmed and I go into a hidey hole for a while and burn out and things languish. So...it's a confluence of reasons, but the idea itself of making connecting to rds easier is definitely good & worth pursuing if you're interested in it!