[comment]: # (tags: ssl) [comment]: # ({f745f323-f745f323}) # 1 MySQL encryption configuration [comment]: # ({/f745f323-f745f323}) [comment]: # ({355d8b52-f87c5964}) ### Overview This section provides several encryption configuration examples for CentOS 8.2 and MySQL 8.4.0 and can be used as a quickstart guide for encrypting the connection to the database.  ::: noteimportant If MySQL host is set to localhost, encryption options will not be available. In this case a connection between Zabbix frontend and the database uses a socket file (on Unix) or shared memory (on Windows) and cannot be encrypted. ::: ::: noteclassic List of encryption combinations is not limited to the ones listed on this page. There are a lot more combinations available. ::: [comment]: # ({/355d8b52-f87c5964}) [comment]: # ({8cb2ef57-30b5ee7b}) ### Pre-requisites Install MySQL database from the [official repository](https://dev.mysql.com/downloads/repo/yum/). See [MySQL documentation](https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/) for details on how to use MySQL repo. MySQL server is ready to accept secure connections using a self-signed certificate. To see which users are using an encrypted connection, run the following query (Performance Schema should be turned ON): ```sqlmysql SELECT sbt.variable_value AS tls_version, t2.variable_value AS cipher, processlist_user AS user, processlist_host AS host FROM performance_schema.status_by_thread AS sbt JOIN performance_schema.threads AS t ON t.thread_id = sbt.thread_id JOIN performance_schema.status_by_thread AS t2 ON t2.thread_id = t.thread_id WHERE sbt.variable_name = 'Ssl_version' and t2.variable_name = 'Ssl_cipher' ORDER BY tls_version; ``` [comment]: # ({/8cb2ef57-30b5ee7b}) [comment]: # ({7b44fd81-fda36821}) ### Transport-only encryption [comment]: # ({/7b44fd81-fda36821}) [comment]: # ({195216c8-9ebeae19}) #### MySQL configuration Modern versions of the database are ready out-of-the-box for `required` [encryption mode](/manual/appendix/install/db_encrypt#terminology). A server-side certificate will be created after initial setup and launch. Create users and roles for the main components: ```sqlmysql mysql> CREATE USER   'zbx_srv'@'%' IDENTIFIED WITH caching_sha2_password BY '',   'zbx_web'@'%' IDENTIFIED WITH caching_sha2_password BY '' REQUIRE SSL   PASSWORD HISTORY 5; mysql> CREATE ROLE 'zbx_srv_role', 'zbx_web_role'; mysql> GRANT SELECT, UPDATE, DELETE, INSERT, CREATE, DROP, ALTER, INDEX, REFERENCES ON zabbix.* TO 'zbx_srv_role'; mysql> GRANT SELECT, UPDATE, DELETE, INSERT ON zabbix.* TO 'zbx_web_role'; mysql> GRANT 'zbx_srv_role' TO 'zbx_srv'@'%'; mysql> GRANT 'zbx_web_role' TO 'zbx_web'@'%'; mysql> SET DEFAULT ROLE 'zbx_srv_role' TO 'zbx_srv'@'%'; mysql> SET DEFAULT ROLE 'zbx_web_role' TO 'zbx_web'@'%'; ``` Note that the X.509 protocol is not used to check identity, but the user is configured to use only encrypted connections. See [MySQL documentation](https://dev.mysql.com/doc/refman/8.4/en/create-user.html#create-user-tls) for more details about configuring users. Run to check connection (socket connection cannot be used to test secure connections): ```bash mysql -u zbx_srv -p -h 10.211.55.9 --ssl-mode=REQUIRED ``` Check current status and available cipher suites: ```sqlmysql mysql> status -------------- mysql Ver 8.4.0 for Linux on x86_64 (MySQL Community Server - GPL) Connection id: 62 Current database: Current user: zbx_srv@bfdb.local SSL: Cipher in use is TLS_AES_256_GCM_SHA384 mysql> SHOW SESSION STATUS LIKE 'Ssl_cipher_list'\G; *************************** 1. row *************************** Variable_name: Ssl_cipher_list Value: TLS_AES_256_GCM_SHA384:TLS_CHACHA20_POLY1305_SHA256:TLS_AES_128_GCM_SHA256:TLS_AES_128_CCM_SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA384:DHE-RSA-AES128-GCM-SHA256:DHE-DSS-AES128-GCM-SHA256:DHE-RSA-AES128-SHA256:DHE-DSS-AES128-SHA256:DHE-DSS-AES256-GCM-SHA384:DHE-RSA-AES256-SHA256:DHE-DSS-AES256-SHA256:DHE-RSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-SHA:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES256-SHA:ECDHE-ECDSA-AES256-SHA:DHE-DSS-AES128-SHA:DHE-RSA-AES128-SHA:DHE-DSS-AES256-SHA:DHE-RSA-AES256-SHA:AES256-SHA:CAMELLIA256-SHA:CAMELLIA128-SHA:AES128-GCM-SHA256:AES256-GCM-SHA384:AES128-SHA256:AES256-SHA256:AES128-SHA 1 row in set (0.00 sec) ERROR: No query specified ``` [comment]: # ({/195216c8-9ebeae19}) [comment]: # ({f58390e3-68afdcc8}) #### Frontend To enable transport-only encryption for connections between Zabbix frontend and the database: - Check *Database TLS encryption* - Leave *Verify database certificate* unchecked ![](../../../../../assets/en/manual/appendix/install/encrypt_db_transport.png){width="600"} [comment]: # ({/f58390e3-68afdcc8}) [comment]: # ({28b4b1c0-99c0e65b}) #### Server To enable transport-only encryption for connections between server and the database, configure */etc/zabbix/zabbix\_server.conf*: ```ini ... DBHost=10.211.55.9 DBName=zabbix DBUser=zbx_srv DBPassword= DBTLSConnect=required ... ``` [comment]: # ({/28b4b1c0-99c0e65b}) [comment]: # ({627a05e9-fa9ec203}) ### Encryption with certificate authority verification Copy required MySQL CA to the Zabbix frontend server, assign proper permissions to allow the webserver to read this file. ::: noteclassic This mode doesn't work on RHEL 7 due to older MySQL libraries. ::: [comment]: # ({/627a05e9-fa9ec203}) [comment]: # ({4cb2ce06-9776435f}) #### Frontend To enable encryption with certificate verification for connections between Zabbix frontend and the database: - Check *Database TLS encryption* and *Verify database certificate* - Specify path to Database TLS CA file ![](../../../../../assets/en/manual/appendix/install/encrypt_db_verify_ca.png){width="600"} Alternatively, this can be set in */etc/zabbix/web/zabbix.conf.php*: ```php ... $DB['ENCRYPTION'] = true; $DB['KEY_FILE'] = ''; $DB['CERT_FILE'] = ''; $DB['CA_FILE'] = '/etc/ssl/mysql/ca.pem'; $DB['VERIFY_HOST'] = false; $DB['CIPHER_LIST'] = ''; ... ``` Troubleshoot user using command-line tool to check if connection is possible for required user: ```bash mysql -u zbx_web -p -h 10.211.55.9 --ssl-mode=REQUIRED --ssl-ca=/var/lib/mysql/ca.pem ``` [comment]: # ({/4cb2ce06-9776435f}) [comment]: # ({cfd9329e-a7a54f92}) #### Server To enable encryption with certificate verification for connections between Zabbix server and the database, configure */etc/zabbix/zabbix\_server.conf*: ```ini ... DBHost=10.211.55.9 DBName=zabbix DBUser=zbx_srv DBPassword= DBTLSConnect=verify_ca DBTLSCAFile=/etc/ssl/mysql/ca.pem ... ``` [comment]: # ({/cfd9329e-a7a54f92}) [comment]: # ({edf12bed-fa5d4760}) ### Encryption with full verification [comment]: # ({/edf12bed-fa5d4760}) [comment]: # ({1919f8f1-22fb0ddd}) #### MySQL configuration Set MySQL CE server configuration option (*/etc/my.cnf.d/server-tls.cnf*) to: ```ini [mysqld] ... # in this examples keys are located in the MySQL CE datadir directory ssl_ca=ca.pem ssl_cert=server-cert.pem ssl_key=server-key.pem require_secure_transport=ON tls_version=TLSv1.3 ... ``` Keys for the MySQL CE server and client (Zabbix frontend) should be created manually according to the MySQL CE documentation: [Creating SSL and RSA certificates and keys using MySQL](https://dev.mysql.com/doc/refman/8.4/en/creating-ssl-rsa-files-using-mysql.html) or[Creating SSL certificates and keys using openssl](https://dev.mysql.com/doc/refman/8.4/en/creating-ssl-files-using-openssl.html) ::: noteimportant MySQL server certificate should contain the Common Name field set to the FQDN name as Zabbix frontend will use the DNS name to communicate with the database or IP address of the database host. ::: Create MySQL user: ```sqlmysql mysql> CREATE USER 'zbx_srv'@'%' IDENTIFIED WITH caching_sha2_password BY '', 'zbx_web'@'%' IDENTIFIED WITH caching_sha2_password BY '' REQUIRE X509 PASSWORD HISTORY 5; ``` Check if it is possible to log in with that user: ```bash mysql -u zbx_web -p -h 10.211.55.9 --ssl-mode=VERIFY_IDENTITY --ssl-ca=/var/lib/mysql/ca.pem --ssl-cert=/var/lib/mysql/client-cert.pem --ssl-key=/var/lib/mysql/client-key.pem ``` [comment]: # ({/1919f8f1-22fb0ddd}) [comment]: # ({165e89bf-2f3a5c57}) #### Frontend To enable encryption with full verification for connections between Zabbix frontend and the database: - Check *Database TLS encryption* and *Verify database certificate* - Specify path to *Database TLS key file* - Specify path to *Database TLS CA file* - Specify path to *Database TLS certificate file* Note that *Database host verification* is checked and grayed out - this step cannot be skipped for MySQL. ::: notewarning If *Database TLS cipher list* field is left empty, the common ciphers permitted by both frontend (client) and server will be enabled. Alternatively, the ciphers can be set explicitly, in conformance with the [cipher configuration requirements](https://dev.mysql.com/doc/refman/8.4/en/encrypted-connection-protocols-ciphers.html#encrypted-connection-cipher-configuration). ::: ![](../../../../../assets/en/manual/appendix/install/encrypt_db_verify_full1.png){width=600} Alternatively, this can be set in */etc/zabbix/web/zabbix.conf.php*: ```php ... // Used for TLS connection with strictly defined Cipher list. $DB['ENCRYPTION'] = true; $DB['KEY_FILE'] = '/etc/ssl/mysql/client-key.pem'; $DB['CERT_FILE'] = '/etc/ssl/mysql/client-cert.pem'; $DB['CA_FILE'] = '/etc/ssl/mysql/ca.pem'; $DB['VERIFY_HOST'] = true; $DB['CIPHER_LIST'] = 'TLS_AES_256_GCM_SHA384:TLS_CHACHA20_POLY1305_SHA256:TLS_AES_128_GCM_SHA256:TLS_AES_128_CCM_SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA256:ECDHE-RSA-AES256-GC'; ... // or ... // Used for TLS connection without Cipher list defined - selected by MySQL server $DB['ENCRYPTION'] = true; $DB['KEY_FILE'] = '/etc/ssl/mysql/client-key.pem'; $DB['CERT_FILE'] = '/etc/ssl/mysql/client-cert.pem'; $DB['CA_FILE'] = '/etc/ssl/mysql/ca.pem'; $DB['VERIFY_HOST'] = true; $DB['CIPHER_LIST'] = ''; ... ``` [comment]: # ({/165e89bf-2f3a5c57}) [comment]: # ({e32ce094-c0090000}) #### Server To enable encryption with full verification for connections between Zabbix server and the database, configure */etc/zabbix/zabbix\_server.conf*: ```ini ... DBHost=10.211.55.9 DBName=zabbix DBUser=zbx_srv DBPassword= DBTLSConnect=verify_full DBTLSCAFile=/etc/ssl/mysql/ca.pem DBTLSCertFile=/etc/ssl/mysql/client-cert.pem DBTLSKeyFile=/etc/ssl/mysql/client-key.pem ... ``` [comment]: # ({/e32ce094-c0090000})