Set Up and Troubleshoot Percona PAM With LDAP for External Authentication

In this blog, we’ll look at how to set up 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 set up 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

• Compile and install Samba.

• Create a domain environment with Samba.

• Add users and groups to this domain.

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:

1. 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.

2. Percona Server 5.7 with LDAP authentication

   • OS: CentOS 7

   • IP address: 172.16.0.20.

   • Hostname: ps-ldap-20.example.com.

We also have several users and groups.

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 the article Setting up Samba as an Active Directory Domain Controller as well.

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

[root@samba-10 ~]# yum -y install ntp
* * *
Installed:
  ntp.x86_64 0:4.2.6p5-25.el7.centos.1
Dependency Installed:
  autogen-libopts.x86_64 0:5.18-5.el7                     ntpdate.x86_64 0:4.2.6p5-25.el7.centos.1
[root@samba-10 ~]# ntpdate 0.centos.pool.ntp.org
 7 Apr 06:06:07 ntpdate[9788]: step time server 202.90.132.242 offset 0.807640 sec
[root@samba-10 ~]# systemctl enable ntpd.service
Created symlink from /etc/systemd/system/multi-user.target.wants/ntpd.service to /usr/lib/systemd/system/ntpd.service.
[root@samba-10 ~]# systemctl start ntpd.service

Install compilers and library dependencies for compiling Samba:

[root@samba-10 ~]# yum -y install gcc perl python-devel gnutls-devel libacl-devel openldap-devel
* * *
Installed:
  gcc.x86_64 0:4.8.5-11.el7  gnutls-devel.x86_64 0:3.3.24-1.el7  libacl-devel.x86_64 0:2.2.51-12.el7  openldap-devel.x86_64 0:2.4.40-13.el7  perl.x86_64 4:5.16.3-291.el7  python-devel.x86_64 0:2.7.5-48.el7
Dependency Installed:
  cpp.x86_64 0:4.8.5-11.el7                            cyrus-sasl.x86_64 0:2.1.26-20.el7_2               cyrus-sasl-devel.x86_64 0:2.1.26-20.el7_2             glibc-devel.x86_64 0:2.17-157.el7_3.1
  glibc-headers.x86_64 0:2.17-157.el7_3.1              gmp-devel.x86_64 1:6.0.0-12.el7_1                 gnutls-c++.x86_64 0:3.3.24-1.el7                      gnutls-dane.x86_64 0:3.3.24-1.el7
  kernel-headers.x86_64 0:3.10.0-514.10.2.el7          ldns.x86_64 0:1.6.16-10.el7                       libattr-devel.x86_64 0:2.4.46-12.el7                  libevent.x86_64 0:2.0.21-4.el7
  libmpc.x86_64 0:1.0.1-3.el7                          libtasn1-devel.x86_64 0:3.8-3.el7                 mpfr.x86_64 0:3.1.1-4.el7                             nettle-devel.x86_64 0:2.7.1-8.el7
  p11-kit-devel.x86_64 0:0.20.7-3.el7                  perl-Carp.noarch 0:1.26-244.el7                   perl-Encode.x86_64 0:2.51-7.el7                       perl-Exporter.noarch 0:5.68-3.el7
  perl-File-Path.noarch 0:2.09-2.el7                   perl-File-Temp.noarch 0:0.23.01-3.el7             perl-Filter.x86_64 0:1.49-3.el7                       perl-Getopt-Long.noarch 0:2.40-2.el7
  perl-HTTP-Tiny.noarch 0:0.033-3.el7                  perl-PathTools.x86_64 0:3.40-5.el7                perl-Pod-Escapes.noarch 1:1.04-291.el7                perl-Pod-Perldoc.noarch 0:3.20-4.el7
  perl-Pod-Simple.noarch 1:3.28-4.el7                  perl-Pod-Usage.noarch 0:1.63-3.el7                perl-Scalar-List-Utils.x86_64 0:1.27-248.el7          perl-Socket.x86_64 0:2.010-4.el7
  perl-Storable.x86_64 0:2.45-3.el7                    perl-Text-ParseWords.noarch 0:3.29-4.el7          perl-Time-HiRes.x86_64 4:1.9725-3.el7                 perl-Time-Local.noarch 0:1.2300-2.el7
  perl-constant.noarch 0:1.27-2.el7                    perl-libs.x86_64 4:5.16.3-291.el7                 perl-macros.x86_64 4:5.16.3-291.el7                   perl-parent.noarch 1:0.225-244.el7
  perl-podlators.noarch 0:2.5.1-3.el7                  perl-threads.x86_64 0:1.87-4.el7                  perl-threads-shared.x86_64 0:1.43-6.el7               unbound-libs.x86_64 0:1.4.20-28.el7
  zlib-devel.x86_64 0:1.2.7-17.el7
