Access denied for user 'user" using the auth_pam plugin

first: right, it means that the plugin is installed. In fact, a better way would be to run SHOW PLUGINS - because a plugin may be loaded and disabled (it's a general remark, I don't think it's the source of your problem).

Now:

There could be many reasons why it doesn't authenticate you.

What client do you use - mysql command-line client? From MariaDB distribution?

You connect as "mysql --user=user" ?

You don't have any password in ~/.my.cnf by any chance?

You have suid /sbin/unix_chkpwd binary (which means your pam is recent enough and does not require applications to be run as root to be able to verify password against /etc/shadow)?

You don't see anything relevant logged via syslog (in /var/log/messages for example)?

You have created /etc/pam.d/mysql that specifies pam_unix.so or includes system-login (or whatever you have in your distribution)?

Ok the plugin is installed:

MariaDB [(none)]> SHOW PLUGINS;
...
| pam | ACTIVE | AUTHENTICATION | auth_pam.so | GPL |

I use the mysql client compiled from the mariadb sources.

root@serv:~# mysql --user=user -p
Enter password:
ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: NO)

And the logs:
Feb 7 08:47:12 serv mysqld: PAM unable to dlopen(/lib/security/pam_cap.so): /lib/security/pam_cap.so: undefined symbol: pam_get_item
Feb 7 08:47:12 serv mysqld: PAM adding faulty module: /lib/security/pam_cap.so
Feb 7 08:47:12 serv unix_chkpwd[14656]: check pass; user unknown
Feb 7 08:47:12 serv unix_chkpwd[14656]: password check failed for user (user)
Feb 7 08:47:12 serv mysqld: pam_unix(mysql:auth): authentication failure; logname= uid=105 euid=105 tty= ruser= rhost=
user=user

But the same user authenticates successfully through ssh:
Feb 7 08:50:34 serv sshd[16797]: Accepted password for user from 10.100.1.66 port 41256 ssh2
Feb 7 08:50:34 serv sshd[16797]: pam_unix(sshd:session): session opened for user user by (uid=0)

It seems that mysql does not call pam_unix but some other modules. Any idea what this is about?

No password in my.cnf:
root@serv:~# grep -i pass /etc/mysql/my.cnf
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes

unix_chkpwd has SGID
root@serv:~# ls -la /sbin/unix_chkpwd
-rwxr-sr-x 1 root shadow 35488 2011-10-18 18:26 /sbin/unix_chkpwd

I haven' t created "/etc/pam.d/mysql". I have common-auth, common-account, commom-password, common-session (Ubuntu system)
They have these content:
root@serv:~# cat /etc/pam.d/common-{auth,account,password,session} | grep -v ^#

auth [success=2 default=ignore] pam_unix.so nullok_secure
auth [success=1 default=ignore] pam_winbind.so krb5_auth krb5_ccache_type=FILE cached_login try_first_pass
auth requisite pam_deny.so
auth required pam_permit.so
auth optional pam_mount.so
auth optional pam_cap.so

account [success=2 new_authtok_reqd=done default=ignore] pam_unix.so
account [success=1 new_authtok_reqd=done default=ignore] pam_winbind.so
account requisite pam_deny.so
account required pam_permit.so

password [success=2 default=ignore] pam_unix.so obscure sha512
password [success=1 default=ignore] pam_winbind.so use_authtok try_first_pass
password requisite pam_deny.so
password required pam_permit.so

session [default=1] pam_permit.so
session requisite pam_deny.so
session required pam_permit.so
session required pam_winbind.so
session required pam_unix.so
session requisite pam_mkhomedir.so skel=/etc/skel/ umask=0077
session optional pam_mount.so
session optional pam_ck_connector.so nox11

Which of these should be included in /etc/pam.d/mysql? All of them?

Another issue I have is that I have users that have longer than 16 characters in their username and they also have a "." (dot) between name and surname. The dot can be passed using backticks (``) but I have not found a workaround for the long usernames. These users are inherited from a Windows domain through winbind and cannot be renamed.

For example:

MariaDB [(none)]> CREATE USER `useridios.userides`@localhost IDENTIFIED VIA pam;
ERROR 1470 (HY000): String 'useridios.userides' is too long for user name (should be no longer than 16)

Any workaround for that?

mariadb does not call explicitly pam_unix or any other pam module. It tells pam subsystem to authenticate for a service "mysql" (or any other name that you can specify in CREATE USER).

I'm not sure what PAM does when it cannot find the config file for that service (/etc/pam.d/other perhaps).

You can either create /etc/pam.d/mysql or specify, say, CREATE USER ... IDENTIFIED VIA pam USING 'login' (to use /etc/pam.d/login). But I think that if PAM falls back to /etc/pam.d/other, it should be fine, and you don't have to do anything.

This might be a bug in pam_cap.so. At least this bug entry - https://bugs.launchpad.net/ubuntu/+source/libcap2/+bug/582769 - suggests that it is.

The log can be explained as follows: according to your common-auth, pam_cap.so can only be used if pam_unix (or pam_winbind) authenticate the user successfully. So, authentication succeedes, pam_unix says ok, pam goes down the list of modules, reaches the faulty pam_cap.so, fails to load it, writes to syslog. Then it uses common-account file, starts again from pam_unix, but username is corrupted because of pam_cap failure, and this time pam_unix (that is unix_chkpwd) fails.

A workaround to long user names would be a simple pam module that truncates use names - you put it in /etc/pam.d/mysql at the very end (I suppose). This module would only need to do something like (untested)

PAM_EXTERN int
pam_sm_acct_mgmt(pam_handle_t *pamh, int flags, int argc, const char *argv[])
{
    char *user;
    pam_get_item(pamh, PAM_USER, &user)
    if (strlen(user) > 16) user[16]=0;
    pam_set_item(pamh, PAM_USER, user); // probably redundant
    return PAM_SUCCESS;
}

Hi!

>>>>> "Theodotos" == Theodotos Andreou <email address hidden> writes:

Theodotos> Question #186961 on Maria changed:
Theodotos> https://answers.launchpad.net/maria/+question/186961

Theodotos> Theodotos Andreou posted a new comment:
Theodotos> Another issue I have is that I have users that have longer than 16
Theodotos> characters in their username and they also have a "." (dot) between name
Theodotos> and surname. The dot can be passed using backticks (``) but I have not
Theodotos> found a workaround for the long usernames. These users are inherited
Theodotos> from a Windows domain through winbind and cannot be renamed.

Theodotos> For example:

Theodotos> MariaDB [(none)]> CREATE USER `useridios.userides`@localhost IDENTIFIED VIA pam;
Theodotos> ERROR 1470 (HY000): String 'useridios.userides' is too long for user name (should be no longer than 16)

Theodotos> Any workaround for that?

You can fix that, but only by changing the MYSQL source and recompile

The things to fix are:

mysql_com.h:26:#define USERNAME_CHAR_LENGTH 16

In scripts/mysql_system_tables.sql you have to replace user(16) with
the new value.

If you have existing privilege tables in the mysql database, you have
to alter them to extend the name.

Regards,
Monty
Creator of MySQL and MariaDB