Drizzle

mysql client does not work with auth_pam plugin activated

Asked by Theodotos Andreou

I have setup drizzle with the intention to replace mysql. Mysql community version do not support authentication through pam which can be achieved by the drizzle auth-pam plugin. There was also the requirement that users can create their own database. This can be achieved using the simple_policy plugin. But it seems that the auth-pam plugin does not work with protocols other than mysql-plugin-auth (cleartext). This creates a problem when the mysql client tries to connect because it fails. In this way I cannot use drizzle as a drop in replacement of mysql bacuse web apps like Wordpress depend or work similarly to the mysql client.

drizzle is running:

root@serv:~# ps aux | grep drizzle
root 29435 0.0 1.8 476124 38520 ? Ssl Jan25 4:23 /usr/sbin/drizzled

root@serv:~# netstat -lnptu | grep drizzle
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 28792/drizzled
tcp 0 0 127.0.0.1:4427 0.0.0.0:* LISTEN 28792/drizzled
tcp6 0 0 ::1:3306 :::* LISTEN 28792/drizzled
tcp6 0 0 ::1:4427 :::* LISTEN 28792/drizzled

Configuration:

root@serv:~# cat /etc/drizzle/conf.d/*
plugin-remove=auth_all
plugin-add=auth_pam
log-warnings
plugin-add=mysql_protocol
plugin-add=mysql_unix_socket_protocol
mysql-unix-socket-protocol.path=/var/run/mysqld/mysqld.sock
#plugin-add=regex_policy
plugin-add=simple_user_policy

Now when I run the mysql client it fails to connect:

This is with unix sockets:
root@serv:~# /usr/bin/mysql --password --user=root
Enter password:
ERROR 1045 (28000): Access denied for user 'root' (using password: YES)

And using port 3306:
root@lnx02ist00:~# /usr/bin/mysql --password --user=root --protocol=tcp
Enter password:
ERROR 1045 (28000): Access denied for user 'root' (using password: YES)

Same with the default drizzle protocol (mysql):
root@serv:~# drizzle --user=root --password
Enter password:
ERROR 1045 (28000): Access denied for user 'root' (using password: YES)

It work only when i use the plaintext mysq-plugin-auth protocol:
root@serv:~# drizzle --protocol=mysql-plugin-auth --user=root --password
Enter password:
Welcome to the Drizzle client.. Commands end with ; or \g.
Your Drizzle connection id is 12
Connection protocol: mysql-plugin-auth
Server version: 2011.03.13 Ubuntu

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

drizzle>

But the mysql-plugin-auth is not working with the original mysql-client or it's web based equivalent. Any ideas or workaround?

Question information

Language:
English Edit question
Status:
Solved
For:
Drizzle Edit question
Assignee:
No assignee Edit question
Solved by:
Henrik Ingo
Solved:
Last query:
Last reply:
Revision history for this message
Henrik Ingo (hingo) said : 		#1

Hi Theodotos

This is true, and it is a limitation of PAM. Also the closed source MySQL PAM plugin requires to send the password in cleartext. (In this regard one could say drizzled is in fact a drop in replacement :-) For comparison, consider that also when you login via SSH, the password is sent in cleartext between client and server, it is just that the communication channel is encrypted and secure.

You mentioned previously your user accounts originate in a Windows domain. If you are able to expose them as a LDAP directory, you could instead consider the auth_ldap module in Drizzle. (You should use the 7.1 beta releases as several bugs were fixed.) It allows you to use the standard MySQL protocol. A requirement is that you must maintaine a duplicate version of the password field, it needs to be hashed the MySQL way.

In summary, auth_ldap is a bit more complex, but more secure and transparent to the end user.

http://docs.drizzle.org/plugins/auth_ldap/index.html

Revision history for this message
Theodotos Andreou (theodotos) said : 		#2

That is another idea. I do have other services working over ldap so this could be the solution. I get some errors though.

This is my config:

cat /etc/drizzle/conf.d/auth-ldap.cnf
plugin-add=auth_ldap
auth-ldap.base-dn='DC=dom,DC=example,DC=com'
auth-ldap.bind-dn='CN=Driz,OU=service,DC=com,DC=example,DC=com'
auth-ldap.bind-password='MySecretPass'
auth-ldap.uri=ldap://dc.example.com

And the errors:

# drizzled
terminate called after throwing an instance of 'boost::exception_detail::clone_impl<boost::exception_detail::error_info_injector<boost::program_options::unknown_option> >'
  what(): unknown option auth-ldap.bind-dn
Ακυρώθηκε --> Translation: Canceled?

Is this a bug?

Here is the drizzle version

# drizzled -V
drizzled Ver 2011.03.13 for pc-linux-gnu on x86_64 (Ubuntu)

Revision history for this message
Best Henrik Ingo (hingo) said : 		#3

Yes, it's kind of a bug.

If you're using the stable version (drizzle7-2012.03.14) then auth-ldap.bind-dn is actually called auth-ldap.bind-db. You can change it to bind-db, or you should upgrade to our most recent beta release.

Revision history for this message
Theodotos Andreou (theodotos) said : 		#4

Good grief! I read about it in the docs but I assumed it does not apply to me! Thanks Henry! That solved my problem

Revision history for this message
Theodotos Andreou (theodotos) said : 		#5

Thanks Henrik Ingo, that solved my question.