Complete!

Download, compile, and install Samba:

[root@samba-10 ~]# yum -y install wget
* * *
[root@samba-10 ~]# wget https://www.samba.org/samba/ftp/samba-latest.tar.gz
* * *
2017-04-07 06:16:59 (337 KB/s) - 'samba-latest.tar.gz' saved [21097045/21097045]
[root@samba-10 ~]# tar xzf samba-latest.tar.gz
[root@samba-10 ~]# cd samba-4.6.2/
[root@samba-10 samba-4.6.2]# ./configure --prefix=/opt/samba
Checking for program gcc or cc           : /usr/bin/gcc
Checking for program cpp                 : /usr/bin/cpp
Checking for program ar                  : /usr/bin/ar
Checking for program ranlib              : /usr/bin/ranlib
* * *
Checking compiler for PIE support                                                               : yes
Checking compiler for full RELRO support                                                        : yes
Checking if toolchain accepts -fstack-protector                                                 : yes
'configure' finished successfully (39.119s)
[root@samba-10 samba-4.6.2]# make
WAF_MAKE=1 python ./buildtools/bin/waf build
Waf: Entering directory `/root/samba-4.6.2/bin'
symlink: tevent.py -> python/tevent.py
* * *
[3773/3775] Linking default/source3/modules/libvfs_module_acl_xattr.so
[3774/3775] Linking default/source3/modules/libvfs_module_shadow_copy.so
[3775/3775] Linking default/source3/modules/libvfs_module_dirsort.so
Waf: Leaving directory `/root/samba-4.6.2/bin'
'build' finished successfully (6m58.144s)
[root@samba-10 samba-4.6.2]# make install
WAF_MAKE=1 python ./buildtools/bin/waf install
Waf: Entering directory `/root/samba-4.6.2/bin'
* creating /opt/samba/etc
* creating /opt/samba/private
* * *
* installing bin/default/source3/nmbd/nmbd.inst as /opt/samba/sbin/nmbd
* installing bin/default/file_server/libservice_module_s3fs.inst.so as /opt/samba/lib/service/s3fs.so
Waf: Leaving directory `/root/samba-4.6.2/bin'
'install' finished successfully (1m44.377s)

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.

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

[root@samba-10 samba-4.6.2]# echo "PATH=/opt/samba/sbin:/opt/samba/bin:/usr/sbin:/usr/bin" >> /etc/environment
[root@samba-10 samba-4.6.2]# PATH=/opt/samba/sbin:/opt/samba/bin:/usr/sbin:/usr/bin
[root@samba-10 samba-4.6.2]# which samba-tool
/opt/samba/bin/samba-tool

Setup a systemd script for Samba and ensure that this service auto starts on server boot:

