ALTER USER IDENTIFIED WITH ‘auth_pam’ disables user account

[Ramesh Sivaraman]

When we alter user with auth_pam plugin, server disables the user account. Need to update the mysql.user manually to enable the account. This is an expected behavior as per mysql document.

https://dev.mysql.com/doc/refman/5.7/en/alter-user.html

mysql> create user mytest@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,plugin,password_expired from mysql.user where user='mytest';
+--------+-----------+-----------------------+------------------+
| user | host | plugin | password_expired |
+--------+-----------+-----------------------+------------------+
| mytest | localhost | mysql_native_password | N |
+--------+-----------+-----------------------+------------------+
1 row in set (0.00 sec)

mysql> ALTER USER mytest@localhost IDENTIFIED WITH auth_pam;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,plugin,password_expired from mysql.user where user='mytest';
+--------+-----------+----------+------------------+
| user | host | plugin | password_expired |
+--------+-----------+----------+------------------+
| mytest | localhost | auth_pam | Y |
+--------+-----------+----------+------------------+
1 row in set (0.00 sec)

mysql>

Please add following note in PAM plugin document

" ALTER USER IDENTIFIED WITH ‘auth_pam’ actually disables the account and the only way to fix it is to manually update `mysql.user`"

https://www.percona.com/doc/percona-server/5.7/management/pam_plugin.html

[Laurynas Biveinis (laurynas-biveinis)]

This is suspicious: please provide a quote in https://dev.mysql.com/doc/refman/5.7/en/alter-user.html concerning manual mysql.user updating.

[Ramesh Sivaraman (rameshvs02)]

As per mysql document mysql.user editing is not mentioned.

{doc}
IDENTIFIED WITH auth_plugin

Sets the account authentication plugin to auth_plugin, clears the credentials to the empty string (the credentials are associated with the old authentication plugin, not the new one), and stores the result in the mysql.user account row.

In addition, the password is marked expired. The user must choose a new one when next connecting.
{doc}

But we should make sure to set "password_expired=N" for PAM user before login into server.

[Roel Van de Paar (roel11)]

<extensive discussion on ps slack channel>

[Laurynas Biveinis (laurynas-biveinis)]

Ramesh, does "ALTER USER mytest@localhost IDENTIFIED WITH auth_pam PASSWORD EXPIRE NEVER" work?

[Ramesh Sivaraman (rameshvs02)]

"ALTER USER mytest@localhost IDENTIFIED WITH auth_pam PASSWORD EXPIRE NEVER"" is not changing password_lifetime=0.

mysql> ALTER USER mytest@localhost IDENTIFIED WITH auth_pam PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password_expired,password_lifetime,plugin from mysql.user where user='mytest';
+--------+-----------+------------------+-------------------+----------+
| user | host | password_expired | password_lifetime | plugin |
+--------+-----------+------------------+-------------------+----------+
| mytest | localhost | Y | NULL | auth_pam |
+--------+-----------+------------------+-------------------+----------+
1 row in set (0.00 sec)

mysql>

btw even if we ALTER USER with "PASSWORD EXPIRE NEVER" need to update password_expired column manually.

mysql> ALTER USER mytest@localhost PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password_expired,password_lifetime,plugin from mysql.user where user='mytest';
+--------+-----------+------------------+-------------------+----------+
| user | host | password_expired | password_lifetime | plugin |
+--------+-----------+------------------+-------------------+----------+
| mytest | localhost | Y | 0 | auth_pam |
+--------+-----------+------------------+-------------------+----------+
1 row in set (0.00 sec)

mysql> \q
Bye
root@ramesh-550P5C-550P7C:/home/ramesh/workdir/PS-mysql-5.7.10-1rc1-linux-x86_64-debug# ./bin/mysql -A -S/home/ramesh/workdir/PS-mysql-5.7.10-1rc1-linux-x86_64-debug/socket.sock --plugin-dir=/home/ramesh/workdir/PS-mysql-5.7.10-1rc1-linux-x86_64-debug/lib/mysql/plugin -umytest -pqwqw -e'select @@version' --connect-expired-password;
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1820 (HY000) at line 1: You must reset your password using ALTER USER statement before executing this statement.
root@ramesh-550P5C-550P7C:/home/ramesh/workdir/PS-mysql-5.7.10-1rc1-linux-x86_64-debug#

