MySQL Bugs: #70142: MySQL Workbench 6.0.6 PAM Auth Fails

Bug #70142	MySQL Workbench 6.0.6 PAM Auth Fails
Submitted:	24 Aug 2013 5:03	Modified:	12 Sep 2013 0:23
Reporter:	Jervin R	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	6.0.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When connecting to server with PAM authentication enabled - testing the connection works fine but trying to actually use the new connection i.e. double click from the list returns an access denied error.

Below is the log from PAM when access denied:

Aug 24 00:59:21 centos6 mysqld: pam_warn(mysqld:auth): function=[pam_sm_authenticate] service=[mysqld] terminal=[<unknown>] user=[apc_user] ruser=[apc_user] rhost=[localhost]
Aug 24 00:59:21 centos6 unix_chkpwd[12538]: password check failed for user (apc_user)
Aug 24 00:59:21 centos6 mysqld: pam_unix(mysqld:auth): authentication failure; logname= uid=497 euid=497 tty= ruser=apc_user rhost=localhost  user=apc_user

And is when I only do connection test:

Aug 24 01:00:35 centos6 mysqld: pam_warn(mysqld:auth): function=[pam_sm_authenticate] service=[mysqld] terminal=[<unknown>] user=[apc_user] ruser=[apc_user] rhost=[localhost]

Note this is tested only with Percona Server with its auth_pam_compat that is meant to be compatible with the one that comes with enterprise subscription from Oracle. So this may be Percona Server specific as well, I hope Oracle can verify if this is or not a bug with Workbench.

Percona Server 5.5.32 on Linux, WB 6.0.6 on Windows 7 x64

How to repeat:
1. Follow this procedure for the apc_user http://www.mysqlperformanceblog.com/2013/08/14/getting-percona-pam-to-work-with-percona-se...
2. Setup a connection on WB make sure to toggle enable cleartext authentication under Advanced tab.

Hello Jervin,
 
Thank you for your bug report.
I've tried to reproduce this issue with official MySQL builds and noticed that WB(latest GA, using on Win7) have no issues either in testing connection and/or connecting and executing queries against MySQL 5.5.33(GA) which is hosted on Oracle Linux 6.
 
 So, this seems to be Percona Server specific issue, could you try with official Oracle/MySQL builds? Please let us know how it goes.

// How to repeat

** ensure below parameter is included in my.cnf under mysqld group

[mysqld]
plugin-load=authentication_pam.so

** Bring up MySQL Server 5.5.33
** Ensure PAM plugin is loaded

mysql> select plugin_status from information_schema.plugins where plugin_name='authentication_pam';
+---------------+
| plugin_status |
+---------------+
| ACTIVE        |
+---------------+
1 row in set (0.00 sec)

mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.5.33-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> show plugins\G
..
*************************** 24. row ***************************
   Name: authentication_pam
 Status: ACTIVE
   Type: AUTHENTICATION
Library: authentication_pam.so
License: PROPRIETARY
24 rows in set (0.00 sec)

// Followed steps from http://dev.mysql.com/doc/refman/5.5/en/pam-authentication-plugin.html#pam-authentication-p...

** Verify that Unix authentication in PAM permits you to log in as ushastry with password *************. 

** Set up PAM to authenticate the mysql service. Put the following in /etc/pam.d/mysql: 
vi  /etc/pam.d/mysql

#%PAM-1.0
auth            include         password-auth
account         include         password-auth

** Create a MySQL account with the same user name as the Unix login name and define it to authenticate using the PAM plugin: 

// Created below user account

CREATE USER 'ushastry'@'localhost' IDENTIFIED WITH authentication_pam AS 'mysql';
CREATE USER 'ushastry'@'%' IDENTIFIED WITH authentication_pam AS 'mysql';
GRANT ALL PRIVILEGES ON *.* TO 'ushastry'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'ushastry'@'%';
FLUSH PRIVILEGES;

shell>export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1
shell>export AUTHENTICATION_PAM_LOG=1

** Try to connect to the MySQL server using the mysql command-line client. For example: 

[root@cluster-repo mysql-advanced-5.5.33]# bin/mysql -u ushastry -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.33-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2013, 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>
mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
+--------------------+--------------------+--------------+
| USER()             | CURRENT_USER()     | @@proxy_user |
+--------------------+--------------------+--------------+
| ushastry@localhost | ushastry@localhost | NULL         |
+--------------------+--------------------+--------------+
1 row in set (0.00 sec)

// Try to connect to the MySQL server using WB

Screenshot joining shortly

// Confirmed tables created

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)

mysql>

Thanks,
Umesh

WB Testing connection..

Attachment: PAM_1.png (image/png, text), 207.42 KiB.

WB connecting and executing queries..

Attachment: PAM_2.png (image/png, text), 194.08 KiB.

Unfortunately, I don't have access to an enterprise binary at the moment so I cannot confirm if repeatable there or not.

Umesh,

I noticed on your screenshot/procedure that the connection was only tested and not saved. The failure I was reporting was that during testing, the connection is OK but after saving and double-clicking the connection from the list it would then failed. Did  you get the same behavior?

Hello Jervin,

Please note that the static screenshot which I uploaded here were just to show that connection was tested and also able to connect using the saved connection.

I was able to:
 1. Test connection, and later saved connection
 2. Used saved connection to connect (double-clicking)

Thanks,
Umesh

Umesh, thanks for confirming.

This also affects me on version 6.0.7. Same setup with precona mysql-server 5.5.13 with auth_pam_compat enabled.

Testing and starting the command line connection works, opening the actual connection always ends in permission denied.
I can replicate this using WB on OS X and Linux.