PAM Authentication

Background

A Pluggable Authentication Module (PAM) is the AAA (Authentication, Authorization, and Accounting) framework used in most Linux/Unix systems. Ubuntu, RHEL, macOS (Mac OS X), FreeBSD, and NetBSD all use PAM for authentication. Most Linux/Unix systems that do not come with PAM can be made to work with PAM.

Abstractly, PAM provides this basic API:

Inputs:
    string username
    string password
Output:
    bool success

Anything that prompts the user for a password (such as sshd, Web server back-ends, and the Linux login console) can query PAM for a login result. PAM only provides top-level access to a system (whether a user can log in at all), and not fine-grained access control (such as which files a user may access).

PAM and SingleStore (connection with MySQL Client)

Cleartext Passwords

Typically, SingleStore users (those users created and managed with GRANT … IDENTIFIED BY) are managed by the database internally, and do not exist anywhere else on the Linux/Unix system.

When connecting, the MySQL client normally sends a hashed password to the server. However, the input to PAM must be the cleartext password as every password back-end (Kerberos, /etc/shadow) uses a different hash, which can only be calculated from the cleartext password. The MySQL client binary has supported sending the password in cleartext since version 5.5.27.

Shell

mysql -u steve -h 0 --enable-cleartext-plugin -p

Enter password:

Note that since the password is sent in cleartext, SSL is strongly recommended. Current Java JDBC clients will actually refuse to connect if a cleartext password is requested without SSL.

GRANT Syntax using PAM

The following example creates a new user, where pam_service is a placeholder for the actual PAM service name to use. In this example, PAM will look for a configuration file named /etc/pam.d/pam_service. However, you must change pam_service to the actual PAM service name, and not leave it as pam_service.

SQL

GRANT ALL ON *.* to 'singlestore-db_user'@'127.0.0.1' IDENTIFIED WITH authentication_pam AS 'pam_service';

As most Linux/Unix systems have a PAM service at /etc/pam.d/sshd, there's a straightforward way to test SingleStore and PAM. The following example creates a new 'steve' database user with the default authentication scheme on the host – and the same password 'steve' uses for SSH.

SQL

GRANT ALL ON *.* to 'steve'@'localhost' IDENTIFIED WITH authentication_pam as 'sshd';

Granting permissions to an existing database user via the GRANT command should be done using only the username without the IDENTIFIED WITH clause.