Having separate credentials to log onto a server and access a database on that network is a pain. Why not provide users just one set of credentials for both services? This is a quick how-to guide on how to setup LDAP authentication for MariaDB. As it turns out it’s insanely easy to setup. (The official MariaDB documentation on the subject can be quite hard to find however - which may or may not be the primary reason for this blog post…).

This tutorial assumes that the database server has already been configured to authenticate users via LDAP (blog post on this later!). If you haven’t already, install MariaDB and set it up:

sudo yum install mariadb mariadb-server mariadb-devel
sudo systemctl start mariadb
sudo systemctl enable mariadb
sudo mysql_secure_installation  # yes to all prompts

The next step is to login as the root user and enable the auth_pam plugin. auth_pam delegates MariaDB user authentication to the base operating system through PAM. PAM, or Pluggable Authentication Modules, allow configuring authentication for different software packages via text file. More on this in a later in this blog post.

MariaDB ships with this plugin present, but not enabled. You can install it with INSTALL SONAME 'auth_pam';. To use PAM authentication for a user, create that user with IDENTIFIED VIA pam in place of where you’d usually specify the user password.

mysql -u root -p
INSTALL SONAME 'auth_pam';
CREATE USER 'jstaf'@'%' IDENTIFIED VIA pam;

If I wanted to create a test database for that user account (I’ve named the database after the demo user in this case…):

CREATE DATABASE jstaf;
GRANT ALL ON jstaf.* TO 'jstaf'@'%';

Ok so now that we’ve setup our demo user and our test database, we’ll need to actually setup the PAM config for MariaDB. MariaDB does its best to remain compatible with the original MySQL codebase it was forked from, and this case it is no different - the PAM config for MariaDB is /etc/pam.d/mysql by default.

Create /etc/pam.d/mysql with the following contents:

#%PAM-1.0
auth      required       pam_ldap.so
account   required       pam_ldap.so

As PAM configs go, this is the absolute minimum. After the first line of the file (which merely identifies it as a PAM config to the OS), the following two lines state that:

  • Authentication requires successful authentication using pam_ldap.so, the PAM module responsible for handling LDAP authorization. A user will need to successfully authenticate via password to pass the first line.

  • The account is indeed valid and meets any non-password authorizations (also handled through pam_ldap.so).

Clever users will notice that this same lines could be swapped out for other authentication modules. As an example, pam_unix.so covers standard authentication using local user accounts - there’s a PAM module for pretty much every authentication mechanism out there.

Now all that’s left to do is login with your brand new LDAP-enabled user account:

mysql -u jstaf -p

Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>

Success!