Michael, in one mail you mentioned you should have access to the Red Hat support, so I'd advise here to open a case as this might require some auth-specific knowledge, more than the mariadb one. The ticket will be handled by folks more familiar with this stuff.
Regards, Honza On Tue, Aug 3, 2021 at 5:49 PM Michael Barkdoll <mabarkd...@gmail.com> wrote: > I tried suggestions similarly listed on: > https://access.redhat.com/solutions/2187581 > > None of them seemed to help. > > On Tue, Aug 3, 2021 at 9:39 AM Michael Barkdoll <mabarkd...@gmail.com> > wrote: > >> I removed sections [mysql] and [mariadb] from sssd.conf since sssctl >> config-check didn't want them there. AD authentication issue is still >> present. >> >> >> >> On Tue, Aug 3, 2021 at 9:15 AM Michael Barkdoll <mabarkd...@gmail.com> >> wrote: >> >>> Here is my sssd.conf as well in case some customization in it is somehow >>> causing issues though I don't think it should be causing any issues: >>> >>> >>> # cat /etc/sssd/sssd.conf >>> [sssd] >>> debug_level = 9 >>> domains = domain.college.edu >>> config_file_version = 2 >>> services = nss, pam >>> #default_domain_suffix = AD.SIU.EDU >>> #domain_resolution_order = LOCAL, AD.SIU.EDU >>> domain_resolution_order = implicit_files, DOMAIN.COLLEGE.EDU >>> >>> [domain/domain.college.edu] >>> ad_domain = domain.domain.edu >>> krb5_realm = DOMAIN.COLLEGE.EDU >>> 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 >>> >>> override_homedir = /home/%u >>> fallback_homedir = /home/%u >>> access_provider = ad >>> ad_access_filter = (|(memberOf=CN=CS Current Users,OU=Groups,DC=domain, >>> DC=college,DC=edu)(memberOf=CN=CS Domain Admins,OU=Groups,DC=domain,DC >>> =college,DC=edu)) >>> >>> subdomain_inherit = ignore_group_members, ldap_purge_cache_timeout >>> ignore_group_members = True >>> >>> krb5_lifetime = 7h >>> krb5_renewable_lifetime = 7d >>> krb5_renew_interval = 60s >>> >>> dyndns_update = true >>> dyndns_refresh_interval = 60 >>> dyndns_update_ptr = true >>> dyndns_ttl = 60 >>> >>> debug_level = 9 >>> dyndns_iface = eth0 >>> dyndns_server = 192.168.1.1 >>> >>> ad_hostname = mariadb.domain.college.edu >>> >>> [pam] >>> pam_public_domains = all >>> pam_verbosity = 9 >>> >>> [mysql] >>> debug_level = 9 >>> >>> [mariadb] >>> debug_level = 9 >>> >>> >>> >>> On Tue, Aug 3, 2021 at 9:08 AM Michael Barkdoll <mabarkd...@gmail.com> >>> wrote: >>> >>>> Hi Michal, >>>> >>>> Yes, I'm using version 2 of the PAM plugin. >>>> >>>> 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 | >>>> +------+---------------+----------------+----------------+---------+ >>>> >>>> Concerning (3), I was able to use /etc/pam.d/mariadb this morning >>>> instead of /etc/pam.d/mysql. The only modifications that I've made that I >>>> see currently are what you noted in point (4) to only using CREATE USER >>>> since SQL_MODE has NO_AUTO_CREATE_USER. >>>> >>>> MariaDB [(none)]> SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE; >>>> >>>> +-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+ >>>> | @@SQL_MODE >>>> | @@GLOBAL.SQL_MODE >>>> | >>>> >>>> +-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+ >>>> | >>>> STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION >>>> | >>>> STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION >>>> | >>>> >>>> +-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+ >>>> >>>> >>>> I've updated the user creation to only use (4): >>>> CREATE USER 'user'@'%' IDENTIFIED VIA pam USING 'mariadb'; >>>> >>>> Unix auth appears to work the same as your configuration now using >>>> pam_unix in /etc/pam.d/mariadb. However, AD is not working when I change >>>> /etc/pam.d/mariadb to: >>>> 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 >>>> >>>> MariaDB [(none)]> DROP USER adadmin; >>>> Query OK, 0 rows affected (0.037 sec) >>>> MariaDB [(none)]> CREATE USER 'adadmin'@'%' IDENTIFIED VIA pam USING >>>> 'mariadb'; >>>> Query OK, 0 rows affected (0.024 sec) >>>> >>>> # tail -f /t/pam_output.txt >>>> *** Tue Aug 3 08:56:05 2021 >>>> PAM_TYPE=auth PAM_USER=adadmin PWD=/var/lib/mysql SHLVL=1 >>>> PAM_SERVICE=mariadb _=/usr/bin/env >>>> *** Tue Aug 3 08:56:06 2021 >>>> PAM_TYPE=account PAM_USER=adadmin PWD=/var/lib/mysql >>>> KRB5CCNAME=FILE:/tmp/krb5cc_1767884463_WAaH4K SHLVL=1 PAM_SERVICE=mariadb >>>> _=/usr/bin/env >>>> >>>> # tail -f /var/log/secure >>>> Aug 3 08:56:06 cs-dbserv auth_pam_tool[76893]: pam_sss(mariadb:auth): >>>> authentication success; logname= uid=0 euid=0 tty= ruser= rhost= >>>> user=adadmin >>>> Aug 3 08:56:06 cs-dbserv auth_pam_tool[76893]: >>>> pam_sss(mariadb:account): Access denied for user adadmin: 6 (Permission >>>> denied) >>>> >>>> # tail -f /var/log/messages >>>> Aug 3 08:58:42 mariadb sssd[76951]: Outgoing update query: >>>> Aug 3 08:58:42 mariadb sssd[76951]: ;; ->>HEADER<<- opcode: QUERY, >>>> status: NOERROR, id: 23217 >>>> Aug 3 08:58:42 mariadb sssd[76951]: ;; flags:; QUESTION: 1, ANSWER: 0, >>>> AUTHORITY: 0, ADDITIONAL: 1 >>>> Aug 3 08:58:42 mariadb sssd[76951]: ;; QUESTION SECTION: >>>> Aug 3 08:58:42 mariadb sssd[76951]: ; >>>> 2530806950.server.domain.college.edu. ANY#011TKEY >>>> Aug 3 08:58:42 mariadb sssd[76951]: ;; ADDITIONAL SECTION: >>>> Aug 3 08:58:42 mariadb sssd[76951]: >>>> 2530806950.server.domain.college.edu. 0 ANY TKEY#011gss-tsig. >>>> 1627999122 1627999122 3 NOERROR 1326 YIIFKg[shortened] 0 >>>> Aug 3 08:58:42 mariadb sssd[76951]: Outgoing update query: >>>> Aug 3 08:58:42 mariadb sssd[76951]: ;; ->>HEADER<<- opcode: UPDATE, >>>> status: NOERROR, id: 35535 >>>> Aug 3 08:58:42 mariadb sssd[76951]: ;; flags:; ZONE: 1, PREREQ: 0, >>>> UPDATE: 2, ADDITIONAL: 1 >>>> Aug 3 08:58:42 mariadb sssd[76951]: ;; UPDATE SECTION: >>>> Aug 3 08:58:42 mariadb sssd[76951]: >>>> mariadb.domain.college.edu.#0110#011ANY#011A >>>> Aug 3 08:58:42 mariadb sssd[76951]: >>>> mariadb.domain.college.edu.#01160#011IN#011A#011131.230.133.11 >>>> Aug 3 08:58:42 mariadb sssd[76951]: ;; TSIG PSEUDOSECTION: >>>> Aug 3 08:58:42 mariadb sssd[76951]: >>>> 2530806950.server.domain.college.edu. 0 ANY TSIG#011gss-tsig. >>>> 1627999122 300 28 BAQE[shortened]== 35535 NOERROR 0 >>>> Aug 3 08:58:42 mariadb sssd[76951]: Outgoing update query: >>>> Aug 3 08:58:42 mariadb sssd[76951]: ;; ->>HEADER<<- opcode: QUERY, >>>> status: NOERROR, id: 53259 >>>> Aug 3 08:58:42 mariadb sssd[76951]: ;; flags:; QUESTION: 1, ANSWER: 0, >>>> AUTHORITY: 0, ADDITIONAL: 1 >>>> Aug 3 08:58:42 mariadb sssd[76951]: ;; QUESTION SECTION: >>>> Aug 3 08:58:42 mariadb sssd[76951]: ; >>>> 417880633.server.domain.college.edu. ANY#011TKEY >>>> Aug 3 08:58:42 mariadb sssd[76951]: ;; ADDITIONAL SECTION: >>>> Aug 3 08:58:42 mariadb sssd[76951]: >>>> 417880633.server.domain.college.edu. 0 ANY#011TKEY#011gss-tsig. >>>> 1627999122 1627999122 3 NOERROR 1326 YIIFKg[shortened] 0 >>>> Aug 3 08:58:42 mariadb sssd[76951]: Outgoing update query: >>>> Aug 3 08:58:42 mariadb sssd[76951]: ;; ->>HEADER<<- opcode: UPDATE, >>>> status: NOERROR, id: 49877 >>>> Aug 3 08:58:42 mariadb sssd[76951]: ;; flags:; ZONE: 1, PREREQ: 0, >>>> UPDATE: 1, ADDITIONAL: 1 >>>> Aug 3 08:58:42 mariadb sssd[76951]: ;; UPDATE SECTION: >>>> Aug 3 08:58:42 mariadb sssd[76951]: >>>> mariadb.domain.college.edu.#0110#011ANY#011AAAA >>>> Aug 3 08:58:42 mariadb sssd[76951]: ;; TSIG PSEUDOSECTION: >>>> Aug 3 08:58:42 mariadb sssd[76951]: >>>> 417880633.server.domain.college.edu. 0 ANY#011TSIG#011gss-tsig. >>>> 1627999122 300 28 BAQE[shortened]== 49877 NOERROR 0 >>>> >>>> Also, I noticed when doing the following command pam_acct_mgmt is >>>> showing Permission denied: >>>> # sssctl user-checks -s mariadb adadmin >>>> >>>> user: adadmin >>>> action: acct >>>> service: mariadb >>>> >>>> SSSD nss user lookup result: >>>> - user name: adad...@domain.college.edu >>>> - user id: 1767884463 >>>> - group id: 1767800513 >>>> - gecos: Admin CS - adadmin >>>> - home directory: /home/adadmin >>>> - shell: /bin/bash >>>> >>>> SSSD InfoPipe user lookup result: >>>> - name: adadmin >>>> - uidNumber: 17xxxxxxxxx >>>> - gidNumber: 17xxxxxxxxx >>>> - gecos: Admin CS - adadmin >>>> - homeDirectory: not set >>>> - loginShell: not set >>>> >>>> testing pam_acct_mgmt >>>> >>>> pam_acct_mgmt: Permission denied >>>> >>>> PAM Environment: >>>> - no env - >>>> >>>> This is also showing up in /var/log/secure: >>>> Aug 3 09:03:05 mariadb sssctl[77040]: pam_sss(mariadb:account): Access >>>> denied for user adadmin: 6 (Permission denied) >>>> >>>> Michael Barkdoll >>>> >>>> >>>> On Tue, Aug 3, 2021 at 3:05 AM Michal Schorm <msch...@redhat.com> >>>> wrote: >>>> >>>>> 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 < >>>>> mabarkd...@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 < >>>>> mabarkd...@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 <mabarkd...@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 <hho...@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 < >>>>> mabarkd...@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 <hho...@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 < >>>>> mabarkd...@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 'adu...@college.edu'@'%' IDENTIFIED VIA pam USING >>>>> 'mariadb'; >>>>> >>>>> #GRANT SELECT ON db.* TO 'adu...@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 >>>>> >>>>>
_______________________________________________ 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
