PAM: Use MySQL proxy user and LDAP failure

[Predrag Zecevic]

Hi,

I have used bzr to get source and have compiled plugin. Then i have installed it onto one server (and one client). That was easy part.

It 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 (used latest MySQL 5.5.28, this plugin should work with it?):
---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 set (and also my posixGroup). But, i have created another LDAP posixGroup, to fine grain access:
---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 (from access log):
---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 (according to docs this has to do it: "CREATE USER ''@'' IDENTIFIED WITH auth_pam AS 'mysql, mysql_ro=readonly';") - and thus proxy user is NOT used.

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...

Also, i still do NOT understand why we have to install plugin (at least dialog.so) on client side?
Isn't purpose of PAM client (on server box) to provide authorization against LDAP?

Many thanks for any suggestion, from anybody.

Regards.

[Predrag Zecevic (predrag-zecevic)]

Forgot to mention MySQL version of packages downloaded from dev.mysql.com (if that is relevant):

MySQL-client-5.5.28-1.el6.x86_64
MySQL-server-5.5.28-1.el6.x86_64
MySQL-shared-5.5.28-1.el6.x86_64
MySQL-shared-compat-5.5.28-1.el6.x86_64

All running on CentOS 6 x86_64

So, does anyone has idea how to solve this problem?

Thank you.

[Predrag Zecevic (predrag-zecevic)]

So,

this plug in is not in use, in this combination at all?

[Sergei Glushchenko (sergei.glushchenko)]

Hi Predrag,

Have you set up nss_ldap for the LDAP group membership to be available through NSS API?

[Predrag Zecevic (predrag-zecevic)]

Hi Sergei,

Server is CentOS 6.3, x64 (MySQL versions you have already).
NSS uses following file (i use symbolic links to cover all kind of clients with single conf file):

