This is a demo tutorial to show how we can create InnoDB Cluster with newly installed Certificate and having X509 certificate verification via MySQL Router connection.
Recorded Video
The full process is recorded on Youtube - showing creating InnoDB Cluster with newly installed CA, Server Certificates. The Router creation is configured with SSL Server certificate from the same CA certificate across Server nodes. The creation of User (create user my509user identified by '....' require X509) using X509 certificate PASSTHROUGH verification via Router connection.
https://www.youtube.com/watch?v=w1xgpjw0VTw
Environment
The following environment was tested
Oracle Linux Server release 8.6
MySQL Server 8.0.31
MySQL Shell 8.0.31
MySQL Router 8.0.31
Github Script
The github script provides the steps to guide thru Installation of 3 nodes
https://github.com/ivanxma/mylab/tree/main/13-InnoDBCluster/99-SSL-cert-IC
To configure the node1/node2/node3 hostname under ./comm.sh (Change the hostname based on your environment)
```
export HOST1=workshop20
export HOST2=workshop22
export HOST3=workshop23
```
Background
The creation of InnoDB Cluster creates Internal User (mysql_innodb_cluster_<server_id>@'%')
Here is an example :
mysql> select user,host from mysql.user;
+--------------------------+-------------+
| user | host |
+--------------------------+-------------+
| gradmin | % |
| mysql_innodb_cluster_101 | % |
| mysql_innodb_cluster_201 | % |
| mysql_innodb_cluster_301 | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+--------------------------+-------------+
8 rows in set (0.00 sec)
Note : the RENAME USER does not work.
(e.g. mysql > rename user mysql_innodb_cluster_101@'%' to mysql_innodb_cluster_101@'10.0.%' )
As an example to restrict user creation with Host from subnet 192.0.2.0/24:
group_replication_ssl_mode
is configured as REQUIRED to ensure secured SSL between nodes.REQUIRED | Establish a secure connection if the server supports secure connections. |
group_replication_ssl_mode
can also be configured as VERIFY_CA to ensure certificate being used and verified.VERIFY_CA | Like |
Refer to CA creation [ Script ]Refer to Server certificate creation [ Script ]
mysql > create user my509user@'%' identified by 'my509pass' REQUIRE X509;
Alternatively, user creation can follow more constraint with
REQUIRE SUBJECT "/...."
REQUIRE ISSUER "/..."
For example (Check with the client certificate for the ISSUER or SUBJECT content [ openssl x509 -text -in <certificate file> ] )
example 1 : mysql > create user my509user@'%' identified by 'my509pass' REQUIRE ISSUER "/O=MySQL/CN=MySQL" ;
example 2 : mysql > create user my509user@'%' identified by 'my509pass' REQUIRE SUBJECT "/CN=hostname";
example 3 : mysql > create user my509user@'%' identified by 'my509pass' REQUIRE SUBJECT "/CN=hostname" AND ISSUER "/O=MySQL/CN=MySQL;
mysql -umy509user -pmy509pass -h127.0.0.1 -P6446 --ssl-mode=VERIFY_CA --ssl-ca=~/data/3310/ca.pem --ssl-cert=~/data/3310/client-cert.pem --ssl-key=~/data/3310/client-key.pem
REQUIRE X509 is not really user authentication, but just CA validation of the client cert.
ReplyDeleteI'd argue you'd need REQUIRE SUBJECT in your user grant authenticate a precise certificate is coming from the client. Router does not seem to play well with this concept: https://bugs.mysql.com/bug.php?id=108920
Thanks for this great comment. The article has just been modified to include your valuable info.
DeleteThis comment has been removed by the author.
ReplyDelete