Configuring PAM Authentication and User 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.

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[3332]: check pass; user unknown
Apr 14 12:56:23 localhost unix_chkpwd[3332]: 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)

Thoughts?

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?