GUI clients can't authenticate with PAM plugin

Joshua -

The issue seems to be in the client-side plugin dialog that is needed for the clients in order to talk with PAM plugin on the server. This plugin is distributed together with the PAM plugin. Thus
1) is the dialog plugin available on the client?
2) does the authentication work if you use auth_pam_compat instead of auth_pam on the server? The _compat plugin uses the standard Oracle client-plugin on the client side instead of dialog.

Sergei, is it possible that this has been fixed by fixing bug 1155859?

Joshua,

Which OS MySQL Workbench, Sequel Pro and HeidiSQL are running on? We don't have support for Windows clients with auth_pam at the time as we don't provide windows version of dialog authentication plugin. But nothing prevents you from using auth_pam_compat instead. If you are using these clients on windows please make sure that dialog.so is present in the directory where client expect to find it.

Laurynas,
Bug 1155859 may come to game here too, but issue seems to be different for now.

I tested MySQL Workbench on Windows and Mac. Sequel Pro is Mac only. HeidiSQL is Win only. The databases exist on CentOS/Redhat servers.

Using auth_pam_compat works from local Linux command line with --enable-cleartext-plugin.
Sequel Pro also works.
HeidiSQL fails with "SQL Error (2058): Authentication plugin 'mysql_clear_password' can not be loaded:"
MySQL workbench on Windows test connection works but then when attempting to really connect it fails with an error similar to if the user account does not exist or the wrong password is supplied.
MySQL Workbench on Mac works.

I'm pretty sure the clients do not have the dialog plugin. Also I do not define the plugin_dir variable so Percona Server is using the default of /usr/lib64/mysql/plugin/ on the Red Hat 6.3 server. I will try upgrading the server to the latest 5.5.30 and see if that makes a difference.

I tried with the latest 55-5.5.30-rel30.2.500 and I am still unable to connect with auth_pam.so while authentication_pam.so from MySQL enterprise version does work with MySQL Workbench on Windows. It looks like bug 1155859 fixed in 5.5.30-30.2 is not related.

Would it be possible to estimate the effort needed to support a windows version of the dialog plugin?

Joshua -

Unfortunately at the moment Windows is not on our supported platform list and this is not going to change in the nearest future. If that's your main focus and goal, then I suggest to check with MariaDB. Their PAM plugin is virtually identical to ours and AFAIK they support Windows.

I already checked with WB 6.0.9 , Ubuntu 13.04 and PS 5.5.35 / PS 5.6.15. Its working for me. Check comments on below bug.
https://bugs.launchpad.net/percona-server/+bug/1216208

Please create a Mac OS X version of Percona Server and include the dialog plugin.

With a local copy of Percona Server present that includes the dialog plugin users would be able to connect to a remote server that requires the Percona PAM authentication plugin.

When running MySQL Workbench or Sequel Pro on OS X and trying to connect using a user that requires LDAP authentication it fails because the needed plugin is not available.

MySQL Workbench:
Authentication plugin 'dialog' cannot be loaded: dlopen(/usr/local/mysql/lib/plugin/dialog.so, 2): image not found

Sequel Pro:
MySQL said: Authentication plugin 'dialog' cannot be loaded: dlopen(/usr/local/mysql/lib/plugin/dialog.so, 2): image not found

The mysql client also fails due to the missing dialog.so plugin.

ERROR 2059 (HY000): Authentication plugin 'dialog' cannot be loaded

Reproduced the same issue with W7, WB 6.0.9 and MySQL 5.5.35

With ap_user, it can't connect to MySQL server via command prompt as well as workbench. Give below error.

C:\Users>mysql -h192.168.42.52 -uap_user -p
Enter password: ******
ERROR 2059 (HY000): Authentication plugin 'dialog' cannot be loaded: The specifed module could not be found.

With apc_user (compat), it can connect to server with --enable-cleartext-plugin via command prompt but getting error in workbench like "Access denied for user 'apc_user'@192.168.42.52 ...."

C:\Users\nilnandan>mysql -h192.168.42.52 -uapc_user -p
Enter password: *******
ERROR 2059 (HY000): Authentication plugin 'mysql_clear_password' cannot be loaded: plugin not enabled

C:\Users\nilnandan>mysql -h192.168.42.52 -uapc_user -p --enable-cleartext-plugin

Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 86
Server version: 5.5.35-33.0-log Percona Server (GPL), Release 33.0

Copyright (c) 2000, 2014, 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>

Reproduce the same issue with MySQL 5.6

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -h192.168.43.6 -uap_user -p
Enter password: ******
ERROR 2059 (HY000): Authentication plugin 'dialog' cannot be loaded: The specifi
ed module could not be found.

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -h192.168.43.6 -uapc_user -p --enable-cleartext-plugin
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 50
Server version: 5.6.16-64.0-553.saucy-log (Ubuntu)

Copyright (c) 2000, 2014, 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>

I have done a good amount of investigation on this issue since filing the bug. The issue is that workbench does not support the dialog plugin. Ubuntu workbench does work if the shared & client packages are installed. My guess is that workbench in Ubuntu uses the same library directory as mysql/percona while it does not in Windows. I'm not sure what path is used under windows. It's possible windows workbench doesn't support dynamic client plugin loading.

In any case there is a work around. Downloading the Percona or MariaDB source code and modifying auth_pam.c to reference mysql_clear_password instead of dialog and then compiling/installing. I'm not an expert in c or the mysql plugin api so I'm not sure why auth_pam_compat does not work the same.

static struct st_mysql_auth pam_auth_handler=
{
  MYSQL_AUTHENTICATION_INTERFACE_VERSION,
  "mysql_clear_password",
  &authenticate_user_with_pam_server
};

