Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

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.