Database Connection TLS issue with Azure Database for MySQL flexible server

[Emb3dded]

Issue Summary

• Ghost complains about self signed certificate in chain

Steps to Reproduce

The DB enforces TLS >= 1.2.

1. I followed the config guide for the Database connection mentioned in the documents here: Configuration - Adapt your publication to suit your needs
2. No database__connection__ssl__ca: this is expected to fail:

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

This is all fine. I started looking into the database__connection__ssl__ca config.
3. I tried adding the certificate of the Issuer which was used to sign the X509 Server Cerificate for the Database Server as an trust anchor via the database__connection__ssl__ca config. This is a common “root ca” which is in most truststores (DigiCert Global Root CA)
The error i got afterwards:

2024-02-27 11:26:02 [2024-02-27 10:26:02] ERROR self-signed certificate in certificate chain
2024-02-27 11:26:02 
2024-02-27 11:26:02 self-signed certificate in certificate chain
2024-02-27 11:26:02 
2024-02-27 11:26:02 "Unknown database error"
2024-02-27 11:26:02 
2024-02-27 11:26:02 Error ID:
2024-02-27 11:26:02     500
2024-02-27 11:26:02 
2024-02-27 11:26:02 Error Code: 
2024-02-27 11:26:02     HANDSHAKE_SSL_ERROR
2024-02-27 11:26:02 
2024-02-27 11:26:02 ----------------------------------------
2024-02-27 11:26:02 
2024-02-27 11:26:02 Error: self-signed certificate in certificate chain
2024-02-27 11:26:02     at /var/lib/ghost/versions/5.75.1/node_modules/knex-migrator/lib/database.js:57:19
2024-02-27 11:26:02     at TLSSocket.onConnectSecure (node:_tls_wrap:1659:34)
2024-02-27 11:26:02     at TLSSocket.emit (node:events:517:28)
2024-02-27 11:26:02     at TLSSocket._finishInit (node:_tls_wrap:1070:8)
2024-02-27 11:26:02     at ssl.onhandshakedone (node:_tls_wrap:856:12)

All “root CA” certificates are self signed. If they would not be self signed, they would not be the root of that trust chain Im unsure what this error tells me. I tought perhaps it has something to do with the intermediate certificate not beeing in the truststore (not an good idea to do this, i know, went on just for debugging purposes)
4. Put the whole chain in the truststore (tried once with intermediate than root and once with root than intermediate). Gave me the same error:

2024-02-27 12:46:07 [2024-02-27 11:46:07] ERROR self-signed certificate in certificate chain
2024-02-27 12:46:07 
2024-02-27 12:46:07 self-signed certificate in certificate chain
2024-02-27 12:46:07 
2024-02-27 12:46:07 "Unknown database error"
2024-02-27 12:46:07 
2024-02-27 12:46:07 Error ID:
2024-02-27 12:46:07     500

5. After that im just guessing, tried to add a database__connection__ssl__cert config with the leaf certificate of the database, not sure if this is even an valid config. Gave me an even strange error message:

2024-02-27 13:05:01 [2024-02-27 12:05:01] ERROR error:0480006C:PEM routines::no start line
2024-02-27 13:05:01 
2024-02-27 13:05:01 error:0480006C:PEM routines::no start line
2024-02-27 13:05:01 
2024-02-27 13:05:01 "Unknown database error"
2024-02-27 13:05:01 
2024-02-27 13:05:01 Error ID:
2024-02-27 13:05:01     500
2024-02-27 13:05:01 
2024-02-27 13:05:01 Error Code: 
2024-02-27 13:05:01     ERR_OSSL_PEM_NO_START_LINE
2024-02-27 13:05:01 
2024-02-27 13:05:01 ----------------------------------------
2024-02-27 13:05:01 
2024-02-27 13:05:01 Error: error:0480006C:PEM routines::no start line
2024-02-27 13:05:01     at /var/lib/ghost/versions/5.75.1/node_modules/knex-migrator/lib/database.js:57:19
2024-02-27 13:05:01     at node:internal/tls/secure-context:70:13
2024-02-27 13:05:01     at Array.forEach (<anonymous>)
2024-02-27 13:05:01     at setCerts (node:internal/tls/secure-context:68:3)
2024-02-27 13:05:01     at configSecureContext (node:internal/tls/secure-context:157:5)
2024-02-27 13:05:01     at Object.createSecureContext (node:_tls_common:117:3)
2024-02-27 13:05:01     at Connection.startTLS (/var/lib/ghost/versions/5.75.1/node_modules/mysql2/lib/connection.js:347:31)
2024-02-27 13:05:01     at ClientHandshake.handshakeInit (/var/lib/ghost/versions/5.75.1/node_modules/mysql2/lib/commands/client_handshake.js:149:18)
2024-02-27 13:05:01     at ClientHandshake.execute (/var/lib/ghost/versions/5.75.1/node_modules/mysql2/lib/commands/command.js:45:22)
2024-02-27 13:05:01     at Connection.handlePacket (/var/lib/ghost/versions/5.75.1/node_modules/mysql2/lib/connection.js:478:34)
2024-02-27 13:05:01     at PacketParser.onPacket (/var/lib/ghost/versions/5.75.1/node_modules/mysql2/lib/connection.js:97:12)
2024-02-27 13:05:01     at PacketParser.executeStart (/var/lib/ghost/versions/5.75.1/node_modules/mysql2/lib/packet_parser.js:75:16)
2024-02-27 13:05:01     at Socket.<anonymous> (/var/lib/ghost/versions/5.75.1/node_modules/mysql2/lib/connection.js:104:25)
2024-02-27 13:05:01     at Socket.emit (node:events:517:28)
2024-02-27 13:05:01     at addChunk (node:internal/streams/readable:368:12)
2024-02-27 13:05:01     at readableAddChunk (node:internal/streams/readable:341:9)
2024-02-27 13:05:01     at Readable.push (node:internal/streams/readable:278:10)

---

Setup information

Ghost Version
GHOST_VERSION 5.75.1

Node.js Version
NODE_VERSION 18.19.0

How did you install Ghost?
docker install

Provide details of your host & operating system
alpine linux

Database type
MySQL 8.0

[Emb3dded]

I tried the same connection, same source, same destination with mysqlsh, it works without throwing errors:

❯ mysqlsh -u USER -h HOSTNAME.mysql.database.azure.com -P 3306 --password='PW' --ssl
WARNING: The --ssl option was deprecated, please use --ssl-mode instead. (Option has been processed as --ssl-mode=REQUIRED).
MySQL Shell 8.3.0
Copyright (c) 2016, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
WARNING: Using a password on the command line interface can be insecure.
Creating a session to 'USER@HOSTNAME.mysql.database.azure.com:3306?ssl-mode=required'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 323
Server version: 8.0.35 Source distribution
No default schema selected; type \use <schema> to set one.
MySQL HOSTNAME.mysql.database.azure.com:3306 ssl JS >