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

Enable unix socket authentication by default

Details

    Description

      We have had in Debian/Ubuntu since late 2015 unix socket authentication by default on new MariaDB installations (see MDEV-8375).

      Now in 10.3 it is time to start do this in upstream MariaDB globally.

      Via Debian we have already experience that this works well in many environments and use cases. There is a Windows equivalent for unix socket auth is called auth_namepipe and has been tested to work already in 2015.

      The remaining question is whether to have this as-is in upstream now or should we wait until we have automatic fallback to password if the unix_socket did not match (support for multiple authentication plugins).

      The Debian sources can be found here: https://anonscm.debian.org/cgit/pkg-mysql/mariadb-10.1.git/tree/debian

      Note the recent rewrite by Kristian, which has been upstreamed so the capability is already in upstream: https://anonscm.debian.org/cgit/pkg-mysql/mariadb-10.1.git/commit/debian/patches?id=87c1d427f66c0f351019b3a23c3b1de1089b4170

      Attachments

        Issue Links

          Activity

            otto Otto Kekäläinen created issue -

            If there is no strong demand for this to happen fast due to any policies or whatever, I would rather wait for the automatic fallback. The change of the default authentication method seems to have caused considerable confusion among users, at least I have seen questions about why it is happening and how to get back to the usual password authentication on multiple occasions on resources like stackoverflow and such.

            elenst Elena Stepanova added a comment - If there is no strong demand for this to happen fast due to any policies or whatever, I would rather wait for the automatic fallback. The change of the default authentication method seems to have caused considerable confusion among users, at least I have seen questions about why it is happening and how to get back to the usual password authentication on multiple occasions on resources like stackoverflow and such.
            GeoffMontee Geoff Montee (Inactive) made changes -
            Field Original Value New Value

            I have also run into a few users who are confused by this change. For example, I submitted MDEV-12835 on behalf of the latest confused user. However, I have no idea if the confused users are representative of the majority of MariaDB users.

            GeoffMontee Geoff Montee (Inactive) added a comment - I have also run into a few users who are confused by this change. For example, I submitted MDEV-12835 on behalf of the latest confused user. However, I have no idea if the confused users are representative of the majority of MariaDB users.

            GeoffMontee, fairly representative. Unfortunately, questions about it are quite common on #maria channel on Freenode.

            serg Sergei Golubchik added a comment - GeoffMontee , fairly representative. Unfortunately, questions about it are quite common on #maria channel on Freenode.
            serg Sergei Golubchik made changes -
            Affects Version/s 10.3 [ 22126 ]
            Issue Type Bug [ 1 ] Task [ 3 ]
            serg Sergei Golubchik made changes -
            Component/s Authentication and Privilege System [ 13101 ]
            Component/s Packaging [ 10700 ]
            Component/s Plugin - unix_socket [ 10203 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            julien.fritsch Julien Fritsch made changes -
            Epic Link PT-73 [ 68549 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Priority Major [ 3 ] Critical [ 2 ]

            The old Debian packaging git repo server has been shut down and replaced by Gitlab based system at https://salsa.debian.org/mariadb-team/

            otto Otto Kekäläinen added a comment - The old Debian packaging git repo server has been shut down and replaced by Gitlab based system at https://salsa.debian.org/mariadb-team/
            ralf.gebhardt Ralf Gebhardt made changes -
            Rank Ranked higher
            ralf.gebhardt Ralf Gebhardt made changes -
            serg Sergei Golubchik made changes -
            Fix Version/s 10.4 [ 22408 ]
            serg Sergei Golubchik made changes -
            Priority Critical [ 2 ] Major [ 3 ]
            serg Sergei Golubchik made changes -

            There are different use cases.

            If MariaDB is being installed system-wide and configured as a root user, this is the most common use case, then we can create a root@localhost account that can authenticate either with unix_socket or, alternatively, with a password. But we'll set an invalid password, so by default there is no password-less root account, still a root can log in and do SET PASSWORD if needed.

            Another use case is installing MariaDB locally as a non-root user. In this case the previous approach would require one to sudo to be able to log in as root (debian bug#848616). For this use case we could also create a $USER@localhost account with the same authentication as above.

            I suggest the default behavior of mysql_install_db should cover both cases as above and command line options would cover for other, less common, use cases (like installing MariaDB as non-root and then copying into system-wide location, or forcing password-less root).

            serg Sergei Golubchik added a comment - There are different use cases. If MariaDB is being installed system-wide and configured as a root user, this is the most common use case, then we can create a root@localhost account that can authenticate either with unix_socket or, alternatively, with a password. But we'll set an invalid password, so by default there is no password-less root account, still a root can log in and do SET PASSWORD if needed. Another use case is installing MariaDB locally as a non-root user. In this case the previous approach would require one to sudo to be able to log in as root ( debian bug#848616 ). For this use case we could also create a $USER@localhost account with the same authentication as above. I suggest the default behavior of mysql_install_db should cover both cases as above and command line options would cover for other, less common, use cases (like installing MariaDB as non-root and then copying into system-wide location, or forcing password-less root).
            danblack Daniel Black added a comment - - edited

            For the systemwide case I recommend packaging (rpm/deb) a mysql@localhost IDENTIFIED VIA unix_socket user so:

            • that logrotate (MDEV-16621)
            • debian/additions/debian-start; and
            • other packages the create users/databases.

            It has the advantages that:

            • its a non-root user (aka) mysql, distros often desire for scripts;
            • its a unix account already;
            • removes systemd PermissionStartOnly=true (root) permission for running /etc/mysql/debian-start (other requirements removed in gh pr #1143 and #1105)
            • a low susceptibility to compromise;
            • immune from the dodgy hacks that people have posted everywhere on resetting the root@localhost account;
            • isn't a password that breaks when updating replication master/slaves

            So would be in addition to the root@localhost that people can and will break (but will be able to be reset without `skip-priv-tables`)

            danblack Daniel Black added a comment - - edited For the systemwide case I recommend packaging (rpm/deb) a mysql@localhost IDENTIFIED VIA unix_socket user so: that logrotate ( MDEV-16621 ) debian/additions/debian-start; and other packages the create users/databases. It has the advantages that: its a non-root user (aka) mysql, distros often desire for scripts; its a unix account already; removes systemd PermissionStartOnly=true (root) permission for running /etc/mysql/debian-start (other requirements removed in gh pr #1143 and #1105) a low susceptibility to compromise; immune from the dodgy hacks that people have posted everywhere on resetting the root@localhost account; isn't a password that breaks when updating replication master/slaves So would be in addition to the root@localhost that people can and will break (but will be able to be reset without `skip-priv-tables`)

            A potential downside with creating a mysql@localhost account with unix_socket authentication is that the mysql Unix user's default shell is /sbin/nologin on a lot of systems, so it might be kind of awkward for DBAs to use the account with unix_socket authentication.

            For example, if we create the MariaDB user with unix_socket authentication:

            MariaDB [(none)]> INSTALL SONAME 'auth_socket';
            Query OK, 0 rows affected (0.002 sec)
             
            MariaDB [(none)]> CREATE USER mysql@localhost IDENTIFIED VIA unix_socket;
            Query OK, 0 rows affected (0.000 sec)
             
            MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO mysql@localhost WITH GRANT OPTION;
            Query OK, 0 rows affected (0.000 sec)
            

            And then if we try to log in with the Unix account on RHEL 7:

            [ec2-user@ip-172-30-1-236 ~]$ sudo passwd mysql
            Changing password for user mysql.
            New password:
            Retype new password:
            passwd: all authentication tokens updated successfully.
            [ec2-user@ip-172-30-1-236 ~]$ su -l mysql
            Password:
            Last failed login: Mon Jan 21 03:55:21 UTC 2019 on pts/0
            There was 1 failed login attempt since the last successful login.
            This account is currently not available.
            [ec2-user@ip-172-30-1-236 ~]$ getent passwd mysql
            mysql:x:997:994:MySQL server:/var/lib/mysql:/sbin/nologin
            [ec2-user@ip-172-30-1-236 ~]$ cat /etc/redhat-release
            Red Hat Enterprise Linux Server release 7.5 (Maipo)
            

            It fails if we actually try to login as the user, because the user's default shell is /sbin/nologin.

            So to make this work, you'd either have to change the Unix user's default shell or find ways to authenticate as the Unix user without logging into a shell, such as by using sudo, like in the following example:

            [ec2-user@ip-172-30-1-236 ~]$ sudo -u mysql mysql -u mysql
            Welcome to the MariaDB monitor.  Commands end with ; or \g.
            Your MariaDB connection id is 12
            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 CURRENT_USER();
            +-----------------+
            | CURRENT_USER()  |
            +-----------------+
            | mysql@localhost |
            +-----------------+
            1 row in set (0.000 sec)
            

            If this mysql@localhost account would only be intended for automated things like logrotate and package installation scripts, then maybe this isn't a problem.

            GeoffMontee Geoff Montee (Inactive) added a comment - A potential downside with creating a mysql@localhost account with unix_socket authentication is that the mysql Unix user's default shell is /sbin/nologin on a lot of systems, so it might be kind of awkward for DBAs to use the account with unix_socket authentication. For example, if we create the MariaDB user with unix_socket authentication: MariaDB [(none)]> INSTALL SONAME 'auth_socket'; Query OK, 0 rows affected (0.002 sec)   MariaDB [(none)]> CREATE USER mysql@localhost IDENTIFIED VIA unix_socket; Query OK, 0 rows affected (0.000 sec)   MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO mysql@localhost WITH GRANT OPTION; Query OK, 0 rows affected (0.000 sec) And then if we try to log in with the Unix account on RHEL 7: [ec2-user@ip-172-30-1-236 ~]$ sudo passwd mysql Changing password for user mysql. New password: Retype new password: passwd: all authentication tokens updated successfully. [ec2-user@ip-172-30-1-236 ~]$ su -l mysql Password: Last failed login: Mon Jan 21 03:55:21 UTC 2019 on pts/0 There was 1 failed login attempt since the last successful login. This account is currently not available. [ec2-user@ip-172-30-1-236 ~]$ getent passwd mysql mysql:x:997:994:MySQL server:/var/lib/mysql:/sbin/nologin [ec2-user@ip-172-30-1-236 ~]$ cat /etc/redhat-release Red Hat Enterprise Linux Server release 7.5 (Maipo) It fails if we actually try to login as the user, because the user's default shell is /sbin/nologin. So to make this work, you'd either have to change the Unix user's default shell or find ways to authenticate as the Unix user without logging into a shell, such as by using sudo, like in the following example: [ec2-user@ip-172-30-1-236 ~]$ sudo -u mysql mysql -u mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 12 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 CURRENT_USER(); +-----------------+ | CURRENT_USER() | +-----------------+ | mysql@localhost | +-----------------+ 1 row in set (0.000 sec) If this mysql@localhost account would only be intended for automated things like logrotate and package installation scripts, then maybe this isn't a problem.
            danblack Daniel Black made changes -
            Attachment screenshot-1.png [ 47131 ]
            danblack Daniel Black added a comment -

            Thanks for the tests GeoffMontee. Yes was thinking of it for automated purposes.

            sudo -u mysql mysql -u mysql

            Can still access an interactive mysql@localhost user even with `/bin/nologin` set as the shell, for password recovery without downtime.

            danblack Daniel Black added a comment - Thanks for the tests GeoffMontee . Yes was thinking of it for automated purposes. sudo -u mysql mysql -u mysql Can still access an interactive mysql@localhost user even with `/bin/nologin` set as the shell, for password recovery without downtime.

            I suspect that mysql@localhost could have a rather good chance of conflicting with user accounts, looks like a typical name one could create for some mysql-based applications.

            serg Sergei Golubchik added a comment - I suspect that mysql@localhost could have a rather good chance of conflicting with user accounts, looks like a typical name one could create for some mysql-based applications.
            danblack Daniel Black added a comment -

            I agree someone may have done it, and packaging scripts will need to account for this, however I've never seen or found a stack overflow, dba.stackexchange.com, blog or any other reference to it. I tried searching Google for CREATE USER mysql and haven't found an exact match . All examples I've seen focus on a personal name (dan), a role (admin), root. or and application name.

            danblack Daniel Black added a comment - I agree someone may have done it, and packaging scripts will need to account for this, however I've never seen or found a stack overflow, dba.stackexchange.com, blog or any other reference to it. I tried searching Google for CREATE USER mysql and haven't found an exact match . All examples I've seen focus on a personal name ( dan ), a role ( admin ), root . or and application name.
            serg Sergei Golubchik made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            serg Sergei Golubchik made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]

            Okay. I'll try this — two all-privilege accounts, authenticated with unix_socket. One is root. The other is:

              # Use $auth_root_socket_user if explicitly specified.
              # Otherwise use the owner of datadir - ${user:-$USER}
              # Use 'root' as a fallback
              auth_root_socket_user=${auth_root_socket_user:-${user:-${USER:-root}}}
            

            The logic here is — both root user and the owner of the datadir do not need to know the password, they have the full access to the data anyway. So we don't ask them to provide the password.

            This covers both your mysql@localhost suggestion and debian bug#848616 (installed locally under a normal user) use cases.

            serg Sergei Golubchik added a comment - Okay. I'll try this — two all-privilege accounts, authenticated with unix_socket. One is root. The other is: # Use $auth_root_socket_user if explicitly specified. # Otherwise use the owner of datadir - ${user:-$USER} # Use 'root' as a fallback auth_root_socket_user=${auth_root_socket_user:-${user:-${USER:-root}}} The logic here is — both root user and the owner of the datadir do not need to know the password, they have the full access to the data anyway. So we don't ask them to provide the password. This covers both your mysql@localhost suggestion and debian bug#848616 (installed locally under a normal user) use cases.
            serg Sergei Golubchik made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.4.3 [ 23230 ]
            Fix Version/s 10.4 [ 22408 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            danblack Daniel Black added a comment -

            Nice work serg. Always great to see large chunks of packaging script disappear (https://github.com/mariadb/server/commit/25c6d626570e048fc8ddf1ef500a0c601d8b8a60). Trivial, minor, and not without RPM implications, does `./support-files/mysql-log-rotate.sh` need a `su mysql mysql`?

            danblack Daniel Black added a comment - Nice work serg . Always great to see large chunks of packaging script disappear ( https://github.com/mariadb/server/commit/25c6d626570e048fc8ddf1ef500a0c601d8b8a60 ). Trivial, minor, and not without RPM implications, does `./support-files/mysql-log-rotate.sh` need a `su mysql mysql`?

            No, I don't think so. That's the same with Debian — if a script can read /etc/mysql/debian.cnf then it's run as root and that alone is enough. In mysql-log-rotate.sh it looks like it needs to read ~root/.my.cnf, so it's enough too.

            By the way, with mysql user it's somewhat tricky. I didn't dig too deep but the only sudo that I managed to get working was

            $ sudo sudo -umysql mysql -umysql
            


            which looks a bit redundant to me. For running as root simple sudo mysql works.

            serg Sergei Golubchik added a comment - No, I don't think so. That's the same with Debian — if a script can read /etc/mysql/debian.cnf then it's run as root and that alone is enough. In mysql-log-rotate.sh it looks like it needs to read ~root/.my.cnf , so it's enough too. By the way, with mysql user it's somewhat tricky. I didn't dig too deep but the only sudo that I managed to get working was $ sudo sudo -umysql mysql -umysql which looks a bit redundant to me. For running as root simple sudo mysql works.
            greenman Ian Gilfillan made changes -

            Great to see this finally done upstream! A big thank you serg for taking the time to merge it in.

            otto Otto Kekäläinen added a comment - Great to see this finally done upstream! A big thank you serg for taking the time to merge it in.
            otto Otto Kekäläinen made changes -
            GeoffMontee Geoff Montee (Inactive) made changes -
            GeoffMontee Geoff Montee (Inactive) made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 80295 ] MariaDB v4 [ 133204 ]

            People

              serg Sergei Golubchik
              otto Otto Kekäläinen
              Votes:
              0 Vote for this issue
              Watchers:
              9 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.