MySQL/MariaDB: настройка SSL

Автор: | 12/03/2018
 

После авторизации клиента передача данных между клиентом и сервером 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.pemCSR-файл сервера.

Подпсываем его – создаём публичную часть файл сертификата, используя созданный ранее 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]

Готово.