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
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;
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
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.
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.
4 thoughts on “MariaDB with Active Directory authentication via PAM module”
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.
😂 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.
I was doing this configuration myself for a while the updated one (Update 4-23-2019), but while i was doing it, i had the same errors over and over again. First one was the pam_user_map.h no such file or directory, and i searched the internet for its content and found nothing. The second one was the /lib64/security , dont have that directory. Do you have any tips to fix this problems? Kindest regards
Hi.. I am using PAM with Maria10.4 i get below error. Any guess what it might be
l1mar001:~ # mysql –host=l1mar001 –port=3100 –user=nswamy
ERROR 1045 (28000): Plugin dialog could not be loaded: /usr/local/mysql/lib/plugin/dialog.so: cannot open shared object file: No such file or directory