We're using mariadb-5.5.41 server on CentOS 7 hosts.
It's a neat plan!
However I am unable to get authentication to work with ssl required of the user.
We already have a CA certificate and key established in the environment. And we used those to generate the cert and key to be used with mariadb.
This is the process we used to generate the cert/key:
Code: Select all
openssl genrsa -des3 -out db1.example.com.key 4096
openssl req -new -key db1.example.com.key -out db1.example.com.csr
openssl x509 -req -days 3650 -in db1.example.com.csr -CA ca.crt -CAkey ca.key -set_serial 01 -out db1.example.com.crt
Code: Select all
[root@db1:~] #cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
ssl
ssl-ca=/opt/mysql/ca.crt
ssl-cert=/opt/mysql/db1.example.com.crt
ssl-key=/opt/mysql/db1.example.com.key
[mysqld_safe]
general_log_file=/var/log/mariadb/mariadb.log
general_log=1
log-error=/var/log/mariadb/mariadb_error.log
pid-file=/var/run/mariadb/mariadb.pid
log_slow_queries=/var/log/mysql/mysql-slow.log
long_query_time=2
log-queries-not-using-indexes
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
Code: Select all
[root@db1:~] #ls -ld /opt/mysql/ /opt/mysql/*
drwx------. 2 mysql mysql 86 Jul 20 06:20 /opt/mysql/
-r--------. 1 mysql mysql 2212 Jul 20 05:14 /opt/mysql/ca.crt
-r--------. 1 mysql mysql 1956 Jul 20 05:17 /opt/mysql/db1.example.com.crt
-r--------. 1 mysql mysql 3247 Jul 20 05:15 /opt/mysql/db1.example.com.key
Code: Select all
MariaDB [mysql]> show variables like '%ssl%';
+---------------+---------------------------------+
| Variable_name | Value |
+---------------+---------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /opt/mysql/ca.crt |
| ssl_capath | |
| ssl_cert | /opt/mysql/db1.example.com.crt |
| ssl_cipher | |
| ssl_key | /opt/mysql/db1.example.com.key |
+---------------+---------------------------------+
7 rows in set (0.00 sec)
Code: Select all
MariaDB [(none)]> show status like '%ssl%';
+--------------------------------+----------------------+
| Variable_name | Value |
+--------------------------------+----------------------+
| Com_show_processlist | 0 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 18446744073709551615 |
| Ssl_ctx_verify_mode | 5 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | SERVER |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 128 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
+--------------------------------+----------------------+
24 rows in set (0.00 sec)
Code: Select all
MariaDB [mysql]> grant replication slave on *.* to 'slave1'@'db2.example.com' identified by 'secret';
Query OK, 0 rows affected (0.00 sec)
If I show grants on this user I can confirm that SSL is not required[root@db2:~] #mysql -uslave1 -p -h db1.example.com
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 5.5.41-MariaDB MariaDB Server
Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
Code: Select all
MariaDB [mysql]> show grants for 'slave1'@'db2.example.com';
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for slave1@db2.example.com |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'db2.example.com' IDENTIFIED BY PASSWORD '*somelongpasswordhash' |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Code: Select all
MariaDB [mysql]> grant replication slave on *.* to 'slave2'@'db2.example.com' identified by 'test' require ssl;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Code: Select all
[root@db2:~] #mysql -uslave2 -p -h db1.example.com
Enter password:
ERROR 1045 (28000): Access denied for user 'slave2'@'db2.example.com' (using password: YES)
Code: Select all
MariaDB [mysql]> show grants for 'slave2'@'db2.example.com;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for slave2@db2.example.com |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'slave2'@'db2.example.com' IDENTIFIED BY PASSWORD '*somelongpasswordhash' REQUIRE SSL |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Code: Select all
[root@db1:~] #grep error /etc/my.cnf
log-error=/var/log/mariadb/mariadb_error.log
Code: Select all
[root@db1:~] #tail /var/log/mariadb/mariadb_error.log
150720 5:18:24 InnoDB: Initializing buffer pool, size = 128.0M
150720 5:18:24 InnoDB: Completed initialization of buffer pool
150720 5:18:24 InnoDB: highest supported file format is Barracuda.
150720 5:18:24 InnoDB: Waiting for the background threads to start
150720 5:18:25 Percona XtraDB (http://www.percona.com) 5.5.40-MariaDB-36.1 started; log sequence number 120637807
150720 5:18:25 [Note] Plugin 'FEEDBACK' is disabled.
150720 5:18:25 [Note] Server socket created on IP: '0.0.0.0'.
150720 5:18:25 [Note] Event Scheduler: Loaded 0 events
150720 5:18:25 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.41-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
So my questions are 1) how to I bump up the verbosity on the logs so I can get an indication as to why this is failing? 2) what is the best way to troubleshoot this?
OK so question 2 may seem a little redundant to question 1. But I am truly stumped.
Any help would be appreciated.