How to Linux, MariaDB, pam, and SSSD

 So I tired to make MariaDB to work with SSSD to I can use AD user account to access the MariaDB and I found many many web sites with questions not answers.

I spent 3 days chasing down this problem and found the easiest solution.

So here we go,

I used a Oracle Linux 8, and install SSSD and MariaDB, and setup SSSD and PAM to work with it.

Your Linux server name should be <servername>.<your domain name> not <servername>.localhost

eg: MaridDBserver.domain.com

I assume you already installed the MariaDB.

Install SSSD (Required)

#dnf install sssd realmd oddjob oddjob-mkhomedir adcli samba-common samba-common-tools krb5-workstation openldap-clients policycoreutils-python-utils -y

#realm discover <yourdomain name>

eg: #realm discover domain.com

# realm join --user=<your domain user account name> <domain name>

eg: realm join --user= john domain.com

Edit /etc/sssd/sssd.conf (optional)

 

Changed 

use_fully_qualified_names = True

fallback_homedir = /home/%u@%d

to 

use_fully_qualified_names = False

fallback_homedir = /home/%u

restart sssd

# systemctl restart SSSD

Install chronyd (required to match time with AD, change settings if you have local npt service)

#dnf -y install chrony

Check chronyd status

#systemctl restart chronyd

#chronyc sourcestats

Check status of SSSD

# systemctl status sssd

If you see any error fix it before you move on.

Install PAM for MariaDB (required)

Login to MariaDB as root and install PAM plugin

> mysql -u root -p 

> Install soname 'auth_pam'

> > show plugins soname like '%pam%';

+------+--------+----------------+-------------+---------+

| Name | Status | Type           | Library     | License |

+------+--------+----------------+-------------+---------+

| pam  | ACTIVE | AUTHENTICATION | auth_pam.so | GPL     |

+------+--------+----------------+-------------+---------+

1 row in set (0.001 sec)

> exit

Edit SSSD.conf (required)

# vi /etc/sssd/sssd.conf

This is my setting, change yours accordingly.

----------------------------------------------

[sssd]

domains = domain.com #<- change this

config_file_version = 2

services = nss, pam

debug_level=9 #<-- comment out once SSSD with mariaDB works

[domain/domain.com]

ad_domain = domain.com #<- change this

krb5_realm = domain.com #<- change this

realmd_tags = manages-system joined-with-adcli

cache_credentials = True

id_provider = ad

krb5_store_password_if_offline = True

default_shell = /bin/bash

ldap_id_mapping = True

use_fully_qualified_names = False

fallback_homedir = /home/%u

#access_provider = ad #<- we will be using simple access provider not AD, comment it out.

access_provider=simple #<- add this

#access_provider=permit #<- this is for debugging purpose, google it if you are curious.

simple_allow_groups=mariadbusers  # <- this is an AD security group, we will create later, name it to something you like.

[pam]

debug_level=9 #<-- comment out once SSSD with mariaDB works

[nss]

debug_level=9 #<-- comment out once SSSD with mariaDB works

[mariadb]

debug_level=9 #<-- comment out once SSSD with mariaDB works

----------------------------------------------

Restart SSSD

# systemctl restart sssd

Create mariadb and mysql files, it is called "PAM service name" or "authentication_string" by mariaDB

# cd /etc/pam.d

# vi mariadb

and add two line below

auth    required pam_sss.so domains=<your domain name>

account required pam_sss.so domains=<your domain name>

eg:

auth    required pam_sss.so domains=domain.com

account required pam_sss.so domains=domain.com

Copy the mariadb file.

# cp mariadb mysql

Ok you are almost there

Login to mariaDB and create new users.

> login -u root -p

There are 2 ways to create a user.

>  create user '<username>'@'%' identified via pam using 'mariadb';

OR

>  create user '<username>'@'%' identified via pam;

The <username> is a domain user name.

eg: if you have a AD user domain\john

>  create user 'john'@'%' identified via pam;

OR

>  create user 'john'@'%' identified via pam using 'mariadb';

Commit the change

> flush privileges;

Query OK, 0 rows affected (0.002 sec)

Note: % denotes allow from all IPs, change it accordingly to meet your security requirements.

If you add using 'mariadb', it will use the auth string /etc/pam.d/mariadb to initiate the auth, the auth file will tell the mariDB to use pam_sss.so

If you do not add using 'mariadb', it will use the auth string /etc/pam.d/mysql to initiate the auth, the auth file will tell the mariDB to use pam_sss.so

ref: https://mariadb.com/kb/en/authentication-plugin-pam/#creating-users

-------------------------------------------------------------------------------------------------

This is from the article:

You can also specify a PAM service name for MariaDB to use by providing it with the USING clause. For example:

CREATE USER username@hostname IDENTIFIED VIA pam USING 'mariadb';

This line creates a user that needs to be authenticated via the pam authentication plugin using the PAM service name mariadb. As mentioned in a previous section, this service's configuration file will typically be present in /etc/pam.d/mariadb.

If no service name is specified, then the plugin will use mysql as the default PAM service name.

-------------------------------------------------------------------------------------------------

Verify user creation

In this example I created john with mariadb auth string and sam without auth string. sam will use mysql auth string (aka PAM service name).

MariaDB [(none)]> select host,user,plugin,authentication_string from mysql.user;

+---------------------------+-------+--------+-----------------------+

| host                      | user  | plugin | authentication_string |

+---------------------------+-------+--------+-----------------------+

| localhost              | root  |           |                                  |

| %                         | john | pam    | mariadb                   |

| %                         | sam | pam    |                                 |

+---------------------------+-------+--------+-----------------------+

7 rows in set (0.001 sec)

We have both /etc/pam.d/mariadb and /etc/pam.d/mysql, just incase ! OK?

Now create a AD security group "mariadbusers".

You remember "simple_allow_groups=mariadbusers  # <- this is an AD security group, name it as you like." on sssd.conf?

Yes, use the same name, and add users on it.

In this example we should add "domain\john" and "domain\sam"

You must add all AD users who need to login to the server plus mariadb users. It will control server access as well. If your name is not member of this group you won't be able to putty to the server.

Linux Local users such as root is not subject to this group, we can't add linux users to Ad group any ways right? lol

How this work?

When john@domain.com try to login to the server, SSSD will auth and allow john to access the server if john is member of the security group.

Once john logged into the server and try to login to mariaDB, it will use auth string '/etc/pam.d/mariadb' to auth and check if the user is allowed to login using SSSD.conf's access_provider, in this case the Ad group "mariddbusers'

So what happens when you created user without auth string and you don't have /etc/pam.d/mysql? but the user is member of the security group "mariadbusers"?

The user will be able to login to the linux server but not able to login to mariadb, you will get "permission denied" error.

Have fun!