"Unknown SSL profile" for self-host using MySQL with SSL after mysql2 package update (v4.38+)

[stefann42]

Issue Summary

Prior to this change bf6f607 self-hosters who connect to MySQL 5.7 using SSL were able to use this config as as discussed at https://forum.ghost.org/t/configure-mysql-over-tls-ssl/2297/8

“database”: {
  “client”: “mysql”,
  “connection”: {
    “host”: “127.0.0.1”,
    “user”: “user”,
    “password”: “pass”,
    “database”: “ghostdb”,
    “ssl”: {
      “rejectUnauthorized”: “true”,
      “secureProtocol”: “TLSv1_2_method”
    }
  }
}

Upgrading past v4.37 breaks Ghost (node app cannot start) with the error
Unknown SSL profile "{ 'rejectUnauthorized':'true', 'secureProtocol':'TLSv1_2_method'}"

We need to understand what's the correct SSL config for mysql2 library. This is possibly an issue for downstream dependencies.

Relevant code from downstream dependencies:

• How mysql2 parses config
  https://github.com/sidorares/node-mysql2/blob/d74558b605162156b813248a024f7559785de6fb/lib/connection_config.js#L119
• And make sure you use a version of knex dependency that includes this fix Knex doesn't parse JSON values in connectionURIs causing incompatibility with mysql / node-mysql2  knex/knex#4628

Steps to Reproduce

1. Install self-host v4.38 of later
2. Try to use SSL config documented at https://forum.ghost.org/t/configure-mysql-over-tls-ssl/2297/8

Ghost Version

=4.38

Node.js Version

14.x

How did you install Ghost?

Using instructions at https://ghost.org/docs/hosting/

Database type

MySQL 5.7

Browser & OS version

No response

Relevant log / error output

DatabaseError: Unknown SSL profile '{ 'rejectUnauthorized': 'true', 'secureProtocol': 'TLSv1_2_method' }'
at DatabaseStateManager.getState (/var/lib/ghost/versions/4.44.0/core/server/data/db/state-manager.js:64:32)
at DatabaseError.KnexMigrateError (/var/lib/ghost/versions/4.44.0/node_modules/knex-migrator/lib/errors.js:7:26)
at new DatabaseError (/var/lib/ghost/versions/4.44.0/node_modules/knex-migrator/lib/errors.js:55:26)
at /var/lib/ghost/versions/4.44.0/node_modules/knex-migrator/lib/database.js:57:19
at async DatabaseStateManager.getState (/var/lib/ghost/versions/4.44.0/core/server/data/db/state-manager.js:40:13)
at async DatabaseStateManager.makeReady (/var/lib/ghost/versions/4.44.0/core/server/data/db/state-manager.js:73:25)
at async initDatabase (/var/lib/ghost/versions/4.44.0/core/boot.js:69:5)
at async bootGhost (/var/lib/ghost/versions/4.44.0/core/boot.js:412:9)

TypeError: Unknown SSL profile '{ 'rejectUnauthorized': 'true', 'secureProtocol': 'TLSv1_2_method' }'
at Function.getSSLProfile (/var/lib/ghost/versions/4.44.0/node_modules/mysql2/lib/connection_config.js:229:13)
at new ConnectionConfig (/var/lib/ghost/versions/4.44.0/node_modules/mysql2/lib/connection_config.js:119:28)
at Object.exports.createConnection (/var/lib/ghost/versions/4.44.0/node_modules/mysql2/index.js:10:35)
at /var/lib/ghost/versions/4.44.0/node_modules/knex-migrator/node_modules/knex/lib/dialects/mysql/index.js:62:38
at new Promise (<anonymous>)
at Client_MySQL2.acquireRawConnection (/var/lib/ghost/versions/4.44.0/node_modules/knex-migrator/node_modules/knex/lib/dialects/mysql/index.js:61:12)
at create (/var/lib/ghost/versions/4.44.0/node_modules/knex-migrator/node_modules/knex/lib/client.js:252:39)"

Code of Conduct