[root@samba-10 samba-4.6.2]# echo "[Unit]
Description=Samba PDC
After=syslog.target network.target
[Service]
Type=forking
PIDFile=//opt/samba/var/run/samba.pid
ExecStart=/opt/samba/sbin/samba -D
ExecReload=/usr/bin/kill -HUP $MAINPID
ExecStop=/usr/bin/kill $MAINPID
[Install]
WantedBy=multi-user.target" > /etc/systemd/system/samba.service
[root@samba-10 samba-4.6.2]# systemctl enable samba.service
Created symlink from /etc/systemd/system/multi-user.target.wants/samba.service to /etc/systemd/system/samba.service.

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

[root@samba-10 samba-4.6.2]# rm -f /etc/krb5.conf
[root@samba-10 samba-4.6.2]# cd
[root@samba-10 ~]#

Done!

Create a Domain Environment With Samba

To set up 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:

[root@samba-10 ~]# samba-tool domain provision
Realm [EXAMPLE.ORG]: EXAMPLE.COM
 Domain [EXAMPLE]: EXAMPLE
 Server Role (dc, member, standalone) [dc]: dc
 DNS backend (SAMBA_INTERNAL, BIND9_FLATFILE, BIND9_DLZ, NONE) [SAMBA_INTERNAL]: SAMBA_INTERNAL
 DNS forwarder IP address (write 'none' to disable forwarding) [8.8.8.8]: 8.8.8.8
Administrator password:
Retype password:
Looking up IPv4 addresses
Looking up IPv6 addresses
No IPv6 address will be assigned
Setting up secrets.ldb
Setting up the registry
Setting up the privileges database
Setting up idmap db
Setting up SAM db
Setting up sam.ldb partitions and settings
Setting up sam.ldb rootDSE
Pre-loading the Samba 4 and AD schema
Adding DomainDN: DC=example,DC=com
Adding configuration container
Setting up sam.ldb schema
Setting up sam.ldb configuration data
Setting up display specifiers
Modifying display specifiers
Adding users container
Modifying users container
Adding computers container
Modifying computers container
Setting up sam.ldb data
Setting up well known security principals
Setting up sam.ldb users and groups
Setting up self join
Adding DNS accounts
Creating CN=MicrosoftDNS,CN=System,DC=example,DC=com
Creating DomainDnsZones and ForestDnsZones partitions
Populating DomainDnsZones and ForestDnsZones partitions
Setting up sam.ldb rootDSE marking as synchronized
Fixing provision GUIDs
A Kerberos configuration suitable for Samba AD has been generated at /opt/samba/private/krb5.conf
Once the above files are installed, your Samba4 server will be ready to use
Server Role:           active directory domain controller
Hostname:              samba-10
NetBIOS Domain:        EXAMPLE
DNS Domain:            example.com
DOMAIN SID:            S-1-5-21-1337223342-1741564684-602463608

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:

ERROR(ldb): uncaught exception - operations error at ../source4/dsdb/samdb/ldb_modules/password_hash.c:2820
  File "/opt/samba/lib64/python2.7/site-packages/samba/netcmd/__init__.py", line 176, in _run
    return self.run(*args, **kwargs)
  File "/opt/samba/lib64/python2.7/site-packages/samba/netcmd/domain.py", line 471, in run
    nosync=ldap_backend_nosync, ldap_dryrun_mode=ldap_dryrun_mode)
  File "/opt/samba/lib64/python2.7/site-packages/samba/provision/__init__.py", line 2175, in provision
    skip_sysvolacl=skip_sysvolacl)
  File "/opt/samba/lib64/python2.7/site-packages/samba/provision/__init__.py", line 1787, in provision_fill
    next_rid=next_rid, dc_rid=dc_rid)
  File "/opt/samba/lib64/python2.7/site-packages/samba/provision/__init__.py", line 1447, in fill_samdb
    "KRBTGTPASS_B64": b64encode(krbtgtpass.encode('utf-16-le'))
  File "/opt/samba/lib64/python2.7/site-packages/samba/provision/common.py", line 55, in setup_add_ldif
    ldb.add_ldif(data, controls)
  File "/opt/samba/lib64/python2.7/site-packages/samba/__init__.py", line 225, in add_ldif
    self.add(msg, controls)

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

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

