Tuesday, January 29, 2019

Passwordless authentication (supposedly) using mysql_config_editor

In the DBA operations world there are always challenges with passwords. How do you use the password for login, automation and operation scripts in a way that does not expose the password.

One of the MySQL utilities that addresses some of these questions is mysql_config_editor.

mysql_config_editor enables you to store authentication credentials in a login path file named .mylogin.cnf. On Linux these credentials are stored in the current user's home directory.

Installing mysql_config_editor

To install mysql_config_editor, all you need is the MySQL client installed. It can be particular to the MySQL flavor you use. I use Percona MySQL, so installing the Percona-Server-client rpm is enough for access to mysql_config_editor.

Common mysql_config_editor operations


To set up log in paths using mysql_config_editor 

mysql_config_editor set --login-path=monitor --host=XXXX --port=3306 --user=monitor --password
Enter password:

To log in using mysql_config_editor

 mysql --login-path=monitor
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 5087
 ...
 mysql>

To check what logins are set up in mysql_config_editor

mysql_config_editor print --all
[mysqlconn]
user = root
password = *****
host = localhost
[monitor]
user = monitor
password = *****
host = XXXX
port = 3306

Strong Passwords

The advantages of not having to remember or enter passwords are quite a few. For example, now a password can be a secure non dictionary string, that meets password validation rules.

The password for monitor can be B*kA2aBntGYdvJaf

It can be added to mysql_config_editor once using 
mysql_config_editor set --login-path=monitor --host=XXXX --port=3306 --user=monitor --password
Enter password:

And can be used thereafter by using - 
mysql --login-path=monitor

Using the password in scripts

This means that we can run scripts without providing the password. For example, if we were writing a script to take a backup or promote a slave, that will run on the database server. Here is an example - 
[root@ ~]# mysql --login-path=scripts -e "show slave status \G"
               Slave_IO_State:
                  Master_Host: XXXX
                  Master_User: XXXX
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: mysql_relay_log.000001

Risks and Caveats

While this is a comparatively better way of logins for automation scripts it is still not secure. This is the reason for the "supposedly" in the title of this post. While this is a convenient way, it is by no means completely secure. 

[root@ ~]# mysql_config_editor print --all
[mysqlconn]
user = root
password = *****
host = localhost
[monitor]
user = monitor
password = *****
host = XXXX
port = 3306

We can read the contents of this encrypted file using the my_print_defaults utility. 

[root@ ~]# my_print_defaults -s monitor
--user=monitor
--password=B*kA2aBntGYdvJaf
--host=XXXX
--port=3306

my_print_defaults is a part of standard MySQL install. 

For this reason even though mysql_config_editor saves the password in an encrypted file, it is recommended that this only be used for "root" linux user, and the logins saved in the mysql_config_editor be restricted by 'user'@'localhost'. The root access in these servers also needs to be tightly controlled and regulated. 

Resources:

MySQL storing time

Storing time in databases seems like a daunting task to many. The general principle with storing time in databases is - ALWAYS STORE TIME ...