With this modified plugin command line mysql on Ubuntu, CentOS/Redhat, Windows (via 5.6 server install) and workbench on windows, mac, ubuntu as well as sequel pro on mac works. Heidi sql however fails, but does work with the plugin where dialog is used.

Possibly the auth_pam_compat plugin could be cloned from auth_pam and mysql_clear_password referenced directly instead of dialog? I'm not sure if this would break any functionality that the auth_pam_compat plugin currently offers though.

Joshua -

auth_pam_compat plugin already specified mysql_clear_password as its client-side plugin. Does it work for you under Windows and other platforms without any patching in Workbench?

Laurynas, even though the auth_pam_compat plugin uses mysql_clear_password for some reason it fails to work with mysql workbench. The error returned by workbench doesn't refer to cleartext or dialog. It just looks like a normal authentication failure, similar to when you type the wrong password. The auth_pam_compat plugin works properly from the command line in linux or windows though.

I assume there is something in the auth_pam code (which is missing from auth_pam_compat) that performs client/server handshaking in a way that mysql workbench expects.

Workbench fails for me on OS X.

Bug 1216208, which is marked as a duplicate of this one, reports that Workbench fails with auth_pam_compat

If I understand everything correctly, we have the following situation.
1) On systems where dialog is supported (Linuxes), everything works OK.
2) Systems where dialog is not supported, auth_pam_compat with mysql_clear_password - if it's enabled - is supposed to work, but comments #3, #11, and #13 say it does not. Incidentally, it appears that auth_pam_compat works with the same clients that auth_pam works with.

Thus we need to verify the following: a system setup where client-side mysql_clear_password fails to work with auth_pam_compat, but at the same time it works with other server-side auth plugins (as in patched in #11, or Oracle).

As for providing dialog or other systems, I am sorry to say it's not in our plans right now. I'd also suggest looking into MariaDB's dialog plugin, which should be compatible, and should be provided on more platforms.

Is there any way to encrypt the user's password when using auth_pam with LDAP?

Let me summarize what I have tested so far.

With WB 6.0.9 (Linux client), and PS 5.5.35 on Ubuntu 13.04
I can able to connect from Workbench to PS server with ap_user and apc_user(compact)

With WB 6.0.9 (Linux client), and PS 5.6.15 on Ubuntu 13.04
I can able to connect from Workbench to PS server with ap_user and apc_user(compact)

=========================Now with Window client===================

With WB 6.0.9 (W7 client) and PS 5.5.35 on Ubuntu 13.04

With ap_user, I can't connect to MySQL via command prompt as well as workbench. Gives below error.

C:\Users>mysql -h192.168.42.52 -uap_user -p
Enter password: ******
ERROR 2059 (HY000): Authentication plugin 'dialog' cannot be loaded: The specifed module could not be found.

But With apc_user (compat), I can connect to server with --enable-cleartext-plugin via command prompt

C:\Users\nilnandan>mysql -h192.168.42.52 -uapc_user -p --enable-cleartext-plugin

Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 86
Server version: 5.5.35-33.0-log Percona Server (GPL), Release 33.0

Copyright (c) 2000, 2014, 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>

But getting error in Workbench like "Access denied for user 'apc_user'@192.168.42.52 ...."

As per the above result, this bug is already verified but If anyone facing specific problem then I would suggest to create new bug with more clear test case.

MySQL Workbench 6.1 for MacOS X has following issues with auth_pam_compat:

1. by default cleartext plugin is not enabled, test connection fails with error "Authentication plugin 'mysql_clear_password' cannot be loaded: plugin not enabled"

2. in order to enable cleartext plugin I started workbench as following:
> LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1 /Applications/MySQLWorkbench.app/Contents/MacOS/MySQLWorkbench

3. after 2 done test connection works, but real connection still doesn't. Server-side debugging shows that for test connection workbench sent correct password, while for real connection password sent is empty.

So far it looks like Workbench cannot use cleartext plugin correctly.

This change
=== modified file 'plugin/percona-pam-for-mysql/src/auth_pam_compat.c'
--- plugin/percona-pam-for-mysql/src/auth_pam_compat.c 2013-12-23 22:05:57 +0000
+++ plugin/percona-pam-for-mysql/src/auth_pam_compat.c 2014-05-02 20:24:49 +0000
@@ -96,7 +96,7 @@
     resp->resp[pkt_len]= '\0';

     /* we could only guess whether password was used or not */
- data->info->password_used= PASSWORD_USED_NO_MENTION;
+ data->info->password_used= PASSWORD_USED_YES;
     ++(*num_talks);
   }

makes auth_pam_compat to work with Workbench. I have no ice why. In this case WB tries empty password first and then correct password twice.

Sequel Pro able to connect with auth_pam_compat just fine.

It looks like a bug in Workbench. DriverManager::getConnection throws authentication error only for errors with codes 1045, and 1044, but not for 1698 (ERROR 1698 (28000): Access denied for user/ER_ACCESS_DENIED_NO_PASSWORD_ERROR).

Workbench has following logic described in comments
  // In the 1st connection attempt, no password is supplied
  // If it fails, keychain is checked and used if it exists
  // If it fails, an interactive password request is made

Next connection attempted only in case of authentication error on previous step. Since driver manager does not consider error 1698 as authentication error, next authentication attempts do not performed and connection fails with general error.

http://bugs.mysql.com/bug.php?id=72536

Hello, this is marked as "fix released"
How can I have that fix?

Both auth_pam and auth_pam_compat are not working on latest mysql-workbench on mac

Alle, the original issue has been fixed. There could be a new regression, please log a new bug

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1353