PAM: Use MySQL proxy user and LDAP failure

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.

So,

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

Hi Predrag,

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

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.

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.

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

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?

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?

No it is MySQL limit:

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

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

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

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

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.

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

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: +49 6196 9505 815, Facsimile: +49 6196 9505 894
Mobile: +49 174 3109 288, Skype: predrag.zecevic
E-mail: predrag.zecevic@2e-systems.com

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!