Bug 1796106 - mariadb-10.4.11: Client authentication fails: ERROR 1698 (28000): Access denied for user 'root'@'localhost'
Summary: mariadb-10.4.11: Client authentication fails: ERROR 1698 (28000): Access deni...
Keywords:
Status: CLOSED ERRATA
Alias: None
Product: Fedora
Classification: Fedora
Component: mariadb
Version: 32
Hardware: Unspecified
OS: Unspecified
high
high
Target Milestone: ---
Assignee: Lukas Javorsky
QA Contact: Fedora Extras Quality Assurance
URL: https://mariadb.com/kb/en/authenticat...
Whiteboard:
Depends On:
Blocks: 1793510 1793917
TreeView+ depends on / blocked
 
Reported: 2020-01-29 15:57 UTC by Petr Pisar
Modified: 2020-03-18 04:12 UTC (History)
11 users (show)

Fixed In Version: mariadb-10.4.12-3.fc32 mariadb-10.4-3220200316141821.43bbeeef mariadb-10.4-3020200316141821.a5b0195c mariadb-10.4-3120200316141821.f636be4b
Doc Type: If docs needed, set a value
Doc Text:
Clone Of:
Environment:
Last Closed: 2020-03-18 02:50:46 UTC
Type: Bug
Embargoed:


Attachments (Terms of Use)

