I have previously used PAM user accounts with names that contain upper case letters, and it worked fine for me.

@Geoff Montee:

Was the directory server involved LDAP or AD? I'm thinking this may have to do with how MariaDB interacts with AD.

Note that in /var/log/secure, I see a successful authentication as 'JohnD' at the same timestamp as the failed/rejected MariaDB connection attempt. So it gets past the OS layer but fails at the MariaDB layer.

johnd works even though the ID is rendered 'JohnD' in AD.

It was an LDAP configuration with OpenLDAP on the backend and pam_ldap in the PAM service.

See here:

$ mysql -u LdapTest

[mariadb] Password:

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 155

Server version: 10.3.12-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SELECT USER(), CURRENT_USER();

+--------------------+--------------------+

| USER()             | CURRENT_USER()     |

+--------------------+--------------------+

| LdapTest@localhost | LdapTest@localhost |

+--------------------+--------------------+

1 row in set (0.000 sec)

MariaDB [(none)]> SHOW CREATE USER LdapTest@localhost;

+---------------------------------------------------------------------------+

| CREATE USER for LdapTest@localhost                                        |

+---------------------------------------------------------------------------+

| CREATE USER 'LdapTest'@'localhost' IDENTIFIED VIA pam USING 'mariadbldap' |

+---------------------------------------------------------------------------+

1 row in set (0.000 sec)

PAM service:

$ cat /etc/pam.d/mariadbldap

auth       required     pam_ldap.so

auth       optional     pam_user_map.so   debug

account    required     pam_ldap.so

Looks like there's definitely an AD/Winbind/Samba component here. AD shows the ID as JohnD; but when I look it up on the Linux side with the "id" command, it reports back in all lower case.

e.g.

$ id JohnD
uid=16777999(johnd) gid=16777216(domain users) groups=[etc.]

Number and ID spoofed, but you get the idea.

What's interesting is that we also use IBM Informix with PAM, and it insists on the credentials matching the case shown in AD. So from the Informix side, "JohnD" will work and "johnd" won't. From the MariaDB side, only "johnd" will work.

For the record, this is the PAM configuration file (which works just fine with IBM Informix):

$ cat /etc/pam.d/password-auth
#%PAM-1.0

1. This file is auto-generated.
2. User changes will be destroyed the next time authconfig is run.
   auth required pam_env.so
   auth sufficient pam_unix.so nullok try_first_pass
   auth requisite pam_succeed_if.so uid >= 1000 quiet_success
   auth sufficient pam_winbind.so use_first_pass
   auth required pam_deny.so

account required pam_unix.so broken_shadow
account sufficient pam_localuser.so
account sufficient pam_succeed_if.so uid < 1000 quiet
account [default=bad success=ok user_unknown=ignore] pam_winbind.so
account required pam_permit.so

password requisite pam_pwquality.so try_first_pass local_users_only retry=3 authtok_type=
password sufficient pam_unix.so sha512 shadow nullok try_first_pass use_authtok
password sufficient pam_winbind.so use_authtok
password required pam_deny.so

session optional pam_keyinit.so revoke
session required pam_limits.so
-session optional pam_systemd.so
session optional pam_oddjob_mkhomedir.so umask=0077
session [success=1 default=ignore] pam_succeed_if.so service in crond quiet use_uid
session required pam_unix.so
session optional pam_winbind.so

This looks like a known issue with winbindd. Someone even submitted a patch back in 2014 to add the "winbind preserve case" configuration option:

https://lists.samba.org/archive/samba-technical/2014-September/102480.html

But I don't see that option listed in the manual pages for winbindd or smb.conf, so I don't know if the new option was actually committed:

https://www.samba.org/samba/docs/current/man-html/winbindd.8.html

https://www.samba.org/samba/docs/current/man-html/smb.conf.5.html

If this is causing problems for you, then you might want to submit a bug with the winbindd developers:

