maria-discuss team mailing list archive

[Michael Barkdoll <mabarkdoll@xxxxxxxxx>]

Hi Michal,

Yes, I'm using version 2 of the PAM plugin.

MariaDB [(none)]> show plugins soname like '%pam%';
+------+---------------+----------------+----------------+---------+
| Name | Status        | Type           | Library        | License |
+------+---------------+----------------+----------------+---------+
| pam  | ACTIVE        | AUTHENTICATION | auth_pam.so    | GPL     |
| pam  | NOT INSTALLED | AUTHENTICATION | auth_pam_v1.so | GPL     |
+------+---------------+----------------+----------------+---------+

Concerning (3), I was able to use /etc/pam.d/mariadb this morning instead
of /etc/pam.d/mysql.  The only modifications that I've made that I see
currently are what you noted in point (4) to only using CREATE USER since
SQL_MODE has NO_AUTO_CREATE_USER.

MariaDB [(none)]> SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;
+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+
| @@SQL_MODE
                 | @@GLOBAL.SQL_MODE
                                  |
+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+
|
STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+


I've updated the user creation to only use (4):
CREATE USER 'user'@'%' IDENTIFIED VIA pam USING 'mariadb';

Unix auth appears to work the same as your configuration now using pam_unix
in /etc/pam.d/mariadb.  However, AD is not working when I change
/etc/pam.d/mariadb to:
auth optional pam_exec.so log=/t/pam_output.txt /t/pam_log_script.sh
auth required pam_sss.so
account optional pam_exec.so log=/t/pam_output.txt /t/pam_log_script.sh
account required pam_sss.so

MariaDB [(none)]> DROP USER adadmin;
Query OK, 0 rows affected (0.037 sec)
MariaDB [(none)]> CREATE USER 'adadmin'@'%' IDENTIFIED VIA pam USING
'mariadb';
Query OK, 0 rows affected (0.024 sec)

# tail -f /t/pam_output.txt
*** Tue Aug  3 08:56:05 2021
PAM_TYPE=auth PAM_USER=adadmin PWD=/var/lib/mysql SHLVL=1
PAM_SERVICE=mariadb _=/usr/bin/env
*** Tue Aug  3 08:56:06 2021
PAM_TYPE=account PAM_USER=adadmin PWD=/var/lib/mysql
KRB5CCNAME=FILE:/tmp/krb5cc_1767884463_WAaH4K SHLVL=1 PAM_SERVICE=mariadb
_=/usr/bin/env

# tail -f /var/log/secure
Aug  3 08:56:06 cs-dbserv auth_pam_tool[76893]: pam_sss(mariadb:auth):
authentication success; logname= uid=0 euid=0 tty= ruser= rhost=
user=adadmin
Aug  3 08:56:06 cs-dbserv auth_pam_tool[76893]: pam_sss(mariadb:account):
Access denied for user adadmin: 6 (Permission denied)

