maria-discuss team mailing list archive

[Michael Barkdoll <mabarkdoll@xxxxxxxxx>]

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
>>>
>>>