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

Document in detail the new authentication in 10.4 -- tables, views, common scenarios, troubleshooting, etc.

Details

    Description

      I don't know if we even have the old way documented somewhere in the KB, but this knowledge can be easily obtained online from multiple sources, and it boils down to

      • restart server with skip-grant-tables
      • run update mysql.user set password='' where user='root'
      • run flush privileges

      It doesn't work anymore, obviously. I don't yet know how it's supposed to be done now. I found some way, which is to update mysql.global_priv.Priv to set it to the same long JSON as it is, but to replace the authentication string; but I hope it's not the right way, because it's ugly.


      Later update

      The issue has become more serious after introducing multiple authentication and unix_socket by default. It is neither intuitive nor familiar, so it needs to be documented fully, covering all common scenarios and typical situations, including, but not limited to,

      • how to connect to the server after installing the server system-wide (used to be -uroot without the password or with the password set upon installation, not anymore);
      • how to connect to the server after installing the server locally, under a non-root user (used to be -uroot without the password or with the password set upon installation, not anymore);
      • what, if any, considerations regarding compilation options need to be made when compiling the server from source;
      • how to troubleshoot and fix the broken auth info (used to be via -skip-grant-tables + flush privileges, not anymore);
      • how to see all users' configuration (used to be select user, host, plugin, ... from mysql.user, not anymore);
      • how to reset the password if unix_socket can't be used (used to be via mysql.user update, see comments, but fix the syntax of the suggested update);
      • how to update users in bulk (used to be via mysql.user update, probably not anymore);
        ...

      This must be done before 10.4.3 RC is released

      The documentation must be the first hit in the KB by most obvious key words, e.g. by search for "authentication" and such.
      The page must be linked to from the release notes, and the changes need to be emphasized in all possible ways.

      It is especially important since when users seek help in regard to authentication problems, most often they end up with the information related to MySQL.

      Attachments

        Activity

          serg Sergei Golubchik added a comment - - edited

          Now one can use an update such as

          update mysql.global.priv set privs=json_remove(privs, '$.password') where user='root';
          

          or even

          update mysql.global.priv set privs=json_remove(privs, '$.plugin', '$.password', '$.auth_or') where user='root';
          

          to remove all traces of authentication for the root user.

          I can make it simpler (patch attached), just restart server with skip-grant-tables and run

          flush privileges;
          set password for root@localhost='';
          

          But after MDEV-12484 this should be a moot point, because the root user will always accept connections without a password, so one will be able to connect normally and reset or change the root password without restarting the server in --skip-grant-tables

          serg Sergei Golubchik added a comment - - edited Now one can use an update such as update mysql. global .priv set privs=json_remove(privs, '$.password' ) where user = 'root' ; or even update mysql. global .priv set privs=json_remove(privs, '$.plugin' , '$.password' , '$.auth_or' ) where user = 'root' ; to remove all traces of authentication for the root user. I can make it simpler ( patch attached ), just restart server with skip-grant-tables and run flush privileges ; set password for root@localhost= '' ; But after MDEV-12484 this should be a moot point, because the root user will always accept connections without a password, so one will be able to connect normally and reset or change the root password without restarting the server in --skip-grant-tables

          We need to find a good place in the KB for the new suggested syntax.
          I don't think MDEV-12484 completely eliminates the need. Apart from corner cases, like admins with limited sudo rights (can restart the service, can't run mysql client, etc.), or problems like unavailable unix_socket plugin, or re-configured grants for not using the plugin, there is a big non-corner case, Windows. I see MDEV-12484 mentions auth_namepipe in the description, but never later, so I assume it doesn't come along with it.

          elenst Elena Stepanova added a comment - We need to find a good place in the KB for the new suggested syntax. I don't think MDEV-12484 completely eliminates the need. Apart from corner cases, like admins with limited sudo rights (can restart the service, can't run mysql client, etc.), or problems like unavailable unix_socket plugin, or re-configured grants for not using the plugin, there is a big non-corner case, Windows. I see MDEV-12484 mentions auth_namepipe in the description, but never later, so I assume it doesn't come along with it.

          Some answers

          how to connect to the server after installing the server system-wide (used to be -uroot without the password or with the password set upon installation, not anymore);

          Not on Windows: There are two password-less accounts, root@localhost and whatever user owns the datadir (usually it's mysql), also @localhost. To connect one could use sudo mysql or sudo -u mysql mysql. The second should be a safer solution for scripting, because scripts won't need to run under system root credentials.

          After connecting to the server one can use SET PASSWORD to set a root@localhost (or mysql@localhost) password and access it in the future without sudo (but with a password), if needed.

          on Windows — nothing changed, I presume (mysql_install_db doesn't apply)

          how to connect to the server after installing the server locally, under a non-root user (used to be -uroot without the password or with the password set upon installation, not anymore);

          Same as above. The second password-less account is for the user who owns the datadir. So, after installing MariaDB locally under a non-root account, simply run mysql.

          what, if any, considerations regarding compilation options need to be made when compiling the server from source;

          If one disables unix_socket, say with -DPLUGIN_AUTH_SOCKET=NO (or simply starts the server with --disable-unix-socket) the above will not work, one should either not use mysql_install_db in this case, or start the server with --skip-grant-tables and update privilege tables to not refer to unix_socket plugin.

          how to troubleshoot and fix the broken auth info (used to be via -skip-grant-tables + flush privileges, not anymore);

          --skip-grant-tables + flush privileges + set password or alter user

          how to see all users' configuration (used to be select user, host, plugin, ... from mysql.user, not anymore);

          for example... select user,host,json_detailed(Priv) from mysql.global_priv but this is peeking into internal data, they can change any time in any way and in any release.

          how to reset the password if unix_socket can't be used (used to be via mysql.user update, see comments, but fix the syntax of the suggested update);

          ALTER USER or updating mysql.global_priv but see the disclaimer above.

          how to update users in bulk (used to be via mysql.user update, probably not anymore);

          by updating mysql.global_priv but see the disclaimer above.

          serg Sergei Golubchik added a comment - Some answers how to connect to the server after installing the server system-wide (used to be -uroot without the password or with the password set upon installation, not anymore); Not on Windows: There are two password-less accounts, root@localhost and whatever user owns the datadir (usually it's mysql ), also @localhost . To connect one could use sudo mysql or sudo -u mysql mysql . The second should be a safer solution for scripting, because scripts won't need to run under system root credentials. After connecting to the server one can use SET PASSWORD to set a root@localhost (or mysql@localhost ) password and access it in the future without sudo (but with a password), if needed. on Windows — nothing changed, I presume (mysql_install_db doesn't apply) how to connect to the server after installing the server locally, under a non-root user (used to be -uroot without the password or with the password set upon installation, not anymore); Same as above. The second password-less account is for the user who owns the datadir. So, after installing MariaDB locally under a non-root account, simply run mysql . what, if any, considerations regarding compilation options need to be made when compiling the server from source; If one disables unix_socket, say with -DPLUGIN_AUTH_SOCKET=NO (or simply starts the server with --disable-unix-socket ) the above will not work, one should either not use mysql_install_db in this case, or start the server with --skip-grant-tables and update privilege tables to not refer to unix_socket plugin. how to troubleshoot and fix the broken auth info (used to be via -skip-grant-tables + flush privileges , not anymore); --skip-grant-tables + flush privileges + set password or alter user how to see all users' configuration (used to be select user, host, plugin, ... from mysql.user , not anymore); for example... select user,host,json_detailed(Priv) from mysql.global_priv but this is peeking into internal data, they can change any time in any way and in any release. how to reset the password if unix_socket can't be used (used to be via mysql.user update, see comments, but fix the syntax of the suggested update); ALTER USER or updating mysql.global_priv but see the disclaimer above. how to update users in bulk (used to be via mysql.user update, probably not anymore); by updating mysql.global_priv but see the disclaimer above.

          Also, MDEV-12484 needs to be documented. The new default configuration created by mysql_install_db makes two all-privilege users. One is root and the other is whoever owns the datadir. The logic is that these two users can access all the data anyway, so the password doesn't add much protection. Both users are created with IDENTIFIED WITH mysql_native_password USING "invalid" OR unix_socket. Meaning, one can connect if the system user is root or the datadir owner. And one cannot connect with a password. But after logging in one can use SET PASSWORD to change the password to something valid and then connection with a password (and without sudo) will work too.

          serg Sergei Golubchik added a comment - Also, MDEV-12484 needs to be documented. The new default configuration created by mysql_install_db makes two all-privilege users. One is root and the other is whoever owns the datadir. The logic is that these two users can access all the data anyway, so the password doesn't add much protection. Both users are created with IDENTIFIED WITH mysql_native_password USING "invalid" OR unix_socket . Meaning, one can connect if the system user is root or the datadir owner. And one cannot connect with a password. But after logging in one can use SET PASSWORD to change the password to something valid and then connection with a password (and without sudo) will work too.

          It's been closed, but I still can't even find where it was documented.
          I go to mariadb.com/kb and search for 'authentication', which I think is a reasonable way when you do not know the exact name of the article, and I get the first page like this:

          Pluggable Authentication
          Authentication Plugins
          Authentication with Pluggable Authentication Modules (PAM)
          Authentication Plugin - mysql_old_password
          Authentication Modules in MaxScale
          Authentication Plugin - SHA-256
          Authentication Modules in MaxScale
          Authentication Plugin - GSSAPI
          Authentication Plugin - ed25519
          Authentication Plugin - mysql_native_password
          Configuring PAM Authentication and User Mapping with LDAP Authentication
          Authentication Plugin - Named Pipe
          Authentication Modules in MaxScale
          Authentication Plugin - Unix Socket
          Authentication Plugin - PAM
          Configuring PAM Authentication and User Mapping with Unix Authentication
          GSSAPI Authentication with MariaDB Connector/J
          COM_CHANGE_USER
          MaxScale GSSAPI Client Authenticator
          GSSAPI Client Authenticator
          MaxScale GSSAPI Client Authenticator
          Connecting
          COM_RESET_CONNECTION
          MySQL Authenticator
          PAM Authenticator

          ... and 10 more pages to go, every next one looking worse than previous ones.

          Please make sure the documentation is findable for anyone other than the authors.

          elenst Elena Stepanova added a comment - It's been closed, but I still can't even find where it was documented. I go to mariadb.com/kb and search for 'authentication', which I think is a reasonable way when you do not know the exact name of the article , and I get the first page like this: Pluggable Authentication Authentication Plugins Authentication with Pluggable Authentication Modules (PAM) Authentication Plugin - mysql_old_password Authentication Modules in MaxScale Authentication Plugin - SHA-256 Authentication Modules in MaxScale Authentication Plugin - GSSAPI Authentication Plugin - ed25519 Authentication Plugin - mysql_native_password Configuring PAM Authentication and User Mapping with LDAP Authentication Authentication Plugin - Named Pipe Authentication Modules in MaxScale Authentication Plugin - Unix Socket Authentication Plugin - PAM Configuring PAM Authentication and User Mapping with Unix Authentication GSSAPI Authentication with MariaDB Connector/J COM_CHANGE_USER MaxScale GSSAPI Client Authenticator GSSAPI Client Authenticator MaxScale GSSAPI Client Authenticator Connecting COM_RESET_CONNECTION MySQL Authenticator PAM Authenticator ... and 10 more pages to go, every next one looking worse than previous ones. Please make sure the documentation is findable for anyone other than the authors.
          serg Sergei Golubchik added a comment - greenman : https://mariadb.org/authentication-in-mariadb-10-4/
          greenman Ian Gilfillan added a comment -

          Re-opening to keep on the radar, and incorporate Sergei's blog post

          greenman Ian Gilfillan added a comment - Re-opening to keep on the radar, and incorporate Sergei's blog post
          greenman Ian Gilfillan added a comment -

          Further user feedback incorporated, nothing for a while so can be closed

          greenman Ian Gilfillan added a comment - Further user feedback incorporated, nothing for a while so can be closed

          People

            greenman Ian Gilfillan
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.