# tail -f /var/log/messages
Aug  3 08:58:42 mariadb sssd[76951]: Outgoing update query:
Aug  3 08:58:42 mariadb sssd[76951]: ;; ->>HEADER<<- opcode: QUERY, status:
NOERROR, id:  23217
Aug  3 08:58:42 mariadb sssd[76951]: ;; flags:; QUESTION: 1, ANSWER: 0,
AUTHORITY: 0, ADDITIONAL: 1
Aug  3 08:58:42 mariadb sssd[76951]: ;; QUESTION SECTION:
Aug  3 08:58:42 mariadb sssd[76951]: ;2530806950.server.domain.college.edu.
ANY#011TKEY
Aug  3 08:58:42 mariadb sssd[76951]: ;; ADDITIONAL SECTION:
Aug  3 08:58:42 mariadb sssd[76951]: 2530806950.server.domain.college.edu.
0 ANY TKEY#011gss-tsig. 1627999122 1627999122 3 NOERROR 1326
YIIFKg[shortened] 0
Aug  3 08:58:42 mariadb sssd[76951]: Outgoing update query:
Aug  3 08:58:42 mariadb sssd[76951]: ;; ->>HEADER<<- opcode: UPDATE,
status: NOERROR, id:  35535
Aug  3 08:58:42 mariadb sssd[76951]: ;; flags:; ZONE: 1, PREREQ: 0, UPDATE:
2, ADDITIONAL: 1
Aug  3 08:58:42 mariadb sssd[76951]: ;; UPDATE SECTION:
Aug  3 08:58:42 mariadb sssd[76951]:
mariadb.domain.college.edu.#0110#011ANY#011A
Aug  3 08:58:42 mariadb sssd[76951]:
mariadb.domain.college.edu.#01160#011IN#011A#011131.230.133.11
Aug  3 08:58:42 mariadb sssd[76951]: ;; TSIG PSEUDOSECTION:
Aug  3 08:58:42 mariadb sssd[76951]: 2530806950.server.domain.college.edu.
0 ANY TSIG#011gss-tsig. 1627999122 300 28 BAQE[shortened]== 35535 NOERROR 0
Aug  3 08:58:42 mariadb sssd[76951]: Outgoing update query:
Aug  3 08:58:42 mariadb sssd[76951]: ;; ->>HEADER<<- opcode: QUERY, status:
NOERROR, id:  53259
Aug  3 08:58:42 mariadb sssd[76951]: ;; flags:; QUESTION: 1, ANSWER: 0,
AUTHORITY: 0, ADDITIONAL: 1
Aug  3 08:58:42 mariadb sssd[76951]: ;; QUESTION SECTION:
Aug  3 08:58:42 mariadb sssd[76951]: ;417880633.server.domain.college.edu.
ANY#011TKEY
Aug  3 08:58:42 mariadb sssd[76951]: ;; ADDITIONAL SECTION:
Aug  3 08:58:42 mariadb sssd[76951]: 417880633.server.domain.college.edu. 0
ANY#011TKEY#011gss-tsig. 1627999122 1627999122 3 NOERROR 1326
YIIFKg[shortened] 0
Aug  3 08:58:42 mariadb sssd[76951]: Outgoing update query:
Aug  3 08:58:42 mariadb sssd[76951]: ;; ->>HEADER<<- opcode: UPDATE,
status: NOERROR, id:  49877
Aug  3 08:58:42 mariadb sssd[76951]: ;; flags:; ZONE: 1, PREREQ: 0, UPDATE:
1, ADDITIONAL: 1
Aug  3 08:58:42 mariadb sssd[76951]: ;; UPDATE SECTION:
Aug  3 08:58:42 mariadb sssd[76951]:
mariadb.domain.college.edu.#0110#011ANY#011AAAA
Aug  3 08:58:42 mariadb sssd[76951]: ;; TSIG PSEUDOSECTION:
Aug  3 08:58:42 mariadb sssd[76951]: 417880633.server.domain.college.edu. 0
ANY#011TSIG#011gss-tsig. 1627999122 300 28 BAQE[shortened]== 49877 NOERROR 0

Also, I noticed when doing the following command pam_acct_mgmt is showing
Permission denied:
# sssctl user-checks -s mariadb adadmin

user: adadmin
action: acct
service: mariadb

SSSD nss user lookup result:
 - user name: adadmin@xxxxxxxxxxxxxxxxxx
 - user id: 1767884463
 - group id: 1767800513
 - gecos: Admin CS - adadmin
 - home directory: /home/adadmin
 - shell: /bin/bash

SSSD InfoPipe user lookup result:
 - name: adadmin
 - uidNumber: 17xxxxxxxxx
 - gidNumber: 17xxxxxxxxx
 - gecos: Admin CS - adadmin
 - homeDirectory: not set
 - loginShell: not set

testing pam_acct_mgmt

pam_acct_mgmt: Permission denied

PAM Environment:
 - no env -

This is also showing up in /var/log/secure:
Aug  3 09:03:05 mariadb sssctl[77040]: pam_sss(mariadb:account): Access
denied for user adadmin: 6 (Permission denied)

Michael Barkdoll


On Tue, Aug 3, 2021 at 3:05 AM Michal Schorm <mschorm@xxxxxxxxxx> wrote:

