Configuring PAM Authentication and User Mapping with LDAP Authentication - Source

Revision: 115695
User: Geoff Montee
Date: 2022-04-27 20:54

In this article, we will walk through the configuration of PAM authentication using the ##[[authentication-plugin-pam|pam]]## authentication plugin and user and group mapping with the ##[[user-and-group-mapping-with-pam|pam_user_map]]## PAM module. The primary authentication will be handled by the ##[[https://linux.die.net/man/5/pam_ldap|pam_ldap]]## PAM module, which performs LDAP authentication. We will also set up an OpenLDAP server.

<<toc>>

== Hypothetical Requirements

In this walkthrough, we are going to assume the following hypothetical requirements:

* The LDAP user ##foo## should be mapped to the MariaDB user ##bar##. (##foo: bar##)
* Any LDAP user in the LDAP group ##dba## should be mapped to the MariaDB user ##dba##. (##@dba: dba##)

== Setting up the OpenLDAP Server

Before we can use LDAP authentication, we first need to set up our OpenLDAP Server. This is usually done on a server that is completely separate from the database server.

=== Installing the OpenLDAP Server and Client Components

On the server acting as the OpenLDAP Server, first, we need to install the OpenLDAP components.

On RHEL, CentOS, and other similar Linux distributions that use [[rpm|RPM packages]], that would go like this:

<<code>>
sudo yum install openldap openldap-servers openldap-clients nss-pam-ldapd
<</code>>

=== Configuring the OpenLDAP Server

Next, let's to configure the OpenLDAP Server. The easiest way to do that is to copy the template configuration file that is included with the installation. In many installations, that will be at ##/usr/share/openldap-servers/DB_CONFIG.example##. For example:

<<code>>
sudo cp /usr/share/openldap-servers/DB_CONFIG.example /var/lib/ldap/DB_CONFIG
sudo chown ldap. /var/lib/ldap/DB_CONFIG
<</code>>

==== Configuring the OpenLDAP Port

Sometimes it is useful to change the port used by OpenLDAP. For example, some cloud environments block well-known authentication services, so they block the default LDAP port.

On some systems, the port can be changed by setting ##SLAPD_URLS## in ##/etc/sysconfig/slapd##:

<<code>>
SLAPD_URLS="ldapi:/// ldap://0.0.0.0:3306/"
<</code>>

I used ##3306## because that is the port that is usually used by ##mysqld##, so I know that it is not blocked.

=== Starting the OpenLDAP Server

Next, let's start the OpenLDAP Server and configure it to start on reboot. On ##[[systemd]]## systems, that would go like this:

<<code>>
sudo systemctl start slapd
sudo systemctl enable slapd
<</code>>

=== Installing the Standard LDAP objectClasses

In order to use LDAP for authentication, we also need to install some standard ##objectClasses##, such as ##posixAccount## and ##posixGroup##. In LDAP, things like ##objectClasses## are defined in ##[[https://www.digitalocean.com/community/tutorials/how-to-use-ldif-files-to-make-changes-to-an-openldap-system|LDIF]]## files. In many installations, these specific ##objectClasses## are defined in ##/etc/openldap/schema/nis.ldif##. ##nis.ldif## also depends on ##core.ldif## and ##cosine.ldif##. However, ##core.ldif## is usually installed by default.

We can install them with ##[[http://www.openldap.org/software/man.cgi?query=ldapmodify&sektion=1&apropos=0&manpath=OpenLDAP+2.4-Release|ldapmodify]]##:

<<code>>
sudo ldapmodify -a -Y EXTERNAL -H ldapi:/// -f /etc/openldap/schema/cosine.ldif
sudo ldapmodify -a -Y EXTERNAL -H ldapi:/// -f /etc/openldap/schema/nis.ldif
<</code>>

=== Creating the LDAP Directory Manager User

Next, let’s create a directory manager user. We can do this by using OpenLDAP's [[https://www.openldap.org/doc/admin24/slapdconf2.html|olc]] configuration system to change the ##[[https://www.openldap.org/doc/admin24/slapdconf2.html#olcRootDN:%20%3CDN%3E|olcRootDN]]## directive to the DN of the directory manager user, which means that the user will be a privileged LDAP user that is not subject to access controls. We will also set the root password for the user by changing the ##[[https://www.openldap.org/doc/admin24/slapdconf2.html#olcRootPW:%20%3Cpassword%3E|olcRootPW]]## directive.

We will also set the DN suffix for our backend LDAP database by changing the ##[[https://www.openldap.org/doc/admin24/slapdconf2.html#olcSuffix:%20%3Cdn%20suffix%3E|olcSuffix]]## directive.

