Configuring PAM Group Mapping with MariaDB
Author’s note: For the most up-to-date directions on setting up PAM authentication and user or group mapping with MariaDB, please see the relevant MariaDB documentation page.
Several months ago, I wrote a blog post about configuring PAM authentication and user mapping in MariaDB. While it is useful to map a system user account to a MariaDB user account, a lot of users actually wanted to be able to map all system users in a particular system group to the same MariaDB user account without mapping the system accounts individually. This was not supported by the PAM user mapping plugin when I wrote the original blog post, but this feature was recently added.
The new group mapping feature of the PAM user mapping plugin allows users to do things like map every system user in the dba system group to the dba MariaDB user account, while still requiring the system user’s unique password to log in. This can be a huge advantage, as shared/group passwords can be a huge security issue. In this blog post, I’ll explain how to set this kind of authentication up.
Identical configuration steps
If you would like to set up PAM group mapping with the PAM user mapping module, a lot of the configuration steps are identical to those explained in the last blog post. For example:
- The user mapping module is installed in the same way:
wget https://raw.githubusercontent.com/MariaDB/server/10.1/plugin/auth_pam/mapper/pam_user_map.c gcc pam_user_map.c -shared -lpam -fPIC -o pam_user_map.so sudo install --mode=0755 pam_user_map.so /lib64/security/
- The PAM policy is setup the same way:
sudo tee /etc/pam.d/mysql <<EOF auth required pam_unix.so audit account required pam_unix.so audit auth required pam_user_map.so EOF
- SELinux should still be disabled.
- The mysql user should still have access to /etc/shadow:
sudo groupadd shadow sudo usermod -a -G shadow mysql sudo chown root:shadow /etc/shadow sudo chmod g+r /etc/shadow
Create some test accounts
Now that the PAM user mapping module is setup, let’s create some Linux accounts to test things out:
# generic "dba" account to map other users to sudo useradd dba # generic "dba" group to add DBA users to sudo groupadd dba # a "real" account for Alice sudo useradd alice sudo passwd alice # a "real" account for Bob sudo useradd bob sudo passwd bob # add users to the group sudo usermod -a -G dba alice sudo usermod -a -G dba bob
Notice that the steps here are very similar to those described in the last blog post, but the alice and bob users have been added to the dba system group.
Configuring the group mapping
By default, the pam_user_map.so module still looks at /etc/security/user_map.conf for the mappings. Let’s map the dba system group to the dba MariaDB user account. To map a system group, prefix the group name with @:
sudo tee /etc/security/user_map.conf <<EOF @dba: dba EOF
Set up everything in MariaDB
Finally, let’s set up everything in MariaDB:
-- Install the plugin INSTALL SONAME 'auth_pam'; -- Create the "dba" user CREATE USER 'dba'@'%' IDENTIFIED BY 'strongpassword'; GRANT ALL PRIVILEGES ON *.* TO 'dba'@'%'; -- Create an anonymous catch-all user that will use the PAM plugin and the mysql policy CREATE USER ''@'%' IDENTIFIED VIA pam USING 'mysql'; -- Allow the anonymous user to proxy as the dba user GRANT PROXY ON 'dba'@'%' TO ''@'%';
If you changed the mysql user’s group membership earlier, you will also have to restart the MariaDB service:
sudo service mysql restart
Try it out
Now, let’s try it out. Even though we log in as alice and bob, our MariaDB privileges are actually those of the dba user:
[gmontee@localhost ~]$ mysql -u alice -h 127.0.0.1 [mariadb] Password: Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 12 Server version: 10.0.22-MariaDB-log MariaDB Server Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> SELECT USER(), CURRENT_USER(); +-----------------+----------------+ | USER() | CURRENT_USER() | +-----------------+----------------+ | alice@localhost | dba@% | +-----------------+----------------+ 1 row in set (0.00 sec) MariaDB [(none)]> q Bye [gmontee@localhost ~]$ mysql -u bob -h 127.0.0.1 [mariadb] Password: Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 13 Server version: 10.0.22-MariaDB-log MariaDB Server Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> SELECT USER(), CURRENT_USER(); +---------------+----------------+ | USER() | CURRENT_USER() | +---------------+----------------+ | bob@localhost | dba@% | +---------------+----------------+ 1 row in set (0.00 sec)
Now, if we add a new user account to the dba group, will that system user be recognized as dba by MariaDB without any additional changes?
Let’s try adding a new user called carol:
# a "real" account for Carol sudo useradd carol sudo passwd carol # add user to the group sudo usermod -a -G dba carol
Now let’s see if MariaDB will let carol log in:
[gmontee@localhost ~]$ mysql -u carol -h 127.0.0.1 [mariadb] Password: Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 14 Server version: 10.0.22-MariaDB-log MariaDB Server Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> SELECT USER(), CURRENT_USER(); +-----------------+----------------+ | USER() | CURRENT_USER() | +-----------------+----------------+ | carol@localhost | dba@% | +-----------------+----------------+ 1 row in set (0.00 sec)
It works!
Conclusion
This group mapping functionality should also work with external authentication systems that support PAM, such as Centrify or LDAP. This opens up additional possibilities such as mapping Active Directory groups to MariaDB user accounts.
If anyone tries this out, we would love to hear what you think about this feature.
