Usage of ?sslmode=require and ssl property in pg.Pool

[albertpurnama]

I have the following code snippet

    const pool = new pg.Pool({
      connectionString: env.DATABASE_URL,
      ssl: env.DATABASE_CA_CERT
        ? {
            ca: env.DATABASE_CA_CERT,
            rejectUnauthorized: true,
          }
        : undefined,
    });

seems like a pretty standard setup. I have downloaded CA_CERT from DigitalOcean managed database product.

I copied the connection string which includes the ?sslmode=require query parameter at the end.

I experience the error:

Error: self-signed certificate in certificate chain

It seems like the query param sslmode=require completely overrides the ssl property setting. I confirmed this by removing the sslmode=require and the pool got connected normally.

Can anyone explain a little bit deeper on why this is the case? I might miss some stuff, but if sslmode=require is set on the connection string, how should one set the CA cert for the connection pool?

[hjr3]

Use the sslcert query parameter.

node-postgres/packages/pg-connection-string/README.md

Line 73 in 373093d

* `sslcert=<filename>` - reads data from the given file and includes the result as `ssl.cert`

Example:

node-postgres/packages/pg-connection-string/test/parse.js

Lines 220 to 226 in 373093d

	it('configuration parameter sslcert=/path/to/cert', function () {
	var connectionString = 'pg:///?sslcert=' + __dirname + '/example.cert'
	var subject = parse(connectionString)
	subject.ssl.should.eql({
	cert: 'example cert\n',
	})
	})

[charmander]

You can also go directly to pg-connection-string (which is how pg parses connectionString) and merge the resulting configuration exactly the way you want:

import parseConnectionString from 'pg-connection-string';

const pgConfig = parseConnectionString(env.DATABASE_URL);

if (env.DATABASE_CA_CERT) {
  pgConfig.ssl.ca = env.DATABASE_CA_CERT;
}

const pool = new pg.Pool(pgConfig);

Whether you choose to use sslcert in the query string or a separate environment variable, I would recommend using sslmode=verify-full instead of starting with sslmode=require and setting rejectUnauthorized: true afterwards, for consistency in behavior with any other place that query string might be used, and for clarity. (The current version of pg-connection-string actually treats all of prefer, require, verify-ca, and verify-full as verify-full, but that will probably change in a future major version.)