[Maria-discuss] sssd with authentication plugin pam
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)
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
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
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
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
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
On 8/3/21 4:05 AM, Michal Schorm wrote:
Hello,
Hello Michal How are you doing?
(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
_______________________________________________ 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
-- So many immigrant groups have swept through our town that Brooklyn, like Atlantis, reaches mythological proportions in the mind of the world - RI Safir 1998 http://www.mrbrklyn.com DRM is THEFT - We are the STAKEHOLDERS - RI Safir 2002 http://www.nylxs.com - Leadership Development in Free Software http://www.brooklyn-living.com Being so tracked is for FARM ANIMALS and extermination camps, but incompatible with living as a free human being. -RI Safir 2013
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
*** 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
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
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
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_SERVICE=mysql _=/usr/bin/env than mariadb-pam looks probable to me, but I'm not an expert on that front. packages available here: pam_sss.so -- I was able to confirm pam is working changing /etc/pam.d/mysql to: 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
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
*** 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
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
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
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_SERVICE=mysql _=/usr/bin/env than mariadb-pam looks probable to me, but I'm not an expert on that front. packages available here: pam_sss.so -- I was able to confirm pam is working changing /etc/pam.d/mysql to: 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
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
*** 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
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
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_SERVICE=mysql _=/usr/bin/env than mariadb-pam looks probable to me, but I'm not an expert on that front. pam_sss.so -- I was able to confirm pam is working changing /etc/pam.d/mysql to: 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
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
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
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
> 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
/t/pam_log_script.sh than mariadb-pam looks probable to me, but I'm not an expert on that front. pam_sss.so -- I was able to confirm pam is working changing /etc/pam.d/mysql to: 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
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 | +------+---------------+----------------+----------------+---------+
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
(2) 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
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
> > 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
>> 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
/t/pam_log_script.sh 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. pam_sss.so -- I was able to confirm pam is working changing /etc/pam.d/mysql to: 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
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
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 > >
participants (4)
-
Honza Horak -
Michael Barkdoll -
Michal Schorm -
Ruben Safir