MySQL :: MySQL 8.4 Reference Manual :: 8.4.1.5 PAM Pluggable Authentication (original) (raw)

8.4.1.5 PAM Pluggable Authentication

Note

PAM pluggable authentication is an extension included in MySQL Enterprise Edition, a commercial product. To learn more about commercial products, see https://www.mysql.com/products/.

MySQL Enterprise Edition supports an authentication method that enables MySQL Server to use PAM (Pluggable Authentication Modules) to authenticate MySQL users. PAM enables a system to use a standard interface to access various kinds of authentication methods, such as traditional Unix passwords or an LDAP directory.

PAM pluggable authentication provides these capabilities:

PAM pluggable authentication has been tested on Linux and macOS; note that Windows does not support PAM.

The following table shows the plugin and library file names. The file name suffix might differ on your system. The file must be located in the directory named by theplugin_dir system variable. For installation information, seeInstalling PAM Pluggable Authentication.

Table 8.18 Plugin and Library Names for PAM Authentication

Plugin or File Plugin or File Name
Server-side plugin authentication_pam
Client-side plugin mysql_clear_password
Library file authentication_pam.so

The client-side mysql_clear_password cleartext plugin that communicates with the server-side PAM plugin is built into the libmysqlclient client library and is included in all distributions, including community distributions. Inclusion of the client-side cleartext plugin in all MySQL distributions enables clients from any distribution to connect to a server that has the server-side PAM plugin loaded.

The following sections provide installation and usage information specific to PAM pluggable authentication:

For general information about pluggable authentication in MySQL, see Section 8.2.17, “Pluggable Authentication”. For information about the mysql_clear_password plugin, seeSection 8.4.1.4, “Client-Side Cleartext Pluggable Authentication”. For proxy user information, see Section 8.2.19, “Proxy Users”.

How PAM Authentication of MySQL Users Works

This section provides an overview of how MySQL and PAM work together to authenticate MySQL users. For examples showing how to set up MySQL accounts to use specific PAM services, seeUsing PAM Pluggable Authentication.

  1. The client program and the server communicate, with the client sending to the server the client user name (the operating system user name by default) and password:
    • The client user name is the external user name.
    • For accounts that use the PAM server-side authentication plugin, the corresponding client-side plugin is mysql_clear_password. This client-side plugin performs no password hashing, with the result that the client sends the password to the server as cleartext.
  2. The server finds a matching MySQL account based on the external user name and the host from which the client connects. The PAM plugin uses the information passed to it by MySQL Server (such as user name, host name, password, and authentication string). When you define a MySQL account that authenticates using PAM, the authentication string contains:
    • A PAM service name, which is a name that the system administrator can use to refer to an authentication method for a particular application. There can be multiple applications associated with a single database server instance, so the choice of service name is left to the SQL application developer.
    • Optionally, if proxying is to be used, a mapping from PAM groups to MySQL user names.
  3. The plugin uses the PAM service named in the authentication string to check the user credentials and returns 'Authentication succeeded, Username is_`username`_' or'Authentication failed'. The password must be appropriate for the password store used by the PAM service. Examples:
    • For traditional Unix passwords, the service looks up passwords stored in the/etc/shadow file.
    • For LDAP, the service looks up passwords stored in an LDAP directory.
      If the credentials check fails, the server refuses the connection.
  4. Otherwise, the authentication string indicates whether proxying occurs. If the string contains no PAM group mapping, proxying does not occur. In this case, the MySQL user name is the same as the external user name.
  5. Otherwise, proxying is indicated based on the PAM group mapping, with the MySQL user name determined based on the first matching group in the mapping list. The meaning of“PAM group” depends on the PAM service. Examples:
    • For traditional Unix passwords, groups are Unix groups defined in the /etc/group file, possibly supplemented with additional PAM information in a file such as/etc/security/group.conf.
    • For LDAP, groups are LDAP groups defined in an LDAP directory.
      If the proxy user (the external user) has thePROXY privilege for the proxied MySQL user name, proxying occurs, with the proxy user assuming the privileges of the proxied user.
Installing PAM Pluggable Authentication

This section describes how to install the server-side PAM authentication plugin. For general information about installing plugins, see Section 7.6.1, “Installing and Uninstalling Plugins”.

To be usable by the server, the plugin library file must be located in the MySQL plugin directory (the directory named by the plugin_dir system variable). If necessary, configure the plugin directory location by setting the value ofplugin_dir at server startup.

The plugin library file base name isauthentication_pam, and is typically compiled with the .so suffix.

To load the plugin at server startup, use the--plugin-load-add option to name the library file that contains it. With this plugin-loading method, the option must be given each time the server starts. For example, put these lines in the servermy.cnf file:

[mysqld]
plugin-load-add=authentication_pam.so

