User accounts in MariaDB have traditionally been completely separate from operating system accounts. However, MariaDB has included a PAM authentication plugin since version 5.2.10. With this plugin, DBAs can configure MariaDB user accounts to authenticate via PAM, allowing users to use their Linux username and password to log into the MariaDB server.
However, even when using the PAM authentication plugin, the user account still needs to exist in MariaDB, and the account needs to have privileges. Creating these MariaDB accounts and making sure the privileges are correct can be a lot of work. To decrease the amount of work involved, some users would like to be able to map a Linux user to a different MariaDB user. For example, let’s say that “alice” and “bob” are both DBAs. It would be nice if each of them could log into MariaDB with their own Linux username and password, while MariaDB sees both of them as the same “dba” user. That way, there is only one MariaDB account to keep track of.
Luckily, both PAM and MariaDB support exactly that kind of use case. In this blog post, I will walk you through how to set up this kind of authentication.
Set up the user mapper PAM plugin
MariaDB’s git repository has a simple user mapper PAM plugin. Downloading, compiling, and installing it is simple:
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/
Set up the PAM policy
We want to configure the PAM policy so that:
- Users authenticate with their Linux user names and passwords (i.e. use the pam_unix.so PAM module);
- Login attempts go into the system’s audit logs;
- “Real” user names will be mapped to MariaDB user names (i.e. use the pam_user_map.so PAM module).
We can create a PAM policy to do all of the above with:
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
Create some test accounts
Let’s create some Linux accounts to test things out:
# generic "dba" account to map other users to sudo useradd dba # a "real" account for Alice sudo useradd alice sudo passwd alice # a "real" account for Bob sudo useradd bob sudo passwd bob
Configuring the user account mapping
By default, the pam_user_map.so module looks at /etc/security/user_map.conf for the mappings. Let’s map both “alice” and “bob” to the “dba” user:
sudo tee /etc/security/user_map.conf <<EOF alice: dba bob: dba EOF
Turn off SELinux
Even with SELinux set to permissive mode, you can still run into issues while trying to use MariaDB and PAM together. You may want to disable SELinux entirely. Otherwise, you could have messages like this show up in your system logs:
Apr 14 12:37:45 localhost setroubleshoot: SELinux is preventing /usr/sbin/mysqld from execute access on the file . For complete SELinux messages. run sealert -l 807c6372-91d9-4445-b944-79113756d6c2 Apr 14 12:37:45 localhost python: SELinux is preventing /usr/sbin/mysqld from execute access on the file . ***** Plugin catchall_labels (83.8 confidence) suggests ******************* If you want to allow mysqld to have execute access on the file Then you need to change the label on $FIX_TARGET_PATH Do # semanage fcontext -a -t FILE_TYPE '$FIX_TARGET_PATH' where FILE_TYPE is one of the following: abrt_helper_exec_t, bin_t, boot_t, etc_runtime_t, etc_t, ld_so_t, lib_t, mysqld_exec_t, prelink_exec_t, shell_exec_t, src_t, system_conf_t, system_db_t, textrel_shlib_t, usr_t. Then execute: restorecon -v '$FIX_TARGET_PATH' ***** Plugin catchall (17.1 confidence) suggests ************************** If you believe that mysqld should be allowed execute access on the file by default. Then you should report this as a bug. You can generate a local policy module to allow this access. Do allow this access for now by executing: # grep mysqld /var/log/audit/audit.log | audit2allow -M mypol # semodule -i mypol.pp Apr 14 12:37:59 localhost setroubleshoot: Plugin Exception restorecon_source Apr 14 12:37:59 localhost setroubleshoot: SELinux is preventing /usr/sbin/unix_chkpwd from execute access on the file . For complete SELinux messages. run sealert -l c56fe6e0-c78c-4bdb-a80f-27ef86a1ea85 Apr 14 12:37:59 localhost python: SELinux is preventing /usr/sbin/unix_chkpwd from execute access on the file . ***** Plugin catchall (100. confidence) suggests ************************** If you believe that unix_chkpwd should be allowed execute access on the file by default. Then you should report this as a bug. You can generate a local policy module to allow this access. Do allow this access for now by executing: # grep unix_chkpwd /var/log/audit/audit.log | audit2allow -M mypol # semodule -i mypol.pp
Open up access to /etc/shadow
The pam_unix.so PAM module usually uses the unix_chkpwd utility to handle the authentication. This utility requires read access to /etc/shadow, which is usually unreadable for security reasons. To get PAM authentication to work with MariaDB, you will probably have to allow the mysql user to read this file. This is very easy to do:
sudo groupadd shadow sudo usermod -a -G shadow mysql sudo chown root:shadow /etc/shadow sudo chmod g+r /etc/shadow
Of course, opening up access to /etc/shadow to some users is a security risk. However, if you try to use PAM together with MariaDB without opening up this access, you are likely to see messages like this in the system logs:
Apr 14 12:56:23 localhost unix_chkpwd: check pass; user unknown Apr 14 12:56:23 localhost unix_chkpwd: password check failed for user (alice) Apr 14 12:56:23 localhost mysqld: pam_unix(mysql:auth): authentication failure; logname= uid=991 euid=991 tty= ruser= rhost= user=alice
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 ''@'%';
Since we changed the mysql user’s group membership, we 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”, 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 4 Server version: 10.0.17-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)
Is anyone using a setup like this? If so, how does it work for you? Can you think of any ways to improve this functionality?