[BUG / Enhancement] MySQL SSL - Hostname/IP does not match certificates alt names.

[Jooeee]

Describe the bug When trying to connect to my database through the MySQL (SSL) component I receive the following error:

We've recently moved our database to a Google Cloud Platform MySQL instance and theres no way of editing the SSL certificates it provides. I believe this is an issue with the mysql2 npm package which will hopefully accept a checkServerIdentity flag in the config soon. But until then, would it be possible to allow the user to set rejectUnauthorized: to true or false, either in the app setting or in the triggers and actions for the component? I could then set this to false which would allow the GCP instance to continue to only allow SSL connections.

[vunguyenhung]

Hi @Jooeee, to skip the SSL, could you try to use the MySQL app?

[Jooeee]

Hi @vunguyenhung,

That would work however that would mean the connection is unencrypted. Setting rejectUnauthorized to false is not ideal but is still more secure than no SSL at all. The problem stems from not being able to connect to the Google Cloud MySQL instance with the alt name listed in the certificate but there is no workaround to this apart from changing hosting providers.

[andrewjschuang]

@jcortes are you able to help on this one since you've previously worked on the MySQL SSL app?

I used the AI Code Gen tool and got this response, but am unable to test it:

import mysql from 'mysql2/promise';

export default defineComponent({
  props: {
    mysql_ssl: {
      type: "app",
      app: "mysql_ssl",
    }
  },
  async run({steps, $}) {
    const { host, port, username, password, database, ca, cert, key } = this.mysql_ssl.$auth;

    const connection = await mysql.createConnection({
      host,
      port,
      user: username,
      password,
      database,
      ssl: {
        rejectUnauthorized: true,
        ca,
        cert,
        key,
        checkServerIdentity: (host, cert) => {
          if (cert.subject.CN !== host) {
            if (!cert.subjectaltname.includes(host)) {
              return new Error(`Host: ${host} is not in the cert's list: ${cert.subjectaltname}`);
            }
          }
        },
      }
    });

    const [rows] = await connection.execute('SELECT NOW()');
    return rows;
  },
});

[vunguyenhung]

Hello everyone, I have tested this PR and there're some test cases failed or needed improvement.

Please check the test report below for more information https://vunguyenhung.notion.site/BUG-Enhancement-MySQL-SSL-Hostname-IP-does-not-match-certificates-alt-names-8236-d71496a0553c497bb483dc350a3550ad

[Jooeee]

Hi @vunguyenhung,

I believe that's because the option has been added to the app settings and not the actions or triggers.

[vunguyenhung]

I see, let me try to test it again. Thanks for pointing out

[vunguyenhung]

Hello everyone, I have tested this PR and there're some test cases failed or needed improvement.

Please check the test report below for more information https://vunguyenhung.notion.site/BUG-Enhancement-MySQL-SSL-Hostname-IP-does-not-match-certificates-alt-names-8236-d71496a0553c497bb483dc350a3550ad

[jcortes]

Hi @vunguyenhung I'm not sure if it should work with self signed certificates. Is it possible for the user to help us test on their environment? @Jooeee

[vunguyenhung]

@jcortes yes we can request the user to test it. Could you instruct him to publish your action version to his account?

[Jooeee]

Hi @jcortes and @vunguyenhung, I'm happy to help test this. However, I'm not quite sure how to publish to my account. Could you please provide me with some instructions on how to do that?

[jcortes]

Hi @Jooeee I'm sharing with you my own workflow so you can set it up with your own certs

https://pipedream.com/new?h=tch_wNxf27

[Jooeee]

@jcortes I used the link you provided to copy the workflow over to my account but I still receive the same error.

Not sure if I'm doing something wrong because I know the connection definitely works if rejectUnauthorized: is set to false.

[jcortes]

HI @Jooeee do you have a slack user so we can work along? mine is jcortes just in case you want to ping me.

In the meantime can you try again with this workflow https://pipedream.com/new?h=tch_mv4fgv and share what you can see in the logs tab or in the chrome dev tools console? something with the name rejectUnauthorized!!!

[jcortes]

Hi @vunguyenhung according to user's tests he was able to make it work with rejectUnauthorized: false however I let him know we put some context in the description of the same prop in app's settings if certs are not properly generated. So this one should be ready to go!

cc @Jooeee

[vunguyenhung]

Hi everyone, all test cases are passed! Ready for release!

Test report https://vunguyenhung.notion.site/BUG-Enhancement-MySQL-SSL-Hostname-IP-does-not-match-certificates-alt-names-8236-d71496a0553c497bb483dc350a3550ad