Let’s use the ##[[http://www.openldap.org/software/man.cgi?query=slappasswd&apropos=0&sektion=8&manpath=OpenLDAP+2.4-Release&format=html|slappasswd]]## utility to generate a password hash from a clear-text password. Simply execute:

<<code>>
slappasswd
<</code>>

This utility should provide a password hash that looks kind of like this: ##{SSHA}AwT4jrvmokeCkbDrFAnGvzzjCMb7bvEl##

OpenLDAP's [[https://www.openldap.org/doc/admin24/slapdconf2.html|olc]] configuration system also uses  ##[[https://www.digitalocean.com/community/tutorials/how-to-use-ldif-files-to-make-changes-to-an-openldap-system|LDIF]]## files. Now that we have the password hash, let’s create an ##LDIF## file to create the directory manager user:

<<code>>
tee ~/setupDirectoryManager.ldif <<EOF
dn: olcDatabase={1}monitor,cn=config
changetype: modify
replace: olcAccess
olcAccess: {0}to * 
    by dn.base="gidNumber=0+uidNumber=0,cn=peercred,cn=external,cn=auth" read 
    by dn.base="cn=Manager,dc=support,dc=mariadb,dc=com" read 
    by * none

dn: olcDatabase={2}hdb,cn=config
changetype: modify
replace: olcSuffix
olcSuffix: dc=support,dc=mariadb,dc=com

dn: olcDatabase={2}hdb,cn=config
changetype: modify
replace: olcRootDN
olcRootDN: cn=Manager,dc=support,dc=mariadb,dc=com

dn: olcDatabase={2}hdb,cn=config
changetype: modify
add: olcRootPW
olcRootPW: {SSHA}AwT4jrvmokeCkbDrFAnGvzzjCMb7bvEl

dn: olcDatabase={2}hdb,cn=config
changetype: modify
add: olcAccess
olcAccess: {0}to attrs=userPassword,shadowLastChange 
    by   dn="cn=Manager,dc=support,dc=mariadb,dc=com" write 
    by anonymous auth 
    by self write 
    by * none
olcAccess: {1}to dn.base="" 
    by * read
olcAccess: {2}to * 
    by dn="cn=Manager,dc=support,dc=mariadb,dc=com" write 
    by * read
EOF
<</code>>

Note that I am using the ##dc=support,dc=mariadb,dc=com## domain for my directory. You can change this to whatever is relevant to you.

Now let’s run the ldif file with ##[[http://www.openldap.org/software/man.cgi?query=ldapmodify&sektion=1&apropos=0&manpath=OpenLDAP+2.4-Release|ldapmodify]]##:

<<code>>
sudo ldapmodify -Y EXTERNAL -H ldapi:/// -f ~/setupDirectoryManager.ldif
<</code>>

We will use the new directory manager user to make changes to the LDAP directory after this step.

=== Creating the Structure of the Directory

Next, let's create the structure of the directory by creating parts of our tree.

<<code>>
tee ~/setupDirectoryStructure.ldif <<EOF
dn: dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: dcObject
objectclass: organization
o: MariaDB Support Team
dc: support

dn: cn=Manager,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: organizationalRole
cn: Manager
description: Directory Manager

dn: ou=People,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: organizationalUnit
ou: People

dn: ou=Groups,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: organizationalUnit
ou: Groups

dn: ou=System Users,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: organizationalUnit
ou: System Users
EOF
<</code>>

Now let’s use our new directory manager user and run the ##[[https://www.digitalocean.com/community/tutorials/how-to-use-ldif-files-to-make-changes-to-an-openldap-system|LDIF]]## file with ##[[http://www.openldap.org/software/man.cgi?query=ldapmodify&sektion=1&apropos=0&manpath=OpenLDAP+2.4-Release|ldapmodify]]##:

<<code>>
ldapmodify -a -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -f ~/setupDirectoryStructure.ldif
<</code>>

=== Creating the LDAP Users and Groups

Let's go ahead and create the LDAP users and groups that we are using for this hypothetical scenario.

First, let's create the the ##foo## user:

<<code>>
tee ~/createFooUser.ldif <<EOF
dn: uid=foo,ou=People,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: account
objectClass: posixAccount
objectClass: shadowAccount
cn: foo
uid: foo
uidNumber: 16859
gidNumber: 100
homeDirectory: /home/foo
loginShell: /bin/bash
gecos: foo
userPassword: {crypt}x
shadowLastChange: -1
shadowMax: -1
shadowWarning: 0
EOF
ldapmodify -a -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -f ~/createFooUser.ldif
<</code>>

Then let's create a couple users to go into the ##dba## group:

