December 18, 2015

Configuring PAM Group Mapping with MariaDB

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.

About Geoff Montee

Geoff Montee is a Support Engineer with MariaDB. He has previous experience as a Database Administrator/Software Engineer with the U.S. Government, and as a System Administrator and Software Developer at Florida State University.

Read all posts by Geoff Montee