Description:
In testing mysqlrouter against a router account using SSL certificate authentication, I can get the router to bootstrap, but the resulting configuration is invalid (router will not start). I cannot get the router to connect to the metadata service using the same connection that worked during bootstrap.
I think there are two bugs here:
1. bootstrap generates invalid config
2. the router config file does not properly support metadata polling with cert-based authentication, even though the command line does.
How to repeat:
I am testing on MacOS with the mysql binaries installed via homebrew and openssl@1.1
Create self-signed SSL CA & server certs following steps at: https://dev.mysql.com/doc/refman/8.0/en/creating-ssl-files-using-openssl.html
2x 8.0.29 sandbox instances running self-generated CA and server certs in a cluster
* deploySandboxInstance ports 3310 and 3311
* stop each instance, replace ca, key and cert files, restart sandbox intances
* create a 2 node innodb cluster between the two nodes using mysqlsh. I also created a clusterset, but I don't believe that's relevant.
Router account setup with NO PASSWORD
```plaintext
MySQL 127.0.0.1:3310 ssl JS > cluster.setupRouterAccount('router')
Missing the password for new account router@%. Please provide one.
Password for new account:
Confirm password:
Creating user router@%.
Account router@% was successfully created.
```
Router user altered to require subject and issuer
```plaintext
MySQL 127.0.0.1:3310 ssl JS > \sql alter user 'router'@'%' require subject '/L=development/O=Square/OU=testcluster001/CN=router' issuer '/C=US/ST=NY/L=Wellsville/O=Square/OU=ODS/CN=my ca'
Query OK, 0 rows affected (0.0039 sec)
```
Given router certs generated from the same CA:
```plaintext
~/tmp/testcerts/router ls
ca.pem client-cert.pem client-key.pem client-req.pem
~/tmp/testcerts/router openssl x509 -noout -subject -in client-cert.pem
subject= /L=development/O=Square/OU=testcluster001/CN=router
~/tmp/testcerts/router openssl x509 -noout -subject -in ca.pem
subject= /C=US/ST=NY/L=Wellsville/O=Square/OU=ODS/CN=my ca
```
Connection as ‘router’ with no password and certs fails as expected (ALTER USER worked):
```plaintext
mysql -h 127.0.0.1 -P 3310 -u router
ERROR 1045 (28000): Access denied for user 'router'@'localhost' (using password: NO)
```
Connection with the above cert, key, and ca works:
```plaintext
mysql -h 127.0.0.1 -P 3310 -u router --ssl-ca /Users/jayj/Tmp/testcerts/router/ca.pem --ssl-key /Users/jayj/Tmp/testcerts/router/client-key.pem --ssl-cert /Users/jayj/Tmp/testcerts/router/client-cert.pem
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 148
Server version: 8.0.29 Homebrew
Copyright (c) 2000, 2022, 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 '\h' for help. Type '\c' to clear the current input statement.
mysql> \s
--------------
mysql Ver 8.0.29 for macos12.2 on arm64 (Homebrew)
Connection id: 148
Current database:
Current user: router@localhost
SSL: Cipher in use is TLS_AES_256_GCM_SHA384
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.29 Homebrew
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
TCP port: 3310
Binary data as: Hexadecimal
Uptime: 7 min 44 sec
Threads: 14 Questions: 846 Slow queries: 0 Opens: 633 Flush tables: 3 Open tables: 552 Queries per second avg: 1.823
--------------
```
bootstrap the router using the certficates, note no password entered at prompts:
```plaintext
~ mysqlrouter --bootstrap router@127.0.0.1:3310 --account router --account-create never -d /Users/jayj/mysql-sandboxes/router --ssl-ca /Users/jayj/Tmp/testcerts/router/ca.pem --ssl-cert /Users/jayj/Tmp/testcerts/router/client-cert.pem --ssl-key /Users/jayj/Tmp/testcerts/router/client-key.pem
Please enter MySQL password for router:
# Bootstrapping MySQL Router instance at '/Users/jayj/mysql-sandboxes/router'...
Please enter MySQL password for router:
- Verifying account (using it to run SQL queries that would be run by Router)
- Storing account in keyring
- Adjusting permissions of generated files
- Creating configuration /Users/jayj/mysql-sandboxes/router/mysqlrouter.conf
# MySQL Router configured for the ClusterSet 'testcs001'
After this MySQL Router has been started with the generated configuration
$ mysqlrouter -c /Users/jayj/mysql-sandboxes/router/mysqlrouter.conf
ClusterSet 'testcs001' can be reached by connecting to:
## MySQL Classic protocol
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447
## MySQL X protocol
- Read/Write Connections: localhost:6448
- Read/Only Connections: localhost:6449
```
The router config that is generated is then invalid when I issue the generated `start.sh`:
```plaintext
2022-07-01 08:38:37 io INFO [0x100944580] starting 10 io-threads, using backend 'poll'
2022-07-01 08:38:37 http_server INFO [0x100944580] listening on 0.0.0.0:8443
2022-07-01 08:38:37 main ERROR [0x100944580] Error: option 'metadata_cache.ssl_ca' is not supported
```
if I remove that line from the config, the router starts, but is unable to fetch metadata — even though I clearly was able to do so during the bootstrap:
```plaintext
2022-07-01 08:40:25 metadata_cache WARNING [0x16c903000] Failed connecting with Metadata Server 127.0.0.1:3310: Access denied for user 'router'@'localhost' (using password: NO) (1045)
2022-07-01 08:40:25 metadata_cache WARNING [0x16c903000] Failed connecting with Metadata Server 127.0.0.1:3311: Access denied for user 'router'@'localhost' (using password: NO) (1045)
2022-07-01 08:40:25 metadata_cache ERROR [0x16c903000] Failed fetching metadata from any of the 2 metadata servers.
```
