Opened Case #03003705. I tried to minimize any auth changes on my end but still experience the issue. On Tue, Aug 3, 2021 at 11:20 AM Honza Horak <hhorak@redhat.com> wrote:
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 <mabarkdoll@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 <mabarkdoll@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 <mabarkdoll@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 <mabarkdoll@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: adadmin@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 <mschorm@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 < 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