How to Setup and Troubleshoot Percona PAM with LDAP for External Authentication

In this blog, we’ll look at how to setup and troubleshoot the Percona PAM authentication plugin.

We occasionally get requests from our support clients on how to get Percona Server for MySQL to authenticate with an external authentication service via LDAP or Active Directory. However, we normally do not have access to client’s infrastructure to help troubleshoot these cases. To help them effectively, we need to setup a testbed to reproduce their issues and guide them on how to get authentication to work. Fortunately, we only need to install Samba to provide an external authentication service for both LDAP and AD.

In this article, I will show you how to (a) compile and install Samba, (b) create a domain environment with Samba, (c) add users and groups to this domain and (d) get Percona Server to use these accounts for authentication via LDAP. In my follow-up article, I will discuss how to get MySQL to authenticate credentials with Active Directory.

My testbed environment consists of two machines

Samba PDC
OS: CentOS 7
IP Address: 172.16.0.10
Hostname: samba-10.example.com
Domain name: EXAMPLE.COM
DNS: 8.8.8.8(Google DNS), 8.8.4.4(Google DNS), 172.16.0.10(Samba)
Firewall: none

Percona Server 5.7 with LDAP authentication
OS: CentOS 7
IP Address: 172.16.0.20
Hostname: ps-ldap-20.example.com

and have several users and groups:

Domain Groups and Users
Support: jericho, jervin and vishal
DBA: sidd, paul and arunjith
Search: ldap

Compile and Install Samba

We will install an NTP client on the Samba PDC/samba-10.example.com machine because time synchronization is a requirement for domain authentication. We will also compile and install Samba from source because the Samba implementation in the official repository doesn’t include the Active Directory Domain Controller role. Hence, samba-tool is not included in the official repository. For our testbed, we need this tool because it makes it easier to provision a domain and manage users and groups. So, for CentOS 7, you can either build from source or use a trusted 3rd party build of Samba (as discussed in Samba’s wiki).

For more information, please read Setting up Samba as an Active Directory Domain Controller as well.

1. Install, configure, and run the NTP client. Ensure that this client service runs when the server boots up:

2. Install compilers and library dependencies for compiling Samba:

3. Download, compile and install Samba:

Please take note that when I downloaded Samba, the latest version was 4.6.2. If you have a problem with compiling the latest version of Samba, try using version 4.6.2.

4. Include executable path of Samba to the PATH variable so we can call samba binaries without specifying its absolute path:

5. Setup systemd script for Samba and ensure that this service auto starts on server boot

6. Remove existing /etc/krb5.conf, because the existing configuration prevents us from provisioning a new domain.

7. Done.

Create a domain environment with Samba

1. To setup a domain, all we need to do is to run “samba-tool domain provision” and pass the following details:

Realm: EXAMPLE.COM
Domain: EXAMPLE
Server Role: dc(domain controller)
DNS backend: SAMBA_INTERNAL
DNS forwarder IP address: 8.8.8.8

You will also need to supply the Administrator password. This account is used to join a workstation or server to a domain:

Please take note that if you get the error below, it’s likely due to not removing the existing /etc/krb5.conf before using samba-tool:

You could also get an error if you entered a simple password for the Administrator account.

2. Create a symlink of the generated krb5.conf in /etc. This configuration is used authenticate machines, accounts and services:

3. Start the Samba service:

4. Check network ports to see if Samba is running:

5. Done.

Add users and groups to this domain

Now that Samba is running we can add users and groups, and assign users to groups with samba-tool.

1. Add groups by running “samba-tool group add group_name”:

2. Add users by running “samba-tool user create username”:

3. Add users to their corresponding groups with “samba-tool group addmembers group_name user,user2,usern”:

4. Verify that users, groups and memberships exist with commands “samba-tool user list”, “samba-tool group list” and “samba-tool group listmembers group_name”:

For more information on using samba-tool, just run samba-tool --help.

5. Done.

How to get Percona Server to use these accounts for authentication via LDAP

We will be using the machine ps-ldap-20.example.com to offer MySQL service with LDAP authentication via Percona PAM. If you’re not familiar with Percona PAM, please have a look at this before moving forward.

At this point, our Samba service is running with users, groups and memberships added. We can now query Samba via LDAP ports 389 and 636. We will configure the server to do LDAP lookups when searching for users and groups. This is necessary because we use the name service to validate group membership. We will then install Percona Server for MySQL and configure our PAM plugin to use nss-pam-ldapd to authenticate to LDAP. Finally, we will test LDAP authentication on Percona Server for MySQL using a regular user and proxy user.

1. Install nss-pam-ldapd and nscd. We will use these packages to query LDAP server from our server:

2. Configure nss-pam-ldapd by incorporating our Samba’s LDAP settings:

As you can see above, this config contains LDAP settings, mapping custom LDAP attributes, and LDAP credentials. The value of objectSid was taken from “DOMAIN SID” that was generated when I created a new domain. So, be sure to use the value of “DOMAIN SID” generated on your end. Otherwise, your LDAP queries will not match any record. However, if you’re authenticating from an existing Windows AD server, you can obtain the value of “DOMAIN SID” by running “Get-ADDomain”. Also, you can take a look at this link to get to know more about other configurations for nslcd.conf.

3. Add LDAP lookup to nsswitch service by editing /etc/nsswitch.conf:

Find:
passwd: files sss
shadow: files sss
group: files sss

Replace with:
passwd: files sss ldap
shadow: files sss ldap
group: files sss ldap

4. Run nslcd in debug mode:

5. Test if LDAP lookups work by running “id ” and “getent passwd” on another terminal:

If you take a look at the nslcd terminal again, you will see that it’s trying to resolve the user and group identification with LDAP searches:

Now that we know nslcd is working, shut it down by running “Ctrl-C”.

6. Run nslcd normally and make sure it starts up on boot:

7. Install and run Percona Server for MySQL 5.7 and make sure it runs when the server boots up:

8. Login to MySQL and change the root password:

9. Install the Percona PAM plugin:

10. Configure Percona PAM to authenticate to LDAP by creating /etc/pam.d/mysqld with this content:

11. Create a MySQL user that will authenticate via auth_pam:

12. Login as this user and check grants:

It works! However, if you have 100 support users who have the same MySQL privileges, creating 100 MySQL users is tedious and can be difficult to maintain. If belonging to a group has certain MySQL privileges, setup proxy users instead to map a user’s privilege to its defined group. We will implement this for both dba and support users in the next step.

For now, delete the user we just created:

13. Create proxy user and proxied accounts:

To know more about setting up proxy users, see this article written by Stephane.

14. Let’s try logging in as “jericho” and “paul” and see if they inherit the privileges of their group.

As you can see, they did inherit the MySQL privileges of their groups.

15. Done.

Conclusion

To be honest, setting up Percona PAM with LDAP can be challenging if you add this functionality with existing infrastructure. But hopefully, by setting this up in a lab environment from scratch, and doing some tests, you’ll be confident enough to incorporate this feature in production environments.