maria-discuss team mailing list archive

[Michael Barkdoll <mabarkdoll@xxxxxxxxx>]

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@xxxxxxxxx>
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@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
>>
>>