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.