После авторизации клиента передача данных между клиентом и сервером MySQL/MariaDB происходим в открытом виде, т.е. plaintext.
Если клиент и сервер расположены не на одном хосте и/или не в одной приватной сети – то данные, соответственно, можно перехватить и прочитать.
Содержание
Проверка plaintext
Проверим это.
Создаём тестовую базу:
[simterm]
MariaDB [(none)]> create database ssltest; Query OK, 1 row affected (0.01 sec)
[/simterm]
Добавляем пользователя с правами доступа с удалённого хоста:
[simterm]
MariaDB [(none)]> create user 'ssltest'@'194.***.***.45' identified by 'oR7le8ieh6ue'; Query OK, 0 rows affected (0.02 sec)
[/simterm]
Задаём права доступа к базе:
[simterm]
MariaDB [(none)]> grant all privileges on ssltest.* to 'ssltest'@'194.***.***.45'; Query OK, 0 rows affected (0.01 sec)
[/simterm]
Запускаем на хосте с MariaDB tcpdump:
[simterm]
# tcpdump -i eth0 port 3306 -s 65535 -x -X -n -q -tttt tcpdump: verbose output suppressed, use -v or -vv for full protocol decode listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
[/simterm]
Подключаемся с рабочей машины к этому интансу:
[simterm]
16:05:49 [setevoy@setevoy-arch-work ~/Work/LON.ADS] $ mysql -u ssltest -p -h 52.***.***.75 Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 4 Server version: 10.1.25-MariaDB-1~xenial mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
[/simterm]
И смотрим данные tcpdump:
…
0x0050: 0000 0000 0000 0000 7373 6c74 6573 7400 ……..ssltest.
0x0060: 145f 0ae8 2fe8 17c2 ddcb ad34 d610 92a1 ._../……4….
0x0070: f744 4c03 9c6d 7973 716c 5f6e 6174 6976 .DL..mysql_nativ
0x0080: 655f 7061 7373 776f 7264 0067 035f 6f73 e_password.g._os
…
Пароля в открытом виде мы не увидим, т.к. клиент передаёт только хеш пароля (см. ответ>>> на Stackoverflow, Authentication Method и Secure Password Authentication), а вот перехватить передаваемые данные можно, т.к. они пойдут в открытом виде (да и пользователь виден – ……..ssltest).
Например – запрашиваем список баз:
[simterm]
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | ssltest | +--------------------+
[/simterm]
И в выводе tcpdump:
…
2018-03-12 14:12:00.568225 IP 172.31.35.199.3306 > 194.***.***.45.54644: tcp 137
0x0000: 4508 00bd a3a0 4000 4006 7315 ac1f 23c7 E…..@[email protected]…#.
0x0010: c269 912d 0cea d574 f088 06bf 2e1b b80d .i.-…t……..
0x0020: 8018 01b5 242d 0000 0101 080a 0004 76a4 ….$-……..v.
0x0030: d694 03e7 0100 0001 014b 0000 0203 6465 ………K….de
0x0040: 6612 696e 666f 726d 6174 696f 6e5f 7363 f.information_sc
0x0050: 6865 6d61 0853 4348 454d 4154 4108 5343 hema.SCHEMATA.SC
0x0060: 4845 4d41 5441 0844 6174 6162 6173 650b HEMATA.Database.
0x0070: 5343 4845 4d41 5f4e 414d 450c 2100 c000 SCHEMA_NAME.!…
0x0080: 0000 fd01 0000 0000 0500 0003 fe00 0022 ……………”
0x0090: 0013 0000 0412 696e 666f 726d 6174 696f ……informatio
0x00a0: 6e5f 7363 6865 6d61 0800 0005 0773 736c n_schema…..ssl
0x00b0: 7465 7374 0500 0006 fe00 0022 00 test…….”.
…
information_schema…..ssltest – вот данные в открытом виде.
Соответственно, что бы избежать этого – необходимо включить принудительное шифрование данных между клиентом и сервером.
Настройка SSL в MariaDB
По умолчанию SSL в MariaDB/MySQL имеется, но отключен.
Проверить это можно по значению переменных ssl*:
[simterm]
MariaDB [(none)]> show variables like "%ssl%"; +---------------------+----------------------------+ | Variable_name | Value | +---------------------+----------------------------+ | have_openssl | YES | | have_ssl | DISABLED | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | | | version_ssl_library | OpenSSL 1.0.2g 1 Mar 2016 | +---------------------+----------------------------+
[/simterm]
have_openssl – сервер собран с поддержкой SSL, но сертификат и ключи не настроены, и потому have_ssl DISABLED.
И в status текущего подключения:
[simterm]
MariaDB [(none)]> status -------------- mysql Ver 15.1 Distrib 10.1.25-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 Connection id: 5 Current database: Current user: root@localhost SSL: Not in use ...
[/simterm]
Создание сертификатов
Создаём каталог для файлов ключей:
[simterm]
root@ip-172-31-35-199# cd /etc/mysql && mkdir ssl && cd ssl
[/simterm]
Создаём корневой (Central Authority) ключ:
[simterm]
root@ip-172-31-35-199:/etc/mysql/ssl# openssl genrsa 2048 > ca-key.pem Generating RSA private key, 2048 bit long modulus ........+++ .............................+++ e is 65537 (0x10001)
[/simterm]
Используя его – создаём CA сертификат, используя созданный только что ключ, в Common Name укажем MariaDB CA Root (просто для удобства):
[simterm]
root@ip-172-31-35-199:/etc/mysql/ssl# openssl req -new -x509 -nodes -days 365000 -key ca-key.pem -out ca-cert.pem You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [AU]: State or Province Name (full name) [Some-State]: Locality Name (eg, city) []: Organization Name (eg, company) [Internet Widgits Pty Ltd]: Organizational Unit Name (eg, section) []: Common Name (e.g. server FQDN or YOUR name) []:MariaDB CA Root Email Address []:
[/simterm]
Проверяем:
[simterm]
root@ip-172-31-35-199:/etc/mysql/ssl# ls -l total 8 -rw-r--r-- 1 root root 1302 Mar 12 15:00 ca-cert.pem -rw-r--r-- 1 root root 1675 Mar 12 14:58 ca-key.pem
[/simterm]
Тут у нас есть:
ca-cert.pem– Certificate Authority (CA) сертификатca-key.pem– Certificate Authority (CA) ключ
Далее создаём сертификат для самого сервера, укажем Common Name MariaDB Server:
[simterm]
root@ip-172-31-35-199:/etc/mysql/ssl# openssl req -newkey rsa:2048 -days 365000 -nodes -keyout server-key.pem -out server-req.pem Generating a 2048 bit RSA private key ....................+++ ..................+++ writing new private key to 'server-key.pem' ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [AU]: State or Province Name (full name) [Some-State]: Locality Name (eg, city) []: Organization Name (eg, company) [Internet Widgits Pty Ltd]: Organizational Unit Name (eg, section) []: Common Name (e.g. server FQDN or YOUR name) []:MariaDB Server Email Address []: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []:
[/simterm]
Проверяем:
[simterm]
root@ip-172-31-35-199:/etc/mysql/ssl# ls -l total 16 -rw-r--r-- 1 root root 1229 Mar 12 14:40 ca-cert.pem -rw-r--r-- 1 root root 1679 Mar 12 14:37 ca-key.pem -rw-r--r-- 1 root root 1704 Mar 12 14:42 server-key.pem -rw-r--r-- 1 root root 956 Mar 12 14:42 server-req.pem
[/simterm]
Тут у нас есть server-key.pem – приватный ключ сервера, server-req.pem – CSR-файл сервера.
Подпсываем его – создаём публичную часть файл сертификата, используя созданный ранее CA-ключ и ключ сервера:
[simterm]
root@ip-172-31-35-199:/etc/mysql/ssl# openssl x509 -req -in server-req.pem -days 365000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem Signature ok subject=/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=MariaDB Server Getting CA Private Key
[/simterm]
Теперь у нас есть:
server-cert.pem– сертификат MariaDB сервераserver-key.pem– приватный ключ для MariaDB сервера
Проверяем цепочку:
[simterm]
root@ip-172-31-35-199:/etc/mysql/ssl# openssl x509 -in server-cert.pem -text -noout
Certificate:
Data:
Version: 1 (0x0)
Serial Number: 1 (0x1)
Signature Algorithm: sha256WithRSAEncryption
Issuer: C=AU, ST=Some-State, O=Internet Widgits Pty Ltd, CN=MariaDB CA Root
Validity
Not Before: Mar 12 15:04:28 2018 GMT
Not After : Jul 13 15:04:28 3017 GMT
Subject: C=AU, ST=Some-State, O=Internet Widgits Pty Ltd, CN=MariaDB Server
...
[/simterm]
Выдан – Issuer: C=AU, ST=Some-State, O=Internet Widgits Pty Ltd, CN=MariaDB CA Root, кому – Subject: C=AU, ST=Some-State, O=Internet Widgits Pty Ltd, CN=MariaDB Server, с этим ОК.
Либо с помощь openssl verify:
[simterm]
root@ip-172-31-35-199:/etc/mysql/ssl# openssl verify -CAfile ca-cert.pem server-cert.pem server-cert.pem: OK
[/simterm]
Настройка сервера
Редактируем /etc/mysql/my.cnf и в блоке [mysqld] обновляем параметры SSL:
... ssl-ca=/etc/mysql/ssl/ca-cert.pem ssl-cert=/etc/mysql/ssl/server-cert.pem ssl-key=/etc/mysql/ssl/server-key.pem ...
Перезапускаем сервер:
[simterm]
root@ip-172-31-35-199:/etc/mysql/ssl# systemctl restart mysql
[/simterm]
Проверяем переменные SSL:
[simterm]
MariaDB [(none)]> show variables like "%ssl%"; +---------------------+--------------------------------+ | Variable_name | Value | +---------------------+--------------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /etc/mysql/ssl/ca-cert.pem | | ssl_capath | | | ssl_cert | /etc/mysql/ssl/server-cert.pem | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | /etc/mysql/ssl/server-key.pem | | version_ssl_library | OpenSSL 1.0.2g 1 Mar 2016 | +---------------------+--------------------------------+
[/simterm]
Настройка пользователя
Но если мы подключимся сейчас, и проверим status – SSL будет неактивен:
[simterm]
17:13:08 [setevoy@setevoy-arch-work ~/Work/LON.ADS] $ mysql -u ssltest -p -h 52.***.***.75 Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 6 Server version: 10.1.25-MariaDB-1~xenial mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> status -------------- mysql Ver 15.1 Distrib 10.1.31-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 6 Current database: Current user: ssltest@194.***.***.45 SSL: Not in use ...
[/simterm]
Проверяем привилегии пользователя:
[simterm]
MariaDB [(none)]> show grants for 'ssltest'@'194.***.***.45'; +---------------------------------------------------------------------------------------------------------------------+ | Grants for ssltest@194.***.***.45 | +---------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'ssltest'@'194.***.***.45' IDENTIFIED BY PASSWORD '*921DE8B63F3BD482B53B7D878EAE7D54173CE7DF' | | GRANT ALL PRIVILEGES ON `ssltest`.* TO 'ssltest'@'194.***.***.45' | +---------------------------------------------------------------------------------------------------------------------+
[/simterm]
Обновляем права – добавляем REQUIRE SSL:
[simterm]
MariaDB [(none)]> grant all privileges on ssltest.* to 'ssltest'@'194.***.***.45' identified by 'oR7le8ieh6ue' REQUIRE SSL; Query OK, 0 rows affected (0.00 sec)
[/simterm]
Проверяем ещё раз:
[simterm]
MariaDB [(none)]> show grants for 'ssltest'@'194.***.***.45'; +---------------------------------------------------------------------------------------------------------------------------------+ | Grants for ssltest@194.***.***.45 | +---------------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'ssltest'@'194.***.***.45' IDENTIFIED BY PASSWORD '*921DE8B63F3BD482B53B7D878EAE7D54173CE7DF' REQUIRE SSL | | GRANT ALL PRIVILEGES ON `ssltest`.* TO 'ssltest'@'194.***.***.45' | +---------------------------------------------------------------------------------------------------------------------------------+
[/simterm]
Отключаемся – и пробуем подключиться заново:
[simterm]
MariaDB [(none)]> show grants for 'user'@'host';Ctrl-C -- exit! Aborted 17:18:41 [setevoy@setevoy-arch-work ~/Work/LON.ADS] $ mysql -u ssltest -p -h 52.***.***.75 Enter password: ERROR 1045 (28000): Access denied for user 'ssltest'@'194.***.***.45' (using password: YES)
[/simterm]
ОК – подключение не работает, потому что на работу с SSL не настроен сам клиент.
Настройка MySQL клиента
На MariaDB хосте создаём сертификат клиента, используем Common Name MariaDB client:
[simterm]
root@ip-172-31-35-199:/etc/mysql/ssl# openssl req -newkey rsa:2048 -days 365000 -nodes -keyout client-key.pem -out client-req.pem Generating a 2048 bit RSA private key .................................................................................+++ .........................................................+++ writing new private key to 'client-key.pem' ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [AU]: State or Province Name (full name) [Some-State]: Locality Name (eg, city) []: Organization Name (eg, company) [Internet Widgits Pty Ltd]: Organizational Unit Name (eg, section) []: Common Name (e.g. server FQDN or YOUR name) []:MariaDB client Email Address []: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []:
[/simterm]
Подписываем клиентский сертификат:
[simterm]
root@ip-172-31-35-199:/etc/mysql/ssl# openssl x509 -req -in client-req.pem -days 365000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem Signature ok subject=/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=MariaDB client Getting CA Private Key
[/simterm]
Проверяем, что оба сертификата выданы нашим Central Authority (проверяем цепочку – chain of trust):
[simterm]
root@ip-172-31-35-199:/etc/mysql/ssl# openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem server-cert.pem: OK client-cert.pem: OK
[/simterm]
На рабочей мащине создаём каталог для сертификатов:
[simterm]
17:30:20 [setevoy@setevoy-arch-work ~] $ sudo mkdir /etc/mysql/ssl
[/simterm]
Копируем /etc/mysql/ssl/ca-cert.pem, /etc/mysql/ssl/client-cert.pem и /etc/mysql/ssl/client-key.pem:
[simterm]
17:31:07 [setevoy@setevoy-arch-work ~/Work/Bitbucket/aws-credentials] [master*] $ sudo scp -i rtfm-dev.pem ubuntu@52.***.***.75:/etc/mysql/ssl/{ca-cert.pem,client-cert.pem,client-key.pem} /etc/mysql/ssl/
The authenticity of host '52.***.***.75 (52.***.***.75)' can't be established.
ECDSA key fingerprint is SHA256:19BPYJm0CDFlaHJ+wlB3qEfGbScSdw8UGdflLlD4K4E.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '52.***.***.75' (ECDSA) to the list of known hosts.
ca-cert.pem 100% 1302 21.6KB/s 00:00
client-cert.pem 100% 1172 19.7KB/s 00:00
client-key.pem 100% 1704 28.7KB/s 00:00
[/simterm]
Проверяем файлы:
[simterm]
17:33:09 [setevoy@setevoy-arch-work ~/Work/Bitbucket/aws-credentials] [master*] $ cd /etc/mysql/ssl 17:33:13 [setevoy@setevoy-arch-work /etc/mysql/ssl] $ ls -l total 12 -rw-r--r-- 1 root root 1302 Mar 12 17:31 ca-cert.pem -rw-r--r-- 1 root root 1172 Mar 12 17:31 client-cert.pem -rw-r--r-- 1 root root 1704 Mar 12 17:31 client-key.pem
[/simterm]
Пробуем подключиться:
[simterm]
17:33:17 [setevoy@setevoy-arch-work /etc/mysql/ssl] $ mysql --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem -h 52.***.***.75 -u ssltest -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 4 Server version: 10.1.25-MariaDB-1~xenial mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
[/simterm]
Проверяем статус:
[simterm]
MariaDB [(none)]> status -------------- mysql Ver 15.1 Distrib 10.1.31-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 4 Current database: Current user: ssltest@194.***.***.45 SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384 ...
[/simterm]
SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384 – отлично, SSL активен, трафик шифруется.
В целом – готово.
Что бы клиент постоянно использовал SSL – редактируем /etc/mysql/my.cnf на машине с клиентом, указываем настройки SSL, как для сервера, только указываем ключи клиента:
[simterm]
17:35:26 [setevoy@setevoy-arch-work /etc/mysql/ssl] $ sudo vim /etc/mysql/my.cnf
[/simterm]
Указываем в блоке [client]:
... # The following options will be passed to all MariaDB clients [client] port = 3306 socket = /run/mysqld/mysqld.sock ssl-ca=/etc/mysql/ssl/ca-cert.pem ssl-cert=/etc/mysql/ssl/client-cert.pem ssl-key=/etc/mysql/ssl/client-key.pem ...
Переподключаемся:
[simterm]
17:38:03 [setevoy@setevoy-arch-work /etc/mysql/ssl] $ mysql -h 52.***.***.75 -u ssltest -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 5 Server version: 10.1.25-MariaDB-1~xenial mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
[/simterm]
Готово.
