MySQL TLS + CloudStack

mysql.notes.txt

# CloudStack + MySQL TLS setup, tested on Ubuntu 22.04

MySQL 8 will automatically create self-signed certificates for you, we just need to configure CloudStack to use mysql with server-side TLS mode enabled

# mysql --version;
mysql  Ver 8.0.37-0ubuntu0.22.04.3 for Linux on x86_64 ((Ubuntu))

# run mysql & mysql> SHOW VARIABLES LIKE '%ssl%'; to see if SSL is available

# mysql -u root -p --ssl-mode=required

# Next, check if TLS is available using \s;

mysql> \s;
--------------
mysql  Ver 8.0.37-0ubuntu0.22.04.3 for Linux on x86_64 ((Ubuntu))

Connection id:          19172
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.37-0ubuntu0.22.04.3 (Ubuntu)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /var/run/mysqld/mysqld.sock
Binary data as:         Hexadecimal
Uptime:                 26 days 1 hour 27 min 17 sec


# We can change/add the user to require SSL for example with:
> CREATE USER 'developer'@'192.168.0.100/255.255.255.0' IDENTIFIED BY 'yourpassword' REQUIRE SSL;
# or, like alter user 'my_user'@'%' REQUIRE SSL;

# Enforce security in mysqld
# cat /etc/mysql/mysql.conf.d/mysqld.cnf  | grep -i secure
require_secure_transport = ON

# Configure MySQL connector configuration in CloudStack's db.properties:
# cat /etc/cloudstack/management/db.properties | grep -i ssl
db.cloud.url.params=prepStmtCacheSize=517&cachePrepStmts=true&sessionVariables=sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'&serverTimezone=UTC&verifyServerCertificate=false&useSSL=true&sslMode=REQUIRED
db.cloud.useSSL=true

# Once verified, check if the 'cloud' user is accessing DB over SSL/TLS:
mysql> select * from performance_schema.threads where PROCESSLIST_USER="cloud"\G; # look for CONNECTION_TYPE

or, 
mysql> select t.THREAD_ID,
    ->     t.PROCESSLIST_USER,
    ->     t.PROCESSLIST_HOST,
    ->     t.CONNECTION_TYPE,
    ->     sbt.VARIABLE_VALUE AS cipher
    -> FROM performance_schema.threads t
    -> LEFT JOIN performance_schema.status_by_thread sbt
    ->     ON (t.THREAD_ID = sbt.THREAD_ID AND sbt.VARIABLE_NAME = 'Ssl_cipher')
    -> WHERE t.PROCESSLIST_USER IS NOT NULL;
+-----------+------------------+------------------+-----------------+------------------------+
| THREAD_ID | PROCESSLIST_USER | PROCESSLIST_HOST | CONNECTION_TYPE | cipher                 |
+-----------+------------------+------------------+-----------------+------------------------+
|        42 | event_scheduler  | localhost        | NULL            | NULL                   |
|        52 | cloud            | localhost        | SSL/TLS         | TLS_AES_256_GCM_SHA384 |
|        53 | cloud            | localhost        | SSL/TLS         | TLS_AES_256_GCM_SHA384 |
|        57 | cloud            | localhost        | SSL/TLS         | TLS_AES_256_GCM_SHA384 |
|        62 | cloud            | localhost        | SSL/TLS         | TLS_AES_256_GCM_SHA384 |
|        63 | cloud            | localhost        | SSL/TLS         | TLS_AES_256_GCM_SHA384 |
|        64 | cloud            | localhost        | SSL/TLS         | TLS_AES_256_GCM_SHA384 |
|        68 | cloud            | localhost        | SSL/TLS         | TLS_AES_256_GCM_SHA384 |
|        88 | root             | localhost        | Socket          |                        |
+-----------+------------------+------------------+-----------------+------------------------+
9 rows in set (0.00 sec)