store | blogs | forums | twitter | facebook | wiki | downloads | support portal
Atomic Secure Linux
It is currently Sat Oct 19, 2019 6:10 pm

» Feed - Atomicorp

All times are UTC - 5 hours [ DST ]




Post new topic Reply to topic  [ 2 posts ] 
Author Message
 Post subject: can't authenticate ssl user account
Unread postPosted: Mon Jul 20, 2015 10:59 pm 
Offline
New Forum User
New Forum User

Joined: Mon Jul 20, 2015 10:44 pm
Posts: 1
Location: New Jersey
I need to setup replication via SSL. 2 nodes master/master for H/A. Then 2 slave nodes for backup. Node 3 will slave off of node 1, and node 4 will slave off of node 2 for redundant backups. Nodes 3 & 4 will store backups to a directory mapped to S3 via S3FS. All nodes are physical.

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:
    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


Using those keys I put together this my.cnf file. Haven't gotten around to configuring replication yet, as I have yet to get ssl logins to work.

Code:
    [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


Made sure mariadb could read the cert files:

Code:
    [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

And restarted mariadb. And if I take a look at my SSL variables in my mysql command line, everything is looking good.

Code:
    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)


And if I show status like ssl this is what I see on db1:

Code:
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)


If I create one replication user without SSL required, he is able to log in from node 2 to node 1 no problem:

Code:
 MariaDB [mysql]> grant replication slave on *.* to 'slave1'@'db2.example.com' identified by 'secret';
    Query OK, 0 rows affected (0.00 sec)


Quote:
[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)]>


If I show grants on this user I can confirm that SSL is not required

Code:
    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)


If I create the second slave user with the SSL requirement, and even flush privileges, I can not log into node 1 from node 2

Code:
    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:
    [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)


And if I look at the grants for this user I able to tell that ssl is required for him to be able to login:

Code:
    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)


If I take a look at the error log:

Code:
    [root@db1:~] #grep error /etc/my.cnf
    log-error=/var/log/mariadb/mariadb_error.log


Code:
        [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


I see absolutely NO indication as to why the login for the 'slave2' user (that requires SSL) is failing.

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.


Top
 Profile  
Reply with quote  
 Post subject: Re: can't authenticate ssl user account
Unread postPosted: Wed Jul 22, 2015 9:53 am 
Offline
Atomicorp Staff - Site Admin
Atomicorp Staff - Site Admin

Joined: Wed Dec 31, 1969 8:00 pm
Posts: 8329
Location: earth
Do you even see the slave connecting to the master? Thats the first thing I'd check with a sniffer or something.


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 2 posts ] 

» Feed - Atomicorp

All times are UTC - 5 hours [ DST ]


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group