The pam authentication plugin allows MariaDB to offload user authentication to the system's Pluggable Authentication Module (PAM) framework. PAM is an authentication framework used by Linux, FreeBSD, Solaris, and other Unix-like operating systems.
Note: Windows does not support PAM, so the pam authentication plugin does not support Windows. However, one can use a MariaDB client on Windows to connect to MariaDB server that is installed on a Unix-like operating system and that is configured to use the pam authentication plugin. For an example of how to do this, see the blog post: MariaDB: Improve Security with Two-Step Verification.
PAM makes it possible to implement various authentication scenarios of different complexity. For example,
Authentication using passwords from /etc/shadow (indeed, this is what a default PAM configuration usually does). See the pam_unix PAM module.
Authentication using LDAP. See the pam_ldap PAM module.
Authentication using Microsoft's Active Directory. See the pam_lsass, pam_winbind, and pam_centrifydc PAM modules.
Authentication using one-time passwords (even with SMS confirmation!). See the pam_google_authenticator and pam_securid PAM modules.
Authentication using SSH keys. See the pam_ssh PAM module.
User and group mapping. See the pam_user_map PAM module.
Combining different authentication modules in interesting ways in a PAM service.
Password expiration.
Limiting access by time, date, day of the week, etc. See the pam_time PAM module.
Logging every login attempt.
and so on, the list is in no way exhaustive.
The pam authentication plugin's library is provided in binary packages in all releases on Linux.
Although the plugin's shared library is distributed with MariaDB by default, the plugin is not actually installed by MariaDB by default. There are two methods that can be used to install the plugin with MariaDB.
The first method can be used to install the plugin without restarting the server. You can install the plugin dynamically by executing INSTALL SONAME or INSTALL PLUGIN:
INSTALL SONAME 'auth_pam';The second method can be used to tell the server to load the plugin when it starts up. The plugin can be installed this way by providing the --plugin-load or the --plugin-load-add options. This can be specified as a command-line argument to mysqld or it can be specified in a relevant server option group in an option file:
[mariadb]
...
plugin_load_add = auth_pamThe auth_pam shared library actually refers to version 2.0 of the pam authentication plugin. Version 1.0 of the plugin as the auth_pam_v1 shared library is also available.
If you need to install version 1.0 of the authentication plugin instead of version 2.0, then you can do so. For example, with INSTALL SONAME or INSTALL PLUGIN:
INSTALL SONAME 'auth_pam_v1';Or by specifying in a relevant server option group in an option file:
[mariadb]
...
plugin_load_add = auth_pam_v1You can uninstall the plugin dynamically by executing UNINSTALL SONAME or UNINSTALL PLUGIN:
UNINSTALL SONAME 'auth_pam';If you installed the plugin by providing the --plugin-load or the --plugin-load-add options in a relevant server option group in an option file, then those options should be removed to prevent the plugin from being loaded the next time the server is restarted.
If you installed version 1.0 of the authentication plugin, then you can uninstall that by executing a similar statement for auth_pam_v1:
UNINSTALL SONAME 'auth_pam_v1';The pam authentication plugin tells MariaDB to delegate the authentication to the PAM authentication framework. How exactly that authentication is performed depends on how PAM was configured.
PAM is divided into services. PAM services are configured by PAM configuration files. Typically, the global PAM configuration file is located at /etc/pam.conf and PAM directory-based configuration files for individual services are located in /etc/pam.d/.
If you want to use a PAM service called mariadb for your MariaDB PAM authentication, then the PAM configuration file for that service would also be called mariadb, and it would typically be located at /etc/pam.d/mariadb.
For example, here is a minimal PAM service configuration file that performs simple password authentication with UNIX passwords:
auth required pam_unix.so audit
account required pam_unix.so auditLet's breakdown this relatively simple PAM service configuration file.
Each line of a PAM service configuration file has the following general format:
type control module-path module-arguments
The above PAM service configuration file instructs the PAM authentication framework that for successful authentication (i.e. type=auth), it is required that the pam_unix.so PAM module returns a success.
The above PAM service configuration file also instructs the PAM authentication framework that for an account (i.e. type=account) to be valid, it is required that the pam_unix.so PAM module returns a success.
PAM also supports session and password types, but MariaDB's pam authentication plugin does not support those.
The above PAM service configuration file also provides the audit module argument to the pam_unix PAM module. The pam_unix manual says that this module argument enables extreme debug logging to the syslog.
On most systems, you can find many other examples of PAM service configuration files in your /etc/pam.d/ directory.
If you configure PAM to use the pam_unix PAM module (as in the above example), then you might notice on some systems that this will fail by default with errors like the following:
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=aliceThe problem is that on some systems, the pam_unix PAM module needs access to /etc/shadow in order to function, and most systems only allow root to access that file by default.
Newer versions of PAM do not have this limitation, so you may want to try upgrading your version of PAM to see if that fixes the issue.
If that does not work, then you can work around this problem by giving the user that runs mysqld access to /etc/shadow. For example, if the mysql user runs mysqld, then you could do the following:
sudo groupadd shadow
sudo usermod -a -G shadow mysql
sudo chown root:shadow /etc/shadow
sudo chmod g+r /etc/shadowAnd then you would have to restart the server.
At that point, the server should be able to read /etc/shadow.
The pam authentication plugin uses a setuid wrapper to perform its PAM checks, so it should not need any special workarounds to perform privileged operations, such as reading /etc/shadow when using the pam_unix PAM module. See MDEV-7032 for more information.
<>
Similar to all other authentication plugins, to create a user in MariaDB which uses the pam authentication plugin, you would execute CREATE USER while specifying the name of the plugin in the IDENTIFIED VIA clause:
CREATE USER username@hostname IDENTIFIED VIA pam;If SQL_MODE does not have NO_AUTO_CREATE_USER set, then you can also create the user this way with GRANT:
GRANT SELECT ON db.* TO username@hostname IDENTIFIED VIA pam;You can also specify a PAM service name for MariaDB to use by providing it with the USING clause:
CREATE USER username@hostname IDENTIFIED VIA pam USING 'mariadb';This line creates a user that needs to be authenticated via the pam authentication plugin using the PAM service name mariadb. As mentioned in a previous section, this service's configuration file will typically be present in /etc/pam.d/mariadb.
If no service name is specified, then the plugin will use mysql as the default PAM service name.
For clients that use the libmysqlclient or MariaDB Connector/C libraries, MariaDB provides two client authentication plugins that are compatible with the pam authentication plugin:
dialog
mysql_clear_password
When connecting with a client or utility to a server as a user account that authenticates with the pam authentication plugin, you may need to tell the client where to find the relevant client authentication plugin by specifying the --plugin-dir option:
mariadb --plugin-dir=/usr/local/mysql/lib64/mysql/plugin --user=aliceBoth the dialog and the mysql_clear_password client authentication plugins transmit the password to the server in clear text. Therefore, when you use the pam authentication plugin, it is incredibly important to encrypt client connections using TLS to prevent the clear-text passwords from being seen by unauthorized users.
dialogUsually the pam authentication plugin uses the dialog client authentication plugin to communicate with the user. This client authentication plugin allows MariaDB to support arbitrarily complex PAM configurations with regular or one-time passwords, challenge-response, multiple questions, or just about anything else. When using a MariaDB client library, there is no need to install or enable anything — the dialog client authentication plugin is loaded by the client library completely automatically and transparently for the application.
The dialog client authentication plugin was developed by MariaDB, so MySQL's clients and client libraries as well as third party applications that bundle MySQL's client libraries do not support the dialog client authentication plugin out of the box. If the server tells an unsupported client to use the dialog client authentication plugin, then the client is likely to throw an error like the following:
ERROR 2059 (HY000): Authentication plugin 'dialog' cannot be loaded: /usr/lib/mysql/plugin/dialog.so: cannot open shared object file: No such file or directoryFor some libraries or applications, this problem can be fixed by copying dialog.so or dialog.dll from a MariaDB client installation that is compatible with the system into the system's MySQL client authentication plugin directory. However, not all clients are compatible with the dialog client authentication plugin, so this may not work for every client.
If your client does not support the dialog client authentication plugin, then you may need to use the mysql_clear_password client authentication plugin instead.
The dialog client authentication plugin transmits the password to the server in clear text. Therefore, when you use the pam authentication plugin, it is incredibly important to encrypt client connections using TLS to prevent the clear-text passwords from being seen by unauthorized users.
mysql_clear_passwordUsers can instruct the pam authentication plugin to use the mysql_clear_password client authentication plugin instead of the dialog client authentication plugin by configuring the pam_use_cleartext_plugin system variable on the server. It can be set in a relevant server option group in an option file:
[mariadb]
...
pam_use_cleartext_pluginIt is important to note that the mysql_clear_password plugin has very limited functionality.
The mysql_clear_password client authentication plugin only supports PAM services that require password-based authentication.
The mysql_clear_password client authentication plugin also only supports PAM services that ask the user a single question.
If the PAM service requires challenge-responses, multiple questions, or other similar complicated authentication schemes, then the PAM service is not compatible with mysql_clear_password client authentication plugin. In that case, the dialog client authentication plugin will have to be used instead.
The mysql_clear_password client authentication plugin transmits the password to the server in clear text. Therefore, when you use the pam authentication plugin, it is incredibly important to encrypt client connections using TLS to prevent the clear-text passwords from being seen by unauthorized users.
The mysql_clear_password client authentication plugin is similar to MySQL's mysql_clear_password client authentication plugin.
The mysql_clear_password client authentication plugin is compatible with MySQL clients and most MySQL client libraries, while the dialog client authentication plugin is not always compatible with them. Therefore, the mysql_clear_password client authentication plugin is most useful if you need some kind of MySQL compatibility in your environment, but you still want to use the pam authentication plugin.
Even though the mysql_clear_password client authentication plugin is compatible with MySQL clients and most MySQL client libraries, the mysql_clear_password client authentication plugin may be disabled by default by these clients and client libraries. For example, MySQL's version of the mysql command-line client has the --enable-cleartext-plugin option that must be set in order to use the mysql_clear_password client authentication plugin:
mysql --enable-cleartext-plugin --user=alice -pOther clients may require other methods to enable the authentication plugin. For example, MySQL Workbench has a checkbox titled Enable Cleartext Authentication Plugin under the Advanced tab on the connection configuration screen.
For applications that use MySQL's libmysqlclient, the authentication plugin can be enabled by setting the MYSQL_ENABLE_CLEARTEXT_PLUGIN option with the mysql_options() function:
mysql_options(mysql, MYSQL_ENABLE_CLEARTEXT_PLUGIN, 1);For MySQL compatibility, MariaDB Connector/C also allows applications to set the MYSQL_ENABLE_CLEARTEXT_PLUGIN option with the mysql_optionsv function. However, this option does not actually do anything in MariaDB Connector/C, because the mysql_clear_password client authentication plugin is always enabled for MariaDB clients and client libraries.
MariaDB Connector/C supports pam authentication using the client authentication plugins mentioned in the previous section since MariaDB Connector/C 2.1.0, regardless of the value of the pam_use_cleartext_plugin system variable.
MariaDB Connector/ODBC supports pam authentication using the client authentication plugins mentioned in the previous section since MariaDB Connector/ODBC 1.0.0, regardless of the value of the pam_use_cleartext_plugin system variable.
MariaDB Connector/J supports pam v1 authentication since MariaDB Connector/J 1.4.0, regardless of the value of the pam_use_cleartext_plugin system variable.
MariaDB Connector/J supports pam v2 authentication since MariaDB Connector/J 2.4.4, regardless of the value of the pam_use_cleartext_plugin system variable.
MariaDB Connector/Node.js supports pam authentication since MariaDB Connector/Node.js 0.7.0, regardless of the value of the pam_use_cleartext_plugin system variable.
MySqlConnector for ADO.NET supports pam authentication since MySqlConnector 0.20.0, but only if the pam_use_cleartext_plugin system variable is enabled on the server.
Errors and messages from PAM modules are usually logged using the syslog daemon with the authpriv facility. To determine the specific log file where the authpriv facility is logged, you can check rsyslog.conf.
On RHEL, CentOS, Fedora, and other similar Linux distributions, the default location for these messages is usually /var/log/secure.
On Debian, Ubuntu, and other similar Linux distributions, the default location for these messages is usually /var/log/auth.log.
For example, the syslog can contain messages like the following when MariaDB's pam authentication plugin is configured to use the pam_unix PAM module, and the user enters an incorrect password:
Jan 9 05:35:41 ip-172-30-0-198 unix_chkpwd[1205]: password check failed for user (foo)
Jan 9 05:35:41 ip-172-30-0-198 mysqld: pam_unix(mariadb:auth): authentication failure; logname= uid=997 euid=997 tty= ruser= rhost= user=fooMariaDB's pam authentication plugin can also log additional verbose debug logging to the error log. This is only done if the plugin is a debug build and if pam_debug is set.
The output looks like this:
PAM: pam_start(mariadb, alice)
PAM: pam_authenticate(0)
PAM: conv: send(Enter PASSCODE:)
PAM: conv: recv(123456789)
PAM: pam_acct_mgmt(0)
PAM: pam_get_item(PAM_USER)
PAM: status = 0 user = ��\>The pam_exec PAM module can be used to implement some custom logging. This can be very useful when debugging certain kinds of issues.
For example, first, create a script that writes the log output:
tee /tmp/pam_log_script.sh <<EOF
#!/bin/bash
echo "\${PAM_SERVICE}:\${PAM_TYPE} - \${PAM_RUSER}@\${PAM_RHOST} is authenticating as \${PAM_USER}"
EOF
chmod 0775 /tmp/pam_log_script.shAnd then change the PAM service configuration to execute the script using the pam_exec PAM module:
auth optional pam_exec.so log=/tmp/pam_output.txt /tmp/pam_log_script.sh
auth required pam_unix.so audit
account optional pam_exec.so log=/tmp/pam_output.txt /tmp/pam_log_script.sh
account required pam_unix.so auditWhenever the above PAM service is used, the output of the script will be written to /tmp/pam_output.txt. It may look similar to this:
*** Tue May 14 14:53:23 2019
mariadb:auth - @ is authenticating as alice
*** Tue May 14 14:53:25 2019
mariadb:account - @ is authenticating as alice
*** Tue May 14 14:53:28 2019
mariadb:auth - @ is authenticating as alice
*** Tue May 14 14:53:31 2019
mariadb:account - @ is authenticating as aliceEven when using the pam authentication plugin, the authenticating PAM user account still needs to exist in MariaDB, and the account needs to have privileges in the database. 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 PAM 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 PAM 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. See User and Group Mapping with PAM for more information on how to do this.
There are many PAM modules. The ones described below are the ones that have been seen most often by MariaDB.
The pam_unix PAM module provides support for Unix password authentication. It is the default PAM module on most systems.
For a tutorial on setting up PAM authentication and user or group mapping with Unix authentication, see Configuring PAM Authentication and User Mapping with Unix Authentication.
The pam_user_map PAM module was developed by MariaDB to support user and group mapping.
The pam_ldap PAM module provides support for LDAP authentication.
For a tutorial on setting up PAM authentication and user or group mapping with LDAP authentication, see Configuring PAM Authentication and User Mapping with LDAP Authentication.
This can also be configured for Active Directory authentication.
The pam_sss PAM module provides support for authentication with System Security Services Daemon (SSSD).
This can be configured for Active Directory authentication.
The pam_lsass PAM module provides support for Active Directory authentication. It is provided by PowerBroker Identity Services – Open Edition.
The pam_winbind PAM module provides support for Active Directory authentication. It is provided by winbindd from the samba suite.
This PAM module converts all provided user names to lowercase. There is no way to disable this functionality. If you do not want to be forced to use all lowercase user names, then you may need to configure the pam_winbind_workaround system variable. See MDEV-18686 for more information.
The pam_centrifydc PAM module provides support for Active Directory authentication. It integrates with the commercial Active Directory Bridge from Centrify.
The pam_krb5 PAM module provides support for Kerberos authentication.
This can be configured for Active Directory authentication.
The pam_google_authenticator PAM module provides two-factor identification with Google Authenticator. It is from Google's google-authenticator-libpam open-source project. The PAM module should work with the open-source mobile apps built by Google's google-authenticator and google-authenticator-android projects as well as the closed source Google Authenticator mobile apps that are present in each mobile app store.
For an example of how to use this PAM module, see the blog post: MariaDB: Improve Security with Two-Step Verification.
The pam_securid PAM module provides support for multi-factor authentication. It is part of the commercial RSA SecurID Suite.
Note that current versions of this module are not safe for multi-threaded environments, and the vendor does not officially support the product on MariaDB. See MDEV-10361 about that. However, the module may work with a current version of MariaDB.
The pam_ssh PAM module provides authentication using SSH keys.
The pam_time PAM module provides time-controlled access.
MariaDB is a multi-threaded program, which means that different connections concurrently run in different threads. Current versions of MariaDB's pam authentication plugin execute PAM module code in the server address space. This means that any PAM modules used with MariaDB must be safe for multi-threaded environments. Otherwise, if multiple clients try to authenticate with the same PAM module in parallel, undefined behavior can occur. For example, the pam_fprintd PAM module is not safe for multi-threaded environments, and if you use it with MariaDB, you may experience server crashes.
The pam authentication plugin isolates PAM module code from the server address space, so even PAM modules that are known to be unsafe for multi-threaded environments should not cause issues with MariaDB. See MDEV-15473 for more information.
When a password validation plugin is enabled, MariaDB won't allow an account to be created if the password validation plugin says that the account's password is too weak. This creates a problem for accounts that authenticate with the pam authentication plugin, since MariaDB has no knowledge of the user's password. When a user tries to create an account that authenticates with the pam authentication plugin, the password validation plugin would throw an error, even with strict_password_validation=OFF set.
The workaround is to uninstall the password validation plugin with UNINSTALL PLUGIN, and then create the account, and then reinstall the password validation plugin with INSTALL PLUGIN.
For example:
INSTALL PLUGIN simple_password_check SONAME 'simple_password_check';
Query OK, 0 rows affected (0.002 sec)
SET GLOBAL strict_password_validation=OFF;
Query OK, 0 rows affected (0.000 sec)
CREATE USER ''@'%' IDENTIFIED VIA pam USING 'mariadb';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
UNINSTALL PLUGIN simple_password_check;
Query OK, 0 rows affected (0.000 sec)
CREATE USER ''@'%' IDENTIFIED VIA pam USING 'mariadb';
Query OK, 0 rows affected (0.000 sec)
INSTALL PLUGIN simple_password_check SONAME 'simple_password_check';
Query OK, 0 rows affected (0.001 sec)Accounts that authenticate with the pam authentication plugin should be exempt from password validation checks. See MDEV-12321 and MDEV-10457 for more information.
SELinux may cause issues when using the pam authentication plugin. For example, using pam_unix with the pam authentication plugin while SELinux is enabled can sometimes lead to SELinux errors involving unix_chkpwd, such as the following::
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.ppSometimes issues like this can be fixed by updating the system's SELinux policies. You may be able to update the policies using audit2allow. See SELinux: Generating SELinux Policies with audit2allow for more information.
If you can't get the pam authentication plugin to work with SELinux at all, then it can help to disable SELinux entirely. See SELinux: Changing SELinux's Mode for information on how to do this.
You may run into authentication failures with the following log message in the MariaDB error log:
pam: cannot exec /usr/lib64/mysql/plugin/auth_pam_tool_dir/auth_pam_tool (errno: 12 "Cannot allocate memory")This can happen on operating system setups that are configured to prevent memory overcommit. When the MariaDB server process spawns the auth_pam_tool helper process there's a brief period where the new process inherits the memory of the MariaDB process before releasing that memory and executing the new command. When having a MariaDB server configured to use more than 50% of the server machnines RAM -- which is common for dedicated database servers -- this duplication would lead to an over-commit situation.
Starting with MariaDB 10.5.16, MariaDB 10.6.8, and MariaDB 10.7.4, we changed to use posix_spawn() instead of the classic fork();exec() to prevent this, but systems with older glibc versions prior to 2.26 still use fork();exec() to implement posix_spawn() internally and so are still affected; this is for example still the case on RedHat Enterprise Linux 7.
To solve this you can either:
change the vm.overcommit_memory kernel setting to allow memory overcommit
install the older auth_pam_v1 plugin version that does not spawn a helper process (but may run into problems with file permissions or multi threading with some PAM modules)
See also MDEV-26212 and MDEV-30734
You may find the following PAM-related tutorials helpful:
pam_debugDescription: Enables verbose debug logging to the error log for all authentication handled by the plugin.
This system variable is only available when the plugin is a debug build.
Command line: --pam-debug
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: OFF
Introduced: MariaDB 10.2.2, MariaDB 10.1.17
pam_use_cleartext_pluginDescription: Use the mysql_clear_password client authentication plugin instead of the dialog client authentication plugin. This may be needed for compatibility reasons, but it only supports simple PAM configurations that don't require any input besides a password.
Command line: --pam-use-cleartext-plugin
Scope: Global
Dynamic: No
Data Type: boolean
Default Value: OFF
Introduced: MariaDB 10.1.1, MariaDB 5.5.32
pam_winbind_workaroundDescription: Configures the authentication plugin to compare the user name provided by the client with the user name returned by the PAM module in a case insensitive manner. This may be needed if you use the pam_winbind PAM module, which is known to convert all user names to lowercase, and which does not allow this behavior to be disabled.
Command line: --pam-winbind-workaround
Scope: Global
Dynamic: Yes
Data Type: boolean
Default Value: OFF
Introduced: MariaDB 10.4.5, MariaDB 10.3.15, MariaDB 10.2.24, MariaDB 10.1.39
pamDescription: Controls how the server should treat the plugin when the server starts up.
Valid values are:
OFF - Disables the plugin without removing it from the mysql.plugins table.
ON - Enables the plugin. If the plugin cannot be initialized, then the server will still continue starting up, but the plugin will be disabled.
FORCE - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error.
FORCE_PLUS_PERMANENT - Enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error. In addition, the plugin cannot be uninstalled with UNINSTALL SONAME or UNINSTALL PLUGIN while the server is running.
See Plugin Overview: Configuring Plugin Activation at Server Startup for more information.
Command line: --pam=value
Data Type: enumerated
Default Value: ON
Valid Values: OFF, ON, FORCE, FORCE_PLUS_PERMANENT
This page is licensed: CC BY-SA / Gnu FDL
In this article, we will 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_ldap PAM module, which performs LDAP authentication. We will also set up an OpenLDAP server.
In this walkthrough, we are going to assume the following hypothetical requirements:
The LDAP user foo should be mapped to the MariaDB user bar. (foo: bar)
Any LDAP user in the LDAP group dba should be mapped to the MariaDB user dba. (@dba: dba)
Before we can use LDAP authentication, we first need to set up our OpenLDAP Server. This is usually done on a server that is completely separate from the database server.
On the server acting as the OpenLDAP Server, first, we need to install the OpenLDAP components.
On RHEL, CentOS, and other similar Linux distributions that use RPM packages, that would go like this:
sudo yum install openldap openldap-servers openldap-clients nss-pam-ldapdNext, let's to configure the OpenLDAP Server. The easiest way to do that is to copy the template configuration file that is included with the installation. In many installations, that will be at /usr/share/openldap-servers/DB_CONFIG.example:
sudo cp /usr/share/openldap-servers/DB_CONFIG.example /var/lib/ldap/DB_CONFIG
sudo chown ldap. /var/lib/ldap/DB_CONFIGSometimes it is useful to change the port used by OpenLDAP. For example, some cloud environments block well-known authentication services, so they block the default LDAP port.
On some systems, the port can be changed by setting SLAPD_URLS in /etc/sysconfig/slapd:
SLAPD_URLS="ldapi:/// ldap://0.0.0.0:3306/"I used 3306 because that is the port that is usually used by mysqld, so I know that it is not blocked.
Next, let's start the OpenLDAP Server and configure it to start on reboot. On systemd systems, that would go like this:
sudo systemctl start slapd
sudo systemctl enable slapdIn order to use LDAP for authentication, we also need to install some standard objectClasses, such as posixAccount and posixGroup. In LDAP, things like objectClasses are defined in LDIF files. In many installations, these specific objectClasses are defined in /etc/openldap/schema/nis.ldif. nis.ldif also depends on core.ldif and cosine.ldif. However, core.ldif is usually installed by default.
We can install them with ldapmodify:
sudo ldapmodify -a -Y EXTERNAL -H ldapi:/// -f /etc/openldap/schema/cosine.ldif
sudo ldapmodify -a -Y EXTERNAL -H ldapi:/// -f /etc/openldap/schema/nis.ldifNext, let’s create a directory manager user. We can do this by using OpenLDAP's olc configuration system to change the olcRootDN directive to the DN of the directory manager user, which means that the user will be a privileged LDAP user that is not subject to access controls. We will also set the root password for the user by changing the olcRootPW directive.
We will also set the DN suffix for our backend LDAP database by changing the olcSuffix directive.
Let’s use the slappasswd utility to generate a password hash from a clear-text password. Simply execute:
slappasswdThis utility should provide a password hash that looks kind of like this: {SSHA}AwT4jrvmokeCkbDrFAnGvzzjCMb7bvEl
OpenLDAP's olc configuration system also uses LDIF files. Now that we have the password hash, let’s create an LDIF file to create the directory manager user:
tee ~/setupDirectoryManager.ldif <<EOF
dn: olcDatabase={1}monitor,cn=config
changetype: modify
replace: olcAccess
olcAccess: {0}to *
by dn.base="gidNumber=0+uidNumber=0,cn=peercred,cn=external,cn=auth" read
by dn.base="cn=Manager,dc=support,dc=mariadb,dc=com" read
by * none
dn: olcDatabase={2}hdb,cn=config
changetype: modify
replace: olcSuffix
olcSuffix: dc=support,dc=mariadb,dc=com
dn: olcDatabase={2}hdb,cn=config
changetype: modify
replace: olcRootDN
olcRootDN: cn=Manager,dc=support,dc=mariadb,dc=com
dn: olcDatabase={2}hdb,cn=config
changetype: modify
add: olcRootPW
olcRootPW: {SSHA}AwT4jrvmokeCkbDrFAnGvzzjCMb7bvEl
dn: olcDatabase={2}hdb,cn=config
changetype: modify
add: olcAccess
olcAccess: {0}to attrs=userPassword,shadowLastChange
by dn="cn=Manager,dc=support,dc=mariadb,dc=com" write
by anonymous auth
by self write
by * none
olcAccess: {1}to dn.base=""
by * read
olcAccess: {2}to *
by dn="cn=Manager,dc=support,dc=mariadb,dc=com" write
by * read
EOFNote that I am using the dc=support,dc=mariadb,dc=com domain for my directory. You can change this to whatever is relevant to you.
Now let’s run the ldif file with ldapmodify:
sudo ldapmodify -Y EXTERNAL -H ldapi:/// -f ~/setupDirectoryManager.ldifWe will use the new directory manager user to make changes to the LDAP directory after this step.
Next, let's create the structure of the directory by creating parts of our tree.
tee ~/setupDirectoryStructure.ldif <<EOF
dn: dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: dcObject
objectclass: organization
o: MariaDB Support Team
dc: support
dn: cn=Manager,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: organizationalRole
cn: Manager
description: Directory Manager
dn: ou=People,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: organizationalUnit
ou: People
dn: ou=Groups,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: organizationalUnit
ou: Groups
dn: ou=System Users,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: organizationalUnit
ou: System Users
EOFNow let’s use our new directory manager user and run the LDIF file with ldapmodify:
ldapmodify -a -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -f ~/setupDirectoryStructure.ldifLet's go ahead and create the LDAP users and groups that we are using for this hypothetical scenario.
First, let's create the foo user:
tee ~/createFooUser.ldif <<EOF
dn: uid=foo,ou=People,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: account
objectClass: posixAccount
objectClass: shadowAccount
cn: foo
uid: foo
uidNumber: 16859
gidNumber: 100
homeDirectory: /home/foo
loginShell: /bin/bash
gecos: foo
userPassword: {crypt}x
shadowLastChange: -1
shadowMax: -1
shadowWarning: 0
EOF
ldapmodify -a -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -f ~/createFooUser.ldifThen let's create a couple users to go into the dba group:
tee ~/createDbaUsers.ldif <<EOF
dn: uid=gmontee,ou=People,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: account
objectClass: posixAccount
objectClass: shadowAccount
cn: gmontee
uid: gmontee
uidNumber: 16860
gidNumber: 100
homeDirectory: /home/gmontee
loginShell: /bin/bash
gecos: gmontee
userPassword: {crypt}x
shadowLastChange: -1
shadowMax: -1
shadowWarning: 0
dn: uid=bstillman,ou=People,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: account
objectClass: posixAccount
objectClass: shadowAccount
cn: bstillman
uid: bstillman
uidNumber: 16861
gidNumber: 100
homeDirectory: /home/bstillman
loginShell: /bin/bash
gecos: bstillman
userPassword: {crypt}x
shadowLastChange: -1
shadowMax: -1
shadowWarning: 0
EOF
ldapmodify -a -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -f ~/createDbaUsers.ldifNote that each of these users needs a password, so we can set it for each user with ldappasswd:
ldappasswd -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -S uid=foo,ou=People,dc=support,dc=mariadb,dc=com
ldappasswd -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -S uid=gmontee,ou=People,dc=support,dc=mariadb,dc=com
ldappasswd -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -S uid=bstillman,ou=People,dc=support,dc=mariadb,dc=comAnd then let's create our dba group
tee ~/createDbaGroup.ldif <<EOF
dn: cn=dba,ou=Groups,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: posixGroup
gidNumber: 678
EOF
ldapmodify -a -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -f ~/createDbaGroup.ldifAnd then let's add our two users to it:
tee ~/addUsersToDbaGroup.ldif <<EOF
dn: cn=dba,ou=Groups,dc=support,dc=mariadb,dc=com
changetype: modify
add: memberuid
memberuid: gmontee
dn: cn=dba,ou=Groups,dc=support,dc=mariadb,dc=com
changetype: modify
add: memberuid
memberuid: bstillman
EOF
ldapmodify -a -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -f ~/addUsersToDbaGroup.ldifWe also need to create LDAP 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. Instead of the People organizationalUnit, we will create them in the System Users organizationalUnit.
tee ~/createSystemUsers.ldif <<EOF
dn: uid=bar,ou=System Users,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: account
objectClass: posixAccount
objectClass: shadowAccount
cn: bar
uid: bar
uidNumber: 16862
gidNumber: 100
homeDirectory: /home/bar
loginShell: /bin/bash
gecos: bar
userPassword: {crypt}x
shadowLastChange: -1
shadowMax: -1
shadowWarning: 0
dn: uid=dba,ou=System Users,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: account
objectClass: posixAccount
objectClass: shadowAccount
cn: dba
uid: dba
uidNumber: 16863
gidNumber: 100
homeDirectory: /home/dba
loginShell: /bin/bash
gecos: dba
userPassword: {crypt}x
shadowLastChange: -1
shadowMax: -1
shadowWarning: 0
EOF
ldapmodify -a -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -f ~/createSystemUsers.ldifAt this point, we can move onto setting up the MariaDB Server.
First, we need to make sure that the LDAP and PAM libraries are installed.
On RHEL, CentOS, and other similar Linux distributions that use RPM packages, we need to install the following packages:
sudo yum install openldap-clients nss-pam-ldapd pam pam-develNext, let's configure LDAP on the system. We can use authconfig for this:
sudo authconfig --enableldap \
--enableldapauth \
--ldapserver="ldap://172.30.0.238:3306" \
--ldapbasedn="dc=support,dc=mariadb,dc=com" \
--enablemkhomedir \
--updateBe sure to replace -–ldapserver and -–ldapbasedn with values that are relevant for your environment.
The following steps apply to MariaDB Server in versions 10.2.32.7, 10.3.23., 10.4.13.6, 10.5.2 and earlier. In later releases, the pam_user_map PAM module is now included in the base install.
Next, let's install the pam_user_map PAM module.
Before the module can be compiled from source, we may need to install some dependencies.
On RHEL, CentOS, and other similar Linux distributions that use RPM packages, we need to install gcc and pam-devel:
sudo yum install gcc pam-develOn Debian, Ubuntu, and other similar Linux distributions that use DEB packages, we need to install gcc and libpam0g-dev:
sudo apt-get install gcc libpam0g-devAnd then we can build and install the library with the following:
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/Next, let's configure the pam_user_map PAM module based on our hypothetical requirements.
The configuration file for the pam_user_map PAM module is /etc/security/user_map.conf. Based on our hypothetical requirements, ours would look like:
foo: bar
@dba:dbaNext, let's install the pam authentication plugin.
Log into the MariaDB Server and execute the following:
INSTALL SONAME 'auth_pam';Next, 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.
Since we are only doing LDAP authentication with the pam_ldap PAM module and group mapping with the pam_user_map PAM module, our configuration file would look like this:
auth required pam_ldap.so
auth required pam_user_map.so
account required pam_ldap.soIf we want to allow authentication from LDAP users and from local Unix users through pam_unix, while giving priority to the local users, then we could do this instead:
auth [success=1 new_authtok_reqd=1 default=ignore] pam_unix.so audit
auth required pam_ldap.so try_first_pass
auth required pam_user_map.so
account sufficient pam_unix.so audit
account required pam_ldap.soConfiguring the pam_unix PAM Module
If you also want to allow authentication from local Unix users, the 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.
If the mysql user is running mysqld, then we can do that by executing the following:
sudo groupadd shadow
sudo usermod -a -G shadow mysql
sudo chown root:shadow /etc/shadow
sudo chmod g+r /etc/shadowThe server needs to be restarted for this change to take affect.
Next, let's create the MariaDB users. Remember that our PAM service is called mariadb.
First, let's create the MariaDB user for the user mapping: foo: bar
That means that we need to create a bar user:
CREATE USER 'bar'@'%' IDENTIFIED BY 'strongpassword';
GRANT ALL PRIVILEGES ON *.* TO 'bar'@'%' ;And then let's create the MariaDB user for the group mapping: @dba: dba
That means that we need to create a dba user:
CREATE USER 'dba'@'%' IDENTIFIED BY 'strongpassword';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'%' ;And then 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:
DELETE FROM mysql.db WHERE User='' AND Host='%';
FLUSH PRIVILEGES;And then let's create the anonymous proxy user:
CREATE USER ''@'%' IDENTIFIED VIA pam USING 'mariadb';
GRANT PROXY ON 'bar'@'%' TO ''@'%';
GRANT PROXY ON 'dba'@'%' TO ''@'%';Next, 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.
First, let's test out our foo user:
$ mysql -u foo -h 172.30.0.198
[mariadb] Password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 134
Server version: 10.3.10-MariaDB MariaDB Server
Copyright (c) 2000, 2018, 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() |
+------------------------------------------------+----------------+
| foo@ip-172-30-0-198.us-west-2.compute.internal | bar@% |
+------------------------------------------------+----------------+
1 row in set (0.000 sec)We can verify that our foo LDAP user was properly mapped to the bar MariaDB user by looking at the return value of CURRENT_USER().
Then let's test out our gmontee user in the dba group:
$ mysql -u gmontee -h 172.30.0.198
[mariadb] Password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 135
Server version: 10.3.10-MariaDB MariaDB Server
Copyright (c) 2000, 2018, 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() |
+----------------------------------------------------+----------------+
| gmontee@ip-172-30-0-198.us-west-2.compute.internal | dba@% |
+----------------------------------------------------+----------------+
1 row in set (0.000 sec)And then let's test out our bstillman user in the dba group:
$ mysql -u bstillman -h 172.30.0.198
[mariadb] Password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 136
Server version: 10.3.10-MariaDB MariaDB Server
Copyright (c) 2000, 2018, 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() |
+------------------------------------------------------+----------------+
| bstillman@ip-172-30-0-198.us-west-2.compute.internal | dba@% |
+------------------------------------------------------+----------------+
1 row in set (0.000 sec)We can verify that our gmontee and bstillman LDAP users in the dba LDAP group were properly mapped to the dba MariaDB user by looking at the return values of CURRENT_USER().
If you chose the option that also allowed local Unix authentication, then let's test that out. Let's create a Unix user and give the user a password real quick:
sudo useradd alice
sudo passwd aliceAnd let's also map this user to dba:
@dba:dba
foo: bar
alice: dbaAnd we know that the existing anonymous user already has the PROXY privilege granted to the dba user, so this should just work without any other configuration. Let's test it out:
$ mysql -u alice -h 172.30.0.198
[mariadb] Password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 141
Server version: 10.3.10-MariaDB MariaDB Server
Copyright (c) 2000, 2018, 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@ip-172-30-0-198.us-west-2.compute.internal | dba@% |
+--------------------------------------------------+----------------+
1 row in set (0.000 sec)We can verify that our alice Unix user was properly mapped to the dba MariaDB user by looking at the return values of CURRENT_USER().
This page is licensed: CC BY-SA / Gnu FDL
In this article, we will 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.
In this walkthrough, we are going to assume the following hypothetical requirements:
The Unix user foo should be mapped to the MariaDB user bar. (foo: bar)
Any Unix user in the Unix group dba should be mapped to the MariaDB user dba. (@dba: dba)
Let's go ahead and create the Unix users and groups that we are using for this hypothetical scenario.
First, 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.
sudo useradd foo
sudo passwd foo
sudo useradd alice
sudo passwd alice
sudo useradd bob
sudo passwd bobAnd then let's create our dba group and add our two users to it:
sudo groupadd dba
sudo usermod -a -G dba alice
sudo usermod -a -G dba bobWe 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.
sudo useradd bar
sudo useradd dba -g dbaNext, let's install the pam_user_map PAM module.
Before the module can be compiled from source, we may need to install some dependencies.
On RHEL, CentOS, and other similar Linux distributions that use RPM packages, we need to install gcc and pam-devel:
sudo yum install gcc pam-develOn Debian, Ubuntu, and other similar Linux distributions that use DEB packages, we need to install gcc and libpam0g-dev:
sudo apt-get install gcc libpam0g-devAnd then we can build and install the library with the following:
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/Next, let's configure the pam_user_map PAM module based on our hypothetical requirements.
The configuration file for the pam_user_map PAM module is /etc/security/user_map.conf. Based on our hypothetical requirements, ours would look like:
foo: bar
@dba:dbaNext, let's install the pam authentication plugin.
Log into the MariaDB Server and execute the following:
INSTALL SONAME 'auth_pam';Next, 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.
Since 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:
auth required pam_unix.so audit
auth required pam_user_map.so
account required pam_unix.so auditThe 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.
If the mysql user is running mysqld, then we can do that by executing the following:
sudo groupadd shadow
sudo usermod -a -G shadow mysql
sudo chown root:shadow /etc/shadow
sudo chmod g+r /etc/shadowThe server needs to be restarted for this change to take affect.
Next, let's create the MariaDB users. Remember that our PAM service is called mariadb.
First, let's create the MariaDB user for the user mapping: foo: bar
That means that we need to create a bar user:
CREATE USER 'bar'@'%' IDENTIFIED BY 'strongpassword';
GRANT ALL PRIVILEGES ON *.* TO 'bar'@'%' ;And then let's create the MariaDB user for the group mapping: @dba: dba
That means that we need to create a dba user:
CREATE USER 'dba'@'%' IDENTIFIED BY 'strongpassword';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'%' ;And then 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:
DELETE FROM mysql.db WHERE User='' AND Host='%';
FLUSH PRIVILEGES;And then let's create the anonymous proxy user:
CREATE USER ''@'%' IDENTIFIED VIA pam USING 'mariadb';
GRANT PROXY ON 'bar'@'%' TO ''@'%';
GRANT PROXY ON 'dba'@'%' TO ''@'%';Next, 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.
First, let's test out our foo user:
$ mysql -u foo -h 172.30.0.198
[mariadb] Password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 22
Server version: 10.3.10-MariaDB MariaDB Server
Copyright (c) 2000, 2018, 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() |
+------------------------------------------------+----------------+
| foo@ip-172-30-0-198.us-west-2.compute.internal | bar@% |
+------------------------------------------------+----------------+
1 row in set (0.000 sec)We can verify that our foo Unix user was properly mapped to the bar MariaDB user by looking at the return value of CURRENT_USER().
Then let's test out our alice user in the dba group:
$ mysql -u alice -h 172.30.0.198
[mariadb] Password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.3.10-MariaDB MariaDB Server
Copyright (c) 2000, 2018, 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@ip-172-30-0-198.us-west-2.compute.internal | dba@% |
+--------------------------------------------------+----------------+
1 row in set (0.000 sec)And then let's test out our bob user in the dba group:
$ mysql -u bob -h 172.30.0.198
[mariadb] Password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 10.3.10-MariaDB MariaDB Server
Copyright (c) 2000, 2018, 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() |
+------------------------------------------------+----------------+
| bob@ip-172-30-0-198.us-west-2.compute.internal | dba@% |
+------------------------------------------------+----------------+
1 row in set (0.000 sec)We 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().
This page is licensed: CC BY-SA / Gnu FDL
Even when using the pam authentication plugin, the authenticating PAM user account still needs to exist in MariaDB, and the account needs to have privileges in the database. 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 PAM 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 PAM 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.
Although most PAM modules usually do not do things like this, PAM supports the ability to change the user name in the process of authentication.The MariaDB pam authentication plugin fully supports this feature of PAM.
Rather than building user and group mapping into the pam authentication plugin, MariaDB thought that it would cover the most use cases and offer the most flexibility to offload this functionality to an external PAM module. The pam_user_map PAM module was implemented by MariaDB to facilitate this. This PAM module can be configured in the PAM service used by the pam authentication plugin, just like other PAM modules.
Unlike MariaDB, MySQL and Percona implemented group mapping in their PAM authentication plugins. If you've read through MySQL's PAM authentication documentation on group mapping or Percona's PAM authentication documentation on group mapping, you've probably seen syntax where the group mappings are provided in the CREATE USER statement like this:
CREATE USER ''@''
IDENTIFIED WITH authentication_pam
AS 'mysql, root=developer, users=data_entry';Since MariaDB's user and group mapping is performed by an external PAM module, MariaDB's pam authentication plugin does not support this syntax. Instead, the user and group mappings for the pam_user_map PAM module are configured in an external configuration file. This is discussed in a later section.
The pam_user_map PAM module gets installed as part of all our MariaDB server packages since MariaDB 10.5, and was added since 10.2.31, 10.3.22, and 10.4.12 in previous MariaDB major releases where it was not present from the beginning.
Some Linux distributions have not picked up this change in their own packages yet, so when e.g. installing MariaDB server from stock Ubuntu packages on Ubuntu 20.04LTS you still won't have the pam_user_map module installed even though the MariaDB server installed is more recent than MariaDB 10.3.22.
When using such an installation, and not being able to switch to our own MariaDB package repositories, it may be necessary to compile the PAM module from source as described in the next section, or to manually extract it from one of our server packages and copy it to the target system.
Installing Compilation Dependencies
Before the module can be compiled from source, you may need to install some dependencies.
On RHEL, CentOS, and other similar Linux distributions that use RPM packages, you need to install gcc, pam-devel and MariaDB-devel:
sudo yum install gcc pam-devel MariaDB-develOn Debian, Ubuntu, and other similar Linux distributions that use DEB packages, you need to install gcc, libpam0g-dev:
sudo apt-get install gcc libpam0g-dev libmariadb-devCompiling and Installing the pam_user_map PAM Module
The pam_user_map PAM module can be built by downloading plugin/auth_pam/mapper/pam_user_map.c file from the MariaDB source tree and compiling it after minor adjustments. Once it is built, it can be installed to the system's PAM module directory, which is typically /lib64/security/.
For example: (replace 10.4 in the URL with the actual server versions)
wget https://raw.githubusercontent.com/MariaDB/server/10.4/plugin/auth_pam/mapper/pam_user_map.c
sed -ie 's/config_auth_pam/plugin_auth_common/' pam_user_map.c
gcc -I/usr/include/mysql/ pam_user_map.c -shared -lpam -fPIC -o pam_user_map.so
sudo install --mode=0755 pam_user_map.so /lib64/security/You will also need to adjust the major version number in the URL on the first line to match your installed MariaDB version, and the #-I
gcc line, as depending on operating system and MariaDB server version the plugin_auth_common.h file may be installed in different directories than /usr/include/mysql/The pam_user_map PAM module uses the configuration file at the path /etc/security/user_map.conf to determine its user and group mappings. The file's format is described below.
To map a specific PAM user to a specific MariaDB user:
orig_pam_user_name: mapped_mariadb_user_nameOr to map any PAM user in a specific PAM group to a specific MariaDB user, the group name is prefixed with @:
@orig_pam_group_name: mapped_mariadb_user_nameFor example, here is an example /etc/security/user_map.conf:
=========================================================
#comments and empty lines are ignored
john: jack
bob: admin
top: accounting
@group_ro: readonlyWith user and group mapping, configuring PAM is done similar to how it is normally done with the pam authentication plugin. However, when configuring the PAM service, you will have to add an auth line for the pam_user_map PAM module to the service's PAM configuration file:
auth required pam_unix.so audit
auth required pam_user_map.so
account required pam_unix.so auditWith user and group mapping, creating users is done similar to how it is normally done with the pam authentication plugin. However, one major difference is that you will need to GRANT the PROXY privilege on the mapped user to the original user.
For example, if you have the following configured in /etc/security/user_map.conf:
foo: bar
@dba:dbaThen you could execute the following to grant the relevant privileges:
CREATE USER 'bar'@'%' IDENTIFIED BY 'strongpassword';
GRANT ALL PRIVILEGES ON *.* TO 'bar'@'%' ;
CREATE USER 'dba'@'%' IDENTIFIED BY 'strongpassword';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'%' ;
CREATE USER ''@'%' IDENTIFIED VIA pam USING 'mariadb';
GRANT PROXY ON 'bar'@'%' TO ''@'%';
GRANT PROXY ON 'dba'@'%' TO ''@'%';Note that the ''@'%' account is a special catch-all anonymous account. Any login by a user that has no more specific account match in the system will be matched by this anonymous account.
Also note that you might not be able to create the ''@'%' anonymous account by default on some systems without doing some extra steps first. See Fixing a Legacy Default Anonymous Account for more information.
In case any user mapping is performed, the original user name is returned by the SQL function USER(), while the authenticated user name is returned by the SQL function CURRENT_USER(). The latter actually defines what privileges are available to a connected user.
For example, if we have the following configured:
foo: barThen the following output would verify that it is working properly:
$ mysql -u foo -h 172.30.0.198
[mariadb] Password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 22
Server version: 10.3.10-MariaDB MariaDB Server
Copyright (c) 2000, 2018, 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() |
+------------------------------------------------+----------------+
| foo@ip-172-30-0-198.us-west-2.compute.internal | bar@% |
+------------------------------------------------+----------------+
1 row in set (0.000 sec)We can verify that our foo PAM user was properly mapped to the bar MariaDB user by looking at the return value of CURRENT_USER().
By default, the pam_user_map PAM module does not perform any logging. However, if you want to enable debug logging, then you can add the debug module argument to the service's PAM configuration file:
auth required pam_unix.so audit
auth required pam_user_map.so debug
account required pam_unix.so auditWhen debug logging is enabled, the pam_user_map PAM module will write log entries to the same syslog location as other PAM modules, which is typically /var/log/secure on many systems.
For example, this debug log output can look like the following:
Jan 9 05:42:13 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): Opening file '/etc/security/user_map.conf'.
Jan 9 05:42:13 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): Incoming username 'alice'.
Jan 9 05:42:13 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): User belongs to 2 groups [alice,dba].
Jan 9 05:42:13 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): Check if user is in group 'dba': YES
Jan 9 05:42:13 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): User mapped as 'dba'
Jan 9 05:43:36 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): Opening file '/etc/security/user_map.conf'.
Jan 9 05:43:36 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): Incoming username 'bob'.
Jan 9 05:43:36 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): User belongs to 2 groups [bob,dba].
Jan 9 05:43:36 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): Check if user is in group 'dba': YES
Jan 9 05:43:36 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): User mapped as 'dba'
Jan 9 06:08:36 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): Opening file '/etc/security/user_map.conf'.
Jan 9 06:08:36 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): Incoming username 'foo'.
Jan 9 06:08:36 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): User belongs to 1 group [foo].
Jan 9 06:08:36 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): Check if user is in group 'dba': NO
Jan 9 06:08:36 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): Check if username 'foo': YES
Jan 9 06:08:36 ip-172-30-0-198 mysqld: pam_user_map(mariadb:auth): User mapped as 'bar'With user and group mapping, any PAM user or any PAM user in a given PAM group can be mapped to a specific MariaDB user account. However, due to the way PAM works, a PAM user with the same name as the mapped MariaDB user account must exist.
For example, if the configuration file for the PAM service file contained the following:
auth required pam_sss.so
auth required pam_user_map.so debug
account sufficient pam_unix.so
account sufficient pam_sss.soAnd if /etc/security/user_map.conf contained the following:
@dba: dbaThen any PAM user in the PAM group dba would be mapped to the MariaDB user account dba. But if a PAM user with the name dba did not also exist, then the pam_user_map PAM module's debug logging would write errors to the syslog like the following:
Sep 27 17:17:05 dbserver1 mysqld: pam_user_map(mysql:auth): Opening file '/etc/security/user_map.conf'.
Sep 27 17:17:05 dbserver1 mysqld: pam_user_map(mysql:auth): Incoming username 'alice'.
Sep 27 17:17:05 dbserver1 mysqld: pam_user_map(mysql:auth): User belongs to 4 groups [dba,mongod,mongodba,mysql].
Sep 27 17:17:05 dbserver1 mysqld: pam_user_map(mysql:auth): Check if user is in group 'mysql': YES
Sep 27 17:17:05 dbserver1 mysqld: pam_user_map(mysql:auth): User mapped as 'dba'
Sep 27 17:17:05 dbserver1 mysqld: pam_unix(mysql:account): could not identify user (from getpwnam(dba))
Sep 27 17:17:05 dbserver1 mysqld: pam_sss(mysql:account): Access denied for user dba: 10 (User not known to the underlying authentication module)
Sep 27 17:17:05 dbserver1 mysqld: 2018-09-27 17:17:05 72 [Warning] Access denied for user 'alice'@'localhost' (using password: NO)In the above log snippet, notice that both the pam_unix and the pam_sss PAM modules are complaining that the dba PAM user does not appear to exist, and that these complaints cause the PAM authentication process to fail, which causes the MariaDB authentication process to fail as well.
This can be fixed by creating a PAM user with the same name as the mapped MariaDB user account, which is dba in this case.
You may also be able to work around this problem by essentially disabling PAM's account verification for the service with the pam_permit PAM module. For example, in the above case, that would be:
auth required pam_sss.so
auth required pam_user_map.so debug
account required pam_permit.soSee MDEV-17315 for more information.
You may find the following PAM and user mapping-related tutorials helpful:
This page is licensed: CC BY-SA / Gnu FDL