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 <[email protected]> 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 <[email protected]> > 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: [email protected] >> - 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 <[email protected]> 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 <[email protected]> >>> 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 <[email protected]> >>> 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 <[email protected]> >>> 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 <[email protected]> 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 < >>> [email protected]> 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 <[email protected]> >>> 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 < >>> [email protected]> 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 '[email protected]'@'%' IDENTIFIED VIA pam USING >>> 'mariadb'; >>> >>>>> #GRANT SELECT ON db.* TO '[email protected]'@'%' 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 : [email protected] >>> >>>>> Unsubscribe : https://launchpad.net/~maria-discuss >>> >>>>> More help : https://help.launchpad.net/ListHelp >>> >>>
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
