MySQL and MariaDB authentication against pam_unix

The PAM authentication plugin is an extension included in MySQL Enterprise Edition (since 5.5) and in MariaDB (since 5.2).

• Pluggable authentication module
• Understanding PAM

MySQL authentication against pam_unix

• The PAM Authentication Plugin

Check if plugin is available:

# ll lib/plugin/auth*so
-rwxr-xr-x 1 mysql mysql 42937 Sep 18  2015 lib/plugin/authentication_pam.so
-rwxr-xr-x 1 mysql mysql 25643 Sep 18  2015 lib/plugin/auth.so
-rwxr-xr-x 1 mysql mysql 12388 Sep 18  2015 lib/plugin/auth_socket.so
-rwxr-xr-x 1 mysql mysql 25112 Sep 18  2015 lib/plugin/auth_test_plugin.so

Install PAM plugin:

mysql> INSTALL PLUGIN authentication_pam SONAME 'authentication_pam.so';

Check plugin information:

mysql> SELECT * FROM information_schema.plugins WHERE plugin_name = 'authentication_pam'<br>G
*************************** 1. row ***************************
           PLUGIN_NAME: authentication_pam
        PLUGIN_VERSION: 1.1
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: AUTHENTICATION
   PLUGIN_TYPE_VERSION: 1.1
        PLUGIN_LIBRARY: authentication_pam.so
PLUGIN_LIBRARY_VERSION: 1.7
         PLUGIN_AUTHOR: Georgi Kodinov
    PLUGIN_DESCRIPTION: PAM authentication plugin
        PLUGIN_LICENSE: PROPRIETARY
           LOAD_OPTION: ON

It seems like this set-up is persisted and survives a database restart because of the mysql schema table:

mysql> SELECT * FROM mysql.plugin;
+--------------------+-----------------------+
| name               | dl                    |
+--------------------+-----------------------+
| authentication_pam | authentication_pam.so |
+--------------------+-----------------------+

Configuring PAM on Ubuntu/Debian:

#%PAM-1.0
#
# /etc/pam.d/mysql
#
@include common-auth
@include common-account
@include common-session-noninteractive

Create the database user matching to the O/S user:

mysql> CREATE USER 'oli'@'localhost'
IDENTIFIED WITH authentication_pam AS 'mysql'
;
mysql> GRANT ALL PRIVILEGES ON test.* TO 'oli'@'localhost';

Verifying user in the database:

mysql> SELECT user, host, authentication_string FROM `mysql`.`user` WHERE user = 'oli';
+-----------+-----------+-------------------------------------------+
| user      | host      | authentication_string                     |
+-----------+-----------+-------------------------------------------+
| oli       | localhost | mysql                                     |
+-----------+-----------+-------------------------------------------+

mysql> SHOW CREATE USER 'oli'@'localhost';
+-----------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for oli@localhost                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER 'oli'@'localhost' IDENTIFIED WITH 'authentication_pam' AS 'mysql' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK |
+-----------------------------------------------------------------------------------------------------------------------------------+

Connection tests:

# mysql --user=oli --host=localhost
ERROR 2059 (HY000): Authentication plugin 'mysql_clear_password' cannot be loaded: plugin not enabled

# mysql --user=oli --host=localhost --enable-cleartext-plugin --password=wrong
ERROR 1045 (28000): Access denied for user 'oli'@'localhost' (using password: YES)

# tail /var/log/auth.log
Feb 13 15:15:14 chef unix_chkpwd[31600]: check pass; user unknown
Feb 13 15:15:14 chef unix_chkpwd[31600]: password check failed for user (oli)

# mysql --user=oli --host=localhost --enable-cleartext-plugin --password=rigth
ERROR 1045 (28000): Access denied for user 'oli'@'localhost' (using password: YES)

# tail /var/log/auth.log
Feb 13 15:15:40 chef unix_chkpwd[31968]: check pass; user unknown
Feb 13 15:15:40 chef unix_chkpwd[31968]: password check failed for user (oli)

Some research led to the following result: The non privileged mysql user is not allowed to access the file /etc/shadow thus it should be added to the group shadow to make it work:

# ll /sbin/unix_chkpwd
-rwxr-sr-x 1 root shadow 35536 Mar 16  2016 /sbin/unix_chkpwd

# usermod -a -G shadow mysql

• Comments - PAM Authentication Plugin
• MDEV-7032: new pam plugin with a suid wrapper

Connection tests:

# mysql --user=oli --host=localhost --enable-cleartext-plugin --password=rigth

mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
+---------------+----------------+--------------+
| USER()        | CURRENT_USER() | @@proxy_user |
+---------------+----------------+--------------+
| oli@localhost | oli@localhost  | NULL         |
+---------------+----------------+--------------+