<<code>>
tee ~/createDbaUsers.ldif <<EOF
dn: uid=gmontee,ou=People,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: account
objectClass: posixAccount
objectClass: shadowAccount
cn: gmontee
uid: gmontee
uidNumber: 16860
gidNumber: 100
homeDirectory: /home/gmontee
loginShell: /bin/bash
gecos: gmontee
userPassword: {crypt}x
shadowLastChange: -1
shadowMax: -1
shadowWarning: 0

dn: uid=bstillman,ou=People,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: account
objectClass: posixAccount
objectClass: shadowAccount
cn: bstillman
uid: bstillman
uidNumber: 16861
gidNumber: 100
homeDirectory: /home/bstillman
loginShell: /bin/bash
gecos: bstillman
userPassword: {crypt}x
shadowLastChange: -1
shadowMax: -1
shadowWarning: 0
EOF
ldapmodify -a -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -f ~/createDbaUsers.ldif
<</code>>

Note that each of these users needs a password, so we can set it for each user with ##[[http://www.openldap.org/software/man.cgi?query=ldappasswd&apropos=0&sektion=1&manpath=OpenLDAP+2.4-Release&format=html|ldappasswd]]##:

<<code wrap=true>>
ldappasswd -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -S uid=foo,ou=People,dc=support,dc=mariadb,dc=com
ldappasswd -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -S uid=gmontee,ou=People,dc=support,dc=mariadb,dc=com
ldappasswd -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -S uid=bstillman,ou=People,dc=support,dc=mariadb,dc=com
<</code>>

And then let's create our ##dba## group

<<code>>
tee ~/createDbaGroup.ldif <<EOF
dn: cn=dba,ou=Groups,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: posixGroup
gidNumber: 678
EOF
ldapmodify -a -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -f ~/createDbaGroup.ldif
<</code>>

And then let's add our two users to it:

<<code wrap=true>>
tee ~/addUsersToDbaGroup.ldif <<EOF
dn: cn=dba,ou=Groups,dc=support,dc=mariadb,dc=com
changetype: modify
add: memberuid
memberuid: gmontee

dn: cn=dba,ou=Groups,dc=support,dc=mariadb,dc=com
changetype: modify
add: memberuid
memberuid: bstillman
EOF
ldapmodify -a -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -f ~/addUsersToDbaGroup.ldif
<</code>>

We also need to create LDAP users with the same name as the ##bar## and ##dba## MariaDB users. See [[user-and-group-mapping-with-pam/#pam-user-with-same-name-as-mapped-mariadb-user-must-exist|here]] to read more about why. No one will be logging in as these users, so they do not need passwords. Instead of the ##People## ##organizationalUnit##, we will create them in the ##System Users## ##organizationalUnit##.

<<code>>
tee ~/createSystemUsers.ldif <<EOF
dn: uid=bar,ou=System Users,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: account
objectClass: posixAccount
objectClass: shadowAccount
cn: bar
uid: bar
uidNumber: 16862
gidNumber: 100
homeDirectory: /home/bar
loginShell: /bin/bash
gecos: bar
userPassword: {crypt}x
shadowLastChange: -1
shadowMax: -1
shadowWarning: 0

dn: uid=dba,ou=System Users,dc=support,dc=mariadb,dc=com
objectClass: top
objectClass: account
objectClass: posixAccount
objectClass: shadowAccount
cn: dba
uid: dba
uidNumber: 16863
gidNumber: 100
homeDirectory: /home/dba
loginShell: /bin/bash
gecos: dba
userPassword: {crypt}x
shadowLastChange: -1
shadowMax: -1
shadowWarning: 0
EOF
ldapmodify -a -x -D cn=Manager,dc=support,dc=mariadb,dc=com -W -f ~/createSystemUsers.ldif
<</code>>

== Setting up the MariaDB Server

At this point, we can move onto setting up the MariaDB Server.

=== Installing LDAP and PAM Libraries

First, we need to make sure that the LDAP and PAM libraries are installed.

On RHEL, CentOS, and other similar Linux distributions that use [[rpm|RPM packages]], we need to install the following packages:

<<code>>
sudo yum install openldap-clients nss-pam-ldapd pam pam-devel
<</code>>

=== Configuring LDAP

Next, let's configure LDAP on the system. We can use ##[[https://linux.die.net/man/8/authconfig|authconfig]]## for this:

<<code>>
sudo authconfig --enableldap \
   --enableldapauth \
   --ldapserver="ldap://172.30.0.238:3306" \
   --ldapbasedn="dc=support,dc=mariadb,dc=com" \
   --enablemkhomedir \
   --update