https://www.samba.org/samba/bugreports.html

The thing I still don't grok is why IBM Informix works correctly, on exactly the same system, using exactly the same PAM configuration file that Maria is using. With that product, if the case doesn't exactly match what's in the AD server, the authentication is rejected.

So Maria is in this case requiring a case conversion to lower, while IBM Informix is prohibiting it.

I don't know anything about Informix, so I can't comment on that specifically. I can't comment on why you are seeing it behave differently with Informix. Are you sure that Informix is even using winbindd, and that it isn't communicating with AD in some other way?

Regardless, with my LDAP test above, I have confirmed that MariaDB's PAM authentication plugin supports user names that contain upper case characters, as long as the underlying PAM module also supports them.

Additionally, you have shown that Linux's "id" utility also converts your winbindd user names into all lower case characters. If I look at my LDAP user with the same Linux utility, then I see the user name with the original upper case characters, as expected:

$ id LdapTest

uid=16866(LdapTest) gid=100(users) groups=100(users)

It really sounds like the behavior that you are seeing is caused by winbindd or pam_winbind. If you query the user's info with wbinfo, does that also show the user name in all lower case characters?

https://www.samba.org/samba/docs/current/man-html/wbinfo.1.html

The best evidence I have that the problem has to do with MariaDB and not necessarily winbindd is that at the OS level, the authentication succeeds with the mixed-case ID; it simply fails at the MariaDB layer.

From /var/log/secure:

Feb 19 14:26:24 mariadev1 mysqld: pam_winbind(password-auth:auth): user 'JohnD' granted access

From the mariadb-error.log file:

2019-02-19 14:26:24 543009 [Warning] Access denied for user 'JohnD'@'[host-redacted]' (using password: NO)

I do wonder if some of the extra stuff in the PAM config might be throwing it off. Let me test with the simplified one you were using.

I turned on some debugging, and here's what I see in /var/log/secure when "JohnD" tries logging in to Informix (the connection is successful and lets him in):

Feb 21 19:01:15 ifxdev1 oninit: pam_unix(password-auth:auth): authentication failure; logname= uid=202 euid=0 tty= ruser=JohnD rhost=aedev-rwandell.aemaine.com user=JohnD
Feb 21 19:01:15 ifxdev1 oninit: pam_winbind(password-auth:auth): Verify user 'JohnD'
Feb 21 19:01:15 ifxdev1 oninit: pam_winbind(password-auth:auth): user 'JohnD' granted access
Feb 21 19:01:15 ifxdev1 oninit: pam_winbind(password-auth:auth): Returned user was 'johnd'
Feb 21 19:01:15 ifxdev1 oninit: pam_winbind(password-auth:account): user 'johnd' granted access

And here's what I see in /var/log/secure when JohnD tries accessing MariaDB (MariaDB rejects the connection):

Feb 21 19:02:20 mariadev1 mysqld: pam_unix(password-auth:auth): authentication failure; logname= uid=502 euid=502 tty= ruser= rhost= user=JohnD
Feb 21 19:02:20 mariadev1 mysqld: pam_winbind(password-auth:auth): Verify user 'JohnD'
Feb 21 19:02:20 mariadev1 mysqld: pam_winbind(password-auth:auth): user 'JohnD' granted access
Feb 21 19:02:20 mariadev1 mysqld: pam_winbind(password-auth:auth): Returned user was 'johnd'
Feb 21 19:02:20 mariadev1 mysqld: pam_winbind(password-auth:account): user 'johnd' granted access

What we see is that the PAM stack output is, in both cases, identical. But Informix is allowing the connection and MariaDB is not. I note that in both cases, the user returned by pam_winbind is the lower-case version rather than the mixed case one.

[HEAVY SPECULATION MODE ENGAGED!]