[root@samba-10 ~]# ln -s /opt/samba/private/krb5.conf /etc

Start the Samba service:

[root@samba-10 ~]# systemctl start samba.service

Check network ports to see if Samba is running:

[root@samba-10 ~]# yum -y install net-tools
* * *
[root@samba-10 ~]# netstat -tapn
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:464             0.0.0.0:*               LISTEN      13296/samba
tcp        0      0 0.0.0.0:53              0.0.0.0:*               LISTEN      13302/samba
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      875/sshd
tcp        0      0 0.0.0.0:88              0.0.0.0:*               LISTEN      13296/samba
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      1327/master
tcp        0      0 0.0.0.0:636             0.0.0.0:*               LISTEN      13294/samba
tcp        0      0 0.0.0.0:445             0.0.0.0:*               LISTEN      13307/smbd
tcp        0      0 0.0.0.0:1024            0.0.0.0:*               LISTEN      13291/samba
tcp        0      0 0.0.0.0:1025            0.0.0.0:*               LISTEN      13291/samba
tcp        0      0 0.0.0.0:3268            0.0.0.0:*               LISTEN      13294/samba
tcp        0      0 0.0.0.0:3269            0.0.0.0:*               LISTEN      13294/samba
tcp        0      0 0.0.0.0:389             0.0.0.0:*               LISTEN      13294/samba
tcp        0      0 0.0.0.0:135             0.0.0.0:*               LISTEN      13291/samba
tcp        0      0 0.0.0.0:139             0.0.0.0:*               LISTEN      13307/smbd

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.

Add groups by running samba-tool group add group_name:

[root@samba-10 ~]# samba-tool group add support
Added group support
[root@samba-10 ~]# samba-tool group add dba
Added group dba
[root@samba-10 ~]# samba-tool group add search
Added group search

Add users by running samba-tool user create username:

[root@samba-10 ~]# samba-tool user create jericho
New Password:
Retype Password:
User 'jericho' created successfully
[root@samba-10 ~]# samba-tool user create jervin
New Password:
Retype Password:
User 'jervin' created successfully
[root@samba-10 ~]# samba-tool user create vishal
New Password:
Retype Password:
User 'vishal' created successfully
[root@samba-10 ~]# samba-tool user create sidd
New Password:
Retype Password:
User 'sidd' created successfully
[root@samba-10 ~]# samba-tool user create paul
New Password:
Retype Password:
User 'paul' created successfully
[root@samba-10 ~]# samba-tool user create arunjith
New Password:
Retype Password:
User 'arunjith' created successfully
[root@samba-10 ~]# samba-tool user create ldap
New Password:
Retype Password:
User 'ldap' created successfully

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

[root@samba-10 ~]# samba-tool group addmembers support jericho,jervin,vishal
Added members to group support
[root@samba-10 ~]# samba-tool group addmembers dba sidd,paul,arunjith
Added members to group dba
[root@samba-10 ~]# samba-tool group addmembers search ldap
Added members to group search

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

[root@samba-10 ~]# samba-tool user list
Administrator
arunjith
jericho
jervin
krbtgt
vishal
Guest
ldap
paul
sidd
[root@samba-10 ~]# samba-tool group list
Allowed RODC Password Replication Group
Enterprise Read-Only Domain Controllers
Denied RODC Password Replication Group
Pre-Windows 2000 Compatible Access
Windows Authorization Access Group
Certificate Service DCOM Access
Network Configuration Operators
Terminal Server License Servers
Incoming Forest Trust Builders
Read-Only Domain Controllers
Group Policy Creator Owners
Performance Monitor Users
Cryptographic Operators
Distributed COM Users
Performance Log Users
Remote Desktop Users
Account Operators
Event Log Readers
RAS and IAS Servers
Backup Operators
Domain Controllers
Server Operators
Enterprise Admins
Print Operators
Administrators
Domain Computers
Cert Publishers
DnsUpdateProxy
Domain Admins
Domain Guests
Schema Admins
Domain Users
Replicator
IIS_IUSRS
DnsAdmins
Guests
Users
support
search
dba
[root@samba-10 ~]# samba-tool group listmembers support
jervin
jericho
vishal
[root@samba-10 ~]# samba-tool group listmembers dba
arunjith
sidd
paul
[root@samba-10 ~]# samba-tool group listmembers search
ldap

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