<</code>>

Be sure to replace ##-–ldapserver## and ##-–ldapbasedn## with values that are relevant for your environment.

=== Installing the pam_user_map PAM Module

The following steps apply to MariaDB Server in versions 10.2.32.7, 10.3.23., 10.4.13.6, 10.5.2 and earlier. In later releases, the pam_user_map PAM module is now included in the base install.

Next, let's [[user-and-group-mapping-with-pam/#installing-the-pam_user_map-pam-module|install the pam_user_map PAM module]].

Before the module can be compiled from source, we may need to install some dependencies.

On RHEL, CentOS, and other similar Linux distributions that use [[rpm|RPM packages]], we need to install ##gcc## and ##pam-devel##:

<<code>>
sudo yum install gcc pam-devel
<</code>>

On Debian, Ubuntu, and other similar Linux distributions that use [[installing-mariadb-deb-files|DEB packages]], we need to install ##gcc## and ##libpam0g-dev##:

<<code>>
sudo apt-get install gcc libpam0g-dev
<</code>>

And then we can build and install the library with the following:

<<code>>
wget https://raw.githubusercontent.com/MariaDB/server/10.4/plugin/auth_pam/mapper/pam_user_map.c 
gcc pam_user_map.c -shared -lpam -fPIC -o pam_user_map.so 
sudo install --mode=0755 pam_user_map.so /lib64/security/ 
<</code>>

=== Configuring the pam_user_map PAM Module

Next, let's [[user-and-group-mapping-with-pam/#configuring-the-pam_user_map-pam-module|configure the pam_user_map PAM module]] based on our hypothetical requirements.

The configuration file for the ##pam_user_map## PAM module is ##/etc/security/user_map.conf##. Based on our hypothetical requirements, ours would look like:

<<code>>
foo: bar
@dba:dba
<</code>>

=== Installing the PAM Authentication Plugin

Next, let's [[authentication-plugin-pam/#installing-the-plugin|install the ##pam## authentication plugin]].

Log into the MariaDB Server and execute the following:

<<sql>>
INSTALL SONAME 'auth_pam';
<</sql>>

=== Configuring the PAM Service

Next, let's [[authentication-plugin-pam/#configuring-the-pam-service|configure the PAM service]]. We will call our service ##mariadb##, so our PAM service configuration file will be located at ##/etc/pam.d/mariadb## on most systems.

==== Configuring PAM to Allow Only LDAP Authentication

Since we are only doing LDAP authentication with the ##[[https://linux.die.net/man/5/pam_ldap|pam_ldap]]## PAM module and group mapping with the ##pam_user_map## PAM module, our configuration file would look like this:

<<code>>
auth required pam_ldap.so
auth required pam_user_map.so
account required pam_ldap.so
<</code>>

==== Configuring PAM to Allow LDAP and Local Unix Authentication

If we want to allow authentication from LDAP users **and** from local Unix users through ##[[https://linux.die.net/man/8/pam_unix|pam_unix]]##, while giving priority to the local users, then we could do this instead:

<<code>>
auth [success=1 new_authtok_reqd=1 default=ignore] pam_unix.so audit
auth required pam_ldap.so try_first_pass
auth required pam_user_map.so
account sufficient pam_unix.so audit
account required pam_ldap.so
<</code>>

===== Configuring the pam_unix PAM Module

If you also want to allow authentication from local Unix users, the ##pam_unix## PAM module adds [[authentication-plugin-pam/#configuring-the-pam-service|some additional configuration steps]] on a lot of systems. We basically have to give the user that runs ##mysqld## access to ##/etc/shadow##.

If the ##mysql## user is running ##mysqld##, then we can do that by executing the following:

<<code>>
sudo groupadd shadow
sudo usermod -a -G shadow mysql
sudo chown root:shadow /etc/shadow
sudo chmod g+r /etc/shadow
<</code>>

The [[starting-and-stopping-mariadb-starting-and-stopping-mariadb|server needs to be restarted]] for this change to take affect.

== Creating MariaDB Users

Next, let's [[authentication-plugin-pam/#creating-users|create the MariaDB users]]. Remember that our PAM service is called ##mariadb##.

First, let's create the MariaDB user for the user mapping: ##foo: bar##

That means that we need to create a ##bar## user:

<<sql>>
CREATE USER 'bar'@'%' IDENTIFIED BY 'strongpassword';
GRANT ALL PRIVILEGES ON *.* TO 'bar'@'%' ;
<</sql>>

And then let's create the MariaDB user for the group mapping: ##@dba: dba##

That means that we need to create a ##dba## user:

