Pam Authentication for Percona MySQL
Pam Installation and user group configuration
1. How to install pam plug on Percona MySQL server.
2. Configure shadow folder associated with Percona MySQL
3. Configure pam plugin service for mysqld service.
4. Identify Percona DB Group user.
6. Define user name and group name on linux.
7. login user as Jack and Tom to verify group.
Percona PAM Authentication Plugin is a free and Open Source implementation of the MySQL‘s authentication plugin. This plugin acts as a mediator between the MySQL server, the MySQL client, and the PAM stack. The server plugin requests authentication from the PAM stack, forwards any requests and messages from the PAM stack over the wire to the client (in cleartext) and reads back any replies for the PAM stack.
Supported Percona MySQL version PS:5.7.30-33-log
1. How to install pam plug on Percona MySQL server.
===============================
Pam auth install
===============================
mysql> INSTALL PLUGIN auth_pam SONAME 'auth_pam.so';
-- chekc result
mysql> show plugins;
| auth_pam | ACTIVE | AUTHENTICATION | auth_pam.so | GPL |
mysql> select * from plugin;
| auth_pam | auth_pam.so |
2. Configure shadow folder associated with Percona MySQL
===============================
check: shadow folder if ready
===============================
ls -al /etc/shadow
# chgrp mysql /etc/shadow
# chmod g+r /etc/shadow
-- Check result
cat /etc/shadow
sssd:!!:18430::::::
mysql:!!:18430::::::
3. Configure pam plugin service for mysqld service.
===========================================
check: edit file of mysqld on pam.d folder
===========================================
vi /etc/pam.d/mysqld
auth required pam_warn.so
auth required pam_unix.so audit
account required pam_unix.so audit
4. Identify Percona DB Group user.
[DB Group Defination]
dbgrp_dba
dbgrp_ops
dbgrp_developer
dbgrp_support
======================================================
Create Proxy User with pam authentication in MySQL
======================================================
CREATE USER ''@'' IDENTIFIED WITH auth_pam AS 'mysqld, pam_dba=dbgrp_dba, pam_ops=dbgrp_ops, pam_developer=dbgrp_developer, pam_support=dbgrp_support';
[Check user created ]
SELECT Host,User,ssl_type,plugin,authentication_string FROM mysql.user WHERE plugin = 'auth_pam' and authentication_string <> '';
===================================
Create DB Group User in MySQL
===================================
[Group User of dbgrp_dba]
CREATE USER 'dbgrp_dba' IDENTIFIED BY 'XXXXXX';
GRANT ALL PRIVILEGES ON *.* TO 'dbgrp_dba';
GRANT PROXY ON 'dbgrp_dba' TO ''@'';
[Group User of dbgrp_developer]
CREATE USER 'dbgrp_developer' IDENTIFIED BY 'YYYYYY';
GRANT ALL PRIVILEGES ON testA.* TO 'dbgrp_developer';
GRANT ALL PRIVILEGES ON testB.* TO 'dbgrp_developer';
GRANT ALL PRIVILEGES ON testC.* TO 'dbgrp_developer';
GRANT PROXY ON 'dbgrp_developer' TO ''@'';
[Check all user created]
select Host,User,ssl_type,plugin,authentication_string from mysql.user;
5. Restart Mysql Service .
================================================
Restart MySQL service after group user created
================================================
tail -f /var/lib/mysql/mysqld.log -- monitor Percona MySQL log
tail -f /var/log/secure | grep mysqld -- monitor pam access login
-- Restart Percona MySQL service
# systemctl restart mysqld.service
# Restult on 5.7
Jun 18 13:58:10 polkitd[872]: Registered Authentication Agent for unix-process:15895:5462648 (system bus name :1.238 [/usr/bin/pkttyagent --notify-fd 5 --fallback], object path /org/freedesktop/PolicyKit1/AuthenticationAgent, locale en_US.UTF-8)
Jun 18 13:58:11 polkitd[872]: Unregistered Authentication Agent for unix-process:15895:5462648 (system bus name :1.238, object path /org/freedesktop/PolicyKit1/AuthenticationAgent, locale en_US.UTF-8) (disconnected from bus)
Jun 18 14:00:31 groupmod[16456]: group changed in /etc/group (group sysop/500)
Jun 18 14:00:31 gpasswd[16462]: members of group sysop set by root to
Jun 18 14:00:31 groupmod[16468]: group changed in /etc/group (group apache/501)
Jun 18 14:00:31 gpasswd[16474]: members of group apache set by root to
Jun 18 14:00:31 groupmod[16480]: group changed in /etc/group (group sysop/500)
Jun 18 14:00:31 gpasswd[16486]: user apache added by root to group sysop
Jun 18 14:00:31 groupmod[16492]: group changed in /etc/group (group apache/501)
Jun 18 14:00:32 gpasswd[16498]: user sysop added by root to group apache
6. Define user name and group name on linux.
================================================
PAM User and Group setup on OS level
================================================
[Linux Pam Group Defination]
pam_dba
pam_developer
pam_support
[Linux User - for testing Percona 5.7]
[pam-Jake]
# useradd pam-Jake
# passwd pam-Jake
# groupadd pam_dba
# usermod -g pam_dba pam-Jake
[pam-Tom]
# useradd pam-Tom
# passwd pam-Tom
# groupadd pam_developer
# usermod -g pam_developer pam-Tom
7. login user as Jack and Tom to verify group.
================================================
PAM User login from remote server
================================================
# mysql -upam-Jack -p
mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
+------------------------+----------------+--------------+
| USER() | CURRENT_USER() | @@proxy_user |
+------------------------+----------------+--------------+
| pam-Jack@localhost | dbgrp_dba@localhost | ''@'' |
+------------------------+----------------+--------------+
# mysql -upam-Tom -p
mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
+------------------------+-----------------+--------------+
| USER() | CURRENT_USER() | @@proxy_user |
+------------------------+-----------------+--------------+
| pam-Tom@localhost | dbgrp_developer@localhost | ''@'' |
+------------------------+-----------------+--------------+