And you're 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.

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

[root@ps-20 ~]# yum -y install nss-pam-ldapd

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

[root@ps-20 ~]# echo "uid nslcd
gid ldap
pagesize 1000
referrals off
idle_timelimit 800
filter passwd (&(objectClass=user)(objectClass=person)(!(objectClass=computer)))
map    passwd uid           sAMAccountName
map    passwd uidNumber     objectSid:S-1-5-21-1337223342-1741564684-602463608
map    passwd gidNumber     objectSid:S-1-5-21-1337223342-1741564684-602463608
map    passwd homeDirectory "/home/$cn"
map    passwd gecos         displayName
map    passwd loginShell    "/bin/bash"
filter group (|(objectClass=group)(objectClass=person))
map    group gidNumber      objectSid:S-1-5-21-1337223342-1741564684-602463608
uri ldaps://172.16.0.10
base dc=example,dc=com
tls_reqcert never
binddn cn=ldap,cn=Users,dc=example,dc=com
bindpw MyLdapPasswordDontCopyIt2017" > /etc/nslcd.conf

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.

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.

Run nslcd in debug mode:

[root@ps-20 ~]# nslcd -d
nslcd: DEBUG: add_uri(ldaps://172.16.0.10)
nslcd: DEBUG: ldap_set_option(LDAP_OPT_X_TLS_REQUIRE_CERT,0)
nslcd: version 0.8.13 starting
nslcd: DEBUG: unlink() of /var/run/nslcd/socket failed (ignored): No such file or directory
nslcd: DEBUG: initgroups("nslcd",55) done
nslcd: DEBUG: setgid(55) done
nslcd: DEBUG: setuid(65) done
nslcd: accepting connections

Test if LDAP lookups work by running id and getent passwd on another terminal:

[root@ps-20 ~]# id jervin
uid=1107(jervin) gid=1107(jervin) groups=1107(jervin),1103(support)
[root@ps-20 ~]# id paul
uid=1110(paul) gid=1110(paul) groups=1110(paul),1104(dba)
[root@ps-20 ~]# getent passwd
root:x:0:0:root:/root:/bin/bash
bin:x:1:1:bin:/bin:/sbin/nologin
daemon:x:2:2:daemon:/sbin:/sbin/nologin
adm:x:3:4:adm:/var/adm:/sbin/nologin
lp:x:4:7:lp:/var/spool/lpd:/sbin/nologin
sync:x:5:0:sync:/sbin:/bin/sync
shutdown:x:6:0:shutdown:/sbin:/sbin/shutdown
halt:x:7:0:halt:/sbin:/sbin/halt
mail:x:8:12:mail:/var/spool/mail:/sbin/nologin
operator:x:11:0:operator:/root:/sbin/nologin
games:x:12:100:games:/usr/games:/sbin/nologin
ftp:x:14:50:FTP User:/var/ftp:/sbin/nologin
nobody:x:99:99:Nobody:/:/sbin/nologin
avahi-autoipd:x:170:170:Avahi IPv4LL Stack:/var/lib/avahi-autoipd:/sbin/nologin
systemd-bus-proxy:x:999:997:systemd Bus Proxy:/:/sbin/nologin
systemd-network:x:998:996:systemd Network Management:/:/sbin/nologin
dbus:x:81:81:System message bus:/:/sbin/nologin
polkitd:x:997:995:User for polkitd:/:/sbin/nologin
tss:x:59:59:Account used by the trousers package to sandbox the tcsd daemon:/dev/null:/sbin/nologin
postfix:x:89:89::/var/spool/postfix:/sbin/nologin
sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin
user:x:1000:1000:user:/home/user:/bin/bash
mysql:x:27:27:Percona Server:/var/lib/mysql:/bin/false
nscd:x:28:28:NSCD Daemon:/:/sbin/nologin
nslcd:x:65:55:LDAP Client User:/:/sbin/nologin
Administrator:*:500:500::/home/Administrator:/bin/bash
arunjith:*:1111:1111::/home/arunjith:/bin/bash
jericho:*:1106:1106::/home/jericho:/bin/bash
jervin:*:1107:1107::/home/jervin:/bin/bash
krbtgt:*:502:502::/home/krbtgt:/bin/bash
vishal:*:1108:1108::/home/vishal:/bin/bash
Guest:*:501:501::/home/Guest:/bin/bash
ldap:*:1112:1112::/home/ldap:/bin/bash
paul:*:1110:1110::/home/paul:/bin/bash
sidd:*:1109:1109::/home/sidd:/bin/bash

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:

* * *
nslcd: [7b23c6] <passwd=1107> DEBUG: ldap_simple_bind_s("cn=ldap,cn=Users,dc=example,dc=com","***") (uri="ldaps://172.16.0.10")
nslcd: [7b23c6] <passwd=1107> DEBUG: ldap_result(): CN=jervin,CN=Users,DC=example,DC=com
nslcd: [7b23c6] <passwd=1107> DEBUG: ldap_result(): end of results (1 total)
nslcd: [3c9869] DEBUG: connection from pid=10468 uid=0 gid=0
nslcd: [3c9869] <passwd=1107> DEBUG: myldap_search(base="dc=example,dc=com", filter="(&(&(objectClass=user)(objectClass=person)(!(objectClass=computer)))(objectSid=�1�5�0�0�0�0�0�515�0�0�0ae68b44f�c2bce6778dde8...
* * *
nslcd: [5558ec] <passwd="paul"> DEBUG: myldap_search(base="dc=example,dc=com", filter="(&(&(objectClass=user)(objectClass=person)(!(objectClass=computer)))(sAMAccountName=paul))")
nslcd: [5558ec] <passwd="paul"> DEBUG: ldap_result(): CN=paul,CN=Users,DC=example,DC=com
nslcd: [5558ec] <passwd="paul"> DEBUG: ldap_result(): end of results (1 total)
* * *
nslcd: [e2a9e3] <passwd(all)> DEBUG: myldap_search(base="dc=example,dc=com", filter="(&(objectClass=user)(objectClass=person)(!(objectClass=computer)))")
nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=Administrator,CN=Users,DC=example,DC=com
nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=arunjith,CN=Users,DC=example,DC=com
nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=jericho,CN=Users,DC=example,DC=com
nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=jervin,CN=Users,DC=example,DC=com
nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=krbtgt,CN=Users,DC=example,DC=com
nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=vishal,CN=Users,DC=example,DC=com
nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=Guest,CN=Users,DC=example,DC=com
nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=ldap,CN=Users,DC=example,DC=com
nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=paul,CN=Users,DC=example,DC=com
nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=sidd,CN=Users,DC=example,DC=com
nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): end of results (10 total)

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

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

[root@ps-20 ~]# systemctl start nslcd.service
[root@ps-20 ~]# systemctl enable nslcd.service
Created symlink from /etc/systemd/system/multi-user.target.wants/nslcd.service to /usr/lib/systemd/system/nslcd.service.

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

[root@ps-20 ~]# rpm -Uvh https://www.percona.com/redir/downloads/percona-release/redhat/percona-release-0.1-4.noarch.rpm
Retrieving https://www.percona.com/redir/downloads/percona-release/redhat/percona-release-0.1-4.noarch.rpm
Preparing...                          ################################# [100%]
Updating / installing...
   1:percona-release-0.1-4            ################################# [100%]
[root@ps-20 ~]# yum -y install Percona-Server-server-57
* * *
[root@ps-20 ~]# mysqld --initialize-insecure --user=mysql
[root@ps-20 ~]# systemctl start mysqld.service
[root@ps-20 ~]# systemctl enable mysqld.service
Created symlink from /etc/systemd/system/mysql.service to /usr/lib/systemd/system/mysqld.service.
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.

Login to MySQL and change the root password:

[root@ps-20 ~]# mysql -uroot
mysql> SET PASSWORD=PASSWORD('MyNewAndImprovedPassword');

Install the Percona PAM plugin:

mysql> delete from mysql.user where user='';
Query OK, 0 rows affected (0.00 sec)
mysql> INSTALL PLUGIN auth_pam SONAME 'auth_pam.so';
Query OK, 0 rows affected (0.01 sec)
mysql> INSTALL PLUGIN auth_pam_compat SONAME 'auth_pam_compat.so';
Query OK, 0 rows affected (0.00 sec)

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

auth required pam_ldap.so
account required pam_ldap.so

Create a MySQL user that will authenticate via auth_pam:

mysql> CREATE USER jervin@'%' IDENTIFIED WITH auth_pam;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON support.* TO jervin@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Log in as this user and check grants:

[root@ps-20 ~]# mysql -u jervin
Password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 22
Server version: 5.7.17-13 Percona Server (GPL), Release 13, Revision fd33d43
Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> SHOW GRANTS;
+-----------------------------------------------------+
| Grants for jervin@%                                 |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO 'jervin'@'%'                  |
| GRANT ALL PRIVILEGES ON `support`.* TO 'jervin'@'%' |
+-----------------------------------------------------+
2 rows in set (0.00 sec)

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:

mysql> DROP USER jervin@'%'; 
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER ''@'' IDENTIFIED WITH auth_pam as 'mysqld,support=support_users,dba=dba_users';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER support_users@'%' IDENTIFIED BY 'some_password';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER dba_users@'%' IDENTIFIED BY 'some_password';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON support.* TO support_users@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO dba_users@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT PROXY ON support_users@'%' TO ''@'';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT PROXY ON dba_users@'%' TO ''@'';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

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

[root@ps-20 ~]# mysql -ujericho -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 25
Server version: 5.7.17-13 Percona Server (GPL), Release 13, Revision fd33d43
Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> SELECT user(), current_user(), @@proxy_user;
+-------------------+-----------------+--------------+
| user()            | current_user()  | @@proxy_user |
+-------------------+-----------------+--------------+
| jericho@localhost | support_users@% | ''@''        |
+-------------------+-----------------+--------------+
1 row in set (0.00 sec)
mysql> SHOW GRANTS;
+------------------------------------------------------------+
| Grants for support_users@%                                 |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'support_users'@'%'                  |
| GRANT ALL PRIVILEGES ON `support`.* TO 'support_users'@'%' |
+------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> quit
Bye
[root@ps-20 ~]# mysql -upaul -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 27
Server version: 5.7.17-13 Percona Server (GPL), Release 13, Revision fd33d43
Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> SELECT user(), current_user(), @@proxy_user;
+----------------+----------------+--------------+
| user()         | current_user() | @@proxy_user |
+----------------+----------------+--------------+
| paul@localhost | dba_users@%    | ''@''        |
+----------------+----------------+--------------+
1 row in set (0.00 sec)
mysql> SHOW GRANTS;
+------------------------------------------------+
| Grants for dba_users@%                         |
+------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'dba_users'@'%' |
+------------------------------------------------+
1 row in set (0.00 sec)

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

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.