MemSQL’s Kerberos setup with SSL
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.
- Only required for MemSQL 5.0.6
Remove the following files: Libselinux.so*
mv /var/lib/memsql/*/objdir/usr/local/lib64/libselinux.so* ~/tmp
The above command will work if you substitute “/var/lib/memsql/” with your install path and * with your node-type/port combination (by default it looks like “master-3306” or “leaf-3306”).
- Cleartext 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, such as PAM. 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. More information can be found here: https://dev.mysql.com/doc/refman/5.5/en/cleartext-authentication-plugin.html. The minimum version of MySQL CLI to have to enable this functionality is 5.5.27.
- Ensure that your boxes are able to connect and kinit via Kerberos. Run kinit –p and authenticate using your password. If this step completes successfully, your machine’s Kerberos settings are configured appropriately.
kinit –p username
- Create a user account within MemSQL. Here ‘memsql’ refers to a file within /etc/pam.d which has the PAM rules for authentication.
| memsql> GRANT ALL ON *.* TO username@’%’ identified with authentication_pam as ‘memsql’;
Query OK, 0 rows affected (0.43 sec) |
- Make sure the PAM Kerberos libraries are installed, `sudo apt-get install libpam-krb5`. On RHEL / CentOS, `sudo yum install pam_krb5`.
- Within /etc/pam.d/memsql, create the set of rules to let PAM know which service to authenticate against (Kerberos in this example). Ensure to set the permissions on this file to `chown memsql:memsql /etc/pam.d/memsql; chmod 660 /etc/pam.d/memsql`.
| auth [success=done default=ignore] pam_krb5.so minimum_uid=1000
account required pam_krb5.so |
- If you see an error such as “ERROR 1897 (HY000): Authentication failed, review the auth log and/or secure log, as well as possibly the syslog (/var/log/authlog and/or /var/log/secure, as well as /var/log/syslog). Within this file, the error you will likely see in this case is `pam_krb5[18545]: error resolving user name ‘username’ to uid/gid pair.` This most likely means that you are using LDAP/AD which is not properly mapping the uid/gid into sssd or nss. To mitigate this, we can add `no_user_check` to the pam_krb5 module, as below:
| auth [success=done default=ignore] pam_krb5.so no_user_check
account required pam_krb5.so no_user_check |
The above code has been necessary at least in RedHat 6/7, is probably necessary in other versions of RHEL/CentOS as well, and possibly other Linuxes. This is done because “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 username -h 0 –enable-cleartext-plugin –p |
- Because we are sending passwords in cleartext, some clients (such as JDBC) will require that the connection use SSL, and it is strongly recommended for connections as possible. The instructions below will guide you through generating some self-signed SSL certificates for testing purposes (stolen from here: http://docs.memsql.com/docs/ssl-network-encryption).
- Create the directory for your certs on your MA
cd /var/lib/memsql
mkdir certs
cd certs
- Create the subject string for certificate signing requests. You can do so by editing the details below to match your organization.
SUBJ=“/C=US/ST=CA/L=San Francisco/O=MemSQL/CN=”
CA_SUBJ=“${SUBJ}memsql.ssl.test.ca”
SERV_SUBJ=“${SUBJ}memsql.ssl.test.server”
CLIENT_SUBJ=“${SUBJ}memsql.ssl.test.client”
- Create the CA cert and key
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca-cert.pem -subj “$CA_SUBJ”
- Create the server cert, key, and sign each with the CA
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem -subj “$SERV_SUBJ”
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
- Create the client cert, key, and sign each with the CA
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem -subj “$CLIENT_SUBJ”
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
- Verify the certificate chain
openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
- To use these new certificates, copy the ca- and server- PEM files to all hosts in the cluster. Edit the memsql.cnf file on all nodes of your cluster to add the certificate paths in the [server] section as shown below. Then, restart the memsql service on all nodes of your cluster.
## These can be absolute paths, or relative to the memsqld binary.ssl-ca = /var/lib/memsql/certs/ca-cert.pemssl-cert = /var/lib/memsql /certs/server-cert.pemssl-key = /var/lib/memsql /certs/server-key.pem
Ensure that these certificates are legible by the memsql user. The easiest way to do so is to recursively chown the certificate folder so that it’s owned by this user.
chown –R memsql:memsql /var/lib/memsql/certs
- Log in and verify you can authenticate.
| mysql -u username -h 0 –enable-cleartext-plugin -p –ssl-ca=ca-cert.pem -e “status”
Enter password: ————– mysql Ver 14.14 Distrib 5.6.23, for osx10.10 (x86_64) using EditLine wrapper Connection id: 43 Current database: Current user: username@ 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 Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 ————–
|
Debugging
Sometimes, this doesn’t work right away. To debug, change `pam_krb5.so` to `pam_permit.so` in your PAM config file. This module always approves users regardless of passwords. Try to log in again, if it works, PAM is working. If it doesn’t, fix PAM. Make sure the `/etc/pam.d/memsql` file is readable by memsql.
If PAM is working, you have a Kerberos problem. To check on whether Kerberos is configured correctly, you can actually just test the it by running `kinit -p USER`, since this is essentially what pam_krb5.so does. If that doesn’t work, try these things:
- Ensure you can connect to the Kerberos server, it uses port 88
- Ensure your krb5.conf is set up, you should have default_realm configured, as well as the realms and domain_realm sections filled out
- If reverse DNS isn’t necessarily set up properly on all your servers, try setting dns_canonicalize_hostname = false in /etc/krb5.conf lib defaults section
If PAM works, and kinit works, then auth should work. Restart the 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.