Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21396

Cannot specify multiple authentication plugins (mysql_native_password and unix_socket)

    XMLWordPrintable

    Details

      Description

      Resolved

      Please see comment, my mistake, can be closed. Pardon!

      The environment

      $ lsb_release -a
      No LSB modules are available.
      Distributor ID:	Ubuntu
      Description:	Ubuntu 18.04.3 LTS
      Release:	18.04
      Codename:	bionic
       
      $ uname -a
      Linux test-vm-01 4.15.0-72-generic #81-Ubuntu SMP Tue Nov 26 12:20:02 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux
       
      $ mysql --version
      mysql  Ver 15.1 Distrib 10.4.11-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
      

      MariaDB was installed from the MariaDB repository

      root user is set to use unix_socket auth by running mariadb-secure-installation with params

      Enter current password for root (enter for none): - <Enter>
      Switch to unix_socket authentication [Y/n] - Y
      Change the root password? [Y/n] - n
      Remove anonymous users? [Y/n] - Y
      Disallow root login remotely? [Y/n] - Y
      Remove test database and access to it? [Y/n] - Y
      Reload privilege tables now? [Y/n] - Y
      

      Reference

      MariaDB documentation for CREATE USER states that One can specify many authentication plugins, they all works as alternatives ways of authenticating a user.

      CREATE USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret') OR unix_socket;
      

      The issue

      Base

      sudo mysql -u root
       
      SELECT user,host,plugin,authentication_string FROM mysql.user;
       
      +-------+-----------+-----------------------+-----------------------+
      | User  | Host      | plugin                | authentication_string |
      +-------+-----------+-----------------------+-----------------------+
      | root  | localhost | unix_socket           |                       |
      | mysql | localhost | mysql_native_password | invalid               |
      +-------+-----------+-----------------------+-----------------------+
       
      CREATE USER 'testuser'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass') OR unix_socket;
      GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost' WITH GRANT OPTION;
      FLUSH PRIVILEGES;
       
      SELECT user,host,plugin,authentication_string FROM mysql.user;
       
      +----------+-----------+-----------------------+-------------------------------------------+
      | User     | Host      | plugin                | authentication_string                     |
      +----------+-----------+-----------------------+-------------------------------------------+
      | root     | localhost | unix_socket           |                                           |
      | mysql    | localhost | mysql_native_password | invalid                                   |
      | testuser | localhost | mysql_native_password | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |
      +----------+-----------+-----------------------+-------------------------------------------+
       
      QUIT;
      
      

      All unix_socket'ish login attempts fail

      $ sudo mysql -u testuser
      ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
      # mysql -u testuser
      # ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
      

      Using password (testpass) works

      $ mysql -u testuser -p
      Enter password:
      

      Extra observation 1

      If testuser at this point is altered to use unix_socket auth explicitly...

      sudo mysql -u root
      ALTER USER testuser@localhost IDENTIFIED VIA unix_socket;
      FLUSH PRIVILEGES;
       
      SELECT user,host,plugin,authentication_string FROM mysql.user;
       
      +----------+-----------+-----------------------+-------------------------------------------+
      | User     | Host      | plugin                | authentication_string                     |
      +----------+-----------+-----------------------+-------------------------------------------+
      | root     | localhost | unix_socket           |                                           |
      | mysql    | localhost | mysql_native_password | invalid                                   |
      | testuser | localhost | unix_socket           |                                           |
      | warpnode | localhost | mysql_native_password | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |
      +----------+-----------+-----------------------+-------------------------------------------+
       
      QUIT;
      

      ...then login no longer works on BOTH methods

      # mysql -u testuser
      ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
      $ sudo mysql -u testuser
      ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
      $ mysql -u testuser -p
      Enter password:
      ERROR 1698 (28000): Access denied for user 'testuser'@'localhost'
      

      Extra observation 2

      If testuser at this point is altered to use mysql_native_password auth explicitly...

      ALTER USER 'testuser'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass');
      FLUSH PRIVILEGES;
      SELECT user,host,plugin,authentication_string FROM mysql.user;
       
      +----------+-----------+-----------------------+-------------------------------------------+
      | User     | Host      | plugin                | authentication_string                     |
      +----------+-----------+-----------------------+-------------------------------------------+
      | root     | localhost | unix_socket           |                                           |
      | mysql    | localhost | mysql_native_password | invalid                                   |
      | testuser | localhost | mysql_native_password | *00E247AC5F9AF26AE0194B41E1E769DEE1429A29 |
      +----------+-----------+-----------------------+-------------------------------------------+
      
      

      ...then using password (testpass) works (again)

      $ mysql -u testuser -p
      Enter password:
      

      Expected behaviour

      If the documentation is correct, then

      1) IDENTIFIED VIA mysql_native_password USING PASSWORD('testpass') OR unix_socket should yield ability to log in via both methods

      # mysql -u testuser
      $ mysql -u testuser -p
      

      2) Explicit ALTER USER testuser@localhost IDENTIFIED VIA unix_socket should yield ability to log in via unix_socket

      # mysql -u testuser
      

      Thanks in advance!

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            kroko Reinis Adovics
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: