MariaDB PAM LDAP authentication with newest SSSD daemon

Posted on , updated on by Yannick Jaquier
MariaDB ,

Table of contents

Preamble

In an old MariaDB instance we had MariaDB PAM LDAP users’ authentication based on our corporate LDAP server. We recently migrated this MariaDB 10.0.13 running on Red Hat Enterprise Linux Server release 6.10 (Santiago) in MariaDB 10.5.9 running on Red Hat Enterprise Linux release 8.2 (Ootpa).

And as you might guess nothing worked or I would not have written this blog post and if you are here it’s most probably because you are in same situation…

At the end we discovered that the issue was 100% linked to the Operating System and had nothing to see with MariaDB instance where configuration is piece of cake…

I recall that in this blog post I will not build any LDAP server and our clear objective is to use our corporate server (Oracle Unified Directory, OUD) already containing all our employees.

This first part will preparation of Os packages and database required parameters second part about configuration of legacy services nslcd – local LDAP name service daemon and third part will be a migration to the latest way of working using System Security Services Daemon (SSSD) service.

Extracted from official Red Hat documentation SSSD is the new way of accessing remote directories and has below benefits:

  • Reduced load on identity and authentication servers
  • Offline authentication
  • A single user account: improved consistency of the authentication process

My testing has been done on Red Hat Enterprise Linux release 8.2 (Ootpa) and MariaDB Community 10.5.9.

MariaDB PAM LDAP Linux and database configuration

For required packages it is quite simple:

dnf install openldap-clients nss-pam-ldapd pam pam-devel

Ensure you have the PAM LDAP library:

[root@server1 ~]# ll /lib64/security/pam_ldap.so
-rwxr-xr-x. 1 root root 33328 Aug 12  2018 /lib64/security/pam_ldap.so

From MariaDB it’s as simple as:

MariaDB [(none)]> install soname 'auth_pam';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> install plugin if not exists pam soname 'auth_pam';
Query OK, 0 rows affected, 1 warning (0.001 sec)

You can confirm the plugin is active with:

MariaDB [(none)]> show plugins;
+-------------------------------+----------+--------------------+-------------+---------+
| Name                          | Status   | Type               | Library     | License |
+-------------------------------+----------+--------------------+-------------+---------+
.
.
.
| pam                           | ACTIVE   | AUTHENTICATION     | auth_pam.so | GPL     |
+-------------------------------+----------+--------------------+-------------+---------+

In our my.cnf file we have added:

[client]
.
plugin_dir=/maria_10.5.9/software/10.5.9/lib/plugin
 
[mariadb]
plugin_dir=/maria_10.5.9/software/10.5.9/lib/plugin
.

This to avoid this below error:

[mariapoc@server1 ~]$ $MARIADB_HOME/bin/mysql --defaults-file=$MARIADB_HOME/conf/my.cnf --user=yjaquier -p
Enter password:
ERROR 1045 (28000): Plugin dialog could not be loaded: /usr/local/mysql/lib/plugin/dialog.so: cannot open shared object file: No such file or directory

Then I created and account for myself, account that is matching my corporate LDAP short login with below commands:

MariaDB [(none)]> CREATE USER 'yjaquier'@'%' IDENTIFIED VIA pam USING 'mariadb';
Query OK, 0 rows affected (0.029 sec)
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'yjaquier'@'%' ;
Query OK, 0 rows affected (0.029 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.002 sec)

Connection did not work and checking in /var/log/secure file I have seen:

Nov 19 10:02:35 SERVER1 auth_pam_tool[931652]: pam_ldap(mariadb:auth): error reading from nslcd: Connection reset by peer

And using journalctl -xfl I have seen:

Nov 19 10:02:34 server1 nslcd[931632]: [8b4567] <authc="yjaquier"> no available LDAP server found, sleeping 1 seconds
Nov 19 10:02:35 server1 nslcd[931632]: [8b4567] <authc="yjaquier"> failed to bind to LDAP server ldap://127.0.0.1/: Can't contact LDAP server: Transport endpoint is not connected
Nov 19 10:02:35 server1 nslcd[931632]: [8b4567] <authc="yjaquier"> no available LDAP server found: Can't contact LDAP server: Transport endpoint is not connected
Nov 19 10:02:35 server1 auth_pam_tool[931652]: pam_ldap(mariadb:auth): error reading from nslcd: Connection reset by peer

