Percona Server for MySQL 8.0.19 includes the experimental simple LDAP authentication plugin, which allows for direct authentication using an LDAP server. Similarly to the data masking plugin added in 8.0.18, this plugin was also added as a community contribution from Francisco Miguel Biete.
Authenticating with LDAP
In earlier versions, Percona Server for MySQL supported the built-in authentication mechanism of MySQL and also included a port of the PAM plugin from MySQL enterprise.
As LDAP servers can be configured as backends for PAM, this already provided a way to authenticate SQL users using LDAP, by adding PAM as an intermediate layer — requiring setting up both the auth_pam plugin and configuring PAM to use LDAP for authenticating mysqld users.
With this plugin this process becomes simple, as LDAP authentication can be enabled by 3 simple steps:
- Install the LDAP authentication plugin
- Configure the LDAP server, as described in the plugin’s documentation
- CREATE a user using the LDAP authentication, or ALTER existing users
A simple example looks like the following:
1 2 3 4 5 6 7 | INSTALL PLUGIN authentication_ldap_simple SONAME 'authentication_ldap_simple.so'; SET GLOBAL authentication_ldap_simple_server_host = 'hostname'; SET GLOBAL authentication_ldap_simple_bind_base_dn = 'ou=people,dc=example,com=com'; CREATE USER ldapuser IDENTIFIED WITH ldap_simple BY 'cn=ldapuser,ou=people,dc=example,com=com'; |
Authentication Performance
By having three possible different authentication methods in the server, one might wonder if there is a performance benefit in using one over another. For example, since using LDAP is possible with two different plugins, which one is better?
To analyze this, we performed simple measurements. The measurement used a C++ client program created for this purpose, which connected to the server, executed a SELECT on an empty table, then disconnected — doing this repeatedly over and over, in multiple threads and processes.
We included both multiple processes and multiple thread test cases to verify that possible locks in the MySQL client library do not affect the test result: with this, we were able to verify that both multi-threaded and multi-process clients result in the same throughput.
The above graph shows the results of our measurements. It displays the execution time compared to the single process single-threaded execution of the default authentication test case, using a logarithmic scale.
We had to use a logarithmic scale because we noticed performance issues with the PAM plugin and also with all tested methods under high contention.
The first issue shows that the PAM plugin has a slow throughput compared to anything else: in the single-threaded test, it performed 15 times slower than the default plugin, and with 10 threads it was 57 times slower. We omitted PAM results with larger concurrency numbers as this is already above any other result.
This clearly shows the advantage of the new LDAP authentication plugin compared to the earlier PAM plugin.
For the other methods, the execution times only increase by 15-30% up to 20 threads or processes which results in a rapidly increasing throughput level. At its peak, the default plugin was able to handle around 100 000 connections per second, while the LDAP plugin measured at 60 000.
Above 20 concurrent tests, we can see a rapid increase of the execution time reaching more than 20 times the baseline at 100 threads, which results in a decreasing overall throughput.
This is also where we can see the advantage of thread pooling: while in the low concurrency levels (below 50) it performed worse than the default threading implementation, with 100 concurrent threads it performed more than 25% better: while the default threading implementation was only able to handle 23 000 login requests per second there, with thread pooling the server was able to perform more than 30 000 requests per second.
A Note on TCP Limitations
As shown, the tests initiated several hundred thousand connections within seconds. The LDAP authentication plugin creates new TCP connections for each authentication attempt, which can quickly result in the exhaustion of the entire port range. To use the LDAP authentication in a scenario like this, the net.ipv4.tcp_tw_reuse sysctl setting has to be 1.
Conclusion
Overall we can see that the LDAP authentication performs similarly to the default authentication method, and is viable for use in heavily loaded environments.
Is a user’s password sent in plain-text like auth_pam plugin?
Yes, the password is transferred in plain text. LDAP and PAM authentication should be used over TLS for additional security. My favourite option would be to alter the user to require ssl always.