How do we set the `sslmode` when using knex with pg? What is the default `sslmode` (if present) when we set `ssl: true`?

[virajshelke]

Environment

Knex version: 1.0.1 Database + version: PG (any version above 8) OS: Linux

Feature discussion / request

1. Explain what is your use case Establishing a SSL based PG connection where I want to make sure that sslmode is set to verify-full. When we set the ssl: true for PG dialect what kind of sslmode does it use? So PG has 6 sslmode (Refer - https://www.postgresql.org/docs/8.4/libpq-connect.html#LIBPQ-CONNECT-SSLMODE). I couldn't see a way to use sslmode option with knex, is this a missing feature (I couldn't find it in the knex codebase)? Also what sslmode does it internally set (if at all there is some default that is set by the pg npm package)?

2. Explain what kind of behaviour you are getting and how you think it should do N/A

3. Explain what kind of feature would support this Establishing a secured connection with SSL

4. Give some API proposal, how the feature should work

   knex({
         client: 'pg',
         connection: {
           host: config.db.host,
           port: config.db.port,
           user: config.db.username,
           password: config.db.password,
           database: getDbName(tenant, config.db.name),
           ssl: true,
           sslmode: 'verify-full' // this could be a enum so that user cannot pass anything as string, also this will be a optional param
         }
   })

Missing / erroneus documentation

Can we have these details in the document?

[virajshelke]

Refer - https://node-postgres.com/features/ssl

Are we exposing the same options? Looks like we need to write the connection string manually to put sslmode instead of passing it as a key in the connection object!

Can anyone confirm on the same?

[virajshelke]

https://github.com/brianc/node-postgres/issues/1949

[virajshelke]

Any update anyone? :)

[jcfinnerup]

Id like something like this too :)

[rmourey26]

I'd also like this feature added as well :-)

Keep in mind Replit is now recommending knexjs as the pg client of choice for their (replit) serverless postgres DB offering (NeonDB).

[igl]

We are just using connection-strings with the sslmode param attached, maybe you can add it the same way to your host option?

We also didn't get knex to work with @neondatabase/serverless as a replacement for pg yet. Easier knex support for that driver would be also neat. Neon is also the database underneath vercels postgres offering so lots of folks are going to need that.

How we use knex + neon right now:

knex({
        client: 'pg',
        connection: 'postgres://foo:bar@bla-bla-bla-pooler.eu-central-1.aws.neon.tech/neon?sslmode=require',
        pool: {
            min: 0
        }
})

[kibertoad]

I'll try picking this up

[answerquest]

If we use the connection string as shown:
connection: 'postgres://foo:bar@bla-bla-bla-pooler.eu-central-1.aws.neon.tech/neon?sslmode=require',
Then how do we provide the 3 ssl files (server-ca, client-cert, client-key) ?

[ferm10n]

Based off this unit test, the sslmode can be set without using a connectionString like this:

knex({
  client: 'pg',
  connection: {
    host: 'localhost',
    user: 'postgres',
    // etc
    ssl: {
      sslmode: 'prefer', // or 'require', whatever
      sslca: '/path/ca.pem',
      sslkey: '/path/cert.key',
      sslcert: '/path/cert.crt',
      // etc
    },
  },
});

This structure is missing from the knex types however: https://github.com/knex/knex/blob/5ffe289c33d40a94367559283b5948be7c6169c6/types/index.d.ts#L3047

But it's also missing from DefinitelyTyped's pg types too, so go figure https://github.com/DefinitelyTyped/DefinitelyTyped/blob/d63e32a2324ee77c655ba5eb89ed938bf9bf9825/types/pg/index.d.ts#LL26C51-L26C51

---

when ssl: true is set, it looks like it's translated to ssl: { sslmode: true } based on https://github.com/brianc/node-postgres/blob/e2d8fa2dc29001e6578c4b8f111708fa96b094d0/packages/pg/lib/connection-parameters.js#LL137C62-L137C62

But that also allows for setting the sslmode like this:

knex({
  client: 'pg',
  connection: {
    host: 'localhost',
    user: 'postgres',
    // etc
    ssl: 'require',
  },
});

---

UPDATE I tried these methods out and they didn't work, I'm pretty sure it's because of https://github.com/brianc/node-postgres/issues/2720

[vinipachecov]

We are just using connection-strings with the sslmode param attached, maybe you can add it the same way to your host option?

We also didn't get knex to work with @neondatabase/serverless as a replacement for pg yet. Easier knex support for that driver would be also neat. Neon is also the database underneath vercels postgres offering so lots of folks are going to need that.

How we use knex + neon right now:

knex({
        client: 'pg',
        connection: 'postgres://foo:bar@bla-bla-bla-pooler.eu-central-1.aws.neon.tech/neon?sslmode=require',
        pool: {
            min: 0
        }
})

Thank you @igl. Your example works.

[elvince]

Hi, I'm also stuck in using the connection string. Isn't a way to properly manage the sslmode out of connectionstring?

thanks