Support provided a nice support article that worked for me: https://access.redhat.com/solutions/3710201 Main differences were inside /etc/sssd/sssd.conf to add: [domain/dc.local] ad_gpo_map_network = +mysql and also modified /etc/pam.d/mysql to have: auth include system-auth account required pam_nologin.so account include system-auth password include system-auth session optional pam_keyinit.so force revoke session include system-auth session required pam_loginuid.so Last, the user was created with: CREATE USER 'aduser'@'%' IDENTIFIED VIA pam USING 'mysql'; These differences might be worth noting in mariadb's documentation for RHEL servers. On Wed, Aug 4, 2021 at 10:21 AM Michael Barkdoll <mabarkdoll@gmail.com> wrote:
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 > >