self signed certificate error

[eric-elsholz]

Hi. Sorry ahead of time, I don't have a lot of detail here. We updated our packages today and afterwards started seeing a connection error from Sequelize ORM: "self signed certificate". We do in fact use a self signed certificate but this has never been a problem for us in the past. We downgraded from [email protected] to [email protected] and then we were able to connect again.

[ianwremmel]

If I’m reading bf029c8 correctly, I think there may have been a bunch of options (including rejectUnauthorized) being inadvertently set to falsy values. They are now not set at all unless you pass them in. The last release was a breaking change, but it’s probably easiest to fix by passing rejectUnauthorized: false to pg.

[ianwremmel]

Sorry, I think I actually meant checkServerIdentity.

[charmander]

I think there may have been a bunch of options (including rejectUnauthorized) being inadvertently set to falsy values

tls.connect treats rejectUnauthorized: undefined as rejectUnauthorized: false? … It does. Oh no. :(

Well, it’s a good thing that was fixed. We should probably add a test for it. It might also be worth a security announcement (@brianc).

[powerc9000]

God I thought I was going crazy! Thanks for this info rolling back to 7.12 worked for me.

[eldimious]

I am facing the same issue. Rolling back to 7.12.1 worked for me too.

[znarf]

Spotted that in our staging system (heroku), happy to give more details.

[BrandonZacharie]

I'm using Sequelize as well and I got the "self signed certificate in chain" error until I rolled back to 7.12.1

[Kinetic-Pulse]

This one one of the most painful bugs I had to identify.
Probably shouldnt do too many updates at once but ye....
Downgrading is the way for now ~7.12.1

[ptpt]

Same here. Very hard to identify. The only error message we got is:

Error: self signed certificate in certificate chain
    at TLSSocket.onConnectSecure (_tls_wrap.js:1321:34)
    at TLSSocket.emit (events.js:210:5)
    at TLSSocket.EventEmitter.emit (domain.js:476:20)
    at TLSSocket._finishInit (_tls_wrap.js:794:8)
    at TLSWrap.ssl.onhandshakedone (_tls_wrap.js:608:12) {
  code: 'SELF_SIGNED_CERT_IN_CHAIN'
}

And we noticed that it occurs when we query PSQL.

Downgraded to [email protected] and it works now.

[jsanta]

If it may help someone else, we just added the rejectUnauthorized: false, to our sequelize connection configuration:

sequelize: {
    uri:
      'postgres://postgres_user:postgres_user_password@postgres_server:5432/postgres_database',
    options: {
      database: 'postgres_database',
      dialect: 'postgres',
      host: 'postgres_server',
      port: 5432,
      username: 'your_postgres_user',
      password: 'your_postgres_user_password',
      pool: {
        max: 3,
        min: 1,
        idle: 10000,
      },
      dialectOptions: {
        ssl: {
          require: true,
          // Ref.: https://github.com/brianc/node-postgres/issues/2009
          rejectUnauthorized: false,
        },
        keepAlive: true,        
      },      
      ssl: true,
      define: {
        timestamps: false,
      },
    },
  },

lets say the error is still there, but we are not being notified, and the backend is not crashing anymore. Just adjust the configuration according to your own needs.
Best regards from Chile.

[brianc]

Gah, I apologize for this! There aren't enough tests around self-signed cert connecting with node-postgres in travis so it was missed. Totally accidental on my part! I'm going to roll out a backwards-compatible (to the 7.x line) semver patch on top of this one to make sure it behaves exactly as it should.

@charmander what do you mean about security announcement? This makes things more secure now right? By forcing unauthorized cert rejection, where as before it was ignoring it? We should probably make the defaults be the same as the node.js defaults in v8.0. Do you mean announcing the current behavior is insecure in that it allows unauthorized certs unless you specifically set rejectUnauthorized to something to true? I agree that's worth calling out. We should also re-introduce this breaking change in 8.0 (w/ a deprecation notice in the 7.x line) so we can get folks to migrate to better ssl configurations.

[brianc]

Okay - released [email protected] which reverts [email protected] 🤕 sorry about that!

I'd like to get self-signed and "proper" ssl connections tested in travis. Anyone have a docker file handy that sets both of those things up? Then we can be covered on this behavior going forward.

[charmander]

@charmander what do you mean about security announcement? This makes things more secure now right? By forcing unauthorized cert rejection, where as before it was ignoring it?

@brianc Yep, it does! I meant an announcement that pg 0.8.7 through 7.12.0 don’t check certificates.

(edit)

Do you mean announcing the current behavior is insecure in that it allows unauthorized certs unless you specifically set rejectUnauthorized to something to true? I agree that's worth calling out. We should also re-introduce this breaking change in 8.0 (w/ a deprecation notice in the 7.x line) so we can get folks to migrate to better ssl configurations.

Yes, exactly that.

[sehrope]

+1 to having the default in a new major being to validate so that it's an explicit action to disable things.

FYI, the default for libpq (ex: via psql) is not to validate either so the default behavior of this module till now is not totally crazy either. May be worth calling that out in any security announcement: https://www.postgresql.org/docs/current/libpq-ssl.html (Still a +1 to making a security note calling out the behavior)

[sehrope]

@brianc RE: a test setup, the defaults for a Debian / Ubuntu postgresql server install configures it to use a self signed "snake oil" cert. Setting it up in Docker container should be similar.

The PGJDBC driver has a whole bunch of SSL related configs and tests for running things on Travis. Includes overwriting the certs, setting up specific users, installing the sslinfo extension for verifying things. May be worth a peek: https://github.com/pgjdbc/pgjdbc

[hghammoud]

any news on this subject? any pg version that properly supports self signed certs?

[brianc]

I'll add this to [email protected] which is under active development & should be released in a week or two, so will look at this shortly sorry for the delay

[charmander]

any news on this subject? any pg version that properly supports self signed certs?

@hghammoud What’s “properly”? If you mean the behaviour of not verifying certificates, that was already restored in 7.14.0. If you want verification, you’ll have to specify options like:

{
    ssl: {
        rejectUnauthorized: true,
        ca: /* the certificate */,
    },
}

and that’s not going to change with 8.0 except for rejectUnauthorized: true being implied (maybe). It’s already done “properly” with respect to the difference between self-signed and trusted by the system.

[brianc]

@charmander - I was looking at the postgres docs...

https://www.postgresql.org/docs/9.1/libpq-ssl.html

Particularly at "Table 31-1. SSL Mode Descriptions" - I think specifying ssl: true and having rejectUnauthorized to false is actually pretty much in line w/ how libpq does things. I think what we should do is here is support being able to opt in to this behavior via the PGSSLMODE=verify-ca"...at that point instead of returning truewe should return{ rejectUnauthorized: true }` to use it as the config.

Other than that I'm not even totally sure deprecating the ability to leave it undefined it is really the right thing as it behaves the same in libpq....undefined implies false. On one hand it's nice to let users know "hey, are you sure you don't want to reject unauthorized certificates?" but libpq doesn't do that either, it just happily goes along and doesn't verify unauthorized certs unless explicitly asked to do so. as far as I can see this is the final kinda dangling issue WRT me releasing a new semver minor 7.x w/ the warning included as well as doing (or not) doing any changes for 8.0.

[charmander]

It’s in line with how libpq does things, but I still think it’s better to be secure by default – in line with Node’s tls. I was surprised by it, at least, but maybe that was influenced by reading the code (which can make for misleading reading because the change to not verifying by default was an accident). Parsing libpq’s connection strings into the appropriate settings would be a good change, though, yeah. (And to be clear, the deprecating the ability to leave it undefined isn’t supposed to be permanent, right? It’ll default to rejectUnauthorized: false with the new major version.)

[brianc]

(And to be clear, the deprecating the ability to leave it undefined isn’t supposed to be permanent, right? It’ll default to rejectUnauthorized: false with the new major version.)

Well that's why I brought it up - I'm not sure we should default to rejectUnauthorized: false if you do something like

const client = new Client({ ssl: true })

I guess we could do that....you're right its more secure by default.

I think in that case we should make sure if you use the environment variables:

PGSSLMODE=require 
PGSSLMODE=verify-ca

We pass the proper value of either { rejectUnauthorized: false } or { rejectUnauthorized: true } into the ssl config so that stays in line with how the environment variable works. So, yeah in 8.0 I'll need to get some better tests around ssl handshakes in their various forms and then fix the behavior of the environment vars to do what's "expected" in all cases and make sure native and pure js line up in behavior...that'll be the last thing before 8.0! I'll start working on this this week...it's a bit more fiddly that the other changes because SSL is sorta complex by design so...it'll take a bit (but not too long!)

[brianc]

To elaborate on these changes a bit I think it should be like...

const client = new Client({ ssl: true })
// default ssl config = { rejectUnauthorized: true }

process.env.PGSSLMODE='require'
// default ssl config = { rejectUnauthorized: false }
const client = new Client() // does NOT reject unauthorized
const client = new Client({ ssl: true }) // rejects unauthorized
const client = new Client({ ssl: { rejectUnauthorized: false }}) // does NOT reject unauthorized
const client = new Client({ ssl: { rejectUnauthorized: true }}) // rejects unauthorized

process.env.PGSSLMODE = 'verify-ca'
// default ssl config = { rejectUnauthorized: true }
const client = new Client() // rejects unauthorized
const client = new Client({ ssl: true }) // rejects unauthorized
const client = new Client({ ssl: { rejectUnauthorized: false }}) // does NOT reject unauthorized
const client = new Client({ ssl: { rejectUnauthorized: true }}) // rejects unauthorized

I think in every case we want the connection parameters (which parse env vars) to generate a default ssl config, and in every case we want to allow any custom ssl: { ... } config passed to the client to overwrite the default config and extend it.

e.g.

PGSSLMODE=require
const client = new Client({
  ssl: {
     rejectUnauthorized: true
  }
})

should make it reject unauthorized. Anything you pass explicitly to { ssl: { ... } } takes precedence over environment variables and has the "final say" in the ssl config.

[brianc]

I'll document these permutations and also include a note to discourage the use of new Client({ ssl: true }) as the meaning is ambiguous. Perhaps we deprecate it in 8.0 in favor of environment variables or a more explicit full ssl: { ... } config

[Dzirt2006]

this topic helped me https://stackoverflow.com/questions/61097695/self-signed-certificate-error-during-query-the-heroku-hosted-postgres-database/66773313#66773313
my solution:
const db = new Sequelize( process.env.DATABASE_URL || postgres://postgres:w2w2@localhost:5432/${databaseName}, { logging: false, ssl: { rejectUnauthorized: false } //solved the problem with self signed sertificate }

[WithoutATowel]

Hi all! I'm running into the same issue, but none of the recommended fixes seem to work.

My setup:

• Node v15.12.0
• Postgres database ("Heroku Postgres" add-on)
• pg v8.5.1
• sequelize v6.6.2
• sequelize-cli v6.2.0

My Sequelize connection parameters:

{
  "development": {
    "username": "XXXXXX",
    "password": "XXXXXX",
    "database": "party_playlist",
    "host": "127.0.0.1",
    "dialect": "postgres"
  },
  "production": {
    "use_env_variable": "DATABASE_URL",
    "dialect": "postgres",
    "dialectOptions": {
      "ssl": {
         "require": "true",
         "rejectUnauthorized": "false"
      }
    }
  }
}

What I've tried:

1. Adding rejectUnauthorized: false to my Sequelize connection params
2. Adding ssl:true to the config outside of dialectOptions
3. Adding NODE_TLS_REJECT_UNAUTHORIZED=0 to my Heroku environment variables
4. Adding PGSSLMODE=no-verify to my Heroku environment variables
5. Toggling Heroku's Automatic Certificate Management feature on/off
6. Rolling back to pg v7 (various subversions)

1-5 have no effect, and rolling back to pg v7 breaks the app (locally, any attempted reads/writes to the database hang with no error message; on heroku, the db:migrate command runs without an error message, but the db isn't updated). I assume that pg v7 is incompatible with my Node version or some other package version in my project, but I have no idea how to figure out what a compatible version set would be other than by trial and error, which isn't feasible. Also, as a side note, this is a hobby project so I'm not worried about MITM attacks.

Interestingly, I'm able to connect to my Heroku database just fine when I run heroku pg:psql!

Any ideas how I can fix this one? Any and all help would be much appreciated! Also posted this to StackOverflow if folks would like internet points.

[charmander]

@WithoutATowel If it’s being passed directly to pg, the value of "ssl" should be { "rejectUnauthorized": false }, not { "require": "true", "rejectUnauthorized": "false" } (note the quotes).

[WithoutATowel]

Lol... Thank you @charmander!

[wamujlb]

@adrianhorning08 hi! I also use DO managed database but it doesn't work for me. I don't understand what I'm doing wrong.
Thank you in advance

export const pool = new Pool({
  user: DB_CONFIG.USER,
  password: DB_CONFIG.PASSWORD,
  host: DB_CONFIG.HOST,
  port: DB_CONFIG.PORT,
  database: DB_CONFIG.DATABASE,
  ssl: {
    rejectUnauthorized: true,
    ca: DB_CONFIG.CA_CERT,
  },
});

[sted6]

Hi all,

Having the same issue here. Even after setting dialectOptions as suggested previously in the thread.

I've got;

const proConfig = {
  use_env_variable: "DATABASE_URL",
  dialect: "postgres",
  dialectOptions: {
    ssl: {
      require: true,
      rejectUnautorized: false,
    },
  },
};

const db =
  process.env.NODE_ENV === "production"
    ? new Sequelize(process.env.DATABASE_URL, proConfig)
    : new Sequelize(
        process.env.DB_NAME,
        process.env.DB_USER,
        process.env.DP_PASSWORD,
        {
          dialect: "postgres",
          host: process.env.DB_HOST,
          port: process.env.DB_PORT,
        }
      );

And I'm still getting:

UnhandledPromiseRejectionWarning: SequelizeConnectionError: self signed certificate

Is there anything else I could try?

[charmander]

@sted6 The spelling is rejectUnauthorized with an h. Also, require: true isn’t a thing, so you can remove that.

[sted6]

@charmander uhg I could of swore I even copy pasted that. Thank you so much.

[forrestpark]

Hi, I've tried all mentioned options but am still having issues. I've posted my issue here: https://stackoverflow.com/questions/68327934/error-self-signed-certificate-while-running-sequelize-dbmigrate-on-heroku. Any help would be greatly appreciated. Thank you!

[charmander]

2021-07-10T07:06:18.758245+00:00 app[web.1]: dialectOptions: { ssl: { rejectUnautorized: false } }

const production = {
  "use_env_variable": "DATABASE_URL",
  "dialect": "postgres",
  "dialectOptions": {
    "ssl": {
      "rejectUnautorized": false,
    }
  }
}

@forrestpark Same typo as @sted6, it’s rejectUnauthorized with an h.

[RodrigoBeiraoCardoso]

hello everyone, i have the same problem and ive tried litterally everything i found, from global variables on heroku to all kinds of those params: const sequelize = new Sequelize(database, user, password, {
host,
port,
dialect: 'postgres',
dialectOptions: {
ssl: {
rejectUnauthorized: "false"
},
},
logging: false
})
ive tried it that way like everyone else, ive also had ssl:true, and the require:true and that part outside the dialect options just the ssl {reject..} ive also tried reverting pg to a previous version, ahh ive put on heroku 2 global variables one USE_SSL=true and another one that was noded_reject_etc= 0 (cant remenber the right name) and nothing works nothing ahah
I really need help guys would apreciate anyone who can help

[jsanta]

@RodrigoBeiraoCardoso

rejectUnauthorized: "false"

Should be rejectUnauthorized: false without quotes. A boolean is needed not a string ;)
Everything else looks fine to me.

[RodrigoBeiraoCardoso]

@RodrigoBeiraoCardoso

rejectUnauthorized: "false"

Should be rejectUnauthorized: false without quotes. A boolean is needed not a string ;)
Everything else looks fine to me.

yeahh that was just a mistake that i copy pasted ahah ive tried without it and still the same error, ive tried like this dialectOptions: {
ssl: {
rejectUnauthorized: false // <<<<<<< YOU NEED THIS
}
},
ssl: true,
Like this dialectOptions: {
ssl: {
require: true
rejectUnauthorized: false // <<<<<<< YOU NEED THIS
}
},
ssl: true,
and like all that but without the ssl:true
also tried just the ssl: {
rejectUnauthorized: false // <<<<<<< YOU NEED THIS
},
withouth being inside the dialect and nothing works.
The grobal variables ive tried on heroku are USE_SSL =true (which i still use) and NODE_TLS_REJECT_UNAUTHORIZED = 0 (wich im not using anymor)

[Tosinkoa]

Pls help, I have try all the answer here, nothing seems to work for me.
I've spent 2days on this with no avails, pls help.
After adding all answers here, this is was what I tried as a last attempt.

import pkg from 'pg';
const { Pool } = pkg;

const pool = new Pool({
  user: process.env.USER,
  host: process.env.HOST,
  database: process.env.DATABASE,
  password: process.env.PASSWORD,
  port: process.env.PORT,
  ssl: {
    require: true,
    rejectUnauthorized: false
  }
})

console.log("Connected to database... ✅✅✅")
export default pool

import express from "express"
import rootRoute from "./src/root_Route.js"
import cookieParser from "cookie-parser"
import passport from "passport"
import connectPgSimple from "connect-pg-simple"
import session from "express-session"
import dotenv from "dotenv"
import cors from "cors"
import "./src/LIB/DB-Client.js"
import "./src/PASSPORT_STRATEGY/google-auth-strategy.js"
import "./src/PASSPORT_STRATEGY/facebook-auth-strategy.js"
import pool from "./src/LIB/DB-Client.js"
dotenv.config()
const app = express()

app.use(
  cors({
    origin: ["http://localhost:3000", "https://frontend.netlify.app"],
    credentials: true,
    methods: "GET, PUT, POST, DELETE",
    optionsSuccessStatus: 200
  })
)
const PgStore = connectPgSimple(session)
const store = new PgStore({ conString: process.env.DATABASE_URL, schemaName: "hidden", createTableIfMissing: true })

app.use(express.json())
app.use(cookieParser())
app.set("trust proxy", 1)

app.use(
  session({
    store: store,
    secret: "myscecret",
    saveUninitialized: false,
    resave: false,
    cookie: {
      maxAge: 1000 * 60 * 60 * 24,
      secure: true,
      httpOnly: true,
      sameSite: "none",
    },
  })
)

app.get("/", (req, res) => {
  const userId = req.session.user ?? req.user
  res.send("API Running...")
})

app.use(passport.initialize())
app.use(passport.session())
rootRoute(app)

const PORT = process.env.PORT || 4000

app.listen(PORT, (req, res) => console.log(`Server running on PORT:${PORT}...`))

I also added this as an env in heroku PGSSLMODE=no-verify, But still I get this error: Failed to prune sessions: self signed certificate

Thanks in advance 🙏🙏

[charmander]

@Tosinkoa You haven’t passed the pool to connect-pg-simple in that code, so none of the configuration other than DATABASE_URL is being used.

[Tosinkoa]

Everything works fine now, thank you so much.

@Tosinkoa You haven’t passed the pool to connect-pg-simple in that code, so none of the configuration other than DATABASE_URL is being used.

[pantelis-karamolegkos]

I have the following node script

const { Pool } = require('pg');
const { parse } = require('pg-connection-string');

const config = parse(process.env.DATABASE_URL);
config.ssl = { rejectUnauthorized: true };

const pool = new Pool(config);

pool.connect((err, client, done) => {
  if (err) {
    console.error('Error acquiring client', err.stack);
    return;
  }

  const testQuery = 'SELECT * FROM information_schema.tables';
  client.query(testQuery, (err, result) => {
    done();
    if (err) {
      console.error('Error executing query', err.stack);
      return;
    }
    console.log('Query result:', result.rows);
    pool.end();
  });
});

Before running the script I am exporting the NODE_EXTRA_CA_CERTS=../aws-global-bundle.pem where the .pem file is this one.

My question is how can I enforce the verify-full option as described in the official postgresql documentation and what is the practical difference with setting the rejectUnauthorized to true ?

I have tried the following

config.ssl = { 
  rejectUnauthorized: true,
  sslmode: 'verify-full',
  ca: fs.readFileSync('./global-bundle.pem').toString(),
};

However it is as if the sslmode: 'verify-full', is not actually being taken into account because when I use sslmode: 'foobar', it does not complain at all (and connects as expected.

[charmander]

@pantelis-karamolegkos sslmode is a connection string parameter (and has a corresponding environment variable named PGSSLMODE). It’s translated to the ssl: … property of the pg configuration object when you use a connection string. There is no ssl.sslmode. rejectUnauthorized: true/verify-full is the default behavior of Node’s tls module that pg passes the ssl object to directly, so all you need is

config.ssl = {
  ca: fs.readFileSync('./global-bundle.pem'),
};