After modifying my.cnf, restart the server to cause the new settings to take effect.

Alternatively, to load the plugin at runtime, use this statement, adjusting the .so suffix as necessary:

INSTALL PLUGIN authentication_pam SONAME 'authentication_pam.so';

INSTALL PLUGIN loads the plugin immediately, and also registers it in themysql.plugins system table to cause the server to load it for each subsequent normal startup without the need for --plugin-load-add.

To verify plugin installation, examine the Information SchemaPLUGINS table or use theSHOW PLUGINS statement (seeSection 7.6.2, “Obtaining Server Plugin Information”). For example:

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
       FROM INFORMATION_SCHEMA.PLUGINS
       WHERE PLUGIN_NAME LIKE '%pam%';
+--------------------+---------------+
| PLUGIN_NAME        | PLUGIN_STATUS |
+--------------------+---------------+
| authentication_pam | ACTIVE        |
+--------------------+---------------+

If the plugin fails to initialize, check the server error log for diagnostic messages.

To associate MySQL accounts with the PAM plugin, seeUsing PAM Pluggable Authentication.

Uninstalling PAM Pluggable Authentication

The method used to uninstall the PAM authentication plugin depends on how you installed it:

UNINSTALL PLUGIN authentication_pam;
Using PAM Pluggable Authentication

This section describes in general terms how to use the PAM authentication plugin to connect from MySQL client programs to the server. The following sections provide instructions for using PAM authentication in specific ways. It is assumed that the server is running with the server-side PAM plugin enabled, as described inInstalling PAM Pluggable Authentication.

To refer to the PAM authentication plugin in theIDENTIFIED WITH clause of aCREATE USER statement, use the name authentication_pam. For example:

CREATE USER user
  IDENTIFIED WITH authentication_pam
  AS 'auth_string';

The authentication string specifies the following types of information:

For example, if an account uses themysql-unix PAM service name and should map operating system users in the root andusers PAM groups to thedeveloper and data_entry MySQL users, respectively, use a statement like this:

CREATE USER user
  IDENTIFIED WITH authentication_pam
  AS 'mysql-unix, root=developer, users=data_entry';

Authentication string syntax for the PAM authentication plugin follows these rules:

pam_service_name[,pam_group_name=mysql_user_name]...

The plugin parses the authentication string for each connection attempt that uses the account. To minimize overhead, keep the string as short as possible.

If the plugin successfully authenticates the external user name (the name passed by the client), it looks for a PAM group mapping list in the authentication string and, if present, uses it to return a different MySQL user name to the MySQL server based on which PAM groups the external user is a member of:

The following sections describe how to set up several authentication scenarios that use the PAM authentication plugin:

Variations on these scenarios are possible:

The examples make the following assumptions. You might need to make some adjustments if your system is set up differently.

The PAM authentication plugin checks at initialization time whether the AUTHENTICATION_PAM_LOG environment value is set in the server's startup environment. If so, the plugin enables logging of diagnostic messages to the standard output. Depending on how your server is started, the message might appear on the console or in the error log. These messages can be helpful for debugging PAM-related issues that occur when the plugin performs authentication. For more information, seePAM Authentication Debugging.

PAM Unix Password Authentication without Proxy Users

This authentication scenario uses PAM to check external users defined in terms of operating system user names and Unix passwords, without proxying. Every such external user permitted to connect to MySQL Server should have a matching MySQL account that is defined to use PAM authentication through traditional Unix password store.

  1. Verify that Unix authentication permits logins to the operating system with the user nameantonio and password_antoniopassword_.
  2. Set up PAM to authenticate MySQL connections using traditional Unix passwords by creating amysql-unix PAM service file named/etc/pam.d/mysql-unix. The file contents are system dependent, so check existing login-related files in the /etc/pam.d directory to see what they look like. On Linux, themysql-unix file might look like this:
#%PAM-1.0  
auth            include         password-auth  
account         include         password-auth

For macOS, use login rather thanpassword-auth.
The PAM file format might differ on some systems. For example, on Ubuntu and other Debian-based systems, use these file contents instead: 

@include common-auth  
@include common-account  
@include common-session-noninteractive
  1. Create a MySQL account with the same user name as the operating system user name and define it to authenticate using the PAM plugin and the mysql-unix PAM service:
CREATE USER 'antonio'@'localhost'  
  IDENTIFIED WITH authentication_pam  
  AS 'mysql-unix';  
GRANT ALL PRIVILEGES  
  ON mydb.*  
  TO 'antonio'@'localhost';

Here, the authentication string contains only the PAM service name, mysql-unix, which authenticates Unix passwords. 4. Use the mysql command-line client to connect to the MySQL server as antonio. For example: 

$> mysql --user=antonio --password --enable-cleartext-plugin  
Enter password: antonio_password

The server should permit the connection and the following query returns output as shown: 

mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;  
+-------------------+-------------------+--------------+  
| USER()            | CURRENT_USER()    | @@proxy_user |  
+-------------------+-------------------+--------------+  
| antonio@localhost | antonio@localhost | NULL         |  
+-------------------+-------------------+--------------+

This demonstrates that the antonio operating system user is authenticated to have the privileges granted to the antonio MySQL user, and that no proxying has occurred.

Note

The client-side mysql_clear_password authentication plugin leaves the password untouched, so client programs send it to the MySQL server as cleartext. This enables the password to be passed as is to PAM. A cleartext password is necessary to use the server-side PAM library, but may be a security problem in some configurations. These measures minimize the risk:

PAM LDAP Authentication without Proxy Users

This authentication scenario uses PAM to check external users defined in terms of operating system user names and LDAP passwords, without proxying. Every such external user permitted to connect to MySQL Server should have a matching MySQL account that is defined to use PAM authentication through LDAP.

To use PAM LDAP pluggable authentication for MySQL, these prerequisites must be satisfied:

Configure MySQL for PAM LDAP authentication as follows:

  1. Verify that Unix authentication permits logins to the operating system with the user nameantonio and password_antoniopassword_.
  2. Set up PAM to authenticate MySQL connections using LDAP by creating a mysql-ldap PAM service file named /etc/pam.d/mysql-ldap. The file contents are system dependent, so check existing login-related files in the /etc/pam.d directory to see what they look like. On Linux, themysql-ldap file might look like this:
#%PAM-1.0  
auth        required    pam_ldap.so  
account     required    pam_ldap.so

If PAM object files have a suffix different from.so on your system, substitute the correct suffix.
The PAM file format might differ on some systems. 3. Create a MySQL account with the same user name as the operating system user name and define it to authenticate using the PAM plugin and the mysql-ldap PAM service: 

CREATE USER 'antonio'@'localhost'  
  IDENTIFIED WITH authentication_pam  
  AS 'mysql-ldap';  
GRANT ALL PRIVILEGES  
  ON mydb.*  
  TO 'antonio'@'localhost';

Here, the authentication string contains only the PAM service name, mysql-ldap, which authenticates using LDAP. 4. Connecting to the server is the same as described inPAM Unix Password Authentication without Proxy Users.

PAM Unix Password Authentication with Proxy Users and Group Mapping

The authentication scheme described here uses proxying and PAM group mapping to map connecting MySQL users who authenticate using PAM onto other MySQL accounts that define different sets of privileges. Users do not connect directly through the accounts that define the privileges. Instead, they connect through a default proxy account authenticated using PAM, such that all the external users are mapped to the MySQL accounts that hold the privileges. Any user who connects using the proxy account is mapped to one of those MySQL accounts, the privileges for which determine the database operations permitted to the external user.

The procedure shown here uses Unix password authentication. To use LDAP instead, see the early steps ofPAM LDAP Authentication without Proxy Users.

  1. Verify that Unix authentication permits logins to the operating system with the user nameantonio and password_antoniopassword_.
  2. Verify that antonio is a member of theroot or users PAM group.
  3. Set up PAM to authenticate themysql-unix PAM service through operating system users by creating a file named/etc/pam.d/mysql-unix. The file contents are system dependent, so check existing login-related files in the /etc/pam.d directory to see what they look like. On Linux, themysql-unix file might look like this:
#%PAM-1.0  
auth            include         password-auth  
account         include         password-auth

For macOS, use login rather thanpassword-auth.
The PAM file format might differ on some systems. For example, on Ubuntu and other Debian-based systems, use these file contents instead: 

@include common-auth  
@include common-account  
@include common-session-noninteractive
  1. Create a default proxy user (''@'') that maps external PAM users to the proxied accounts:
CREATE USER ''@''  
  IDENTIFIED WITH authentication_pam  
  AS 'mysql-unix, root=developer, users=data_entry';

Here, the authentication string contains the PAM service name, mysql-unix, which authenticates Unix passwords. The authentication string also maps external users in the root andusers PAM groups to thedeveloper anddata_entry MySQL user names, respectively.
The PAM group mapping list following the PAM service name is required when you set up proxy users. Otherwise, the plugin cannot tell how to perform mapping from external user names to the proper proxied MySQL user names. 5. Create the proxied accounts and grant to each one the privileges it should have: 

CREATE USER 'developer'@'localhost'  
  IDENTIFIED WITH mysql_no_login;  
CREATE USER 'data_entry'@'localhost'  
  IDENTIFIED WITH mysql_no_login;  
GRANT ALL PRIVILEGES  
  ON mydevdb.*  
  TO 'developer'@'localhost';  
GRANT ALL PRIVILEGES  
  ON mydb.*  
  TO 'data_entry'@'localhost';

