Server: [17.0.2] "occ maintenance:install" uses "oc_<admin>" before creating it

[MichaIng]

Steps to reproduce

1. Install MariaDB database and any working webserver
2. Download and extract https://download.nextcloud.com/server/releases/nextcloud-17.0.2.tar.bz2 into webserver dirs
3. Run

sudo -u www-data php /path/to/nextcloud/occ maintenance:install --no-interaction --database 'mysql' --database-name 'nextcloud' --database-user 'root' --database-pass 'password' --admin-user 'admin' --admin-pass 'password' --data-dir '/path/to/nextcloud_data'

1. Watch
   > Error while trying to create admin user: Failed to connect to the database: An exception occurred in driver: SQLSTATE[HY000] [1698] Access denied for user 'oc_admin'@'localhost'

Expected behaviour

oc_admin user should be created before using it for any further actions, install should succeed. This is the case when using previous Nextcloud v17.0.1 on exactly same system + install steps: https://download.nextcloud.com/server/releases/nextcloud-17.0.1.tar.bz2
Hence I'm pretty sure that it related to a change/regression from v17.0.2.

Actual behaviour

oc_admin does not even exist when checking mysql.user database table, hence the above error message.
Reference: https://github.com/MichaIng/DietPi/issues/3275

Server configuration

Operating system: Debian Buster

Web server: not relevant

Database: MariaDB 10.3.18

PHP version: 7.3.11

Nextcloud version: 17.0.2

Updated from an older Nextcloud/ownCloud or fresh install: fresh install

Where did you install Nextcloud from: https://download.nextcloud.com/server/releases/nextcloud-17.0.2.tar.bz2

Nextcloud configuration:

Config report

root@VM-Buster:~# cat /var/www/nextcloud/config/config.php
<?php
$CONFIG = array (
  'passwordsalt' => 'zGc5ZyTHqxRUX9mhprT69nDuf0hMZ7',
  'secret' => 'rjzCM7W5LZVLfXq7uSS+O/PDGsx9r8YSk8AOyLBzJmU2qLr0',
  'trusted_domains' =>
  array (
    0 => 'localhost',
  ),
  'datadirectory' => '/path/to/nextcloud_data',
  'dbtype' => 'mysql',
  'version' => '17.0.2.1',
  'overwrite.cli.url' => 'http://localhost',
  'dbname' => 'nextcloud',
  'dbhost' => 'localhost',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'mysql.utf8mb4' => true,
  'dbuser' => 'oc_admin',
  'dbpassword' => 'XlN7PYIyqCKIQZCkThrerBFRwHRUFi',
);

• As can be seen, the config.php is created with user + password, but the user does not exist in database.
  

MariaDB log:

2019-12-21 16:26:04 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.3.18-MariaDB-0+deb10u1'  socket: '/run/mysqld/mysqld.sock'  port: 3306  Debian 10
2019-12-21 16:26:05 39 [Warning] Access denied for user 'oc_admin'@'localhost'
2019-12-21 16:26:05 40 [Warning] Access denied for user 'oc_admin'@'localhost'
root@VM-Buster:~# mysql -e 'select user,host from mysql.user'
+----------+-----------+
| user     | host      |
+----------+-----------+
| allo_db  | localhost |
| ompd     | localhost |
| root     | localhost |
| tmp_root | localhost |
+----------+-----------+

But the database itself has been created already:

root@VM-Buster:~# ls -l /path/to/mysql/nextcloud/
total 4.0K
-rw-rw---- 1 mysql mysql 60 Dec 21 16:26 db.opt

[MichaIng]

Found it, reverting this @nickvergessen fixed the issue: https://github.com/nextcloud/server/commit/f3ad49b866ae6e616559cb2d49c3632078735a5b

root@VM-Buster:~# sudo -u www-data php /path/to/nextcloud/occ maintenance:install -vvv --no-interaction --database 'mysql' --database-name 'nextcloud' --database-user 'root' --database-pass 'password' --admin-user 'admin' --admin-pass 'password' --data-dir '/path/to/nextcloud_data'
Nextcloud was successfully installed

I guess the authentication plugin must be chosen depending on MySQL/MariaDB version, since it is not available on current MariaDB at least.

[kesselb]

