Set up LDAP authentication for MySQL and MongoDB

Set up LDAP authentication for MySQL and MongoDBBy the end of this article, you should be able to have a Percona Server for MongoDB and Percona Server for MySQL instance able to authenticate on an OpenLDAP backend. While this is mostly aimed at testing scenarios, it can be easily extended for production by following the OpenLDAP production best practices i.e. attending to security and high availability.

The first step is to install OpenLDAP via the

slapd

  package in Ubuntu.

sudo apt update 
sudo apt install slapd ldap-utils

During installation, it will ask you for a few things listed below:

  • DNS Domain Name:
    ldap.local
  • Organization Name:
    Percona
  • Administrator password:
    percona

All these values are arbitrary, you can choose whatever suits your organization—especially the password.

Once

slapd

  is running, we can create our logical groups and actual users on the LDAP server. To make it simple, we use LDIF files instead of GUIs. Our first file,

perconadba.ldif

 contains our

perconadba

  group definition. Take note of the root name part

dc=ldap,dc=local

  it is simply the broken down value of our DNS Domain Name during the installation of

slapd

 .

dn: ou=perconadba,dc=ldap,dc=local 
objectClass: organizationalUnit 
ou: perconadba

We can add this definition into LDAP with the command shown below. With the

-W

  option, it will prompt you for a password.

ldapadd -x -W -D "cn=admin,dc=ldap,dc=local" -f perconadba.ldif

The next step is to create our user in LDAP, this user will be looked up by both MongoDB and MySQL during authentication to verify their password. Our LDIF file (

percona.ldif

 ) would look like this:

dn: uid=percona,ou=perconadba,dc=ldap,dc=local 
objectClass: top 
objectClass: account 
objectClass: posixAccount 
objectClass: shadowAccount 
cn: percona 
uid: percona 
uidNumber: 1100 
gidNumber: 100 
homeDirectory: /home/percona 
loginShell: /bin/bash 
gecos: percona 
userPassword: {crypt}x 
shadowLastChange: -1 
shadowMax: -1 
shadowWarning: -1

The

-1

  values for the

shadow*

  fields are important, we set them to negative to mean the password shadow does not expire. If these are set to zero (0), then MySQL will not be able to authenticate since PAM will complain that the password has expired and needs to be changed.

We can then add this user into LDAP, again the command below will ask for the admin password we entered during slapd’s installation.

ldapadd -x -W -D "cn=admin,dc=ldap,dc=local" -f percona.ldif

To verify, we can search for the user we just entered using the command below. Notice we used the -w parameter to specify the admin password inline.

ldapsearch -x -D 'cn=admin,dc=ldap,dc=local' -w percona  
	-b 'ou=perconadba,dc=ldap,dc=local' '(uid=percona)'

As last step on setting up our LDAP user properly is to give it a valid password. The -s parameter below is the actual password we will set for this user.

ldappasswd -s percona -D "cn=admin,dc=ldap,dc=local" -w percona  
	-x "uid=percona,ou=perconadba,dc=ldap,dc=local"

At this point you should have a generic LDAP server that should work for both MongoDB and MySQL.

PAM Configuration for MySQL

To make this work for a MySQL and support PAM authentication, take note of the following configuration files. Instructions on setting up PAM for MySQL is aplenty on this blog I just need to specify Ubuntu Bionic specific configuration files to make it work.

/etc/nslcd.conf

The only important difference with this configuration—compared to Jaime’s post for example—is the values for

filter

 . If you are using Windows Active Directory, the map values are also important (posixAccount objectClass has been deprecated on recent release of Windows Active Directory).

uid nslcd 
gid nslcd 
uri ldap:///localhost 
base ou=perconadba,dc=ldap,dc=local 
filter passwd (&(objectClass=account)(objectClass=posixAccount)) 
filter group (&(objectClass=shadowAccount)(objectClass=account)) 
map    passwd uid           uid 
map    passwd uidNumber     uidNumber 
map    passwd gidNumber     gidNumber 
map    passwd homeDirectory "/home/$uid" 
map    passwd gecos         uid 
map    passwd loginShell    "/bin/bash" 
map    group gidNumber      gidNumber 
binddn cn=admin,dc=ldap,dc=local 
bindpw percona 
tls_cacertfile /etc/ssl/certs/ca-certificates.crt

/etc/nsswitch.conf

Also for nsswitch.conf, make sure that passwd, group and shadow does LDAP lookups.

... 
passwd:         compat systemd ldap 
group:          compat systemd ldap 
shadow:         compat systemd ldap 
gshadow:        files ldap 
...

SASL for MongoDB

Adamo’s excellent post on MongoDB LDAP Authentication has all the details on configuring MongoDB itself. To complement that, if you use this LDAP test setup, you need the take note of the following configuration files with specific differences.

/etc/mongod.conf

In the

mongod.conf

  configuration file, I explicitly added the saslauthd socket path.

security: 
  authorization: enabled 
setParameter: 
  saslauthdPath: /var/run/saslauthd/mux 
  authenticationMechanisms: PLAIN,SCRAM-SHA-1

/etc/saslauthd.conf

For the saslauthd daemon configuration, the configuration has no actual difference – just take note I used differing values based on the LDAP setup above. Specifically, the

ldap_filter

  and

ldap_search_base

  are key options here which are concatenated during an LDAP search to come up with the

percona

  user’s account information.

ldap_servers: ldap://localhost:389/ 
ldap_search_base: ou=perconadba,dc=ldap,dc=local 
ldap_filter: (uid=%u) 
# Optional: specify a user to perform ldap queries 
ldap_bind_dn: CN=admin,DC=ldap,DC=local 
# Optional: specify ldap user’s passwordi 
ldap_password: percona

Enterprise quality features should not be complex and expensive. Tell us about your experience with our software and external authentication in the comments below!

Leave a Reply

Your email address will not be published. Required fields are marked *