The proxied accounts use themysql_no_login authentication plugin to prevent clients from using the accounts to log in directly to the MySQL server. Instead, users who authenticate using PAM are expected to use the developer or data_entry account by proxy based on their PAM group. (This assumes that the plugin is installed. For instructions, seeSection 8.4.1.9, “No-Login Pluggable Authentication”.) For alternative methods of protecting proxied accounts against direct use, seePreventing Direct Login to Proxied Accounts. 6. Grant to the proxy account thePROXY privilege for each proxied account: 

GRANT PROXY  
  ON 'developer'@'localhost'  
  TO ''@'';  
GRANT PROXY  
  ON 'data_entry'@'localhost'  
  TO ''@'';
  1. Use the mysql command-line client to connect to the MySQL server as antonio.
$> mysql --user=antonio --password --enable-cleartext-plugin  
Enter password: antonio_password

The server authenticates the connection using the default''@'' proxy account. The resulting privileges for antonio depend on which PAM groups antonio is a member of. Ifantonio is a member of theroot PAM group, the PAM plugin mapsroot to thedeveloper MySQL user name and returns that name to the server. The server verifies that''@'' has thePROXY privilege fordeveloper and permits the connection. The following query returns output as shown: 

mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;  
+-------------------+---------------------+--------------+  
| USER()            | CURRENT_USER()      | @@proxy_user |  
+-------------------+---------------------+--------------+  
| antonio@localhost | developer@localhost | ''@''        |  
+-------------------+---------------------+--------------+

This demonstrates that the antonio operating system user is authenticated to have the privileges granted to the developer MySQL user, and that proxying occurs through the default proxy account.
If antonio is not a member of theroot PAM group but is a member of theusers PAM group, a similar process occurs, but the plugin maps user PAM group membership to the data_entry MySQL user name and returns that name to the server: 

mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;  
+-------------------+----------------------+--------------+  
| USER()            | CURRENT_USER()       | @@proxy_user |  
+-------------------+----------------------+--------------+  
| antonio@localhost | data_entry@localhost | ''@''        |  
+-------------------+----------------------+--------------+

This demonstrates that the antonio operating system user is authenticated to have the privileges of the data_entry MySQL user, and that proxying occurs through the default proxy account.

Note

The client-side mysql_clear_password authentication plugin leaves the password untouched, so client programs send it to the MySQL server as cleartext. This enables the password to be passed as is to PAM. A cleartext password is necessary to use the server-side PAM library, but may be a security problem in some configurations. These measures minimize the risk:

PAM Authentication Access to Unix Password Store

On some systems, Unix authentication uses a password store such as /etc/shadow, a file that typically has restricted access permissions. This can cause MySQL PAM-based authentication to fail. Unfortunately, the PAM implementation does not permit distinguishing “password could not be checked” (due, for example, to inability to read /etc/shadow) from “password does not match.” If you are using Unix password store for PAM authentication, you may be able to enable access to it from MySQL using one of the following methods:

chmod u-s /usr/sbin/unix_chkpwd  
setcap cap_dac_read_search+ep /usr/sbin/unix_chkpwd

Adjust the path to unix_chkpwd as necessary for your platform.

PAM Authentication Debugging

The PAM authentication plugin checks at initialization time whether the AUTHENTICATION_PAM_LOG environment value is set. If so, the plugin enables logging of diagnostic messages to the standard output. These messages may be helpful for debugging PAM-related issues that occur when the plugin performs authentication.

Setting AUTHENTICATION_PAM_LOG=1 (or some other arbitrary value) does not include any passwords. If you wish to include passwords in these messages, setAUTHENTICATION_PAM_LOG=PAM_LOG_WITH_SECRET_INFO.

Some messages include reference to PAM plugin source files and line numbers, which enables plugin actions to be tied more closely to the location in the code where they occur.

Another technique for debugging connection failures and determining what is happening during connection attempts is to configure PAM authentication to permit all connections, then check the system log files. This technique should be used only on a temporary basis, and not on a production server.

Configure a PAM service file named/etc/pam.d/mysql-any-password with these contents (the format may differ on some systems):

#%PAM-1.0
auth        required    pam_permit.so
account     required    pam_permit.so

Create an account that uses the PAM plugin and names themysql-any-password PAM service:

CREATE USER 'testuser'@'localhost'
  IDENTIFIED WITH authentication_pam
  AS 'mysql-any-password';

The mysql-any-password service file causes any authentication attempt to return true, even for incorrect passwords. If an authentication attempt fails, that tells you the configuration problem is on the MySQL side. Otherwise, the problem is on the operating system/PAM side. To see what might be happening, check system log files such as/var/log/secure,/var/log/audit.log,/var/log/syslog, or/var/log/messages.

After determining what the problem is, remove themysql-any-password PAM service file to disable any-password access.