MariaDB authentication against pam_unix

• PAM Authentication Plugin

Check if plugin is available:

# ll lib/plugin/auth*so
-rwxr-xr-x 1 mysql mysql 12462 Nov  4 14:37 lib/plugin/auth_0x0100.so
-rwxr-xr-x 1 mysql mysql 33039 Nov  4 14:37 lib/plugin/auth_gssapi_client.so
-rwxr-xr-x 1 mysql mysql 80814 Nov  4 14:37 lib/plugin/auth_gssapi.so
-rwxr-xr-x 1 mysql mysql 19015 Nov  4 14:37 lib/plugin/auth_pam.so
-rwxr-xr-x 1 mysql mysql 13028 Nov  4 14:37 lib/plugin/auth_socket.so
-rwxr-xr-x 1 mysql mysql 23521 Nov  4 14:37 lib/plugin/auth_test_plugin.so

Install PAM plugin:

mysql> INSTALL SONAME 'auth_pam';

Check plugin information:

mysql> SELECT * FROM information_schema.plugins WHERE plugin_name = 'pam'<br>G
*************************** 1. row ***************************
           PLUGIN_NAME: pam
        PLUGIN_VERSION: 1.0
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: AUTHENTICATION
   PLUGIN_TYPE_VERSION: 2.0
        PLUGIN_LIBRARY: auth_pam.so
PLUGIN_LIBRARY_VERSION: 1.11
         PLUGIN_AUTHOR: Sergei Golubchik
    PLUGIN_DESCRIPTION: PAM based authentication
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: ON
       PLUGIN_MATURITY: Stable
   PLUGIN_AUTH_VERSION: 1.0

Configuring PAM on Ubuntu/Debian:

#%PAM-1.0
#
# /etc/pam.d/mysql
#
@include common-auth
@include common-account
@include common-session-noninteractive

Create the database user matching to the O/S user:

mysql> CREATE USER 'oli'@'localhost'
IDENTIFIED VIA pam USING 'mariadb'
;
mysql> GRANT ALL PRIVILEGES ON test.* TO 'oli'@'localhost';

Verifying user in the database:

mysql> SELECT user, host, authentication_string FROM `mysql`.`user` WHERE user = 'oli';
+------+-----------+-----------------------+
| user | host      | authentication_string |
+------+-----------+-----------------------+
| oli  | localhost | mariadb               |
+------+-----------+-----------------------+

Connection tests:

# mysql --user=oli --host=localhost --password=wrong
ERROR 2059 (HY000): Authentication plugin 'dialog' cannot be loaded: /usr/local/mysql/lib/plugin/dialog.so: cannot open shared object file: No such file or directory

# tail /var/log/auth.log
Feb 13 17:11:16 chef mysqld: pam_unix(mariadb:auth): unexpected response from failed conversation function
Feb 13 17:11:16 chef mysqld: pam_unix(mariadb:auth): conversation failed
Feb 13 17:11:16 chef mysqld: pam_unix(mariadb:auth): auth could not identify password for [oli]
Feb 13 17:11:16 chef mysqld: pam_winbind(mariadb:auth): getting password (0x00000388)
Feb 13 17:11:16 chef mysqld: pam_winbind(mariadb:auth): Could not retrieve user's password

# mysql --user=oli --host=localhost --password=wrong --plugin-dir=$PWD/lib/plugin
ERROR 1045 (28000): Access denied for user 'oli'@'localhost' (using password: NO)
Feb 13 17:11:30 chef mysqld: pam_unix(mariadb:auth): authentication failure; logname= uid=1001 euid=1001 tty= ruser= rhost=  user=oli
Feb 13 17:11:30 chef mysqld: pam_winbind(mariadb:auth): getting password (0x00000388)
Feb 13 17:11:30 chef mysqld: pam_winbind(mariadb:auth): pam_get_item returned a password
Feb 13 17:11:30 chef mysqld: pam_winbind(mariadb:auth): request wbcLogonUser failed: WBC_ERR_AUTH_ERROR, PAM error: PAM_USER_UNKNOWN (10), NTSTATUS: NT_STATUS_NO_SUCH_USER, Error message was: No such user

Add mysql user to the shadow group:

# ll /sbin/unix_chkpwd
-rwxr-sr-x 1 root shadow 35536 Mar 16  2016 /sbin/unix_chkpwd

# usermod -a -G shadow mysql

Connection tests:

# mysql --user=oli --host=localhost --password=right --plugin-dir=$PWD/lib/plugin

mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
+---------------+----------------+--------------+
| USER()        | CURRENT_USER() | @@proxy_user |
+---------------+----------------+--------------+
| oli@localhost | oli@localhost  | NULL         |
+---------------+----------------+--------------+