[Sergei Glushchenko (sergei.glushchenko)]

Actually plugin interface has a flag which indicates if plugin support SET PASSWORD. Since PAM doesn't support SET PASSWORD by design this flag is not set.

ALTER TABLE IDENTIFIED WITH expires password as per doc.

When password is expired and SET PASSWORD is not supported by plugin, server simply disabling the user. I have not found related documentation, but it is what behavior is.

Server doesn't allow ALTER USER PASSWORD EXPIRE NEVER on such user. I am not sure about the reason. Might actually be upstream bug.

[Roel Van de Paar (roel11)]

I've pinged upstream who relevant developer is.

[Laurynas Biveinis (laurynas-biveinis)]

Sergei, which flag is that?

[Sergei Glushchenko (sergei.glushchenko)]

Laurynas,

Hmm... actually it is not even flag:

/**
  Only the plugins that are known to use the mysql.user table
  to store their passwords support password expiration atm.
  TODO: create a service and extend the plugin API to support
  password expiration for external plugins.

  @retval false expiration not supported
  @retval true expiration supported
*/
bool auth_plugin_supports_expiration(const char *plugin_name)
{
 return (!plugin_name || !*plugin_name ||
         plugin_name == native_password_plugin_name.str
#if defined(HAVE_OPENSSL)
         || plugin_name == sha256_password_plugin_name.str
#endif
         );
}

It is checked in sql_auth_cache.cc / acl_load():

        if (table->s->fields > table_schema->password_expired_idx())
        {
          char *tmpstr= get_field(&global_acl_memory,
                           table->field[table_schema->password_expired_idx()]);
          if (tmpstr && (*tmpstr == 'Y' || *tmpstr == 'y'))
          {
            user.password_expired= true;

            if (!auth_plugin_supports_expiration(user.plugin.str))
            {
              sql_print_warning("'user' entry '%s@%s' has the password ignore "
                                "flag raised, but its authentication plugin "
                                "doesn't support password expiration. "
                                "The user id will be ignored.",
                                user.user ? user.user : "",
                                user.host.get_host() ? user.host.get_host() : "");
              continue;
            }
            password_expired= true;
          }
        }

But probably correct flag would be AUTH_FLAG_USES_INTERNAL_STORAGE and one place actually is using it for that purpose (set_and_validate_user_attributes in sql_user.cc):

    if (!(auth->authentication_flags & AUTH_FLAG_USES_INTERNAL_STORAGE))
    {
      if (thd->lex->sql_command == SQLCOM_SET_OPTION)
      {
        /*
          A plugin that does not use internal storage and
          hence does not support SET PASSWORD
        */
        char warning_buffer[MYSQL_ERRMSG_SIZE];
        my_snprintf(warning_buffer, sizeof(warning_buffer),
                    "SET PASSWORD has no significance for user '%s'@'%s' as "
                    "authentication plugin does not support it.",
                    Str->user.str, Str->host.str);
        warning_buffer[MYSQL_ERRMSG_SIZE-1]= '\0';
        push_warning(thd, Sql_condition::SL_NOTE,
                     ER_SET_PASSWORD_AUTH_PLUGIN,
                     warning_buffer);
        plugin_unlock(0, plugin);
        what_to_set= NONE_ATTR;
        return (0);
      }
    }

[Roel Van de Paar (roel11)]

Is there anything 'dangling' here which could cause unforeseen issue?

[Laurynas Biveinis (laurynas-biveinis)]

This is an upstream bug. The workaround is not very nice, but working. For now we can document as a known issue and leave for the upstream to fix. If they don't, we can re-triage.

[Roel Van de Paar (roel11)]

Upstream confirmed tracking this in http://bugs.mysql.com/bug.php?id=79999

[Roel Van de Paar (roel11)]

Upstream bug fixed

[Yura Sorokin (yura-sorokin)]

The fix will be in 5.7 trunk after upstream 5.7.12 merge.
https://github.com/percona/percona-server/pull/542

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-3359