You are here
MySQL and MariaDB authentication against pam_unix
Mon, 2017-02-13 18:02 — Shinguz
MySQL authentication against
MariaDB authentication against
The PAM authentication plugin is an extension included in MySQL Enterprise Edition (since 5.5) and in MariaDB (since 5.2).
MySQL authentication against pam_unix
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'\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
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
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'\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 | +---------------+----------------+--------------+
Taxonomy upgrade extras:
- Shinguz's blog
- Log in or register to post comments