maria-discuss team mailing list archive

[Michael Barkdoll <mabarkdoll@xxxxxxxxx>]

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 <mabarkdoll@xxxxxxxxx>
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 <mabarkdoll@xxxxxxxxx>
> 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 <mabarkdoll@xxxxxxxxx>
>> 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: adadmin@xxxxxxxxxxxxxxxxxx
>>>  - 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 <mschorm@xxxxxxxxxx> 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 <mabarkdoll@xxxxxxxxx>
>>>> 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@xxxxxxxxx> 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@xxxxxxxxx>
>>>> 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@xxxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxxx>
>>>> 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@xxxxxxxxx> 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@xxxxxxxxxxx'@'%' IDENTIFIED VIA pam USING
>>>> 'mariadb';
>>>> >>>>> #GRANT SELECT ON db.* TO 'aduser@xxxxxxxxxxx'@'%' 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@xxxxxxxxxxxxxxxxxxx
>>>> >>>>> Unsubscribe : https://launchpad.net/~maria-discuss
>>>> >>>>> More help   : https://help.launchpad.net/ListHelp
>>>>
>>>>