From this output it was clear that nslcd daemon is the process doing the LDAP call and that I needed to configure but it did not go as expected…

Ldapsearch configuration

To start debugging LDAP accesses I wanted at least to be able to perform a ldapsearch to confirm I was able to query my LDAP server. The installation of openldap is one of the prerequisite of MariaDB PAM LDAP authentication if you want to build your own LDAP server. But as explained I obviously plan to use our corporate LDAP server…

In /etc/openldap/ldap.conf I have only configured BASE and URI parameters:

BASE ou=employee,dc=company,dc=com
URI ldaps://ldap-server.company.com

Remark:
Our LDAP server is using the LDAP secure protocol so the ldaps protocol keyword.

My first ldapsearch command was on my own account:

[root@server1 ~]# ldapsearch -H "ldaps://ldap-server.company.com/" -b "ou=employee,dc=company,dc=com" -s sub "(uid=yjaquier)"
ldap_sasl_interactive_bind_s: Can't contact LDAP server (-1)
        additional info: error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed (self signed certificate in certificate chain)

Okay my LDAP server has a certificate self signed by my company. Looks okay for an internal one, why woud we pay for an internal company service…

Changing the way to connect does not display certificate error any more:

[root@server1 ~]# ldapsearch -x -H "ldaps://ldap-server.company.com/" -b "ou=employee,dc=company,dc=com" -s sub "(uid=yjaquier)"
ldap_sasl_bind(SIMPLE): Can't contact LDAP server (-1)

But (-d for debug option) error is still there in real:

