Hello,

(1)
Since MariaDB 10.4, there is a new version 2 of the PAM plugin, which
has been made default.
Based on your message it looks like you are using the PAMv2 plugin,
which is what I would recommend, though you can check again by:
MariaDB [(none)]> show plugins soname like '%pam%';
+------+---------------+----------------+----------------+---------+
| Name | Status        | Type           | Library        | License |
+------+---------------+----------------+----------------+---------+
| pam  | ACTIVE        | AUTHENTICATION | auth_pam.so    | GPL     |
| pam  | NOT INSTALLED | AUTHENTICATION | auth_pam_v1.so | GPL     |
+------+---------------+----------------+----------------+---------+


(2)
> On Mon, Aug 2, 2021 at 5:35 PM Michael Barkdoll <mabarkdoll@gmail.com> wrote:
>> I see Redhat has issues with MariaDB 10.3 working with pam plugin but it sounded like 10.5 should work?
>> https://bugzilla.redhat.com/show_bug.cgi?id=1942330
We are not aware of any more issues with the MariaDB PAM plugin at this moment.


(3)
I tried to reproduce your issue on RHEL-8.4.0 with the RPMs from the
mariadb-10.5 module provided by Red Hat.

The authentication for the local users works out-of-the-box.
I didn't need to use your workaround:
> On Mon, Aug 2, 2021 at 10:07 PM Michael Barkdoll <mabarkdoll@gmail.com> wrote:
>> I was able to get local users working by renaming the /etc/pam.d/mariadb to /etc/pam/d/mysql contents:

The "... USING 'mariadb';" clause worked as expected for me.
When omitted, the authentication stopped working because I only
specified PAM configuration for the PAM 'mariadb' service, not 'mysql'
service which is the default one used by MariaDB server.

I haven't tested Active Directory.


(4)
I also spotted you are using both:

CREATE USER 'user'@'%' IDENTIFIED VIA pam USING 'mariadb';
GRANT SELECT ON db.* TO 'user'@'%' IDENTIFIED VIA pam;

My understanding of the upstream documentation:
  https://mariadb.com/kb/en/authentication-plugin-pam/#creating-users
is that only one of those lines is needed.

--

Michal

--

Michal Schorm
Software Engineer
Core Services - Databases Team
Red Hat

--