Description Petr Pisar 2020-01-29 15:57:06 UTC
After upgrading mariadb from 10.3.21 to 10.4.11, perl-Test-mysqld tests fail (bug #1793917). This reason is that the server refuses a client to authenticate, probably due this <https://github.com/MariaDB/server/commit/7f6d88944c> mariadb change.

How to reproduce:

(1) Create an empty /tmp/t directory. Create etc, tmp, var subdirectories. Create /tmp/t/etc/my.cnf configuration file with this content:

[mysqld]
datadir=/tmp/t/var
pid-file=/tmp/t/tmp/mysqld.pid
skip-networking
socket=/tmp/t/tmp/mysql.sock
tmpdir=/tmp/t/tmp


(2) Initialalize the database:

$ /usr/bin/mysql_install_db --defaults-file='/tmp/t/etc/my.cnf' --basedir='/usr'
Installing MariaDB/MySQL system tables in '/tmp/t/var' ...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system


Two all-privilege accounts were created.
One is root@localhost, it has no password, but you need to
be system 'root' user to connect. Use, for example, sudo mysql
The second is test@localhost, it has no password either, but
you need to be the system 'test' user to connect.
After connecting you can set the password, if you would need to be
able to connect as any of these users with a password and without sudo

See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.

You can start the MariaDB daemon with:
cd '/usr' ; /usr/bin/mysqld_safe --datadir='/tmp/t/var'

You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/mysql-test' ; perl mysql-test-run.pl

Please report any problems at http://mariadb.org/jira

The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/

(3) Start the database:

$ /usr/libexec/mysqld --defaults-file=/tmp/t/etc/my.cnf
2020-01-29 16:45:27 0 [Note] /usr/libexec/mysqld (mysqld 10.4.11-MariaDB) starting as process 39770 ...
2020-01-29 16:45:27 0 [Note] InnoDB: Using Linux native AIO
2020-01-29 16:45:27 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-01-29 16:45:27 0 [Note] InnoDB: Uses event mutexes
2020-01-29 16:45:27 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-01-29 16:45:27 0 [Note] InnoDB: Number of pools: 1
2020-01-29 16:45:27 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-01-29 16:45:27 0 [Note] mysqld: O_TMPFILE is not supported on /tmp/t/tmp (disabling future attempts)
2020-01-29 16:45:27 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-01-29 16:45:27 0 [Note] InnoDB: Completed initialization of buffer pool
2020-01-29 16:45:27 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-01-29 16:45:27 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2020-01-29 16:45:27 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-01-29 16:45:27 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-01-29 16:45:27 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-01-29 16:45:27 0 [Note] InnoDB: 10.4.11 started; log sequence number 60972; transaction id 21
2020-01-29 16:45:27 0 [Note] InnoDB: Loading buffer pool(s) from /tmp/t/var/ib_buffer_pool
2020-01-29 16:45:27 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-01-29 16:45:27 0 [Note] InnoDB: Buffer pool(s) load completed at 200129 16:45:27
2020-01-29 16:45:27 0 [Note] Reading of all Master_info entries succeeded
2020-01-29 16:45:27 0 [Note] Added new Master_info '' to hash table
2020-01-29 16:45:27 0 [Note] /usr/libexec/mysqld: ready for connections.
Version: '10.4.11-MariaDB'  socket: '/tmp/t/tmp/mysql.sock'  port: 0  MariaDB Server

(4) Connect with a client as the same non-root user:

$ mariadb -S /tmp/t/tmp/mysql.sock 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

The server logs:

2020-01-29 16:45:57 8 [Warning] Access denied for user 'root'@'localhost'


After studying mysql_install_db usage help and code, I believe that the user should be able to authenticate because --auth-root-socket-user value defaults to --user or $USER environment variable.

Please note that overriding the client user to the user who executes the client (or to a non-existent user) passes:

$ mariadb -S /tmp/t/tmp/mysql.sock --user $USER
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.4.11-MariaDB MariaDB Server
[...]

Basically I'm after a simple round trip test when a non-root starts a new database and the same user connects to it. In my opinion this use case should work by default.

I suspect that either the client or the server injects the "root" username if not specified and that blows up.

Comment 1 Petr Pisar 2020-01-29 16:06:25 UTC
For your information, perl-Test-mysqld passes everywhere --user=root explicitly. But that also stopped working. So I found that current mariadb prefers the non-root mode, but it seems the feature is not implemented properly.

Comment 2 Remi Collet 2020-02-10 09:52:30 UTC
Same issue with GLPI which is FTBFS in rawhide

Using "root" account, I got some explanation

    Two all-privilege accounts were created.
    One is root@localhost, it has no password, but you need to
    be system 'root' user to connect. Use, for example, sudo mysql
    The second is mockbuild@localhost, it has no password either, but
    you need to be the system 'mockbuild' user to connect.

But after switching to mockbuild

++ /usr/bin/mysqladmin --no-defaults --socket=/builddir/build/BUILD/glpi-b7f90388babf6b621dff121701b69f8d20124e77/mysql.sock --user=mockbuild ping
+ RESPONSE='mysqld is alive'

So seems OK, but trying to run some SQL tests

"(1698) Access denied for user 'mockbuild'@'localhost'"

Comment 3 Remi Collet 2020-02-10 09:54:53 UTC
Build.log (with root, ok on older version)
=> https://kojipkgs.fedoraproject.org//work/tasks/5220/41445220/build.log

Build.log (with mockbuild)
=> https://kojipkgs.fedoraproject.org//work/tasks/5390/41445390/build.log

Comment 4 Remi Collet 2020-02-10 10:51:12 UTC
Fixed for GLPI using the proper socket (UDS instead of network)
https://src.fedoraproject.org/rpms/glpi/c/d52caf26612c50db04197520d48b4d3152f366b2?branch=master

Comment 5 Ben Cotton 2020-02-11 16:34:05 UTC
This bug appears to have been reported against 'rawhide' during the Fedora 32 development cycle.
Changing version to 32.

Comment 6 Lukas Javorsky 2020-02-12 15:32:54 UTC
And what about the option --auth-root-authentication-method=normal

Or alternatively add:

[mysql_install_db]
auth_root_authentication_method=normal

into option file (my.cnf).

They mention it here: https://mariadb.com/kb/en/authentication-from-mariadb-104/
In section Reverting to the Previous Authentication Method for root@localhost.

Comment 7 Lukas Javorsky 2020-02-12 15:33:57 UTC
The option is added to 'mysql_install_db' command

Comment 8 Petr Pisar 2020-02-12 16:00:30 UTC
Those all are workarounds. It should work out of the box.

In my humble opinion the client library should pass a login name of the process to the server if a UNIX socket is used and the user did not specified any user name. Alternatively if no user name is passed in the MySQL protocol (if it is possible) the server should figure out the user name from the socket.

Comment 9 Lukas Javorsky 2020-02-13 10:14:47 UTC
Yes it should if there was unix_socket authentication installed, but it's not. It tries it, but unsuccessfully and then install mysql_native_password.

You can see look that up when you start that server by 'mariadb -S /tmp/t/tmp/mysql.sock --user $USER'

Then write this sequence of mysql commands:
USE mysql;
SELECT User, Host, plugin FROM mysql.user;

This table appears:
+-------+--------------------------------------------------+-----------------------+
| User  | Host                                             | plugin                |
+-------+--------------------------------------------------+-----------------------+
| root  | localhost                                        | mysql_native_password |
| lukas | localhost                                        | mysql_native_password |
|       | localhost                                        |                       |
|       | ci-vm-10-0-136-27.hosted.upshift.rdu2.redhat.com |                       |
+-------+--------------------------------------------------+-----------------------+
4 rows in set (0.004 sec)

But that's still not answer why it tries to connect as root@localhost.

Comment 10 Lukas Javorsky 2020-02-17 12:24:53 UTC
I've contacted an upstream with this bug.

Link to the issue: https://jira.mariadb.org/browse/MDEV-21745

Comment 11 Lukas Javorsky 2020-02-17 12:50:40 UTC
(In reply to Lukas Javorsky from comment #9)
> Yes it should if there was unix_socket authentication installed, but it's
> not. It tries it, but unsuccessfully and then install mysql_native_password.
> 
> You can see look that up when you start that server by 'mariadb -S
> /tmp/t/tmp/mysql.sock --user $USER'
> 
> Then write this sequence of mysql commands:
> USE mysql;
> SELECT User, Host, plugin FROM mysql.user;
> 
> This table appears:
> +-------+--------------------------------------------------+-----------------
> ------+
> | User  | Host                                             | plugin         
> |
> +-------+--------------------------------------------------+-----------------
> ------+
> | root  | localhost                                        |
> mysql_native_password |
> | lukas | localhost                                        |
> mysql_native_password |
> |       | localhost                                        |                
> |
> |       | ci-vm-10-0-136-27.hosted.upshift.rdu2.redhat.com |                
> |
> +-------+--------------------------------------------------+-----------------
> ------+
> 4 rows in set (0.004 sec)
> 
> But that's still not answer why it tries to connect as root@localhost.

I found out that 'mysql.user' table has expired, so I've tried it in a new one 'mysql.global_priv'.

Instead of:
> SELECT User, Host, plugin FROM mysql.user;
Use:
SELECT CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)) FROM mysql.global_priv;