I still think we should remove all these create database / database user logic.

Two questions regarding the issue:

1) Do you see "Database user creation failed." in nextcloud.log or anywhere else?

2) What happens if you run CREATE USER 'oc_admin'@'%' IDENTIFIED WITH mysql_native_password BY 'password' (e.g. with mysql cli or phpmyadmin)? executeUpdate($query); should return the number of affected rows. Code does not check the return value. We should probably.

[MichaIng]

@kesselb
Ah yeah sorry, did not think about nextcloud.log as no user has been created yet, hence no web UI access, but the data dir has been created + log file, which shows that it is indeed a syntax error with current MariaDB:

{"reqId":"xrH9chkw6JHDA0HXMXrZ","level":3,"time":"2019-12-21T15:30:23+00:00","remoteAddr":"","user":"--","app":"mysql.setup","method":"","url":"--","message":{"Exception":"Doctrine\\DBAL\\Exception\\SyntaxErrorException","Message":"An exception occurred while executing 'CREATE USER 'oc_admin'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yYQ01guPXB5VrLFmG1iqfgdVFAW6Ex'':\n\nSQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;

I played around a bid in mariadb console:

MariaDB [(none)]> CREATE USER 'test'@'localhost' IDENTIFIED WITH 'unix_socket';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> CREATE USER 'test2'@'localhost' IDENTIFIED WITH 'mysql_native_password';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> select user,password,host,plugin from mysql.user;
+----------+-------------------------------------------+-----------+-------------+
| user     | password                                  | host      | plugin      |
+----------+-------------------------------------------+-----------+-------------+
| root     |                                           | localhost | unix_socket |
...
| test     |                                           | localhost | unix_socket |
| test2    |                                           | localhost |             |
+----------+-------------------------------------------+-----------+-------------+
8 rows in set (0.000 sec)
MariaDB [(none)]> CREATE USER 'test3'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'letssee';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BY 'letssee'' at line 1
MariaDB [(none)]> CREATE USER 'test3'@'localhost' IDENTIFIED BY 'letssee' WITH 'mysql_native_password';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''mysql_native_password'' at line 1

• So "with '<plugin>'" as general syntax works, e.g. the MariaDB unix_socket plugin which allows login without password from matching unix user.
• Even mysql_native_password seems to work somehow, although no plugin seems to be applied, with of course is what we want in MariaDB for usual password authentication. However the combination to apply password within same query via by '<password>' does not work.

=> To fix things with MariaDB while keeping MySQL 8.0.4+ support it should be possible to create the user with authentication plugin first and apply the password with a second query.

[kesselb]

Probably related: https://bugs.php.net/bug.php?id=76243

[MichaIng]

@kesselb
This was the reason for Nextcloud to add this authentication plugin flag to their install command query, to support newest MySQL. The issue is that MariaDB does not allow to set a plugin AND a password in the same query.

[kayee72]

I had this issue when I just upated my Nextcloud Docker image from 17.0.1 to 17.0.2.

I did a new install (as it is a development instance on my NAS) and it fell over under install. Nextcloud 17.0.2 does not create the user oc_admin so Reverting back to 17.0.1 solves this.

Then once Nextcloud is setup with the right bind to the volume to have persistent data, this 17.0.1 instance can be used to create the database (and the oc_admin user) and then the image can be simply via whatever Docker method.

I used Portainer to simply update the image, but nextcloud and the mariadb databse was already existing and 17.0.2 can then be used.

So, fresh installs you must use 17.0.1 and then update the imge. If you do a fresh install with 17.0.2 this causes the problem.

[nickvergessen]

So I checked the docs and actually it seems MariaDB is using IDENTIFIED VIA instead of IDENTIFIED WITH in https://github.com/nextcloud/server/blob/5bf3d1bb384da56adbf205752be8f840aac3b0c5/lib/private/Setup/MySQL.php#L105

Can someone confirm this?

[MichaIng]

VIA plugin USING password:

...hostname IDENTIFIED VIA mysql_native_password USING '*54958E764CE10E50764C2EECBB71D01F08549980';

I'll test this later.

[gcommit]

Is there a way i could patch this on my local instance? I can't install 17.0.2 because of this

[nickvergessen]

Is there a way i could patch this on my local instance?

Yes just adjust lib/private/Setup/MySQL.php line 105 and 107 manually

[gcommit]

Is there a way i could patch this on my local instance?

Yes just adjust lib/private/Setup/MySQL.php line 105 and 107 manually

Which one? The one of michaelng is already implemented, but its still resulting in the same error. An „oc_“ is added to my given (already existing) db user

[MichaIng]

Actually WITH and VIA works both, it is the BY that is not supported in this combination when declaring the authentication module, but USING works in both cases:

MariaDB [(none)]> CREATE USER 'test'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BY 'password'' at line 1
MariaDB [(none)]> CREATE USER 'test'@'localhost' IDENTIFIED VIA mysql_native_password BY 'password';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BY 'password'' at line 1
MariaDB [(none)]> CREATE USER 'test'@'localhost' IDENTIFIED WITH mysql_native_password USING 'password';
ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number
MariaDB [(none)]> CREATE USER 'test'@'localhost' IDENTIFIED VIA mysql_native_password USING 'password';
ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number
MariaDB [(none)]> select password('test');
+-------------------------------------------+
| password('test')                          |
+-------------------------------------------+
| *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+-------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> CREATE USER 'test'@'localhost' IDENTIFIED WITH mysql_native_password USING '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
Query OK, 0 rows affected (0.003 sec)

MariaDB [(none)]> CREATE USER 'test2'@'localhost' IDENTIFIED via mysql_native_password USING '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
Query OK, 0 rows affected (0.001 sec)

• Docs verify this, WITH/VIA: https://mariadb.com/kb/en/create-user/#identified-viawith-authentication_plugin
  See the boxes for v10.4, when MariaDB supports to add plain text password with: USING PASSWORD('secret');
  With v10.3.18 that does not work:

MariaDB [(none)]> CREATE USER 'test4'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('test');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PASSWORD('test')' at line 1

So, if MySQL 8 only supports WITH plugin BY 'password_plain' (yes: https://dev.mysql.com/doc/refman/8.0/en/create-user.html), then it is and will be incompatible with MariaDB, since MariaDB currently only supports WITH/VIA plugin USING 'password_hash' and will support WITH/VIA plugin USING PASSWORD('password_hash'). +1 for MySQL in this case since it is annoying to have two different commands for the passwords, depending on whether a plugin is defined or not...

Hence from what I see, when the authentication plugin must be set for MySQL, this and the password need to be assigned in two queries. SET PASSWORD cannot be used for a similar reason. MySQL (8) requires the plain text password there, while MariaDB expects the hash or PASSWORD('plain'):

• https://dev.mysql.com/doc/refman/8.0/en/set-password.html
• https://mariadb.com/kb/en/set-password/

MariaDB [(none)]> SET PASSWORD for test@localhost = PASSWORD('test');
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SET PASSWORD for test@localhost = 'test';
ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number
MariaDB [(none)]> SET PASSWORD for test@localhost = '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
Query OK, 0 rows affected (0.000 sec)

Only left option is ALTER USER, where luckily the syntax finally matches:

• https://dev.mysql.com/doc/refman/8.0/en/alter-user.html#alter-user-authentication
• https://mariadb.com/kb/en/alter-user/#identified-by-password
• => ALTER USER 'user' IDENTIFIED BY 'password_plain';

MariaDB [(none)]> ALTER USER test@localhost IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> ALTER USER test@localhost IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> ALTER USER test@localhost IDENTIFIED BY PASSWORD('test');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '('test')' at line 1

• MariaDB has very inconsistent syntax when its about the passwords, in every command/option it is handled differently...

[dpavlotzky]

The problem never was in the SQL syntax, but in the authentication method (see https://www.php.net/manual/en/mysqli.requirements.php), so trying to fix this by changing the SQL query won't work.

The SQL changed in this pull request is used to create the nextcloud user in the MySQL database. This works by connecting to the MySQL server using _another_ MySQL account (say root) with enough privileges to create other accounts.

The pull request specifically sets the authentication method to something PHP understands (mysql_native_password), but, by default accounts in MySQL 8 use the caching_sha2_password authentication method. So, out of the box, PHP, and therefore Nextcloud, cannot connect to MySQL to create the nextcloud user in the first place.

To make this work somebody already either

• changed the authentication method for the MySQL account used to create the nextcloud user to mysql_native_password
• or created the nextcloud user in advance
• or changed the default authentication method to mysql_native_password

IMHO the best course of action is to revert this, since the old query (CREATE USER '$name'@'localhost' IDENTIFIED BY '$password') works fine on

• MySQL 5.6
• MySQL 5.7
• MySQL 8.0
• MariaDB 10.1
• MariaDB 10.2
• MariaDB 10.3
• MariaDB 10.4

add documentation on MySQL 8.0 and how to change the authentication method and wait for PHP to add support for the caching_sha2_password authentication method.

[MichaIng]

@dpavlotzky
Not sure if I understood everything correctly, but:

• The reason for the initial change was the default authentication method change in MySQL 8 that you described.
• Since of course Nextcloud does not want to change any MySQL settings (default authentication method), it is set within the user creation query as you suggested.
• With this change the install command works fine with MySQL 8, which did not work before due to the incompatible default authentication method.
• But now indeed the syntax to set an authentication method AND a related password does not work with MariaDB. So indeed the fix for MySQL 8 now created a syntax problem with MariaDB, as can be derived from the docs.

The idea to revert the change and instead explain to admins via docs how to change the default authentication method, or, leave things broken until PHP supports the new one, is an alternative but not very nice for admins as long as a simple change or additional query in the install command can make it work without any manual changes.

[dpavlotzky]

Not sure if I understood everything correctly, but:

• The reason for the initial change was the default authentication method change in MySQL 8 that you described.

Yes

• Since of course Nextcloud does not want to change any MySQL settings (default authentication method), it is set within the user creation query as you suggested.

Yes

• With this change the install command works fine with MySQL 8, which did not work before due to the incompatible default authentication method.

No, out of the box Mysql 8 won't work: they changed the default authentication method to an authentication method PHP does not understand (yet). So in order to connect to the database an account needs to be created that uses mysql_native_password as authentication method.

So, the account you provide (say the root account) to create the Nextcloud admin account needs to have it authentication method set to mysql_native_password as well, so someone (the administrator) already has to take some action to make it work.

The following code will create a MySQL 8 container with an empty root password. Nextcloud will not be able to create the admin user account in the database because the root account has the default authentication method caching_sha2_password

docker run -e MYSQL_ALLOW_EMPTY_PASSWORD=true -d --name mysql --hostname mysql mysql:8

When we change the default authentication method to mysql_native_password Nextcloud will be able to create the admin account:

docker run -e MYSQL_ALLOW_EMPTY_PASSWORD=true -d --name mysql --hostname mysql mysql:8 --default-authentication-plugin=mysql_native_password

• But now indeed the syntax to set an authentication method AND a related password does not work with MariaDB. So indeed the fix for MySQL 8 now created a syntax problem with MariaDB, as can be derived from the docs.

Yes

The idea to revert the change and instead explain to admins via docs how to change the default authentication method, or, leave things broken until PHP supports the new one, is an alternative but not very nice for admins as long as a simple change or additional query in the install command can make it work without any manual changes.

I understand, but in order to use Nextcloud with MySQL 8 admins already _must_ either change the default authentication method or create an account with the mysql_native_password authentication method. And since that is the case, IMHO it is better to revert the change and explain to admins how they can make Nextcloud work with MySQL 8.

[MichaIng]

@dpavlotzky

No, out of the box Mysql 8 won't work: they changed the default authentication method to an authentication method PHP does not understand (yet). So in order to connect to the database an account needs to be created that uses mysql_native_password as authentication method.
...
but in order to use Nextcloud with MySQL 8 admins already must either change the default authentication method or create an account with the mysql_native_password authentication method.

But that exactly has been implemented/"fixed": https://github.com/nextcloud/server/commit/f3ad49b866ae6e616559cb2d49c3632078735a5b
And that exact implementation breaks MariaDB, because MariaDB does not support the same syntax to set the authentication method and the password in the same query.

So all you say is true and has been "fixed" with Nextcloud 17.0.2. So now (and as of this issue) the authentication method itself is not a topic anymore, but only the syntax used to set it 😉.

[dpavlotzky]

So all you say is true and has been "fixed" with Nextcloud 17.0.2. So now (and as of this issue) the authentication method itself is not a topic anymore, but only the syntax used to set it 😉.

No, this has not been fixed. What has been implemented is to explicitly set the authentication method for the Nextcloud database user to mysql_native_password. If the SQL syntax is changed to something compatible with MariaDB and MySQL, it still won’t work out of the box with MySQL 8.

And that’s my point. This change only works out of the box and is useful in a very specific scenario:

• The database administrator has set the authentication method for the database user (root or some other user with the necessary privileges) used to create the nextcloud database account to mysql_native_password but left the default authentication method caching_sha2_password.

If the database administrator changed the default authentication method, a system-wide setting, to mysql_native_password the change is not necessary because the old syntax

CREATE USER '$name'@'localhost' IDENTIFIED BY '$password'";

will work just fine on MySQL 8.

But changing the syntax probably makes the SQL compatible with more database, but it will not completely resolve the issue until PHP starts supporting caching_sha2_password and the documentation needs to be clear on what needs to be done in order to make Nextcloud work with MySQL 8.

And when PHP has implemented support for the caching_sha2_password this syntax is not what you want, because you want the database administrator to have control over the authentication method used for the Nextcloud database user.

[MichaIng]

@dpavlotzky
Ah, I got your point now. You don't mean the Nextcloud database user but the admin user, that is invoked to create Nextcloud database and user. Yeah that is another issue that is also present with MariaDB for a long time, since MariaDB by default ships a root user with unix_socket plugin authentication (at least on Debian/Raspbian), which means that only the UNIX root user can login, then without password, but no other UNIX user can.

For this we create a temporary database user with full admin privileges and password authentication, to be invoked by the occ install command, and remove it after install has been done.

Okay but then these are two different issues, although similar, but at a different stage:

• Nextcloud must not touch the authentication method for the database admin of course. It must be part of a manual setup step, to either change the admin authentication method or create a temporary user, like we do, to be invoked. When MySQL 8 now has a similar issue like MariaDB, since now both have an PHP-incompatible default authentication method, then all that can and should be done about that is adding possible solutions to docs.nextcloud.com.
• The reason I opened this issue was not about the admin user, but about the Nextcloud user creation only. And this is where Nextcloud can and should have a compatible method, that does not rely on changed MySQL/MariaDB defaults or settings.

Please lets keep this issue/topic about the Nextcloud database user creation only and in case we can create a new issue about how to setup the admin user in the docs repo, where it IMO belongs to: https://github.com/nextcloud/documentation/issues

[nickvergessen]

If the database administrator changed the default authentication method, a system-wide setting

That is not really an acceptable solution. It's on my todo to look into this a bit more and we should fix this in our code, so it works for everyone without having to fiddle around with the database config first.

[dpavlotzky]

To add another interesting twist to the mix: PHP 7.4 supports the caching_sha2_password authentication method (confirmed by testing it). Yay!

That is not really an acceptable solution. It's on my todo to look into this a bit more and we should fix this in our code, so it works for everyone without having to fiddle around with the database config first.

For the sake of clarity:

• I will be referring to the user account that is supplied to create the Nextcloud database user as __database admin account__
• I will be referring to the Nextcloud database user account as __nextcloud account__

I don't think you can fix this in code because PHP versions prior to PHP 7.4 simply don't support caching_sha2_password. So if the administrator installing Nextcloud supplies a __database_admin_account__ that uses the caching_sha2_password PHP version prior to 7.4 simply won't be able to connect to the database.

I think the error can be handled more gracefully though, and give a hint to the administrator that the authentication method of the __database admin account__ is not supported and refer to the (to be written) documentation to get this working. But as @Michalng pointed out the above is not the issue he opened this issue for.

I think it would be nice if the "old" query would be used when PHP 7.4 is detected so the __nextcloud account__ that is created will use the default authentication method set by the database administrator instead of forcing the use of msyql_native_password.

[MichaIng]

Great about PHP7.4. But that alone would not solve it for MariaDB which uses unix_socket plugin. I also doubt that there is any solution for the database admin user here since as last resort it requires full root permissions. Adding too many extra checks etc makes things probably too complicated and hence more vulnerable for other bugs/API changes.