On Mon, Aug 2, 2021 at 11:18 PM Michael Barkdoll <mabarkdoll@gmail.com> wrote:
>
> Thanks, I used /etc/pam.d/mysql to add a pam_exec.so line as well to try to output the environment variables.
>
> # cat /etc/pam.d/mysql
> auth optional pam_exec.so log=/t/pam_output.txt /t/pam_log_script.sh
> auth required pam_sss.so
> account optional pam_exec.so log=/t/pam_output.txt /t/pam_log_script.sh
> account required pam_sss.so
>
> cat /t/pam_log_script.sh
> #!/bin/bash
> echo `env`
>
> # cat /t/pam_output.txt
> *** Mon Aug  2 16:08:15 2021
> PAM_TYPE=auth PAM_USER=adadmin PWD=/var/lib/mysql SHLVL=1 PAM_SERVICE=mysql _=/usr/bin/env
> *** Mon Aug  2 16:08:15 2021
> PAM_TYPE=account PAM_USER=adadmin PWD=/var/lib/mysql KRB5CCNAME=FILE:/tmp/krb5cc_1767884463_WAaH4K SHLVL=1 PAM_SERVICE=mysql _=/usr/bin/env
>
> Also, I turned on rsyslogd and I see the following in /var/log/secure:
> Aug  2 16:08:15 server auth_pam_tool[63628]: pam_sss(mysql:auth): authentication success; logname= uid=0 euid=0 tty= ruser= rhost= user=adadmin
> Aug  2 16:08:15 server auth_pam_tool[63628]: pam_sss(mysql:account): Access denied for user adadmin: 6 (Permission denied)
>
> On Mon, Aug 2, 2021 at 3:49 PM Honza Horak <hhorak@redhat.com> wrote:
>>
>> Sharing with folks maintaining the RPMs on the RHEL side, Michal and Lukas, whether it looks familiar by any chance. You're right that the pam module should work fine with 10.5, the BZ you referenced was only related to 10.3. The theory that it might be something wrong with the sssd rather than mariadb-pam looks probable to me, but I'm not an expert on that front.
>>
>> Honza
>>
>> On Mon, Aug 2, 2021 at 10:07 PM Michael Barkdoll <mabarkdoll@gmail.com> wrote:
>>>
>>> Sorry, I wasn't replying to the listserv initially.  Complete list of packages available here:
>>> https://pastebin.com/raw/Ux8sac73
>>>
>>> Operating System is Rocky linux 8.4 should be 100% binary compatible with Redhat 8.4.
>>> I used mariadb AppStream 10.5 for the install with maria-pam 10.5.9 as well.  I will confirm the same on Redhat 8.4.
>>>
>>> Update:
>>> I was able to get local users working by renaming the /etc/pam.d/mariadb to /etc/pam/d/mysql contents:
>>> auth required pam_unix.so audit
>>> account required pam_unix.so audit
>>>
>>> However, I still can't get AD user accounts to work even with the pam_sss.so --  I was able to confirm pam is working changing /etc/pam.d/mysql to:
>>> auth required pam_permit.so audit
>>> account required pam_permit.so audit
>>>
>>> But, then no authentication is taking place.  I think the issue must be with sssd's pam_sss.so.
>>>
>>> I tried increasing the verbosity of the sssd logs.
>>> https://pastebin.com/raw/FsJv4DYR
>>> https://pastebin.com/raw/2TKhYygT
>>>
>>> Not sure if there is anything useful in there.
>>>
>>> On Mon, Aug 2, 2021 at 12:31 PM Honza Horak <hhorak@redhat.com> wrote:
>>>>
>>>> Michael, can you share, please, which operating system and builds (upstream packages or those from the distribution) do you use?
>>>>
>>>> Thanks,
>>>> Honza
>>>>
>>>> On Mon, Aug 2, 2021 at 5:35 PM Michael Barkdoll <mabarkdoll@gmail.com> wrote:
>>>>>
>>>>> Hi, I'm having issues getting the pam plugin to work with Rocky Linux 8 (RHEL 8) with AppStream MariaDB 10.5.  I've installed mariadb appstream for 10.5 and mariadb-pam packages.
>>>>>
>>>>> Added the following to /etc/my.cnf.d:
>>>>> [mariadb]
>>>>> plugin_load_add = auth_pam
>>>>>
>>>>> My sssd is joined to Active Directory.  I've created /etc/pam.d/mariadb trying both local pam_unix and pam_sss configurations:
>>>>> # /etc/pam.d/mariadb for local accounts
>>>>> auth required pam_unix.so audit
>>>>> account required pam_unix.so audit
>>>>>
>>>>> # /etc/pam.d/mariadb for sssd active directory accounts
>>>>> auth required pam_sss.so
>>>>> account required pam_sss.so
>>>>>
>>>>> Tried creating local accounts with:
>>>>> #CREATE USER 'user'@'%' IDENTIFIED VIA pam USING 'mariadb';
>>>>> #GRANT SELECT ON db.* TO 'user'@'%' IDENTIFIED VIA pam;
>>>>> #CREATE USER 'user2'@'%' IDENTIFIED VIA pam;
>>>>> #GRANT SELECT ON db.* TO 'user2'@'%' IDENTIFIED VIA pam;
>>>>>
>>>>> I've also tried creating AD accounts:
>>>>> #CREATE USER 'aduser'@'%' IDENTIFIED VIA pam USING 'mariadb';
>>>>> #GRANT SELECT ON db.* TO 'aduser'@'%' IDENTIFIED VIA pam;
>>>>> #CREATE USER 'aduser@college.edu'@'%' IDENTIFIED VIA pam USING 'mariadb';
>>>>> #GRANT SELECT ON db.* TO 'aduser@college.edu'@'%' IDENTIFIED VIA pam;
>>>>>
>>>>> I see Redhat has issues with MariaDB 10.3 working with pam plugin but it sounded like 10.5 should work?
>>>>> https://bugzilla.redhat.com/show_bug.cgi?id=1942330
>>>>>
>>>>> I feel like I'm missing something in my /etc/sssd/sssd.conf file or some pam configuration steps.
>>>>>
>>>>> I'm using authselect with sssd:
>>>>> authselect select custom/user-profile with-mkhomedir with-sudo with-pamaccess
>>>>>
>>>>> All attempts to `mysql -u user -p` fail.
>>>>>
>>>>> MariaDB [(none)]> show plugins;
>>>>> | pam                           | ACTIVE   | AUTHENTICATION     | auth_pam.so | GPL     |
>>>>>
>>>>> I tried adding a [pam] section to sssd.
>>>>>
>>>>> [pam]
>>>>> pam_public_domains = all
>>>>> pam_verbosity = 3
>>>>>
>>>>> Didn't seem to help.  I used realmd to join AD.  Any help is much appreciated.
>>>>>
>>>>> mysql -u user -p
>>>>> Enter password:
>>>>> ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: NO)
>>>>>
>>>>> _______________________________________________
>>>>> Mailing list: https://launchpad.net/~maria-discuss
>>>>> Post to     : maria-discuss@lists.launchpad.net
>>>>> Unsubscribe : https://launchpad.net/~maria-discuss
>>>>> More help   : https://help.launchpad.net/ListHelp