> Hello,
>
> (1)
> Since MariaDB 10.4, there is a new version 2 of the PAM plugin, which
> has been made default.
> Based on your message it looks like you are using the PAMv2 plugin,
> which is what I would recommend, though you can check again by:
> MariaDB [(none)]> show plugins soname like '%pam%';
> +------+---------------+----------------+----------------+---------+
> | Name | Status        | Type           | Library        | License |
> +------+---------------+----------------+----------------+---------+
> | pam  | ACTIVE        | AUTHENTICATION | auth_pam.so    | GPL     |
> | pam  | NOT INSTALLED | AUTHENTICATION | auth_pam_v1.so | GPL     |
> +------+---------------+----------------+----------------+---------+
>
>
> (2)
> > On Mon, Aug 2, 2021 at 5:35 PM Michael Barkdoll <mabarkdoll@xxxxxxxxx>
> wrote:
> >> I see Redhat has issues with MariaDB 10.3 working with pam plugin but
> it sounded like 10.5 should work?
> >> https://bugzilla.redhat.com/show_bug.cgi?id=1942330
> We are not aware of any more issues with the MariaDB PAM plugin at this
> moment.
>
>
> (3)
> I tried to reproduce your issue on RHEL-8.4.0 with the RPMs from the
> mariadb-10.5 module provided by Red Hat.
>
> The authentication for the local users works out-of-the-box.
> I didn't need to use your workaround:
> > On Mon, Aug 2, 2021 at 10:07 PM Michael Barkdoll <mabarkdoll@xxxxxxxxx>
> wrote:
> >> I was able to get local users working by renaming the
> /etc/pam.d/mariadb to /etc/pam/d/mysql contents:
>
> The "... USING 'mariadb';" clause worked as expected for me.
> When omitted, the authentication stopped working because I only
> specified PAM configuration for the PAM 'mariadb' service, not 'mysql'
> service which is the default one used by MariaDB server.
>
> I haven't tested Active Directory.
>
>
> (4)
> I also spotted you are using both:
>
> CREATE USER 'user'@'%' IDENTIFIED VIA pam USING 'mariadb';
> GRANT SELECT ON db.* TO 'user'@'%' IDENTIFIED VIA pam;
>
> My understanding of the upstream documentation:
>   https://mariadb.com/kb/en/authentication-plugin-pam/#creating-users
> is that only one of those lines is needed.
>
> --
>
> Michal
>
> --
>
> Michal Schorm
> Software Engineer
> Core Services - Databases Team
> Red Hat
>
> --
>
> On Mon, Aug 2, 2021 at 11:18 PM Michael Barkdoll <mabarkdoll@xxxxxxxxx>
> wrote:
> >
> > Thanks, I used /etc/pam.d/mysql to add a pam_exec.so line as well to try
> to output the environment variables.
> >
> > # cat /etc/pam.d/mysql
> > auth optional pam_exec.so log=/t/pam_output.txt /t/pam_log_script.sh
> > auth required pam_sss.so
> > account optional pam_exec.so log=/t/pam_output.txt /t/pam_log_script.sh
> > account required pam_sss.so
> >
> > cat /t/pam_log_script.sh
> > #!/bin/bash
> > echo `env`
> >
> > # cat /t/pam_output.txt
> > *** Mon Aug  2 16:08:15 2021
> > PAM_TYPE=auth PAM_USER=adadmin PWD=/var/lib/mysql SHLVL=1
> PAM_SERVICE=mysql _=/usr/bin/env
> > *** Mon Aug  2 16:08:15 2021
> > PAM_TYPE=account PAM_USER=adadmin PWD=/var/lib/mysql
> KRB5CCNAME=FILE:/tmp/krb5cc_1767884463_WAaH4K SHLVL=1 PAM_SERVICE=mysql
> _=/usr/bin/env
> >
> > Also, I turned on rsyslogd and I see the following in /var/log/secure:
> > Aug  2 16:08:15 server auth_pam_tool[63628]: pam_sss(mysql:auth):
> authentication success; logname= uid=0 euid=0 tty= ruser= rhost=
> user=adadmin
> > Aug  2 16:08:15 server auth_pam_tool[63628]: pam_sss(mysql:account):
> Access denied for user adadmin: 6 (Permission denied)
> >
> > On Mon, Aug 2, 2021 at 3:49 PM Honza Horak <hhorak@xxxxxxxxxx> wrote:
> >>
> >> Sharing with folks maintaining the RPMs on the RHEL side, Michal and
> Lukas, whether it looks familiar by any chance. You're right that the pam
> module should work fine with 10.5, the BZ you referenced was only related
> to 10.3. The theory that it might be something wrong with the sssd rather
> than mariadb-pam looks probable to me, but I'm not an expert on that front.
> >>
> >> Honza
> >>
> >> On Mon, Aug 2, 2021 at 10:07 PM Michael Barkdoll <mabarkdoll@xxxxxxxxx>
> wrote:
> >>>
> >>> Sorry, I wasn't replying to the listserv initially.  Complete list of
> packages available here:
> >>> https://pastebin.com/raw/Ux8sac73
> >>>
> >>> Operating System is Rocky linux 8.4 should be 100% binary compatible
> with Redhat 8.4.
> >>> I used mariadb AppStream 10.5 for the install with maria-pam 10.5.9 as
> well.  I will confirm the same on Redhat 8.4.
> >>>
> >>> Update:
> >>> I was able to get local users working by renaming the
> /etc/pam.d/mariadb to /etc/pam/d/mysql contents:
> >>> auth required pam_unix.so audit
> >>> account required pam_unix.so audit
> >>>
> >>> However, I still can't get AD user accounts to work even with the
> pam_sss.so --  I was able to confirm pam is working changing
> /etc/pam.d/mysql to:
> >>> auth required pam_permit.so audit
> >>> account required pam_permit.so audit
> >>>
> >>> But, then no authentication is taking place.  I think the issue must
> be with sssd's pam_sss.so.
> >>>
> >>> I tried increasing the verbosity of the sssd logs.
> >>> https://pastebin.com/raw/FsJv4DYR
> >>> https://pastebin.com/raw/2TKhYygT
> >>>
> >>> Not sure if there is anything useful in there.
> >>>
> >>> On Mon, Aug 2, 2021 at 12:31 PM Honza Horak <hhorak@xxxxxxxxxx> wrote:
> >>>>
> >>>> Michael, can you share, please, which operating system and builds
> (upstream packages or those from the distribution) do you use?
> >>>>
> >>>> Thanks,
> >>>> Honza
> >>>>
> >>>> On Mon, Aug 2, 2021 at 5:35 PM Michael Barkdoll <mabarkdoll@xxxxxxxxx>
> wrote:
> >>>>>
> >>>>> Hi, I'm having issues getting the pam plugin to work with Rocky
> Linux 8 (RHEL 8) with AppStream MariaDB 10.5.  I've installed mariadb
> appstream for 10.5 and mariadb-pam packages.
> >>>>>
> >>>>> Added the following to /etc/my.cnf.d:
> >>>>> [mariadb]
> >>>>> plugin_load_add = auth_pam
> >>>>>
> >>>>> My sssd is joined to Active Directory.  I've created
> /etc/pam.d/mariadb trying both local pam_unix and pam_sss configurations:
> >>>>> # /etc/pam.d/mariadb for local accounts
> >>>>> auth required pam_unix.so audit
> >>>>> account required pam_unix.so audit
> >>>>>
> >>>>> # /etc/pam.d/mariadb for sssd active directory accounts
> >>>>> auth required pam_sss.so
> >>>>> account required pam_sss.so
> >>>>>
> >>>>> Tried creating local accounts with:
> >>>>> #CREATE USER 'user'@'%' IDENTIFIED VIA pam USING 'mariadb';
> >>>>> #GRANT SELECT ON db.* TO 'user'@'%' IDENTIFIED VIA pam;
> >>>>> #CREATE USER 'user2'@'%' IDENTIFIED VIA pam;
> >>>>> #GRANT SELECT ON db.* TO 'user2'@'%' IDENTIFIED VIA pam;
> >>>>>
> >>>>> I've also tried creating AD accounts:
> >>>>> #CREATE USER 'aduser'@'%' IDENTIFIED VIA pam USING 'mariadb';
> >>>>> #GRANT SELECT ON db.* TO 'aduser'@'%' IDENTIFIED VIA pam;
> >>>>> #CREATE USER 'aduser@xxxxxxxxxxx'@'%' IDENTIFIED VIA pam USING
> 'mariadb';
> >>>>> #GRANT SELECT ON db.* TO 'aduser@xxxxxxxxxxx'@'%' IDENTIFIED VIA
> pam;
> >>>>>
> >>>>> I see Redhat has issues with MariaDB 10.3 working with pam plugin
> but it sounded like 10.5 should work?
> >>>>> https://bugzilla.redhat.com/show_bug.cgi?id=1942330
> >>>>>
> >>>>> I feel like I'm missing something in my /etc/sssd/sssd.conf file or
> some pam configuration steps.
> >>>>>
> >>>>> I'm using authselect with sssd:
> >>>>> authselect select custom/user-profile with-mkhomedir with-sudo
> with-pamaccess
> >>>>>
> >>>>> All attempts to `mysql -u user -p` fail.
> >>>>>
> >>>>> MariaDB [(none)]> show plugins;
> >>>>> | pam                           | ACTIVE   | AUTHENTICATION     |
> auth_pam.so | GPL     |
> >>>>>
> >>>>> I tried adding a [pam] section to sssd.
> >>>>>
> >>>>> [pam]
> >>>>> pam_public_domains = all
> >>>>> pam_verbosity = 3
> >>>>>
> >>>>> Didn't seem to help.  I used realmd to join AD.  Any help is much
> appreciated.
> >>>>>
> >>>>> mysql -u user -p
> >>>>> Enter password:
> >>>>> ERROR 1045 (28000): Access denied for user 'user'@'localhost'
> (using password: NO)
> >>>>>
> >>>>> _______________________________________________
> >>>>> Mailing list: https://launchpad.net/~maria-discuss
> >>>>> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> >>>>> Unsubscribe : https://launchpad.net/~maria-discuss
> >>>>> More help   : https://help.launchpad.net/ListHelp
>
>