Client software depends on server PAM plugin setup

From https://bugs.launchpad.net/bugs/988187
---8<----
Hi Predrag!

Thank you for your bugreport. I think you should report a separate bug
with your last comment. Also please provide following information:

1. Which version of plugin are you using? Did you build plugin from source or use packages? Which source tree/revision did you build?
2. Did you install client-side plugin on Solaris box?
3. Which exactly command did you use to create user?
4. Your PAM config for mysqld
5. Output of SELECT USER(), CURRENT_USER(), @@proxy_user; when you authenticated with PAM.

Regards,
Sergei

Hi Predrag,
Please note that for successfull communication during authentication we need both client-side plugin and server-side plugin installed. If you are using version 0.1 (available for download http://www.percona.com/downloads/Percona-PAM-plugin/0.1/), then both client-side and server-side plugins are located in auth_pam.so. For your Solaris box you should build it from source code.

Hi Sergei;

My environment is:
  * openindiana (OI, solaris) dekstop, mysql clients: 5.5.20 and 5.6.4-m7 (from dev.mysql.com)
  * VirtualBox guest: centos x86_64 desktop, mysql client 5.5.21 (from dev.mysql.com)
  * OpenVZ guest, centos x86_64 server, mysql 5.5.23 (also from dev.mysql.com)

  * kerberos 5 is enabled (using LDAP as backend) if that matters (i don't think so).

Answers:
1) Tried all variants (plugin downloaded from http://www.percona.com/downloads/Percona-PAM-plugin/0.1/)
  a) binary, x86_64 rpm for centos 6
  b) self compiled, also on VBox guest x86_64 centos 6.2, with ./configure --prefix=%{_libdir}/mysql/plugin --disable-static (have created spec file, MySQL-devel and pam-devel deployed)

2) no. i got no impression that i have to do it (at least from documentation available on http://www.percona.com/doc/percona-pam-for-mysql/index.html page). But, we can ignore OI for moment.

3) Users are created this way:
INSTALL PLUGIN auth_pam_server SONAME 'auth_pam.so';

CREATE USER 'micro'@'localhost' IDENTIFIED WITH auth_pam_server;
CREATE USER 'micro'@'predrag-ovz%' IDENTIFIED WITH auth_pam_server;
CREATE USER 'micro'@'solarix%' IDENTIFIED WITH auth_pam_server;
CREATE USER 'micro'@'predrag%' IDENTIFIED WITH auth_pam_server;
CREATE USER 'micro'@'predrag-skype%' IDENTIFIED WITH auth_pam_server;
CREATE USER 'micro'@'predrag-skype.my-domain.com' IDENTIFIED WITH auth_pam_server;
GRANT ALL PRIVILEGES ON *.* TO 'micro'@'predrag-skype%' IDENTIFIED by auth_pam;
GRANT ALL PRIVILEGES ON *.* TO 'micro'@'predrag-skype.my-domain.com' ;

User micro is, in LDAP, defined under ou=People,dc=my-domain,dc=com

4) cat /etc/pam.d/mysqld
auth required pam_warn.so
auth required pam_unix.so audit
account required pam_unix.so audit

5) That is exactly problem. I cannot establish connection using PAM.
  a) centos desktop -> centos server:
    mysql -hpredrag-ovz -umicro -e "select now()"
Password:
ERROR 1698 (28000): Access denied for user 'micro'@'predrag-skype.my-domain.com'

   centos-srv:/var/log/sercure (debug enabled in /etc/pam_ldap.conf)
Apr 26 08:56:10 predrag-ovz mysqld: pam_warn(mysqld:auth): function=[pam_sm_authenticate] service=[mysqld] terminal=[<unknown>] user=[micro] ruser=[micro] rhost=[predrag-skype.my-domain.com]
Apr 26 08:56:18 predrag-ovz mysqld: pam_unix(mysqld:auth): authentication failure; logname= uid=101 euid=101 tty= ruser=micro rhost=predrag-skype.my-domain.com user=micro

  b) everything on centos server
   mysql -hpredrag-ovz -umicro -NBe "select now()"