| root@localhost => {
    "access": 18446744073709551615,
    "plugin": "mysql_native_password",
    "authentication_string": "invalid",
    "auth_or": 
    [
        
        {
        },
        
        {
            "plugin": "unix_socket"
        }
    ]
}  |
| lukas@localhost => {
    "access": 18446744073709551615,
    "plugin": "mysql_native_password",
    "authentication_string": "invalid",
    "auth_or": 
    [
        
        {
        },
        
        {
            "plugin": "unix_socket"
        }
    ]
} |

Comment 12 Petr Pisar 2020-02-17 14:11:14 UTC
(In reply to Lukas Javorsky from comment #11)
> I found out that 'mysql.user' table has expired, so I've tried it in a new
> one 'mysql.global_priv'.
> 
> Instead of:
> > SELECT User, Host, plugin FROM mysql.user;
> Use:
> SELECT CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)) FROM
> mysql.global_priv;
> 
> | root@localhost => {
>     "access": 18446744073709551615,
>     "plugin": "mysql_native_password",
>     "authentication_string": "invalid",
>     "auth_or": 
>     [
>         
>         {
>         },
>         
>         {
>             "plugin": "unix_socket"
>         }
>     ]
An expired root password is in line with the documentation <https://mariadb.com/kb/en/authentication-from-mariadb-104/>:

  [...] then it is configured to try to use the mysql_native_password authentication plugin.
  However, an invalid password is initially set, so in order to authenticate this way,
  a password must be set with SET PASSWORD.

Comment 13 Michal Schorm 2020-03-04 15:10:11 UTC
Today, me and Lukas, compared ours (Fedora) and the upstream (MariaDB) packages.

The behave in the same way now.
That means this issue is present in both Fedora and Upstream packages in 10.4 and 10.5 versions.

---

Lukas will take a look at the UNIX soket based authentication, and will examine the possible consequences of making it deafult authentication method in Fedora.

We both will try to reach upstream (more activelly), since they haven't replied to on of the channel we used (JIRA ticket, mail, IRC)

---

Since there are two packages which FTBFS for F32 because fo this issue, we still see solving this issue as our priority.

However, there is a possible outcome scenario, where it will be wise to stick to this new behaviour and thus the FTBFS packages will need to be patched.
Just saying it may be an option. No decission wasn't made yet, since we still don't have enough information from the upstream, about the goal of the change.

Comment 14 Michal Schorm 2020-03-04 15:46:41 UTC
I have (probabbly) found a way to revert the new behaviour !

When initializing the server ( = when running 'mysql_install_db'),
use '--auth-root-authentication-method=normal' option for the 'mysql_install_db' to revert to the old 10.3 behaviour.

https://mariadb.com/kb/en/authentication-from-mariadb-104/#configuring-mysql_install_db-to-revert-to-the-previous-authentication-method

---

PLEASE test this behaviour after me, to double-check I haven't got just something mis-configured in my test environment that cause it to behave in the old manner.

---

Still, this doesn't necessiraly mean we want to revert the default behaviour instead of sticking to the upstream.
This remains unanswered for now.

Comment 15 Michal Schorm 2020-03-05 02:58:00 UTC
I tried to further study the behaviour. Here are my notes:

============================================

EXPECTED BEHAVIOUR:

"unix_socket authentication plugin is now default on Unix-like systems, which is a major change to authentication in MariaDB"
  https://mariadb.com/kb/en/changes-improvements-in-mariadb-104/
  https://jira.mariadb.org/browse/MDEV-12484

This change was implemented, due to a simple fact, that 2 (unix system) users have ultimate rule over the database files, and thus requiring a password (to access the DB) does not bring in any extra level of protection. Those two users are 'root' (0) and the user that owns the files of the DB - usually 'mysql' (27).
Those two unix system user accounts are mirrored into the database and are the first users one can connect to after the inicialization of the DB.

  -----------------------------------------------------------------------------------------------------------
  | Simple test 1 - DB inicialized as 'mysql' user (default for Fedora 32)
  |
  |   MariaDB [(none)]> select host, user, password, plugin from mysql.user;
  |   +-----------+-------+----------+-----------------------+
  |   | Host      | User  | Password | plugin                |
  |   +-----------+-------+----------+-----------------------+
  |   | localhost | root  | invalid  | mysql_native_password |
  |   | localhost | mysql | invalid  | mysql_native_password |
  |   +-----------+-------+----------+-----------------------+
  |
  ------------------------------------------------------------------------
  | Simple test 2 - DB inicialized as 'mysql-test' user
  |
  |   MariaDB [(none)]> select host, user, password, plugin from mysql.user;
  |   +-----------+------------+----------+-----------------------+
  |   | Host      | User       | Password | plugin                |
  |   +-----------+------------+----------+-----------------------+
  |   | localhost | root       | invalid  | mysql_native_password |
  |   | localhost | mysql-test | invalid  | mysql_native_password |
  |   +-----------+------------+----------+-----------------------+
  |
  -----------------------------------------------------------------------------------------------------------

Now there are three crucial things in play:
1/
The outputs above, done by "select ... from mysql.user;" are not completely valid. Complex authentication rules did not fit into rigid structure of a relational mysql.user table.
  https://mariadb.org/authentication-in-mariadb-10-4/
So the correct query to use should be:

  -----------------------------------------------------------------------------------------------------------
  |
  |   MariaDB [(none)]> select concat(user, '@', host, ' => ', json_detailed(priv)) from mysql.global_priv;
  |   +-------------------------------------------------------+
  |   | concat(user, '@', host, ' => ', json_detailed(priv))  |
  |   +-------------------------------------------------------+
  |   | root@localhost => {
  |       "access": 18446744073709551615,
  |       "plugin": "mysql_native_password",
  |       "authentication_string": "invalid",
  |       "auth_or": 
  |       [
  |           {
  |               "plugin": "unix_socket"
  |           }
  |       ]
  |   }       |
  |   | mysql-test@localhost => {
  |       "access": 18446744073709551615,
  |       "plugin": "mysql_native_password",
  |       "authentication_string": "invalid",
  |       "auth_or": 
  |       [
  |           {
  |               "plugin": "unix_socket"
  |           }
  |       ]
  |   }                                                                                     
  |   +-------------------------------------------------------+
  |
  -----------------------------------------------------------------------------------------------------------

Which shows that there are actually 2 possible methods of authentication for the two DB accounts.


2/
Using 'su' will not get you to the desired behaviour.

  -----------------------------------------------------------------------------------------------------------
  |
  |   # whoami 
  |    root
  |   # mysql -S /tmp/t/tmp/mysql.sock
  |    Welcome to the MariaDB monitor.  Commands end with ; or \g.
  |   # su -c 'mysql -S /tmp/t/tmp/mysql.sock' mysql-test
  |    ERROR 1698 (28000): Access denied for user 'root'@'localhost'
  | 
  |   # whoami 
  |    root
  |   # ssh mysql-test
  |    mysql-test.0.1's password: 
  |   $ mysql -S /tmp/t/tmp/mysql.sock
  |    Welcome to the MariaDB monitor.  Commands end with ; or \g.
  |
  -----------------------------------------------------------------------------------------------------------

3/
The password ('authentication_string') on the two DB accounts is not set. ('invalid' is not a valid password hash)
That mean you should be able to connect to the server via the unix_socket, but not via password unit you set it.

  -----------------------------------------------------------------------------------------------------------
  |
  |   # whoami 
  |    root
  |   # mysql
  |    Welcome to the MariaDB monitor.  Commands end with ; or \g.
  |   # mysql -h 127.0.0.1
  |    ERROR 1698 (28000): Access denied for user 'root'@'localhost'
  |
  -----------------------------------------------------------------------------------------------------------

============================================

NOW BACK TO THE ORIGINAL BZ REQUEST:

> Basically I'm after a simple round trip test when a non-root starts a new database and the same user connects to it. In my opinion this use case should work by default.
> I suspect that either the client or the server injects the "root" username if not specified and that blows up.

The issue is that you:
 * haven't set the DB user password
AND (at the same time)
 * you are trying to log in via the unix_socket, but you are not actually / properly logged in the same user.
If you break any of that condition, it will work. (either set a password and use it; or use the unix_socket auth while correctly logged in)


The big confusion with errors like "Access denied for user 'root'@'localhost'" is IMHO entirely based on wrongly logging in into the user in such a way the 'unix_socket' auth plugin would accept it.
It is NOT true that the client would default to connect as 'root', if nothing is specified via "--user" argument.

============================================

OUTCOME:

The upstream made a bigger change in the way the client (and server) authenticate by default.
We should document it and help affected users adapt.
However we shouldn't revert it or go against it.

The new way of authentication WORKS in Fedora.
On the top of that, users still can decide to fall back to old behaviour in several ways.

============================================

WORK TO DO:

* Make some documentation about the change (perhaps this comment?)
* Get it to the users (fedora-users list? fedora magazine?)

* Find out how to utilize the 'unix_socket' auth with 'su'. (And find out why it's not working)
  Or find the most efficient workaround.

* Help affected package(r)s to adapt.
  This will be the new behaviour now and in the future. The upstreams of the projects that depends on MariaDB should adapt.

Comment 16 Lukas Javorsky 2020-03-05 08:37:58 UTC
(In reply to Michal Schorm from comment #14)
> I have (probabbly) found a way to revert the new behaviour !
> 
> When initializing the server ( = when running 'mysql_install_db'),
> use '--auth-root-authentication-method=normal' option for the
> 'mysql_install_db' to revert to the old 10.3 behaviour.
> 
> https://mariadb.com/kb/en/authentication-from-mariadb-104/#configuring-
> mysql_install_db-to-revert-to-the-previous-authentication-method

Yes I've already mentioned this in comment #6

> ---
> 
> PLEASE test this behaviour after me, to double-check I haven't got just
> something mis-configured in my test environment that cause it to behave in
> the old manner.

Yes it works.

> ---
> 
> Still, this doesn't necessiraly mean we want to revert the default behaviour
> instead of sticking to the upstream.
> This remains unanswered for now.

I don't think we want to do it, it's just temporary, until we find better solution.

Comment 17 Lukas Javorsky 2020-03-05 08:44:56 UTC
(In reply to Michal Schorm from comment #15)
> I tried to further study the behaviour. Here are my notes:
> 
> ============================================
> 
> EXPECTED BEHAVIOUR:
> 
> "unix_socket authentication plugin is now default on Unix-like systems,
> which is a major change to authentication in MariaDB"
>   https://mariadb.com/kb/en/changes-improvements-in-mariadb-104/
>   https://jira.mariadb.org/browse/MDEV-12484
> 
> This change was implemented, due to a simple fact, that 2 (unix system)
> users have ultimate rule over the database files, and thus requiring a
> password (to access the DB) does not bring in any extra level of protection.
> Those two users are 'root' (0) and the user that owns the files of the DB -
> usually 'mysql' (27).
> Those two unix system user accounts are mirrored into the database and are
> the first users one can connect to after the inicialization of the DB.
> 
>  
> -----------------------------------------------------------------------------
> ------------------------------
>   | Simple test 1 - DB inicialized as 'mysql' user (default for Fedora 32)
>   |
>   |   MariaDB [(none)]> select host, user, password, plugin from mysql.user;
>   |   +-----------+-------+----------+-----------------------+
>   |   | Host      | User  | Password | plugin                |
>   |   +-----------+-------+----------+-----------------------+
>   |   | localhost | root  | invalid  | mysql_native_password |
>   |   | localhost | mysql | invalid  | mysql_native_password |
>   |   +-----------+-------+----------+-----------------------+
>   |
>   ------------------------------------------------------------------------
>   | Simple test 2 - DB inicialized as 'mysql-test' user
>   |
>   |   MariaDB [(none)]> select host, user, password, plugin from mysql.user;
>   |   +-----------+------------+----------+-----------------------+
>   |   | Host      | User       | Password | plugin                |
>   |   +-----------+------------+----------+-----------------------+
>   |   | localhost | root       | invalid  | mysql_native_password |
>   |   | localhost | mysql-test | invalid  | mysql_native_password |
>   |   +-----------+------------+----------+-----------------------+
>   |
>  
> -----------------------------------------------------------------------------
> ------------------------------
> 
> Now there are three crucial things in play:
> 1/
> The outputs above, done by "select ... from mysql.user;" are not completely
> valid. Complex authentication rules did not fit into rigid structure of a
> relational mysql.user table.
>   https://mariadb.org/authentication-in-mariadb-10-4/
> So the correct query to use should be:
> 
>  
> -----------------------------------------------------------------------------
> ------------------------------
>   |
>   |   MariaDB [(none)]> select concat(user, '@', host, ' => ',
> json_detailed(priv)) from mysql.global_priv;
>   |   +-------------------------------------------------------+
>   |   | concat(user, '@', host, ' => ', json_detailed(priv))  |
>   |   +-------------------------------------------------------+
>   |   | root@localhost => {
>   |       "access": 18446744073709551615,
>   |       "plugin": "mysql_native_password",
>   |       "authentication_string": "invalid",
>   |       "auth_or": 
>   |       [
>   |           {
>   |               "plugin": "unix_socket"
>   |           }
>   |       ]
>   |   }       |
>   |   | mysql-test@localhost => {
>   |       "access": 18446744073709551615,
>   |       "plugin": "mysql_native_password",
>   |       "authentication_string": "invalid",
>   |       "auth_or": 
>   |       [
>   |           {
>   |               "plugin": "unix_socket"
>   |           }
>   |       ]
>   |   }                                                                     
> 
>   |   +-------------------------------------------------------+
>   |
>  
> -----------------------------------------------------------------------------
> ------------------------------


Also already mentioned in comment #11, and it shows that unix_socket auth may not be properly installed, because it should be in the "plugin:" section, because it's set as default from mariadb-10.4
 

> 
> WORK TO DO:
> 
> * Make some documentation about the change (perhaps this comment?)
> * Get it to the users (fedora-users list? fedora magazine?)
> 
> * Find out how to utilize the 'unix_socket' auth with 'su'. (And find out
> why it's not working)
>   Or find the most efficient workaround.

This is first in my TODO-list, so I'll try my best to find the best solution.

> * Help affected package(r)s to adapt.
>   This will be the new behaviour now and in the future. The upstreams of the
> projects that depends on MariaDB should adapt.

Yes we shouldn't go against the upstream, so I agree with you.

Comment 18 Michal Schorm 2020-03-05 10:05:19 UTC
> Also already mentioned in comment #11, 
> and it shows that unix_socket auth may not be properly installed, because it should be in the "plugin:" section, because it's set as default from mariadb-10.4
I don't agree.
Our RPMs share the same behaviour with the upstream RPMs about this one.

Also it shouldn't matter, since you can run
| # mysql -p
as root, with whatever password, and since it will succeed, beacuse it will use the auth via unix_socket.

what do you mean with:
> unix_socket auth may not be properly installed
exactly ?

Comment 19 Petr Pisar 2020-03-06 09:07:48 UTC
Thanks for the investigation. I indeed made the tests from a process that was created with su by root. If you check such a process you will find out that real, effective, and saved UIDs are equaled to the su-ed ID (/proc/$$/status). The only trace of the parent root is /proc/$$/loginuid. AFAIK peer credentials supplied over the unix socket are validated by kernel and thus the server will see the su-ed ID. I suspect the server verifies /proc/$CLIENT_PID/loginuid in addition.

Comment 20 Lukas Javorsky 2020-03-06 09:43:31 UTC
(In reply to Michal Schorm from comment #18)

> what do you mean with:
> > unix_socket auth may not be properly installed
> exactly ?
I read the authentication documentation again and I've realized that I was wrong. Sorry for misleading informations.
So if I understand it correctly, it tries to log in as root@localhost via unix_socket, but it fails due to wrong UID. And thus it tries the second variant which is mysql_native_password. That's why it's in a plugin section.

Referring to official documentation:
> And when the unix_socket auth fails, the mysql_native_password needs to have root's password set.
> Referring to the official documentation:
> The root@localhost user created by mysql_install_db is created with the ability to use two authentication plugins. 
> First, it is configured to try to use the unix_socket authentication plugin. 
> This allows the the root@localhost user to login without a password via the local Unix socket file defined by the socket system variable, 
> as long as the login is attempted from a process owned by the operating system root user account. 
> Second, if authentication fails with the unix_socket authentication plugin, then it is configured to try to use the mysql_native_password authentication plugin. 
> However, an invalid password is initially set, so in order to authenticate this way, a password must be set with SET PASSWORD.

The last sentence is important.

Comment 21 Lukas Javorsky 2020-03-06 13:08:27 UTC
Somehow the part of the documentation in previous comment get corrupted, 

I wanted to paste this one there:

First, it is configured to try to use the unix_socket authentication plugin. 
This allows the the root@localhost user to login without a password via the local Unix socket file defined by the socket system variable, 
as long as the login is attempted from a process owned by the operating system root user account.
Second, if authentication fails with the unix_socket authentication plugin, then it is configured to try to use the mysql_native_password authentication plugin. 
However, an invalid password is initially set, so in order to authenticate this way, a password must be set with SET PASSWORD.

Comment 22 Lukas Javorsky 2020-03-09 08:44:15 UTC
Yes thanks to Peter I realized, that the testing was made by root user and just "su" to normal test user.
That injects the root access and made the fail.

To make the reproducer work as we want (successful) don't just "su <username>" from root, but ssh on the actual testing user.

I just tried it and it works fine.

Maybe if someone else could verify my steps and tell me if it works for them too, it would be awesome.

If so, this bug can be closed. Thanks everyone for cooperation.

Comment 23 Michal Schorm 2020-03-09 12:06:47 UTC
We haven't finished yet!
The question remains - why the 'su' does not work?

I tried some magic with 'su', 'su -' and 'sudo -u'.
Even though, the behaviour is different for each of them (even their approach differ), I wasn't successful into tricing the authentication that I'm the switched user now.

---

The code seems to be pretty short, easy to read and documented:
https://github.com/MariaDB/server/blob/8f8cc5f4c2d6882f169b19d670b922844f66150d/plugin/auth_socket/auth_socket.c

I'd pay attention to:

|  Authentication is successful if the connection is done via a unix socket and
|  the owner of the client process matches the user name that was used when
|  connecting to mysqld.

and 

|  perform the unix socket based authentication
|  This authentication callback performs a unix socket based authentication -
|  it gets the uid of the client process and considers the user authenticated
|  if it uses username of this uid. That is - if the user is already
|  authenticated to the OS (if she is logged in) - she can use MySQL as herself

Comments.

As far as I understood the comments in the code, it should take either the user name specified on the command line (mysql -u testuser)
or it will check the UID of the client process and then it will match it with the unix user name. (the kernel looks in the /etc/passwd anyway, I'd guess)

The user name looks as expected.

But the UID beahves weird.
It looks like it would take UID of *some* parent process of that client process.

|  ├─sshd─┬─sshd───sshd───bash───bash
|  │      └─sshd───sshd(test_2)───bash───pstree

However at the end, all process are forked prom the init process with UID 1, ran by root.
So with my logic, it would always see 'root', but that's not the case either.
Since you can ssh to 'test_2' user, 'su' to 'test_1' and try 'mysql'. It will believe, you are still 'test_2' user. 

---

Lukas, please examine the code.
* Try to check which UID will the plugin see.
* Where it got the information.
* If it's correct.
* Check what username will kernel return for that UID.

Comment 24 Lukas Javorsky 2020-03-12 12:30:44 UTC
> The code seems to be pretty short, easy to read and documented:
> https://github.com/MariaDB/server/blob/8f8cc5f4c2d6882f169b19d670b922844f66150d/plugin/auth_socket/auth_socket.c

After rerunning the server in gdb and trying to find what really happens, this code have only verification purpose, so it doesn't determinate who the user is, it's just doing the socket part.
You can see that the user_name is passed to this function in "info->user_name" and it's already incorrect.

The real issue is in the 'getlogin()' function, that is called to get the user_name. However it's returning the user_name of logged user not the real user (in this case the user that is switched with 'su' command).

Michael found old jira issue that is linked to this bug (https://jira.mariadb.org/browse/CONC-441).

So next steps should be to properly document this known issue and discuss it with upstream.

Comment 25 Michal Schorm 2020-03-12 12:47:17 UTC
! WE'VE FOUND THE CORE OF THE ISSUE !

It is a bug in the client library.
https://jira.mariadb.org/browse/CONC-441

We are now considering how to proceed further with the issue.

Comment 26 Petr Pisar 2020-03-12 15:26:12 UTC
I recommend you reading getlogin(3) manual. There is explicitly stated that it should not be used for security-related purposes. There is also explained what the function returns (name of a user owning the controlling TTY), but that in reality returns data from utmp database whose up-to-date status is questionable. (E.g. because su(1) does not update it.)

I have no idea why there is a need for a user name. The server should simply compare UID obtained from the socket with EUID of the server and grant the access if they are equal. Otherwise you can get into very peculiar dilemmas if there are more user accounts differing in user names by having the same UID.

Comment 27 Fedora Update System 2020-03-16 16:25:41 UTC
FEDORA-MODULAR-2020-59c880a4e5 has been submitted as an update to Fedora 30 Modular. https://bodhi.fedoraproject.org/updates/FEDORA-MODULAR-2020-59c880a4e5

Comment 28 Fedora Update System 2020-03-16 16:25:51 UTC
FEDORA-MODULAR-2020-7ab010d963 has been submitted as an update to Fedora 32 Modular. https://bodhi.fedoraproject.org/updates/FEDORA-MODULAR-2020-7ab010d963

Comment 29 Fedora Update System 2020-03-17 02:48:59 UTC
mariadb-10.4.12-3.fc32, mariadb-connector-c-3.1.7-2.20200316gitfbf1db6.fc32 has been pushed to the Fedora 32 testing repository. If problems still persist, please make note of it in this bug report.
See https://fedoraproject.org/wiki/QA:Updates_Testing for
instructions on how to install test updates.
You can provide feedback for this update here: https://bodhi.fedoraproject.org/updates/FEDORA-2020-9401f4958c

Comment 30 Fedora Update System 2020-03-17 02:51:41 UTC
mariadb-10.4-3220200316141821.43bbeeef has been pushed to the Fedora 32 Modular testing repository. If problems still persist, please make note of it in this bug report.
See https://fedoraproject.org/wiki/QA:Updates_Testing for
instructions on how to install test updates.
You can provide feedback for this update here: https://bodhi.fedoraproject.org/updates/FEDORA-MODULAR-2020-7ab010d963

Comment 31 Fedora Update System 2020-03-17 03:10:11 UTC
mariadb-10.4-3020200316141821.a5b0195c has been pushed to the Fedora 30 Modular testing repository. If problems still persist, please make note of it in this bug report.
See https://fedoraproject.org/wiki/QA:Updates_Testing for
instructions on how to install test updates.
You can provide feedback for this update here: https://bodhi.fedoraproject.org/updates/FEDORA-MODULAR-2020-59c880a4e5

Comment 32 Fedora Update System 2020-03-17 03:20:13 UTC
mariadb-10.4-3120200316141821.f636be4b has been pushed to the Fedora 31 Modular testing repository. If problems still persist, please make note of it in this bug report.
See https://fedoraproject.org/wiki/QA:Updates_Testing for
instructions on how to install test updates.
You can provide feedback for this update here: https://bodhi.fedoraproject.org/updates/FEDORA-MODULAR-2020-eca72b3db0

Comment 33 Fedora Update System 2020-03-18 02:50:46 UTC
mariadb-10.4.12-3.fc32, mariadb-connector-c-3.1.7-2.20200316gitfbf1db6.fc32 has been pushed to the Fedora 32 stable repository. If problems still persist, please make note of it in this bug report.

Comment 34 Fedora Update System 2020-03-18 02:54:04 UTC
mariadb-10.4-3220200316141821.43bbeeef has been pushed to the Fedora 32 Modular stable repository. If problems still persist, please make note of it in this bug report.

Comment 35 Fedora Update System 2020-03-18 03:46:43 UTC
mariadb-10.4-3020200316141821.a5b0195c has been pushed to the Fedora 30 Modular stable repository. If problems still persist, please make note of it in this bug report.

Comment 36 Fedora Update System 2020-03-18 04:12:08 UTC
mariadb-10.4-3120200316141821.f636be4b has been pushed to the Fedora 31 Modular stable repository. If problems still persist, please make note of it in this bug report.


Note You need to log in before you can comment on or make changes to this bug.