old password algorithm ignored during backup/restore

D

Driesp

Verified User
Joined
Mar 12, 2007
Messages
168
Location
Belgium
Hi

I am running CentOS 8 with MariaDB 10.3.

During a restore I noticed directadmin not backing up (or maybe restoring) the mysql passwords for some users.
These users seem to be using an old encryption algorithm like SHA1.
I suspect new encryption algorithm passwords are stored in the Password field, and old ones are using a mysql_native_password plugin stored in another field of the table mysql.user.

For these users the 'Password' field in the mysql table 'user' is empty.
the 'plugin' field is 'mysql_native_password' with an authentication_string provided.

Maybe directadmin is not aware and ignores this during a backup or restore process.

Kind regards
Dries


edit:
For mariadb passwords in the old format
the field '&passwd' is indeed empty in the file 'database_name.conf' in a directadmin backup file.
 
Last edited:
This problem is still present in the latest directadmin version.

When mariadb users are using the old mysql encryption algorithm in mariadb (10.3), the password is not backed up by directadmin.
The &passwd field is empty in the configuration file 'backup/username_db.conf'

Some old users are using an old encryption algorithm in mariadb.
The 'Password' field in the users table is then empty,
but the 'authentication_string' field is being used.
 
Update:

I noticed that I can move over the contents from the field 'authentication_string' to the 'Password' field while deleting the 'plugin' field.
I don't know what I should do.

Maybe I could upgrade to MariaDB 10.4, it looks like mariadb handles something differently in this version.
What should I do?
 
Last edited:
Thank you for this, but this is not the issue I am having.

After further investigating, I suspect that before 2020 the field 'authentication_string' was used and after 2020 the field 'Password' was (and is) used to store password changes or creations made by directadmin.
MariaDB doesn't have an issue with it, these users all can authenticate, but directadmin doesn't backup the correct field where the password is stored.

Maybe upgrading to MariaDB 10.4 fixes my problem, but this might still be problematic for directadmin servers running MariaDB 10.3.
 
Hi!

https://docs.directadmin.com/other-...very.html#how-to-restore-sql-with-custombuild

The DirectAdmin backup/restores are database type/version independent. However, these backups are not always universally interchangeable between databases. The User databases typically are fine, but the mysql tables in mysql.sql vary per version, so worst case you might not be able to (easily) restore the mysql.sql file into your database.

MySQL 5.7 uses a different password column name (replaced "password" with "authentication_string") so restoring the mysql.user table wouldn't work, just as one example.

When possible, use the same version of MySQL or MariaDB used to create these sql files.
 
Hi
Thank you for your response.

Actually, I think this is a bug in DirectAdmin.
For some older users the field authentication_string is used, but this field is ignored when a backup is made by DirectAdmin.
I could confirm this hypothesis by opening the .tar.gz backup file, and the &passwd field is indeed empty in the .conf file.
When a user restores their backup, the application is broken, the user/application is not able to login on the database anymore.

Kr
Dries
 
I would like to try and reproduce this to see if I also can confirm the bug for you.
I'm running Centos 8 with MariaDB 10.4. Would that also have the issue?
If yes, did you create an admin, reseller or user backup?
And where do I look for the &passwd field? In the database sql backup itself?
 
No, I don't think MariaDB 10.4 has the issue.

I am running MariaDB 10.3 on CentOS 8.
Some time ago, in my case, and I don't know why this was the case, passwords were stored in the field authentication_string only (the Password field is empty in the mysql table).
DirectAdmin only backups the empty value in the Password field and ignores the authentication_string field.
Then open the backup tar.gz file and you will see the &passwd field in the file username_databasename.conf is empty.

Kr
Dries
 
Might be slight differences, I'm not sure, at least the naming is different.
Seems MariaDB 10.4 does indeed not have this issue, the &passwd field has a password in it.

I found this:
Code: 
Y&insert_priv=Y&lock_tables_priv=Y&passwd=*5158936xxxxxetc.
so I presume that is the passwd you're referring to.

There was another one above this one:
Code: 
priv=Y&lock_tables_priv=Y&passwd=*9F9476xxxxxetc.
 
MariaDB 10.4 works with a user 'view' (table), so not a real table, they changed this.
Maybe the user view as built in logic that prevents the Password field to be empty.

Maybe the easiest way for me is to upgrade to 10.4 :)
 
Driesp said:
Maybe the easiest way for me is to upgrade to 10.4 :)
Click to expand...
At first I wanted to suggest that, but I thought maybe you had reasons to stay with 10.3 and upgrading to 10.4 wouldn't solve the 10.3 bug/issue.

However, there is now this thread, so hopefully it get fixed or otherwise others who read this know they can better upgrade maybe.
 
You must log in or register to reply here.
Back
Top