---8<---
$ ls -al /etc/*ldap*conf
lrwxrwxrwx 1 root root 13 Dec 14 12:12 /etc/ldap.conf -> pam_ldap.conf
-rw-r--r-- 1 root root 905 Dec 14 16:32 /etc/pam_ldap.conf
lrwxrwxrwx 1 root root 13 Dec 14 12:12 /etc/sudo-ldap.conf -> pam_ldap.conf

$ egrep -i "(unique|group)" /etc/pam_ldap.conf
# Group member attribute
pam_member_attribute uniquemember
---8<---

Not sure if that is enough.

Regards.

[Predrag Zecevic (predrag-zecevic)]

Ah yes,

i have created /etc/pam.d/mysql

---8<---
auth include password-auth
account include password-auth
---8<---

to use existing (working) setup, created with authconfig utility:
users are authenticated with ldao posix account/group, sudo uses ldap, host and service ACL set in ldap too, kerberos 5 authentication, etc

Documentation on http://www.percona.com/doc/percona-pam-for-mysql/manual.html page is kind of simple.

Regards.

[Sergei Glushchenko (sergei.glushchenko)]

Hi Predrag,

Currently PAM plugin have limitation. It can use in mapping only one group which user is member of, the one which could be obtained with `id -gn <username>`. I'm not familiar with LDAP set up, so I don't know whether you can or not workaround this. This bug report could be converted to feature request for PAM plugin to support mapping for full list of groups which could be listed with `id -Gn <username>`.

Thanks,
Sergei

[Predrag Zecevic (predrag-zecevic)]

HI Sergei,

does auth_pam plugin have something like user_map (like MariaDB has)?

---8<------
  And create /etc/security/user_map.conf with the desired mapping
  in the format: orig_user_name: mapped_user_name
---8<------

So i can map my users to some MySQL user (similar as i want to achieve)

Thanks and best regards.

P.S: If i open Feature request, how long it might take until it is implemented?

[Predrag Zecevic (predrag-zecevic)]

HI Sergei,

does auth_pam plugin have something like user_map (like MariaDB has)?

---8<------
  And create /etc/security/user_map.conf with the desired mapping
  in the format: orig_user_name: mapped_user_name
---8<------

So i can map my users to some MySQL user (similar as i want to achieve)

Also, this is probably auth_pam plugin limitation, or?

---8<---
mysql> GRANT SELECT ON test.* TO 'very_long_username'@'%' IDENTIFIED WITH auth_pam AS 'mysql';
ERROR 1470 (HY000): String 'very_long_username' is too long for user name (should be no longer than 16)
---8<---

On Linux, it works:
---8<---
$ useradd very_long_username
$ echo very_long_username | wc -c
19

$ getent passwd very_long_username
very_long_username:!!:10102:10102::/home/very_long_username:/bin/bash
---8<---

Thanks and best regards.

P.S: If i open Feature request, how long it might take until it is implemented?

[Predrag Zecevic (predrag-zecevic)]

No it is MySQL limit:

http://dev.mysql.com/doc/refman/5.5/en/user-names.html

[Sergei Glushchenko (sergei.glushchenko)]

HI Predrag,

As far as I can see pam_user_map in MariaDB is just a PAM module, which can be used to map users. So, one can build it into pam_user_map.so and include in /etc/pam.d/mysql. It should work for Percona PAM plugin too, because Percona PAM has support for PAM user mapping. Please fill free to fail bug report if it don't.

Thanks,
Sergei

[Predrag Zecevic (predrag-zecevic)]

Hi Sergei,

i need (still) explanation how MySQL 'proxy user' can be used with current plugin implementation (with all limitations it has) - IF that would be possible? If not, just stop reading here.

I have spent hours trying to make it working, but no luck.

Currently I have ended with this setup - just removed Kerberos things - not needed for MySQL authorization (FYI, everything ELSE on system using PAM/LDAP works fine):

---8<------</etc/pam.d/mysql>---
# Derived from password-auth-ac, used for mysql pam_auth

auth required pam_env.so
auth sufficient pam_unix.so nullok try_first_pass
auth requisite pam_succeed_if.so uid >= 500 quiet
auth sufficient pam_ldap.so use_first_pass
auth required pam_deny.so

account required pam_access.so
account required pam_unix.so broken_shadow
account sufficient pam_succeed_if.so uid < 500 quiet
account [default=bad success=ok user_unknown=ignore] pam_ldap.so
account required pam_permit.so

password requisite pam_cracklib.so try_first_pass retry=3 type=
password sufficient pam_unix.so sha512 nullok try_first_pass use_authtok
password sufficient pam_ldap.so use_authtok
password required pam_deny.so

session required pam_limits.so
session [success=1 default=ignore] pam_succeed_if.so service in crond quiet use_uid
session required pam_unix.so
session optional pam_ldap.so
---8<------</etc/pam.d/mysql>---

I am able to ssh (Kerberos 5 with LDAP backend DB authorization + PAM mkhomedir plugin) to box running MySQL server called 'auth_pam-test':
---8<---
$ who am i
predrag_zecevic pts/0 2013-02-12 15:53 (xxx.xxx.xxx)

$ id -gn
zecevic
---8<---

Working with MySQL 5.5.30, fresh installation:
---8<---
$ mysql -uroot mysql -e "show plugins"
...
| auth_pam | ACTIVE | AUTHENTICATION | auth_pam.so | GPL |
---8<---

Proxy user setup:
---8<------<SQL>---
CREATE USER ''@'' IDENTIFIED WITH 'auth_pam' AS 'mysql,zecevic=reader';

CREATE USER 'reader'@'localhost' IDENTIFIED BY 'reader_pass';
GRANT PROXY ON 'reader'@'localhost' TO ''@'';

FLUSH PRIVILEGES;
---8<------<SQL>---

From another box, when trying to connect, i get error (same in both cases: with and without proper password):

---8<---
$ mysql -hauth_pam-test -upredrag_zecevic -p test -e "SELECT USER(), CURRENT_USER(), @@proxy_user, @@external_user;"
Enter password:
ERROR 1045 (28000): Access denied for user 'predrag_zecevic'@'predrag-test.my_domain.com' (using password: YES)
---8<---

On 'auth_pam-test' box (from MySQL general log) you can see proxy user setup and my failed attempt::

---8<---
130212 16:55:05 6 Connect root@localhost on mysql
                    6 Query select @@version_comment limit 1
                    6 Query CREATE USER ''@'' IDENTIFIED WITH 'auth_pam' AS 'mysql,zecevic=reader'
                    6 Query CREATE USER 'reader'@'localhost' IDENTIFIED BY 'reader_pass'
                    6 Query GRANT PROXY ON 'reader'@'localhost' TO ''@''
                    6 Query FLUSH PRIVILEGES
                    6 Quit
130212 16:55:34 8 Connect ...

[Sergei Glushchenko (sergei.glushchenko)]

Hi Predrag,

PAM plugin did its part of work well as you can see in your log. The reason why you cannot login is very simple. You trying to login from remote host to user with access allowed only from localhost. Could you please add also user 'reader'@'%' and try to login again?

Thanks,
Sergei

[Predrag Zecevic (predrag-zecevic)]

Hi Sergei,

well, somehow i had impression that i wanna limit proxy user to localhost only.

I have fixed it, and it worked. Thank you for pointing that.
I will do some more tests now...

Regards.

[qjq (530080910-b)]

Hi Predrag,

I encountered the same situation and I also user 'reader'@'%'.but result is select @@proxy=null,proxy user is NOT used. i want ask you how you solve the problem.

Thanks,
JiaQi

[Predrag Zecevic (predrag-zecevic)]

Hi,

I just gave up. Could not wait any longer for proper solution.
Regards.

Am 25.12.2014 um 08:11 schrieb qjq:
> Hi Predrag,
>
> I encountered the same situation and I also user 'reader'@'%'.but
> result is select @@proxy=null,proxy user is NOT used. i want ask you
> how you solve the problem.
>
> Thanks,
> JiaQi
>

--
Predrag Zečević, Technical Support Analyst, 2e Systems GmbH

Telephone: [phone], Facsimile: [phone]
Mobile: [phone], Skype: predrag.zecevic
E-mail: [e-mail]

Headquarter: 2e Systems GmbH, Königsteiner Str. 87,
                       65812 Bad Soden am Taunus, Germany
Company registration: Amtsgericht Königstein (Germany), HRB 7303
Managing director: Phil Douglas

http://www.2e-systems.com/ - Making your business fly!