Unable to connect to mysql using ssl

[z88kat]

I have tried the following configuration but i am unable to connect to mysql 8 database. Either with the certificate or without by using rejectUnauthorized.

I always receive the error

Error: Connections using insecure transport are prohibited while --require_secure_transport=ON

let mySQLOptions = {
    host: process.env.MYSQL_SERVER,
    port: 3306,
    user: process.env.MYSQL_USER,
    password: process.env.MYSQL_PASSWORD,
    ssl: {
        // TODO: set up your ca correctly to trust the connection
        ca: fs.readFileSync("./config/ssl/RootCA.crt.pem"),
        rejectUnauthorized: false
    }

}

[chill117]

Please refer to the usage examples from the mysql2 module found here and here.

It looks like what you're trying to do is correct - so I suggest to sanity check the value of fs.readFileSync("./config/ssl/RootCA.crt.pem") to make sure it's actually the certificate which you expect. Also try to force nodejs to accept insecure SSL connections globally - then try connecting to your database. If you can connect when ignoring certificate errors, then the problem is with your certificate. If not then the problem is somewhere else.

To debug certificate errors, try to use the curl CLI tool with --verbose and provide your CA certificate. That should give you better error messages than node.

[z88kat]

Thanks I will try that out.

[Zygis0321]

createMySQLStore.Options is missing ssl field and ssl is not being set. The workaround is to pass ssl with connection.

import mysql from "mysql2";
const sessionStore = new MySQLStore(
  {
    schema: {
      tableName: "AdminjsSession",
      columnNames: {
        session_id: "id",
        expires: "expires",
        data: "data",
      },
    },
  },
  mysql.createConnection({
    ...parseMysqlConnectionString(process.env.DATABASE_URL as string),
    ssl: {
      rejectUnauthorized: process.env.NODE_ENV === "production",
    },
  })
);

[chill117]

Please be aware that using rejectUnauthorized in your code is not recommended and can lead to MITM (man-in-the-middle) vulnerabilities in your projects. For local development just use unencrypted HTTP. And for your production or staging environments, use valid SSL certificates signed by a proper CA (certificate authority) - e.g. LetsEncrypt. Or if you really need to use SSL for local/testing, then generate your own self-signed certs locally, then provide the certificate in your database configuration. You should basically never use rejectUnauthorized unless doing a quick demo or script.