MariaDB with Active Directory authentication via PAM module

I needed to get mariadb authenticating users via Active Directory at work. Configuration was confusing until I stumbled across this article saying you can just tie into the system’s PAM configuration., which in my case is already configured for AD authentication. Awesome!

First, enable PAM plugin and restart mariadb:

/etc/my.cnf, anywhere in the mysqld section

plugin-load=auth_pam.so

Restart mariadb:

sudo systemctl restart mariadb

Next, configure a PAM file to interface with mariadb:

sudo vi /etc/pam.d/mysql
auth include system-auth 
account required pam_nologin.so 
account include system-auth 
password include system-auth 
session optional pam_keyinit.so force revoke 
session include system-auth 
session required pam_loginuid.so

Create catch all user in MariaDB and configure to use your PAM configuration:

CREATE USER ''@'%' IDENTIFIED VIA pam USING 'mysql';

Lastly, grant permissions in mariadb being sure to specify pam as the mechanism:

GRANT ALL PRIVILEGES on <database>.* to '<user>'@'<host>' IDENTIFIED VIA pam;

Profit.


Update 4-23-2019

You can use the pam_user_map module to grant permission to AD groups. This allows using Active Directory groups to completely manage permissions instead of creating users manually in the database. The procedure is outlined here.

Compile & Install pam_user_map module

sudo yum -y install gcc pam-devel  
wget https://raw.githubusercontent.com/MariaDB/server/10.4/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/

Create mysql user and grant it permissions you would like your group to have

CREATE USER '<DBUSER>'@'%' IDENTIFIED BY 'strongpassword';
GRANT ALL PRIVILEGES ON *.* TO '<DBUSER>'@'%' ;

CREATE USER ''@'%' IDENTIFIED VIA pam USING 'mariadb';
GRANT PROXY ON '<DBUSER>'@'%' TO ''@'%';

Configure pam_user_map user/group mappings by creating /etc/security/user_map.conf and add with group mappings. Note pam_user_map doesn’t tolerate special characters, such as the carat sign, which powerbroker uses to indicate spaces. I ended up just renaming the group to not have any spaces in it.

#/etc/security/user_map.conf  
@orig_pam_group_name: mapped_mariadb_user_name

Configure PAM to include pam_user_map.so as the last step in the process. Note the process I uploaded earlier doesn’t work well with groups, so here is my new process (I’m using Powerbroker Open for AD mapping)

auth        required      pam_lsass.so      try_first_pass
auth required pam_user_map.so debug
account required pam_permit.so

Note I’ve also included pam_permit.so so I didn’t need to create AD groups to match what I’ve configured in user_map.conf above.

2 thoughts on “MariaDB with Active Directory authentication via PAM module”

  1. What a great write up! You must must have been a “SUPER AWESOME LINUX GURU GUY!”

    Thanks for all the effort you put into these write ups as I’m sure they are appreciated by many.

    1. 😂 Haha yes in a past life that was my title. I don’t think I’ll ever have such a cool title again. Truly it was a golden age for me.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.