2012-04-26 09:02:22
  ## That do not ask for password at all ? ##

   mysql -umicro -NBe "select now()"
Password:
ERROR 2013 (HY000): Lost connection to MySQL server at 'sending authentication information', system error: 32

   /var/log/secure
Apr 26 09:03:26 predrag-ovz mysqld: pam_unix(mysqld:auth): conversation failed
Apr 26 09:03:26 predrag-ovz mysqld: pam_unix(mysqld:auth): unable to obtain a password
Apr 26 09:03:26 predrag-ovz mysqld: pam_unix(mysqld:auth): auth could not identify password for [micro]

  I have double checked: password i am using is saved in LDAP and i can connect to using openldap tools. Also, i am doing copy-paste ...

@Sergei,

we are using lot software (for example sudo) which authenticates via PAM to LDAP and no client action (until now) has to be performed...

I need explanation why?
Regards

The short answer is that's how pluggable authentication in MySQL work. For more details you can read http://dev.mysql.com/doc/refman/5.5/en/pluggable-authentication.html

Thanks.

That info is missing on documentation as well (or at least link to that page)

Just FYI (sorry for digression in this moment)

percona-pam-pluginn is dedicated to linux only.

i cannot compile it on OpenIndiana (solaris) because it depends on linux PAM headers (not present on OI):

gcc -DHAVE_CONFIG_H -I. -I. -I.. -DMYSQL_DYNAMIC_PLUGIN -isystem /opt/MySQL/5.6/include -Wall -Wextra -g -O2 -MT auth_pam_la-auth_pam.lo -MD -MP -MF .deps/auth_pam_la-auth_pam.Tpo -c auth_pam.c -fPIC -DPIC -o .libs/auth_pam_la-auth_pam.o
auth_pam.c:63:31: fatal error: security/pam_misc.h: No such file or directory

Do you have any experience on this? If yes,, can you give some lighs here.

Meantime, i will try to use linux only.

Regards.

And similar if i use Sun (Sol) Studio (MySQL5.6.5-m8 from dev.mysql.com). Tipp came from :

/opt/MySQL/5.6/bin/mysql_config --libmysqld-libs
-L/opt/MySQL/5.6/lib -lmysqld -lthread -lnsl -lm -lsocket -R'$ORIGIN/../lib' -R/opt/studio12u2/lib/stlport4/amd64