<<sql>>
CREATE USER 'dba'@'%' IDENTIFIED BY 'strongpassword';
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'%' ;
<</sql>>

And then to allow for the user and group mapping, we need to [[user-and-group-mapping-with-pam/#creating-users|create an anonymous user that authenticates with the ##pam## authentication plugin]] that is also able to ##PROXY## as the ##bar## and ##dba## users. Before we can create the proxy user, we might need to [[create-user/#fixing-a-legacy-default-anonymous-account|clean up some defaults]]:

<<sql>>
DELETE FROM mysql.db WHERE User='' AND Host='%';
FLUSH PRIVILEGES;
<</sql>>

And then let's create the anonymous proxy user:

<<sql>>
CREATE USER ''@'%' IDENTIFIED VIA pam USING 'mariadb';
GRANT PROXY ON 'bar'@'%' TO ''@'%';
GRANT PROXY ON 'dba'@'%' TO ''@'%';
<</sql>>

== Testing our Configuration

Next, let's test out our configuration by [[user-and-group-mapping-with-pam/#verifying-that-mapping-is-occurring|verifying that mapping is occurring]]. We can verify this by logging in as each of our users and comparing the return value of ##[[user|USER()]]##, which is the original user name and the return value of ##[[current_user|CURRENT_USER()]]##, which is the authenticated user name.

=== Testing LDAP Authentication

First, let's test out our ##foo## user:

<<code>>
$ mysql -u foo -h 172.30.0.198
[mariadb] Password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 134
Server version: 10.3.10-MariaDB MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SELECT USER(), CURRENT_USER();
+------------------------------------------------+----------------+
| USER()                                         | CURRENT_USER() |
+------------------------------------------------+----------------+
| foo@ip-172-30-0-198.us-west-2.compute.internal | bar@%          |
+------------------------------------------------+----------------+
1 row in set (0.000 sec)
<</code>>

We can verify that our ##foo## LDAP user was properly mapped to the ##bar## MariaDB user by looking at the return value of ##[[current_user|CURRENT_USER()]]##.

Then let's test out our ##gmontee## user in the ##dba## group:

<<code>>
$ mysql -u gmontee -h 172.30.0.198
[mariadb] Password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 135
Server version: 10.3.10-MariaDB MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SELECT USER(), CURRENT_USER();
+----------------------------------------------------+----------------+
| USER()                                             | CURRENT_USER() |
+----------------------------------------------------+----------------+
| gmontee@ip-172-30-0-198.us-west-2.compute.internal | dba@%          |
+----------------------------------------------------+----------------+
1 row in set (0.000 sec)
<</code>>

And then let's test out our ##bstillman## user in the ##dba## group:

<<code>>
$ mysql -u bstillman -h 172.30.0.198
[mariadb] Password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 136
Server version: 10.3.10-MariaDB MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SELECT USER(), CURRENT_USER();
+------------------------------------------------------+----------------+
| USER()                                               | CURRENT_USER() |
+------------------------------------------------------+----------------+
| bstillman@ip-172-30-0-198.us-west-2.compute.internal | dba@%          |
+------------------------------------------------------+----------------+
1 row in set (0.000 sec)
<</code>>

We can verify that our ##gmontee## and ##bstillman## LDAP users in the ##dba## LDAP group were properly mapped to the ##dba## MariaDB user by looking at the return values of ##[[current_user|CURRENT_USER()]]##.

=== Testing Local Unix Authentication

If you chose the option that also allowed local Unix authentication, then let's test that out. Let's create a Unix user and give the user a password real quick:

<<code>>
sudo useradd alice
sudo passwd alice
<</code>>

And let's also map this user to ##dba##:

<<code>>
@dba:dba
foo: bar
alice: dba
<</code>>

And we know that the existing anonymous user already has the ##PROXY## privilege granted to the ##dba## user, so this should just work without any other configuration. Let's test it out:

<<code>>
$ mysql -u alice -h 172.30.0.198
[mariadb] Password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 141
Server version: 10.3.10-MariaDB MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SELECT USER(), CURRENT_USER();
+--------------------------------------------------+----------------+
| USER()                                           | CURRENT_USER() |
+--------------------------------------------------+----------------+
| alice@ip-172-30-0-198.us-west-2.compute.internal | dba@%          |
+--------------------------------------------------+----------------+
1 row in set (0.000 sec)
<</code>>

We can verify that our ##alice## Unix user was properly mapped to the ##dba## MariaDB user by looking at the return values of ##[[current_user|CURRENT_USER()]]##.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

Configuring PAM Authentication and User Mapping with LDAP Authentication - Source

Products

Services

Pricing

Resources

About Us

Download MariaDB