4

First:

  • We're running MySQL 5.7.13.
  • OS is Red Hat Enterprise Linux 7.2.
  • The problem was first discovered using Python/Connector 2.1.3
  • System is not domain joined (yet.)
  • All MySQL packages installed from MySQL's own yum repositories!

The scenario: I am attempting to build a simple monitoring solution to keep tabs on some aspects of a set of currently a hundred or so machines under my control (and probably increasing in number in the near future); I'm required to be able to produce reports to security, for instance, giving them package versions to prove that recently disclosed vulnerabilities have been patched on x out of y machines.

I could draw this information out in real time with Ansible et al, but a) there's no guarantee that all machines will be connected at any given time, and b) management tools can give me current status but not necessarily historical data that I may need to report on. So the solution from my perspective is a database that can house records for each system; when a system is updated, we drop a new record into the db indicating the change, and then I can draw this out later.

To this end, we're using a MySQL database. Currently, we have a simple, barebones MySQL install with a small table set created and a single non-root user defined. The user list as defined in MySQL is:

select user, host, authentication_string from mysql.user;
+-------------+-----------+-------------------------------------------+
| user        | host      | authentication_string                     |
+-------------+-----------+-------------------------------------------+
| root        | localhost | *D971D136A477A4C205AEF706...              |
| mysql.sys   | localhost | *THISISNOTAVALIDPASSWORDT...              |
| pkg_manager | localhost | *E91158E2E26F343D6639E4BD...              |
+-------------+-----------+-------------------------------------------+

So there are no empty string usernames present.

The problem account in question is the pkg_manager account. Grant permissions on it are:

mysql> show grants for 'pkg_manager'@'localhost';
+-------------------------------------------------------------------------------------+
| Grants for pkg_manager@localhost                                                    |
+-------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'pkg_manager'@'localhost'                                     |
| GRANT SELECT, INSERT ON `panopticon`.`package` TO 'pkg_manager'@'localhost'         |
| GRANT SELECT, INSERT ON `panopticon`.`package_history` TO 'pkg_manager'@'localhost' |
+-------------------------------------------------------------------------------------+

The pkg_manager account connects with no problem, using a command line like:

[~]$ mysql -u pkg_manager -p --database=panopticon
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.13 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

However, I need to allow an automated tool the ability to sign into the database (it will be pulling a list of systems that are not currently up to date, checking which of those are currently online, and then pushing updates to them and recording each successful update.) The tool will be written in Python, and I have pulled down the Python 2.1.3 connector.

We built an options.cnf file for the tool, and stored it in a restricted directory (initially root:root 0700, currently a :. Initial tests of opening a connection in python through the connector with:

connection = mysql.connector.connect(option_files='/path/to/options.cnf')

resulted in an error 1045, SQLState 28000 message. (Actually, there were a few other things first, mostly involving specifying the socket path. Those, however, are cleared up and we know they are out of the way because now the MySQL log is reporting the failed connection attempts.)

We know that this is partially working, because the full details of the 1045 error are "Access denied for user 'pkg_manager'@'localhost' (using password: YES)". So it's reading the option file, it's identifying the pkg_manager account, and it's seeing the password entry in the file (if there is no password entry present, the end of the message becomes "(using password: NO)".

We then tried to simplify the problem by using the same options file, and creating a client section in it, specifying all of the same fields that are present in the connector_python section. This, too, failed:

mysql --defaults-file="/path/to/options.cnf"

However, we knew that the account worked when manually specifying the login information on the command line, as demonstrated above.

After many hours of searching for user experiences with login failures, we decided to try a hybrid approach - command line login using the --defaults file, but also specifying -p to force us to enter the password on the command line:

[~]$ mysql --defaults-file="/etc/pkg_manager/db_info/options.cnf" -p
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
...
mysql>

So, we know that the rest of the information being provided in the options conf file is correct. Simply running mysql --print-defaults (to determine if there are any other options files in the path search list that might interfere) results in zero startup options listed, so there are no other options to get in the way.

And at this point, I'm pretty much stuck. Official MySQL documentation explicitly states that you can store passwords unencrypted in an options file, in addition to the encrypted local login file: http://dev.mysql.com/doc/refman/5.7/en/password-security-user.html

Yet, clearly, it's not working. I've looked through the list of config file variables that can be set, and I don't see any that seem like they would prevent password auth from an options file. I've turned error logging up to 3, but no details are presented about connection issues besides a single line note indicating that a connection attempt from 'pkg_manager'@'localhost' was rejected.

Before I forget, here's the options file (sans password):

1 # Options file for pkg_manager, easy way to "securely" store database login info.
2 [connector_python]
3 user="pkg_manager"
4 password="*********"
5 database="panopticon"
6 host="localhost"
7 unix_socket="/var/lib/mysql/mysql.sock"
8 
9 [client]
10 user="pkg_manager"
11 password="*********"
12 database="panopticon"
13 host="localhost"
14 socket="/var/lib/mysql/mysql.sock"

And here's the /etc/my.cnf file:

1 # For advice on how to change settings please see
2 # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
3 
4 [mysqld]
5 #
6 # Remove leading # and set to the amount of RAM for the most important data
7 # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
8 # innodb_buffer_pool_size = 128M
9 #
10 # Remove leading # to turn on a very important data integrity option: logging
11 # changes to the binary log between backups.
12 # log_bin
13 #
14 # Remove leading # to set options mainly useful for reporting servers.
15 # The server defaults are faster for transactions and fast SELECTs.
16 # Adjust sizes as needed, experiment to find the optimal values.
17 # join_buffer_size = 128M
18 # sort_buffer_size = 2M
19 # read_rnd_buffer_size = 2M
20 datadir=/data/panopticon
21 socket=/var/lib/mysql/mysql.sock
22 
23 skip-networking
24 
25 # Disabling symbolic-links is recommended to prevent assorted security risks
26 symbolic-links=0
27 
28 log-error=/var/log/mysqld.log
29 log_error_verbosity=3
30 general-log=1
31 general_log_file=/var/log/mysql_general.log
32 pid-file=/var/run/mysqld/mysqld.pid
1

I finally got a chance to put a few more hours into looking at the problem, and I have found the solution. As I had figured, it's frustratingly simple, but it also goes against the advice posted on multiple user forums and even on the MySQL reference documentation.

If my password is stored in the options file in quotation marks, it will fail to connect. When I remove the quotation marks from the password, it connects. I ended up going through the Python connector code before I realized this, mostly because I had tried removing the quotes on the password earlier, but for a slightly different problem, and then getting the two issues a bit conflated in my mind.

I noted, while going through the Python connector code, that I could directly instantiate an options file parser, so I gave that a try (passing the path to my options file as a param.) When I did, I saw that all options, including the password, were imported into the parser state with any quotations they had in the file. However, at no stage after that were steps taken to remove the quotes from the password (at least that I saw), so it seems likely that when the connector opens the options file with the parser, the quotation marks are included and passed on to the server for authentication as part of the password.

I'll most likely take this as a bug report to the MySQL devs, as all other options work fine with quotes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.