[root@server1 ~]# ldapsearch -d 1 -x -H "ldaps://ldap-server.company.com/" -b "ou=employee,dc=company,dc=com" -s sub "(uid=yjaquier)"
ldap_url_parse_ext(ldaps://ldap-server.company.com/)
ldap_create
ldap_url_parse_ext(ldaps://ldap-server.company.com:636/??base)
ldap_sasl_bind
ldap_send_initial_request
ldap_new_connection 1 1 0
ldap_int_open_connection
ldap_connect_to_host: TCP ldap-server.company.com:636
ldap_new_socket: 3
ldap_prepare_socket: 3
ldap_connect_to_host: Trying 164.129.225.251:636
ldap_pvt_connect: fd: 3 tm: -1 async: 0
attempting to connect:
connect success
TLS trace: SSL_connect:before SSL initialization
TLS trace: SSL_connect:SSLv3/TLS write client hello
TLS trace: SSL_connect:SSLv3/TLS write client hello
TLS trace: SSL_connect:SSLv3/TLS read server hello
TLS certificate verification: depth: 2, err: 19, subject: /C=CH/O=STMicroelectronics International N.V./OU=Corporate Services/CN=STMicroelectronics Private Corporate Root CA1, issuer: /C=CH/O=STMicroelectronics International N.V./OU=Corporate Services/CN=STMicroelectronics Private Corporate Root CA1
TLS certificate verification: Error, self signed certificate in certificate chain
TLS trace: SSL3 alert write:fatal:unknown CA
TLS trace: SSL_connect:error in error
TLS: can't connect: error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed (self signed certificate in certificate chain).
ldap_err2string
ldap_sasl_bind(SIMPLE): Can't contact LDAP server (-1)

To go further with this certificates story I extracted the complete chain of certification of my LDAP server with:

[root@server1 ~]# cd /etc/openldap/certs
[root@server1 certs]# openssl s_client -showcerts -verify 5 -connect ldap-server.company.com:636  < /dev/null | awk '/BEGIN/,/END/{ if(/BEGIN/)    {a++}; out="ldap-server-cert"a".pem"; print >out}'
verify depth is 5
depth=2 C = CH, O = STMicroelectronics International N.V., OU = Corporate Services, CN = STMicroelectronics Private Corporate Root CA1
verify error:num=19:self signed certificate in certificate chain
verify return:1
depth=2 C = CH, O = STMicroelectronics International N.V., OU = Corporate Services, CN = STMicroelectronics Private Corporate Root CA1
verify return:1
depth=1 C = CH, O = STMicroelectronics International N.V., OU = Corporate Services, CN = STMicroelectronics Corporate Server CA1
verify return:1
depth=0 CN = server05.domain.com, OU = DIT, O = STMicroelectronics International NV
verify return:1
DONE
[root@server1 certs]# ll
total 12
-rw-r--r--. 1 root root  548 Nov 18 12:12 ldap-server-cert1.pem
-rw-r--r--. 1 root root 2403 Nov 18 12:12 ldap-server-cert2.pem
-rw-r--r--. 1 root root 2183 Nov 18 12:12 ldap-server-cert3.pem

If you verify them we get more or less what we got with ldapsearch command (or what nslcd daemon is throwing):

[root@server1 certs]# for cert in /etc/openldap/certs/*.pem; do openssl verify -show_chain $cert ; done
CN = server05.domain.com, OU = DIT INFRASTRUCTURE & SERVICES, O = STMicroelectronics International NV
error 20 at 0 depth lookup: unable to get local issuer certificate
error /etc/openldap/certs/ldap-server-cert1.pem: verification failed
C = CH, O = STMicroelectronics International N.V., OU = Corporate Services, CN = STMicroelectronics Corporate Server CA1
error 20 at 0 depth lookup: unable to get local issuer certificate
error /etc/openldap/certs/ldap-server-cert2.pem: verification failed
C = CH, O = STMicroelectronics International N.V., OU = Corporate Services, CN = STMicroelectronics Private Corporate Root CA1
error 18 at 0 depth lookup: self signed certificate
error /etc/openldap/certs/ldap-server-cert3.pem: verification failed

I had to insert into my MariaDB server my company Certificate Authority files.

[root@server1 ~]# cd /etc/pki/ca-trust/source/anchors
[root@server1 anchors]# cp ~/certs/companycorporateRootCA.cer.txt .
[root@server1 anchors]# cp ~/certs/companycorporateserverCA.cer.txt .
[root@server1 anchors]# update-ca-trust extract

If you verify again the certificate chain all is good:

[root@server1 anchors]# for cert in /etc/openldap/certs/*.pem; do openssl verify -show_chain $cert ; done
/etc/openldap/certs/ldap-server-cert1.pem: OK
Chain:
depth=0: CN = server05.domain.com, OU = DIT INFRASTRUCTURE & SERVICES, O = STMicroelectronics International NV (untrusted)
depth=1: C = CH, O = STMicroelectronics International N.V., OU = Corporate Services, CN = STMicroelectronics Corporate Server CA1
depth=2: C = CH, O = STMicroelectronics International N.V., OU = Corporate Services, CN = STMicroelectronics Private Corporate Root CA1
/etc/openldap/certs/ldap-server-cert2.pem: OK
Chain:
depth=0: C = CH, O = STMicroelectronics International N.V., OU = Corporate Services, CN = STMicroelectronics Corporate Server CA1 (untrusted)
depth=1: C = CH, O = STMicroelectronics International N.V., OU = Corporate Services, CN = STMicroelectronics Private Corporate Root CA1
/etc/openldap/certs/ldap-server-cert3.pem: OK
Chain:
depth=0: C = CH, O = STMicroelectronics International N.V., OU = Corporate Services, CN = STMicroelectronics Private Corporate Root CA1

And now the ldapsearch command is working:

[root@server1 ~]# ldapsearch -x -H "ldaps://ldap-server.company.com/" -b "ou=employee,dc=company,dc=com" -s sub "(uid=yjaquier)"
# extended LDIF
#
# LDAPv3
# base <ou=employee,dc=company,dc=com> with scope subtree
# filter: (uid=yjaquier)
# requesting: ALL
#

You can also remove the -x option and perform an authenticated LDAP query with (at the prompt supply your LDAP password):

[root@server1 ~]# ldapsearch -D "eduid=ed999999,ou=employee,dc=company,dc=com" -W -H "ldaps://ldap-server.company.com/" -b "ou=employee,dc=company,dc=com" -s sub "(uid=yjaquier)"
Enter LDAP Password:
# extended LDIF
#
# LDAPv3
# base <ou=employee,dc=company,dc=com> with scope subtree
# filter: (uid=yjaquier)
# requesting: ALL
#

My LDAP corporate server is working very well (I had no doubt about it to be honest) and LDAP queries are working well so from this technical point nothing forbid my MariaDB server to use it to authenticate database users.

The certificate configuration was also a pre-requisite for the nslcd daemon or you get error message like:

Nov 19 15:27:43 server1 nslcd[949124]: [8b4567] <authc="yjaquier"> failed to bind to LDAP server ldaps://ldap-server.company.com/: Can't contact LDAP server: error:1416F086:SSL routines:tls_process_server_certificate:certificate ver>
Nov 19 15:27:43 server1 nslcd[949124]: [8b4567] <authc="yjaquier"> no available LDAP server found, sleeping 1 seconds

You can also use a graphical free LDAP browser like Apache Directory Studio (http://directory.apache.org/studio/). With this tool you can automatically accept your server certificates. All search are graphical and much easier:

mariadb_pam_ldap01
mariadb_pam_ldap01

MariaDB PAM LDAP authentication with legacy nslcd configuration

For the MariaDB PAM LDAP authentication using legacy nslcd daemon configure to use pam_ldap.so library in /etc/pam.d/mariadb file:

[root@server1 ~]# cd /etc/pam.d/mariadb
auth    required pam_ldap.so
account required pam_ldap.so

Remark:
In our old configuration we had config=/etc/ldap_mariadb.conf keyword that is not working anymore…

You can add a useful debug option to have more verbose output to debug. Till you are not sure the MariaDB PAM LDAP module is effective it’s really a good idea to start in debug mode by default:

[root@server1 pam.d]# cat mariadb
auth    required pam_ldap.so debug
account required pam_ldap.so debug

As we have seen above MariaDB for PAM LDAP authentication is relying on nslcd daemon that is:

nslcd is a daemon that will do LDAP queries for local processes that want to do user, group and other naming lookups (NSS) or do user authentication, authorization or password modification (PAM).

So in /etc/nslcd.conf configuration file I configured uri and base parameters to match my corporate LDAP server and basedn:

uri ldaps://ldap-server.company.com/
base ou=employee,dc=company,dc=com

And restarted daemon with systemctl restart nslcd. To monitor what was going on you can use journalctl -xfl or tail the /var/log/secure file (you must activate the debug option of the MariaDB PAM LDAP configuration in /etc/pam.d/mariadb):.

[root@server1 etc]# journalctl -xfl
Nov 19 12:32:19 SERVER1 auth_pam_tool[960519]: pam_ldap(mariadb:auth): nslcd authentication; user=yjaquier
Nov 19 12:32:19 SERVER1 auth_pam_tool[960519]: pam_ldap(mariadb:auth): user not handled by nslcd
[root@server1 ~]# tail -f /var/log/secure
Nov 19 12:32:19 server1 auth_pam_tool[960519]: pam_ldap(mariadb:auth): nslcd authentication; user=yjaquier
Nov 19 12:32:19 server1 auth_pam_tool[960519]: pam_ldap(mariadb:auth): user not handled by nslcd

Honestly the debug information are quite poor and what has been key in my debugging session is to put nslcd service in debug mode. To do it use:

[root@server1 ~]# systemctl stop nslcd
[root@server1 ~]# /usr/sbin/nslcd -d > /tmp/nslcd.debug.log 2>&1

If you want to add a timestamp to log:

/usr/sbin/nslcd -d 2>&1 | awk '{ print strftime("%b %d %T"), $0}' > /tmp/nslcd.debug.log

With a failed authentication I got:

nslcd: [8b4567] <authc="yjaquier"> DEBUG: nslcd_pam_authc("yjaquier","mariadb","***")
nslcd: [8b4567] <authc="yjaquier"> DEBUG: myldap_search(base="ou=employee,dc=company,dc=com", filter="(&(objectClass=posixAccount)(uid=yjaquier))")
nslcd: [8b4567] <authc="yjaquier"> DEBUG: ldap_initialize(ldaps://ldap-server.company.com/)
nslcd: [8b4567] <authc="yjaquier"> DEBUG: ldap_set_rebind_proc()
nslcd: [8b4567] <authc="yjaquier"> DEBUG: ldap_set_option(LDAP_OPT_PROTOCOL_VERSION,3)
nslcd: [8b4567] <authc="yjaquier"> DEBUG: ldap_set_option(LDAP_OPT_DEREF,0)
nslcd: [8b4567] <authc="yjaquier"> DEBUG: ldap_set_option(LDAP_OPT_TIMELIMIT,0)
nslcd: [8b4567] <authc="yjaquier"> DEBUG: ldap_set_option(LDAP_OPT_TIMEOUT,0)
nslcd: [8b4567] <authc="yjaquier"> DEBUG: ldap_set_option(LDAP_OPT_NETWORK_TIMEOUT,0)
nslcd: [8b4567] <authc="yjaquier"> DEBUG: ldap_set_option(LDAP_OPT_REFERRALS,LDAP_OPT_ON)
nslcd: [8b4567] <authc="yjaquier"> DEBUG: ldap_set_option(LDAP_OPT_RESTART,LDAP_OPT_ON)
nslcd: [8b4567] <authc="yjaquier"> DEBUG: ldap_set_option(LDAP_OPT_X_TLS,LDAP_OPT_X_TLS_HARD)
nslcd: [8b4567] <authc="yjaquier"> DEBUG: ldap_simple_bind_s(NULL,NULL) (uri="ldaps://ldap-server.company.com/")
nslcd: [8b4567] <authc="yjaquier"> DEBUG: ldap_result(): end of results (0 total)
nslcd: [8b4567] <authc="yjaquier"> DEBUG: "yjaquier": user not found: No such object

The objectClass=posixAccount is an issue for me because we don’t have this property in our LDAP directory. So in my /etc/nslcd.conf file I have added a filter rule to change this objectClass=posixAccount by something we have in our directory (what you choose is really linked on how your LDAP server has been configured). The second option I have put in comment is also possible but I don’t like it too much:

# Yannick's filter
filter passwd (objectClass=company-login)
#filter passwd (uid=*)

And voila the next authentication trial is now working:

nslcd: [7b23c6] <authc="yjaquier"> DEBUG: nslcd_pam_authc("yjaquier","mariadb","***")
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: myldap_search(base="ou=employee,dc=company,dc=com", filter="(&(objectClass=company-login)(uid=yjaquier))")
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: ldap_initialize(ldaps://ldap-server.company.com/)
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: ldap_set_rebind_proc()
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: ldap_set_option(LDAP_OPT_PROTOCOL_VERSION,3)
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: ldap_set_option(LDAP_OPT_DEREF,0)
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: ldap_set_option(LDAP_OPT_TIMELIMIT,0)
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: ldap_set_option(LDAP_OPT_TIMEOUT,0)
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: ldap_set_option(LDAP_OPT_NETWORK_TIMEOUT,0)
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: ldap_set_option(LDAP_OPT_REFERRALS,LDAP_OPT_ON)
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: ldap_set_option(LDAP_OPT_RESTART,LDAP_OPT_ON)
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: ldap_set_option(LDAP_OPT_X_TLS,LDAP_OPT_X_TLS_HARD)
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: ldap_simple_bind_s(NULL,NULL) (uri="ldaps://ldap-server.company.com/")
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: ldap_result(): companyid=111111,ou=employee,dc=company,dc=com
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: myldap_search(base="companyid=111111,ou=employee,dc=company,dc=com", filter="(objectClass=*)")
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: ldap_initialize(ldaps://ldap-server.company.com/)
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: ldap_set_rebind_proc()
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: ldap_set_option(LDAP_OPT_PROTOCOL_VERSION,3)
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: ldap_set_option(LDAP_OPT_DEREF,0)
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: ldap_set_option(LDAP_OPT_TIMELIMIT,0)
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: ldap_set_option(LDAP_OPT_TIMEOUT,0)
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: ldap_set_option(LDAP_OPT_NETWORK_TIMEOUT,0)
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: ldap_set_option(LDAP_OPT_REFERRALS,LDAP_OPT_ON)
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: ldap_set_option(LDAP_OPT_RESTART,LDAP_OPT_ON)
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: ldap_set_option(LDAP_OPT_X_TLS,LDAP_OPT_X_TLS_HARD)
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: ldap_sasl_bind("companyid=111111,ou=employee,dc=company,dc=com","***") (uri="ldaps://ldap-server.company.com/") (ppolicy=yes)
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: got LDAP_CONTROL_PASSWORDPOLICYRESPONSE (No error)
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: myldap_search(base="companyid=111111,ou=employee,dc=company,dc=com", filter="(objectClass=*)")
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: ldap_result(): companyid=111111,ou=employee,dc=company,dc=com
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: ldap_unbind()
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: bind successful
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: myldap_search(base="ou=employee,dc=company,dc=com", filter="(&(objectClass=shadowAccount)(uid=yjaquier))")
nslcd: [7b23c6] <authc="yjaquier"> DEBUG: ldap_result(): end of results (0 total)
nslcd: [3c9869] DEBUG: connection from pid=961756 uid=0 gid=59831
nslcd: [3c9869] <authz="yjaquier"> DEBUG: nslcd_pam_authz("yjaquier","mariadb","","","")
nslcd: [3c9869] <authz="yjaquier"> DEBUG: myldap_search(base="ou=employee,dc=company,dc=com", filter="(&(objectClass=company-login)(uid=yjaquier))")
nslcd: [3c9869] <authz="yjaquier"> DEBUG: ldap_result(): companyid=111111,ou=employee,dc=company,dc=com
nslcd: [3c9869] <authz="yjaquier"> DEBUG: myldap_search(base="ou=employee,dc=company,dc=com", filter="(&(objectClass=shadowAccount)(uid=yjaquier))")
nslcd: [3c9869] <authz="yjaquier"> DEBUG: ldap_result(): end of results (0 total)

At MariaDB level:

[marmtrpq@server1 conf]$ /maria_10.5.9/software/10.5.9/bin/mariadb --defaults-file=/maria_10.5.9/software/10.5.9/conf/my.cnf --user=yjaquier -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 106
Server version: 10.5.9-MariaDB-log MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]> select user(),current_user();
+--------------------+----------------+
| user()             | current_user() |
+--------------------+----------------+
| yjaquier@localhost | yjaquier@%     |
+--------------------+----------------+
1 row in set (0.001 sec)
 
MariaDB [(none)]>

MariaDB PAM LDAP authentication with System Security Services Daemon (SSSD)

For this part I had to request help from Red Hat support as the documentation and the available ressources around this new daemon are quite sparse… The sssd service should already be installed, if not do it with dnf, and the configuration files are in /etc/sssd directory.

The SSSD daemon requires an unique number to fetch into your LDAP directory. Looking at the output of various ldapseach command for my own account I have decided to use employeenumber. This attribute should be in plenty of enterprise LDAP directory. I initially planned to use our internal customized employee id but this attribute contains letter where I work so Red Hat confirmed it is not possible to use it. To test the LDAP authentication it is also strongly suggested to activate the OS authentication with LDAP to easily test the feature.

For debugging it is highly interesting to install SSSD tools with:

Install sssctl with:
[root@server1 ~]# dnf install sssd-tools

I have started by creating my own config file (/etc/sssdsssd.conf). I have kept pam_ldap as domain name to mimic that has been done with nslcd daemon. When you start your trails it is more than interesting to activate a decent debug level. For debugging it is also interesting to implement the Name Service Switch (NSS) SSSD and Pluggable Authentication Module (PAM) service modules to be able to ssh with your LDAP account locally on your server:

[root@server1 sssd]# cat sssd.conf
[sssd]
services = pam, nss
domains = pam_ldap
debug_level = 9
 
[domain/pam_ldap]
debug_level = 9
id_provider = ldap
auth_provider = ldap
ldap_uri = ldaps://ldap-server.company.com
ldap_search_base = ou=employee,dc=company,dc=com
ldap_user_uid_number = employeenumber
ldap_user_gid_number = employeenumber
ldap_user_object_class = company-login
ldap_user_name = uid
access_provider = permit
sudo_provider = ldap
chpass_provider = ldap
autofs_provider = ldap
resolver_provider = ldap
 
[pam]
debug_level = 9
 
[nss]
debug_level = 9

I had an error while creating my own config file:

[root@server1 sssd]# systemctl status sssd
● sssd.service - System Security Services Daemon
   Loaded: loaded (/usr/lib/systemd/system/sssd.service; enabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Mon 2021-11-22 16:56:58 CET; 2min 19s ago
  Process: 1877030 ExecStart=/usr/sbin/sssd -i ${DEBUG_LOGGER} (code=exited, status=4)
 Main PID: 1877030 (code=exited, status=4)
 
Nov 22 16:56:58 server1 systemd[1]: sssd.service: Failed with result 'exit-code'.
Nov 22 16:56:58 server1 systemd[1]: Failed to start System Security Services Daemon.
Nov 22 16:56:58 server1 systemd[1]: sssd.service: Start request repeated too quickly.
Nov 22 16:56:58 server1 systemd[1]: sssd.service: Failed with result 'exit-code'.
Nov 22 16:56:58 server1 systemd[1]: Failed to start System Security Services Daemon.

Which can be solved with:

[root@server1 ~]# sssctl config-check
File ownership and permissions check failed. Expected root:root and 0600.
 
[root@server1 ~]# chmod 600 /etc/sssd/sssd.conf
[root@server1 ~]# chown root:root /etc/sssd/sssd.conf
[root@server1 ~]# sssctl config-check
Issues identified by validators: 0
 
Messages generated during configuration merging: 0
 
Used configuration snippet files: 0
[root@server1 sssd]# sssctl domain-list
implicit_files
pam_ldap

To debug the /var/log/sssd directory will become your best friend and more particularly sssd_pam_ldap.log file. You should see entries with the LDAP account you test. The LDAP search query used that can help you, with ldapsearch command, if this search query is supposed to work. As well as the result of this search command to understand if the query has returned a result or not…

Each time you change /etc/sssd/sssd.conf file use below command to stop/start SSSD daemon, purge log files and delete cached credentials:

[root@server1 sssd]# systemctl stop sssd; rm -f /var/log/sssd/*; rm -f /var/lib/sss/{db,mc}/*; systemctl start sssd; systemctl status sssd
● sssd.service - System Security Services Daemon
   Loaded: loaded (/usr/lib/systemd/system/sssd.service; enabled; vendor preset: enabled)
   Active: active (running) since Tue 2022-02-01 12:40:38 CET; 18ms ago
 Main PID: 4099413 (sssd)
    Tasks: 5 (limit: 100943)
   Memory: 49.3M
   CGroup: /system.slice/sssd.service
           ├─4099413 /usr/sbin/sssd -i --logger=files
           ├─4099414 /usr/libexec/sssd/sssd_be --domain implicit_files --uid 0 --gid 0 --logger=files
           ├─4099415 /usr/libexec/sssd/sssd_be --domain pam_ldap --uid 0 --gid 0 --logger=files
           ├─4099416 /usr/libexec/sssd/sssd_pam --uid 0 --gid 0 --logger=files
           └─4099417 /usr/libexec/sssd/sssd_nss --uid 0 --gid 0 --logger=files
 
Feb 01 12:40:38 server1 sssd[4099413]: Starting up
Feb 01 12:40:38 server1 sssd_be[4099415]: Starting up
Feb 01 12:40:38 server1 sssd_be[4099414]: Starting up
Feb 01 12:40:38 server1 sssd_pam[4099416]: Starting up
Feb 01 12:40:38 server1 sssd_nss[4099417]: Starting up

While investigating with Red Hat support, we noticed that, there are certain attributes that can not be retrieved anonymously from our corporate LDAP directory. So, we added following options under [domain] section in sssd.conf ile:

ldap_default_bind_dn = companyid=111111,ou=employee,dc=company,dc=com
ldap_default_authtok_type = password
ldap_default_authtok = secure_password

For my initial testing I have used my own account and we then created a dedicated LDAP account with minimum privilege to be used on all our MariaDB server where LDAP authentication would be used.

Of course it is not acceptable to have password in clear in this configuration file. You have an option to obfuscate it. Change the two below parameters with those values in sssd.conf file (leave password blank):

ldap_default_authtok_type = obfuscated_password
ldap_default_authtok =

The encrypt password with sss_obfuscate tool:

[root@server1 sssd]# sss_obfuscate --domain=pam_ldap
Enter password:
Re-enter password:
[root@server1 sssd]# sss_obfuscate --domain=pam_ldap
Enter password:
Re-enter password:
[root@server1 sssd]# grep ldap_default sssd.conf
ldap_default_bind_dn = companyid=111111,ou=employee,dc=company,dc=com
ldap_default_authtok_type = obfuscated_password
ldap_default_authtok = AAAQACovNMxOkYUwdmdxA0Tgo+X7oOBikZbQ9zXFZqaWTgR0tE4EmHElZDVAJjHwa2uQTk2vHdLes0iWLXo3/JD8N3EAAQID

Last but not least activate LDAP authentication with:

[root@server1 ~]# authconfig --enableldap --enableldapauth --ldapserver="ldaps://ldap-server.company.com" --ldapbasedn="ou=employee,dc=company,dc=com" --update
Running authconfig compatibility tool.
The purpose of this tool is to enable authentication against chosen services with authselect and minimum configuration. It does not provide all capabilities of authconfig.
 
IMPORTANT: authconfig is replaced by authselect, please update your scripts.
See man authselect-migration(7) to help you with migration to authselect
 
Executing: /usr/bin/authselect check
Executing: /usr/bin/authselect select sssd --force
Executing: /usr/bin/systemctl enable sssd.service
Executing: /usr/bin/systemctl stop sssd.service
Executing: /usr/bin/systemctl start sssd.service
[root@server1 ~]# cat /etc/sysconfig/authconfig
USELDAP=yes
USELDAPAUTH=yes
[root@server1 ~]# authselect list
- nis            Enable NIS for system authentication
- sssd           Enable SSSD for system authentication (also for local users only)
- winbind        Enable winbind for system authentication
[root@server1 ~]# authselect show sssd
.
.
[root@server1 ~]# authselect list-features sssd
.
.
[root@server1 sssd]# sssctl domain-status pam_ldap
Online status: Online
 
Active servers:
LDAP: ldap-server.company.com
 
Discovered LDAP servers:
- ldap-server.company.com

At that stage, as a trial, you should be able to ssh to localhost using your LDAP account and password:

[root@server1 ~]# ssh yjaquier@localhost
yjaquier@localhost's password:
Last login: Tue Feb  1 15:08:12 2022 from 127.0.0.1
Could not chdir to home directory : No such file or directory
/usr/bin/id: cannot find name for group ID 111111
[yjaquier@server1 ~]$ id yjaquier
uid=111111(yjaquier) gid=111111 groups=111111
[yjaquier@server1 ~]$

The user creation remains exactly the same and now /etc/pam.d/mariadb file must match the new SSSD configuration:

[root@server1 ~]# cat /etc/pam.d/mariadb
auth    required pam_sss.so domains=pam_ldap
account required pam_sss.so domains=pam_ldap

And finally LDAP authentication using SSSD worked !!

References

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

Share the knowledge!

Related Posts:

Leave a Reply

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

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>