What I suspect is happening here is that someone complained to IBM about this problem a long time ago, before I configured my Informix databases to use PAM (which was at least three years ago now). And that rather than wait for the winbindd people to fix the problem, they simply coded around it, taking the user name as specified by the user rather than the one that's being returned by pam_winbind.

[END HEAVY SPECULATION MODE]

But that still leaves me with a problem, where mixed case domain account names are not authenticating to MariaDB. I can instruct users to use all lower case for MariaDB ONLY but use mixed-case for everything else. It's a kludgy workaround at best, but it can be done. We could conceivably convert all of the AD accounts to all-lower case to begin with, but that would be a huge project with far-reaching consequences in our enterprise.

tgirsch,

I've created a feature request for you at MDEV-18686.

I'll go ahead and close this one, since this is technically not a bug in MariaDB or in the PAM authentication plugin. Any change to allow something like this would have to come in the form of a feature request like MDEV-18686.

I'm still a bit confused here. On the MariaDB side, I've actually granted priviliges to johnd, the all lower case version, in addition to JohnD. So when JohnD signs in and winbindd returns user ID johnd, shouldn't it let johnd in? It doesn't. The O/S authenticates the ID and allows it to proceed, but MariaDB rejects it.

What do /var/log/secure and mariadb-error.log say when you use johnd instead of JohnD? How exactly did you create the user in MariaDB?

/var/log/secure on MariaDB rejection (using JohnD):

Feb 21 19:02:20 mariadev1 mysqld: pam_unix(password-auth:auth): authentication failure; logname= uid=502 euid=502 tty= ruser= rhost= user=JohnD
Feb 21 19:02:20 mariadev1 mysqld: pam_winbind(password-auth:auth): Verify user 'JohnD'
Feb 21 19:02:20 mariadev1 mysqld: pam_winbind(password-auth:auth): user 'JohnD' granted access
Feb 21 19:02:20 mariadev1 mysqld: pam_winbind(password-auth:auth): Returned user was 'johnd'
Feb 21 19:02:20 mariadev1 mysqld: pam_winbind(password-auth:account): user 'johnd' granted access

mariadb-error.log on rejection:

2019-02-21 19:02:20 7320 [Warning] Access denied for user 'JohnD'@'[host-redacted]' (using password: NO)

/var/log/secure on MariaDB acceptance (using johnd):

Feb 22 17:38:25 mariadev1 mysqld: pam_unix(password-auth:auth): authentication failure; logname= uid=502 euid=502 tty= ruser= rhost= user=johnd
Feb 22 17:38:25 mariadev1 mysqld: pam_winbind(password-auth:auth): user 'johnd' granted access
Feb 22 17:38:25 mariadev1 mysqld: pam_winbind(password-auth:account): user 'johnd' granted access

(nothing in the mariadb-error.log file on success.)

So it looks to me like MariaDB is rejecting the login attempt specifically because the ID returned by winbind – johnd – is not an exact case match for the ID that was provided (JohnD), even though PAM accepts and authorizes the connection.

IOW, PAM and winbind say "yes," but MariaDB says "no."

Forgot to mention how the users were created:

CREATE USER 'JohnD' IDENTIFIED VIA pam USING 'password-auth';
CREATE USER 'johnd' IDENTIFIED VIA pam USING 'password-auth';

Did MariaDB allow you to log in when you provided the lower case user name? For the lower case user name, /var/log/secure does not show the user name change and mariadb-error.log does not show any authentication failures, so it looks like it succeeded.

To try to get the mixed-case version working, you could try testing out the PROXY privilege:

GRANT PROXY ON 'johnd'@'[host-redacted]' TO 'JohnD'@'[host-redacted]';

https://mariadb.com/kb/en/library/grant/#proxy-privileges

Logging in with the all-lower ID requires also creating the ID all-lower in MariaDB and granting access to that., and also requires them to override the ID that AD returns to them. But if we do all of those steps, then it works.

I'll try the PROXY thing to see if that works

Looks like the PROXY workaround works.