Wednesday, April 24, 2019

LDAP Authentication for MySQL

Why LDAP?

LDAP stands for Lightweight Directory Access Protocol. It is based on a client server model. A client queries LDAP server, which responds with an answer or with a pointer to where client can get more information. Most organizations have LDAP set up and configured for managing users and their credentials for internal applications. Using LDAP users can have single sign on for most applications.

When using LDAP with MySQL for authentication - the password management is offloaded to the LDAP service. Users that don't exist within the directory cannot access database. Password management functions such as enforcing a strong password, and password rotation can be off-loaded. 

How to configure LDAP with MySQL? 

If you are using Percona XtraDB i.e. Percona's flavor of MySQL, this can be easily done using the Percona PAM authentication plugin.

Installing Percona PAM authentication plugin

Percona PAM Authentication Plugin acts as a mediator between the MySQL server, the MySQL client, and the PAM stack. The server plugin requests authentication from the PAM stack, forwards any requests and messages from the PAM stack over the wire to the client (in cleartext) and reads back any replies for the PAM stack.

To install the plugin, verify that the plugin exists in the plugin directory. Then, 

mysql> INSTALL PLUGIN auth_pam SONAME 'auth_pam.so';
Query OK, 0 rows affected (0.08 sec)

mysql> INSTALL PLUGIN auth_pam_compat SONAME 'auth_pam_compat.so';
Query OK, 0 rows affected (0.05 sec)


Configure Percona PAM to authenticate to LDAP

Create a file /etc/pam.d/mysqld with the following content - 

auth    required pam_ldap.so audit
account required pam_ldap.so audit

Configure users using LDAP 

Users can be created in two ways -
  • Create user defined outside MySQL tables 
    • LDAP authentication enables MySQL servers to accept connections from users defined outside the MySQL tables in LDAP directories
  • Proxy user support
    • LDAP authentication can return to MySQL a user name different from the operating system user, based on the LDAP group of the external user.
    • For example, an LDAP user named stacy can connect and have the privileges of the MySQL user named dba_users , if the LDAP group for stacy is dba_users.  

Creating user defined outside of MySQL tables

mysql> CREATE USER 'yashada'@'%' IDENTIFIED WITH auth_pam;
Query OK, 0 rows affected (0.04 sec)

mysql> GRANT SELECT ON ycsb.* TO 'yashada'@'%';
Query OK, 0 rows affected (0.02 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql -u yashada –p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.

Creating proxy users

If belonging to a group needs certain MySQL privileges, setup proxy users instead to map a user’s privilege to its defined group. 

A good example of this is a DBA group, or a reporting_users group that needs read_only access. This offloads management of removal and addition of new users from the MySQL DBA and passes it on to LDAP.

mysql> CREATE USER ''@'' IDENTIFIED WITH auth_pam as 'mysqld,DBA=dba_users';
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE USER dba_users@'localhost' IDENTIFIED BY 'somepassword';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO dba_users@'localhost';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> GRANT PROXY ON dba_users@'localhost' TO ''@'';
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.05 sec)

When a user belonging to the group logs in - 

mysql -u yashada -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8587
mysql> show grants;
+-------------------------------------------------+
| Grants for @                                    |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO ''@''                     |
| GRANT PROXY ON 'dba_users'@'localhost' TO ''@'' |
+-------------------------------------------------+
2 rows in set (0.00 sec)

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:

LDAP Authentication for MySQL

Why LDAP? LDAP stands for Lightweight Directory Access Protocol. It is based on a client server model. A client queries LDAP server, whi...