Error:
/opt/solstudio12.2/bin/cc -DHAVE_CONFIG_H -I. -I. -I.. -DMYSQL_DYNAMIC_PLUGIN /opt/MySQL/5.6/include -xO4 -m64 -mt -fsimple=1 -ftrap=%none -nofstore -xbuiltin=%none -xlibmil -xlibmopt -xtarget=generic -I/opt/MySQL/5.6/include -c auth_pam.c -KPIC -DPIC -o .libs/auth_pam_la-auth_pam.o
"auth_pam.c", line 63: cannot find include file: <security/pam_misc.h>
"auth_pam.c", line 78: #error: "Please add support for echo-less input for your platform"
cc: acomp failed for auth_pam.c
gmake[2]: *** [auth_pam_la-auth_pam.lo] Error 1
gmake[2]: Leaving directory `/export/NoBackup/Build/percona-pam-plugin-0.1/src'

Regards.

Predrag -

Yes, the PAM plugin is only supported on Linux currently.

Hi Laurynas,

what about Windows client? Or MacOS?
We have very heterogeneous environment and need to decide ...

Thanks for info and best regards.

Laurynas, look at he we've solved it. In MariaDB pam plugin (client side) should work everywhere.

At least it compiles on Solaris (in buildbot), it compiles and works on Windows (http://blog.montyprogram.com/security-with-two-step-verification/) and it compiles on Mac OS X and FreeBSD (with openpam, not linux-pam).

Predrag: and if you want to know why you need a client plugin for pam even though sudo, for example, doesn't - it's because sudo supports pam, it is linked with libpam. But mysql client and libmysql don't know anything about pam. Client plugin allows to extend them without recompiling. If you'd compile sudo without pam, you'd have to recompile it to add pam support.

@Sergei,

is that MariaDB PAM plugin portable to MySQL?

Regards.

BTW, thanks for clarifications about PAM plugin usage for MySQL.

Predrag -

We had success reports for Mac OS X.

Sergei -

Yes, the platform coupling we have between server and client plugins is not the ideal situation. We'll see what we can do about it.

Sorry to revive this thread.

Does any one have a how-to or a way to compile percona's PAM auth module for MySQL on FreeBSD 8.2/9.0?

Thanks!!

@Jordan,

Are you able to build it on FreeBSD with bzr HEAD? After -- https://bazaar.launchpad.net/~pam-for-mysql-developers/percona-pam-for-mysql/percona-pam-for-mysql/revision/26.2.1 -- it should work with openpam which freebsd uses. Provide us the logs if it still fails.

I have to report some progress here:

Used bzr to get source and have compiled it. Then i have installed it onto one server. That was easy part.

Moreover, i DOES proper authorization against LDAP and that part is working fine (single user use).

What is NOT working is use of MySQL proxy users (http://dev.mysql.com/doc/mysql-security-excerpt/5.5/en/pam-authentication-plugin-usage.html#pam-authentication-unix-with-proxy)

I have spend hours looking for solution, and it is still not clear me how user mapping has to be done...

My Setup on MySQL server side:
---8<---
mysql> CREATE USER ''@'' IDENTIFIED WITH auth_pam AS 'mysql, mysql_ro=readonly';
mysql> CREATE USER 'readonly'@'localhost' IDENTIFIED BY 'very secret password';
mysql> GRANT SELECT ON *.* TO 'readonly'@'localhost';
mysql> GRANT PROXY ON 'readonly'@'localhost' TO ''@'';
---8<---
In LDAP i have my posixAccount setup (and posixGroup). Bu, another LDAP posixGroup is created:
---8<---
dn: cn=mysql_ro,ou=Groups,dc=my-domain,dc=com
cn: mysql_ro
gidnumber: 223
memberuid: predrag_zecevic
objectclass: posixGroup
objectclass: top
---8<---
Is this OK? I would expect PAM plugin to check not only primary group but also secondary groups...

Then, when i connect to MySQL server, following attributes are asked from LDAP server:
---8<---
[17/Dec/2012:12:03:28 +0100] conn=40649 op=1 SRCH base="dc=my-domain,dc=com" scope=2 filter="(uid=predrag_zecevic)" attrs="host authorizedService shadowExpi
re shadowFlag shadowInactive shadowLastChange shadowMax shadowMin shadowWarning uidNumber"
[17/Dec/2012:12:03:28 +0100] conn=40649 op=1 RESULT err=0 tag=101 nentries=1 etime=0
---8<---

So, it has returned NO groups at all (although it has matched my account) and whole thing fails:
---8<---
mysql -hmy-host -upredrag_zecevic -pmy-password -e "SELECT USER(), CURRENT_USER(), @@proxy_user;"
+----------------------------------------+----------------+--------------+
| USER() | CURRENT_USER() | @@proxy_user |
+----------------------------------------+----------------+--------------+
| <email address hidden> | @ | NULL |
+----------------------------------------+----------------+--------------+
---8<---
I guess that mapping wasn't done at all: mysql> CREATE USER ''@'' IDENTIFIED WITH auth_pam AS 'mysql, mysql_ro=readonly';
and proxy user is not in use.

Do you have any idea how to fix this problem?

I am not sure if i have set LDAP entries (that is 389-DS from Fedora Project) for group mapping properly.
Documentation on Percona site is not of big usability...

Many thanks for any suggestion, from anybody.

Regards.

@Predrag,

interesting bug, however, can you report it as a separate issue (since it is not related to present)?

https://bugs.launchpad.net/percona-pam-for-mysql/+bug/1091566