Note: These directions assume the system is already setup to use AD for user logins. What is here will enable a MemSQL to leverage a system's current setup. It will not deploy AD from scratch.
As of MemSQL 5.0, MemSQL includes an authentication plugin that enables a MemSQL Server to use PAM (Pluggable Authentication Modules) to authenticate users. PAM enables a system to use a standard interface to access various kinds of authentication methods, such as Kerberos or Unix Passwords.
- Use the Cleartext Client-Side Authentication Plugin
With native MySQL authentication, the client performs one-way hashing on the password before sending it to the server. This enables the client to avoid sending the password in clear text. However, because the hash algorithm is one way, the original password cannot be recovered on the server side. One-way hashing cannot be done for authentication schemes that require the server to receive the password as entered on the client side. In such cases, the mysql_clear_password client-side plugin can be used to send the password to the server in clear text. There is no corresponding server-side plugin. https://dev.mysql.com/doc/refman/5.5/en/cleartext-authentication-plugin.html
- Create a User Account within MemSQL
memsqlrefers to a file within
/etc/pam.dwhich has the pam rules for authentication.
memsql> GRANT ALL ON *.* TO nick@'%' identified with authentication_pam as 'memsql'; Query OK, 0 rows affected (0.43 sec)
- Create Rules for PAM
/etc/pam.d/memsql, create the set of rules to let PAM know which service to authenticate against (Kerberos in this example). Make sure the PAM Kerberos libraries are installed,
sudo apt-get install libpam-krb5. On RHEL / CentOS,
sudo yum install pam_krb5.
Make sure to set the permissions on this file to:
$ chown memsql:memsql /etc/pam.d/memsql $ chmod 660 /etc/pam.d/memsql---/etc/pam.d/memsql---
auth [success=done default=ignore] pam_krb5.so minimum_uid=1000 account required pam_krb5.soIf you see an error such as
pam_krb5: error resolving user name 'username' to uid/gid pair.You will most likely see this error in
/var/log/syslog. This most likely means you do not have a local user on the system or are using LDAP/AD which is not properly mapping the uid/gid into sssd or nss. A work around (though this is a complete setup, since you are using MemSQL as the user database and an external 3rd party, the Kerberos server, as your authentication verification) is to add
auth [success=done default=ignore] pam_krb5.so no_user_check account required pam_krb5.so no_user_checkThe above code has been necessary at least in RedHat 6/7, probably other versions as well and possibly other linuxes.
"no_user_check tells pam_krb5.so to not check if a user exists on the local system, to skip authorization checks using the user's .k5login file, and to create ccache files owned by the current process's UID. This is useful for situations where a non-privileged server process needs to use Kerberized services on behalf of remote users who may not have local access. Note that such a server should have an encrypted connection with its client in order to avoid allowing the user's password to be eavesdropped." – pam_krb5 man page
- Test the Connection
$ mysql -u nick -h krb.local --enable-cleartext-plugin -p
- Turn on SSL
Because we are sending passwords in cleartext, some clients (such as JDBC) will require that the connection use SSL. Turn on SSL using instructions here: http://docs.memsql.com/docs/ssl-network-encryption
$ mysql -u nick -h krb.local --enable-cleartext-plugin -p --ssl-ca=ca-cert.pem -e "status"
mysql Ver 14.14 Distrib 5.6.23, for osx10.10 (x86_64) using EditLine wrapper
Connection id: 43
Current user: nick@
SSL: Cipher in use is AES256-SHA
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.5.8 MemSQL source distribution (compatible; MySQL Enterprise & MySQL Commercial)
Protocol version: 10
Connection: krb.local via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
If this authentication mode doesn't work immediately, you may need to debug by identifying the problematic component in this chain. To debug, first try changing
pam_permit.so in your PAM config file. This module always approves users regardless of passwords. Try to log in again, if it works, the PAM piece of this configuration is working. If it doesn't, you likely need to fix your PAM configuration and file permissions. Make sure the
/etc/pam.d/memsql file is readable by
memsql. Remember to reconfigure your PAM config file to use
pam_krb5.so instead of
If PAM is working, you have a Kerberos configuration problem. To test this quickly, you can verify that Kerberos authentication for these credentials is valid by running
kinit -p USER, as this is broadly similar to what
pam_krb5.so actually does. If that doesn't work, try these things:
- ensure you can connect to the Kerberos server, it uses port 88
- ensure your
krb5.confis set up, you should have
default_realmconfigured, as well as the
domain_realmsections filled out
- if reverse DNS isn't necessarily set up properly on all your servers, try setting
dns_canonicalize_hostname = falsein
/etc/krb5.conflib defaults section
If PAM works, and kinit works, then auth should work. You may need to restart your cluster if it doesn't. If it still doesn't work, the problem is probably the interface between PAM and Kerberos:
pam_krb5.so. Make sure
pam_krb5.so is installed and accessible.