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.
- 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 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 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 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:
[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
.
- 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)
- Login 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)
- Create proxy user and proxied accounts:
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.
- Let’s try logging in as “jericho” and “paul” and see if they inherit the privileges of their group.
[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.