Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

self signed certificate error #2009

Closed
eric-elsholz opened this issue Nov 19, 2019 · 76 comments
Closed

self signed certificate error #2009

eric-elsholz opened this issue Nov 19, 2019 · 76 comments
Milestone

Comments

@eric-elsholz
Copy link

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 pg@7.13.0 to pg@7.4.3 and then we were able to connect again.

@ianwremmel
Copy link

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
Copy link

Sorry, I think I actually meant checkServerIdentity.

@charmander
Copy link
Collaborator

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
Copy link

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

@eldimious
Copy link

eldimious commented Nov 20, 2019

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

@znarf
Copy link

znarf commented Nov 20, 2019

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

@BrandonZacharie
Copy link

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
Copy link

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
Copy link

ptpt commented Nov 20, 2019

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 pg@7.4.3 and it works now.

@jsanta
Copy link

jsanta commented Nov 20, 2019

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
Copy link
Owner

brianc commented Nov 20, 2019

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
Copy link
Owner

brianc commented Nov 20, 2019

Okay - released pg@7.14.0 which reverts pg@7.13.0 🤕 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
Copy link
Collaborator

charmander commented Nov 20, 2019

@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
Copy link
Contributor

sehrope commented Nov 20, 2019

+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
Copy link
Contributor

sehrope commented Nov 20, 2019

@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
Copy link

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

@brianc
Copy link
Owner

brianc commented Jan 15, 2020

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

@brianc brianc added this to the pg@8.0 milestone Jan 15, 2020
@charmander
Copy link
Collaborator

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.

charmander added a commit to charmander/node-postgres that referenced this issue Jan 15, 2020
Yes, it treats `undefined` as `false`. Discussion in brianc#2009. Introduced unintentionally in pg 0.8.7.
brianc pushed a commit that referenced this issue Jan 15, 2020
Yes, it treats `undefined` as `false`. Discussion in #2009. Introduced unintentionally in pg 0.8.7.
@brianc
Copy link
Owner

brianc commented Jan 28, 2020

@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
Copy link
Collaborator

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
Copy link
Owner

brianc commented Jan 28, 2020

(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
Copy link
Owner

brianc commented Jan 28, 2020

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
Copy link
Owner

brianc commented Jan 28, 2020

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
Copy link

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
Copy link

WithoutATowel commented Apr 2, 2021

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
Copy link
Collaborator

charmander commented Apr 2, 2021

@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
Copy link

Lol... Thank you @charmander!

dprosper added a commit to IBM-Cloud/vpc-tutorials that referenced this issue May 12, 2021
- updated to the latest modules
- fixed an issue encountered with the pg module described here: brianc/node-postgres#2009 (comment)
@wamujlb
Copy link

wamujlb commented Jun 17, 2021

@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
Copy link

sted6 commented Jun 19, 2021

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
Copy link
Collaborator

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

@sted6
Copy link

sted6 commented Jun 19, 2021

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

@forrestpark
Copy link

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
Copy link
Collaborator

charmander commented Jul 12, 2021

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.

@simon1tan

This comment has been minimized.

@RodrigoBeiraoCardoso
Copy link

RodrigoBeiraoCardoso commented Sep 3, 2021

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
Copy link

jsanta commented Sep 3, 2021

@RodrigoBeiraoCardoso

rejectUnauthorized: "false"

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

@RodrigoBeiraoCardoso
Copy link

@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
Copy link

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
Copy link
Collaborator

@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
Copy link

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
Copy link

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
Copy link
Collaborator

@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'),
};

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests