Configuring MariaDB With PAM/Kerberos

October 5, 2016 · by dbversity · in MariaDB, MySQL

Please note below directions assume the system is already setup to use AD for user logins.
What is here will enable a MariaDB to leverage a system’s current setup. It will not deploy AD from scratch.

As of MariaDB 5.0, MariaDB includes an authentication plugin that enables a MariaDB 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 pam_use_cleartext_plugin plugin can be used to send the password to the server in clear text.

[root@dbversity ~]# grep ‘cleartext’ /etc/opt/rh/rh-mariadb101/my.cnf
pam_use_cleartext_plugin
[root@dbversity ~]#
[root@dbversity ~]#
[root@dbversity ~]#
[root@dbversity ~]# mysql -u ad_admin -p’PaSSwoRd’ -P 4306 -h dbversity
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 10.1.16-MariaDB MariaDB Server

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]>

2. Create a User Account within MariaDB
Here MariaDB refers to a file within /etc/pam.d which has the pam rules for authentication.

3. Create Rules for PAM
Within/etc/pam.d/MariaDB, create the set of rules to let PAM know which service to authenticate against (Kerberos in this example).
MariaDB> GRANT ALL ON *.* TO ad_admin@’%’ identified with authentication_pam as ‘MariaDB’;
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.
And make sure to set the permissions on this file to:

4. $ chown mariadb:mariadb /etc/pam.d/MariaDB
$ chmod 660 /etc/pam.d/MariaDB
—/etc/pam.d/MariaDB—
auth [success=done default=ignore] pam_krb5.so minimum_uid=1000
account required pam_krb5.so
If you see an error such as pam_krb5[18545]: error resolving user name ‘username’ to uid/gid pair. You will most likely see this error in /var/log/secure or /var/log/auth or /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 MariaDB as the user database and an external 3rd party, the Kerberos server, as your authentication verification) is to add no_user_check to the pam_krb5 module:

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, probably other versions as well and possibly other linuxes.no_user_check does this:
“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

5. Test the Connection

$ mysql -u ad_admin -h dbversity –P4306 -p

6. Turn on SSL
Because we are sending passwords in cleartext, some clients (such as JDBC) will require that the connection use SSL.

$ mysql -u ad_admin -h dbversity -p –P4306

[root@dbversity ~]# mysql -u ad_admin -p’MyADPassWord’ -P 4306 -h dbversity
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 10.1.16-MariaDB MariaDB Server

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]> \s
————–
mysql Ver 15.1 Distrib 10.1.16-MariaDB, for Linux (x86_64) using EditLine wrapper

Connection id: 20
Current database:
Current user: ad_admin@dbversity.dbversity.net
SSL: Cipher in use is DHE-RSA-AES128-GCM-SHA256
Current pager: stdout
Using outfile: ”
Using delimiter: ;
Server: MariaDB
Server version: 10.1.16-MariaDB MariaDB Server
Protocol version: 10
Connection: dbversity via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
TCP port: 4306
Uptime: 51 min 33 sec

Threads: 2 Questions: 38 Slow queries: 0 Opens: 18 Flush tables: 1 Open tables: 11 Queries per second avg: 0.012
————–

MariaDB [(none)]>

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 just 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/MariaDB file is readable by MariaDB.

If PAM is working, you have a kerberos problem. Since this isn’t real Kerberos by any stretch of the imagination, you can actually just test the auth by running kinit -p USER, since that’s more or less 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_realmsections 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.

Configuration details :

root@dbversity ad_admin]# cat /etc/opt/rh/rh-mariadb101/my.cnf
# This group is read both both by the client and the server
# use it for options that affect everything
[client-server]
#################### <– Client-Server SSL Options–> #######################################
ssl
ssl-cipher = AES128+EECDH:AES128+EDH
ssl-ca = /etc/opt/rh/rh-mariadb101/pki/ca.pem
ssl-cert = /etc/opt/rh/rh-mariadb101/pki/mariadb_cert.pem
ssl-key = /etc/opt/rh/rh-mariadb101/pki/mariadb_private.key

[client]

[mysqld]

port = 4306
performance_schema = on
skip-show-database
symbolic-links = 0
local-infile = 0
bind-address = dbversity
wait_timeout = 1800
interactive_timeout = 1800

#################### <– MariaDB Plugins –> ###############################################

pam_use_cleartext_plugin
plugin-dir = /opt/rh/rh-mariadb101/root/usr/lib64/mysql/plugin/
plugin-load = auth_gssapi.so
plugin-load = auth_pam.so
plugin-load = simple_password_check.so
plugin-load = server_audit.so
plugin-load = pam_unix.so

##################### <– Kerberos configuration –> #######################################

#gssapi-keytab-path = /etc/opt/rh/rh-mariadb101/mariadb.keytab

##################### <– Logging switeches –> #######################################

general-log
general-log-file = /var/opt/rh/rh-mariadb101/lib/mysql/general_query.log
log-output = file
log_warnings = 3

# include all files from the config directory
#
!includedir /etc/opt/rh/rh-mariadb101/my.cnf.d

[root@dbversity ad_admin]#
[root@dbversity ad_admin]# cat /etc/pam.d/mariadb

auth required pam_krb5.so minimum_uid=1000 no_user_check
account required pam_krb5.so no_user_check

[root@dbversity ad_admin]#
[root@dbversity ad_admin]#
[root@dbversity ad_admin]# mysql -u sa_admin -p
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.16-MariaDB MariaDB Server

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]>
MariaDB [(none)]> select user,password,host,ssl_type from mysql.user;
+————-+——————————————-+—————-+———-+
| user | password | host | ssl_type |
+————-+——————————————-+—————-+———-+
| user1 | | % | ANY |
| user2 | | % | ANY |
| user3 | | % | ANY |
| user4 | | % | ANY |
| user5 | | % | ANY |
+————-+——————————————-+—————-+———-+
10 rows in set (0.00 sec)

MariaDB [(none)]> SHOW GRANTS FOR ad_admin@’%’;
+———————————————————————————————+
| Grants for ad_admin@% |
+———————————————————————————————+
| GRANT ALL PRIVILEGES ON *.* TO ‘ad_admin’@’%’ IDENTIFIED VIA pam USING ‘mariadb’ REQUIRE SSL |
+———————————————————————————————+
1 row in set (0.00 sec)

MariaDB [(none)]>

MariaDB [(none)]> exit
Bye
[root@dbversity ad_admin]#
[root@dbversity ad_admin]#
[root@dbversity ad_admin]# ssh ad_admin@dbversity

Password:
Last login: Wed Sep 28 2016 00:50:42 -0400 from GCOTDVMSW783314.dbversity.net
[ad_admin@dbversity ~]$
[ad_admin@dbversity ~]$
[ad_admin@dbversity ~]$ mysql -u ad_admin -p -P 4306 -h dbversity
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.1.16-MariaDB MariaDB Server

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]>

Tags: Configuring MariaDB PAM/Kerberos, Configuring MariaDB With PAM/Kerberos

Configuring MariaDB With PAM/Kerberos

Leave a Reply

Categories

Categories