Description\n-----------\n\nWe walk through the configuration of PAM authentication using the pam authentication plugin and user and group mapping with the pam_user_map PAM module. The primary authentication will be handled by the pam_unix PAM module, which performs standard Unix password authentication.\n\nHypothetical Requirements\n\nIn this walkthrough, we are going to assume the following hypothetical requirements:\n\n The Unix user foo should be mapped to the MariaDB user bar. (foo: bar)\n Any Unix user in the Unix group dba should be mapped to the MariaDB user dba. (@dba: dba)\n\nCreating Our Unix Users and Groups\n\nLet's go ahead and create the Unix users and groups that we are using for this hypothetical scenario.\n\nFirst, let's create the foo user and a couple users to go into the dba group. Note that each of these users needs a password.\n\n``bash\nsudo useradd foo\nsudo passwd foo\nsudo useradd alice\nsudo passwd alice\nsudo useradd bob\nsudo passwd bob\n`\n\nNext, let's create the dba group and add our two users to it:\n\n`bash\nsudo groupadd dba\nsudo usermod -a -G dba alice \nsudo usermod -a -G dba bob\n`\n\nWe also need to create Unix users with the same name as the bar and dba MariaDB users. See here to read more about why. No one will be logging in as these users, so they do not need passwords.\n\n`bash\nsudo useradd bar\nsudo useradd dba -g dba\n`\n\nInstalling the pam_user_map PAM Module\n\nNext, let's install the pam_user_map PAM module.\n\nBefore the module can be compiled from source, we may need to install some dependencies.\n\nOn RHEL, CentOS, and other similar Linux distributions that use RPM packages, we need to install gcc and pam-devel:\n\n`bash\nsudo yum install gcc pam-devel\n`\n\nOn Debian, Ubuntu, and other similar Linux distributions that use DEB packages, we need to install gcc and libpam0g-dev:\n\n`\nsudo apt-get install gcc libpam0g-dev\n`\n\nAnd then we can build and install the library with the following:\n\n`bash\nwget https://raw.githubusercontent.com/MariaDB/server/10.4/plugin/auth_pam/mapper/pam_user_map.c \ngcc pam_user_map.c -shared -lpam -fPIC -o pam_user_map.so \nsudo install --mode=0755 pam_user_map.so /lib64/security/\n`\n\nConfiguring the pam_user_map PAM Module\n\nNext, let's configure the pam_user_map PAM module based on our hypothetical requirements.\n\nThe configuration file for the pam_user_map PAM module is /etc/security/user_map.conf. Based on our hypothetical requirements, ours would look like:\n\n`\nfoo: bar\n@dba:dba\n`\n\nInstalling the PAM Authentication Plugin\n\nNext, let's install the pam authentication plugin.\n\nLog into the MariaDB Server and execute the following:\n\n`\nINSTALL SONAME 'auth_pam';\n`\n\nConfiguring the PAM Service\n\nNext, let's configure the PAM service. We will call our service mariadb, so our PAM service configuration file will be located at /etc/pam.d/mariadb on most systems.\n\nSince we are only doing Unix authentication with the pam_unix PAM module and group mapping with the pam_user_map PAM module, our configuration file would look like this:\n\n`ini\nauth required pam_unix.so audit\nauth required pam_user_map.so\naccount required pam_unix.so audit\n`\n\nConfiguring the pam_unix PAM Module\n\nThe pam_unix PAM module adds some additional configuration steps on a lot of systems. We basically have to give the user that runs mysqld access to /etc/shadow.\n\nIf the mysql user is running mysqld, then we can do that by executing the following:\n\n`bash\nsudo groupadd shadow\nsudo usermod -a -G shadow mysql\nsudo chown root:shadow /etc/shadow\nsudo chmod g+r /etc/shadow\n`\n\nThe server needs to be restarted for this change to take affect.\n\nCreating MariaDB Users\n\nNext, let's create the MariaDB users. Remember that our PAM service is called mariadb.\n\nFirst, let's create the MariaDB user for the user mapping: foo: bar . This means we need to create a bar user:\n\n`sql\nCREATE USER 'bar'@'%' IDENTIFIED BY 'strongpassword';\nGRANT ALL PRIVILEGES ON . TO 'bar'@'%' ;\n`\n\nNext, let's create the MariaDB user for the group mapping: @dba: dba . This means that we need to create a dba user:\n\n`sql\nCREATE USER 'dba'@'%' IDENTIFIED BY 'strongpassword';\nGRANT ALL PRIVILEGES ON . TO 'dba'@'%' ;\n`\n\nNext, to allow for the user and group mapping, we need to create an anonymous user that authenticates with the pam authentication plugin that is also able to PROXY as the bar and dba users. Before we can create the proxy user, we might need to clean up some defaults:\n\n`sql\nDELETE FROM mysql.db WHERE User='' AND Host='%';\nFLUSH PRIVILEGES;\n`\n\nFinally, let's create the anonymous proxy user:\n\n`sql\nCREATE USER ''@'%' IDENTIFIED VIA pam USING 'mariadb';\nGRANT PROXY ON 'bar'@'%' TO ''@'%';\nGRANT PROXY ON 'dba'@'%' TO ''@'%';\n`\n\nTesting our Configuration\n\nNext, let's test out our configuration by verifying that mapping is occurring. We can verify this by logging in as each of our users and comparing the return value of USER(), which is the original user name and the return value of CURRENT_USER(), which is the authenticated user name.\n\nFirst, let's test our foo user:\n\n`bash\n$ mysql -u foo -h 172.30.0.198\n[mariadb] Password:\nWelcome to the MariaDB monitor.  Commands end with ; or \\g.\nYour MariaDB connection id is 22\nServer version: 10.3.10-MariaDB MariaDB Server\n\nCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.\n\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\n\nMariaDB [(none)]> SELECT USER(), CURRENT_USER();\n+------------------------------------------------+----------------+\n| USER()                                         | CURRENT_USER() |\n+------------------------------------------------+----------------+\n| foo@ip-172-30-0-198.us-west-2.compute.internal | bar@%          |\n+------------------------------------------------+----------------+\n1 row in set (0.000 sec)\n`\n\nWe can verify that our foo Unix user was properly mapped to the bar MariaDB user by looking at the return value of CURRENT_USER().\n\nNext, let's test our alice user in the dba group:\n\n`bash\n$ mysql -u alice -h 172.30.0.198\n[mariadb] Password:\nWelcome to the MariaDB monitor.  Commands end with ; or \\g.\nYour MariaDB connection id is 19\nServer version: 10.3.10-MariaDB MariaDB Server\n\nCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.\n\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\n\nMariaDB [(none)]> SELECT USER(), CURRENT_USER();\n+--------------------------------------------------+----------------+\n| USER()                                           | CURRENT_USER() |\n+--------------------------------------------------+----------------+\n| alice@ip-172-30-0-198.us-west-2.compute.internal | dba@%          |\n+--------------------------------------------------+----------------+\n1 row in set (0.000 sec)\n`\n\nFinally, let's test our bob user in the dba group:\n\n`bash\n$ mysql -u bob -h 172.30.0.198\n[mariadb] Password:\nWelcome to the MariaDB monitor.  Commands end with ; or \\g.\nYour MariaDB connection id is 20\nServer version: 10.3.10-MariaDB MariaDB Server\n\nCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.\n\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\n\nMariaDB [(none)]> SELECT USER(), CURRENT_USER();\n+------------------------------------------------+----------------+\n| USER()                                         | CURRENT_USER() |\n+------------------------------------------------+----------------+\n| bob@ip-172-30-0-198.us-west-2.compute.internal | dba@%          |\n+------------------------------------------------+----------------+\n1 row in set (0.000 sec)\n`\n\nWe can verify that our alice and bob Unix users in the dba Unix group were properly mapped to the dba MariaDB user by looking at the return values of CURRENT_USER()`.\n\nURL: https://mariadb.com/docs/server/reference/plugins/authentication-plugins/authentication-with-pluggable-authentication-modules-pam/configuring-pam-authentication-and-user-mapping-with-unix-authentication