• I agree to be friendly and polite to people in this repository

[daniellockyer]

@stefann42 I can't seem to reproduce it from your config example. If I replace the smart quotes with double quotes, it functions as expected (ie. it errors because I don't provide the CA certificate). Both our mysql2 and knex dependencies are on the latest version, so I don't think we're missing anything there.

Can you have a look again and check the config you see breaking your site? Also note the SSL docs

[github-actions]

Note from our bot: The needs info label has been added to this issue. Updating your original issue with more details is great, but won't notify us, so please make sure you leave a comment so that we can see when you've updated us.

[stefann42]

Looks like we were using an outdated information, thanks for the pointer to Ghost's SSL docs. That said, serializing the CA public cert as a config parameter is awfully complicated. Do you happen to have a sample config for when you're Ok to ignore SSL cert validation? Unfortunately the node-mysql2 docs have a placeholder where the "SSL" examples are supposed to go.

[stefann42]

Nevermind, after some trial and error this seems to work { 'rejectUnauthorized': 'false' }
This issue can be closed, but it would be useful if you could add this bit to the SSL docs

[ErisDS]

Hey, I realise this was annoying to figure out but it seems the upshot is, mysql2 is really picky about configuration, and was throwing an error because of the 'secureProtocol': 'TLSv1_2_method' part of your config.

If you are using SSL but are ok to skip certificate validation, you only need ssl: { 'rejectUnauthorized': 'false' } in your config.

I'm a little dubious about updating our own docs to cover this, as I'm not clear on what the use case would be for requiring an SSL config but not validating the cert 😬

Going to close as there is no bug - but convo can continue and if we decide we do want to update docs that's very quick!

[stefann42]

@ErisDS There are many cases where it's warranted to use SSL but not validate the cert. Local development for example or if you must encrypt traffic but it's too costly/time consuming to maintain valid certificates for your own infrastructure. Sometimes you just need to get something up and running quickly. IMHO the purpose of documentation is to be helpful not judgmental. Right now self-host docs cover a narrow set of use cases (deploying db on AWS RDBMS). To be helpful give developers the information and trust that they know what they're doing if choose to do something. I opened this issue because we wasted two full days with this issue and we don't want other people to have the same experience.

[berayb]

Hi @ErisDS, having the same problem. Fresh installation, trying to connect Azure MySQL managed service (v8).

Please see my config below:

"database": { "client": "mysql", "connection": { "host": "asd-mysql-blog.mysql.database.azure.com", "user": "asd@asd-mysql-blog", "password": "!!cdY*asd", "database": "asd_blog", "ssl": { "rejectUnauthorized": true, "secureProtocol": "TLSv1_2_method" } }

I get: 

`
node:events:505
  throw er; // Unhandled 'error' event
  Error: read ECONNRESET
at TLSWrap.onStreamRead (node:internal/stream_base_commons:217:20)
Emitted 'error' event on Connection instance at:
at Connection._notifyError (/var/www/ghost/versions/5.1.1/node_modules/mysql2/lib/connection.js:236:12)
at Connection._handleFatalError (/var/www/ghost/versions/5.1.1/node_modules/mysql2/lib/connection.js:167:10)
at Connection._handleNetworkError (/var/www/ghost/versions/5.1.1/node_modules/mysql2/lib/connection.js:180:10)
at TLSSocket.<anonymous> (/var/www/ghost/versions/5.1.1/node_modules/mysql2/lib/connection.js:350:14)
at TLSSocket.emit (node:events:527:28)
at TLSSocket._tlsError (node:_tls_wrap:906:8)
at TLSSocket.emit (node:events:527:28)
at emitErrorNT (node:internal/streams/destroy:157:8)
at emitErrorCloseNT (node:internal/streams/destroy:122:3)
at processTicksAndRejections (node:internal/process/task_queues:83:21) {
errno: -104,
code: 'ECONNRESET',
syscall: 'read',
fatal: true
}

`

[ErisDS]

@berayb that's a different issue, posted here: #14990