SOLVED [CPANEL-25785] phpMyAdmin won't connect if mysql user password has special char

[vicos]

Just tried to launch phpmyadmin from cpanel. It didn't authenticate properly and instead threw up a login screen --- which can't be used because password authentication is disabled.

Problem: the mysql user password for the database contained a tilde (~)

As soon as I changed the password to include only alpha/numeric, the problem went away. I suspect that more than one special character will cause this issue.

 

[cPanelLauren]

Hi @vicos


I've been trying to recreate the initial issue you were experiencing. Here is what I did from my cPanel UI:

1. Created a DB
2. created User with a password that included a tilde dZ~{[email protected]*
3. Assigned that user to the DB and allowed all privileges
4. Attempted to log into PHPMyAdmin with no issues

The thread here details some of the information that makes it extremely helpful for us to provide an accurate response to you:
Guide To Opening An Effective Forums Thread

 

[vicos]

Ok, what immediately comes to mind is that this domain was just moved from a Cpanel 76.0.18 server. The domain itself has existed for many, many years and undergone many server migrations and versions of Cpanel/mySQL. So, it may be a legacy data issue.

It's not a problem for me as I resolved the issue by changing the password. Just trying to save someone some future trouble.

 

[cPanelLauren]

Hi @vicos

For sure! I just want to make sure there's not an ongoing problem with the software that isn't being addressed. If you're able to replicate this behavior with any other account please let us know!

 

[sparek-3]

Yea... I'm seeing this issue as well. Pulling my hair out with it too.

Unfortunately I am not able to duplicate it when I try to replicate the circumstances that lead to this issue. But like @vicos this is happening on accounts that were transferred over this past weekend. ... Or at least that's a common theme between my experience and @vicos's - at this point I really can't rule anything out.

I really don't have any helpful information at all in regards to this. Like @vicos the password in question contained two special characters. But I really don't think that's the issue, because the restore process doesn't rely on actually knowing the account's password (at least I assume?). Instead it uses GRANT information in the mysql.sql file in restore, at least that would be my assumption. I suspect that just resetting the account's password causes it to get back in sync. But I do not know what causes it to get out of sync.

So... right now, all I can offer is... me too.

 

[sparek-3]

Actually...

How does /scripts/restorepkg do MySQL passwords?

I was able to replicate this issue, and I enable MySQL query logging so I could see what was happening.

The restore process correctly grants access to the user with the correct password:

GRANT USAGE ON *.* TO 'theuser'@'localhost' IDENTIFIED BY PASSWORD '*86DF961E288706245C97EB616C49DE5E6DDCD5C1';

*86DF961E288706245C97EB616C49DE5E6DDCD5C1 is the correct password hash - this is stored in plain sight in the mysql.sql file in the cPanel backup package.

But then just a few queries later:

GRANT USAGE ON *.* TO <<a mysql sub user>>,'theuser'@'theserver' IDENTIFIED BY PASSWORD '*AEAF3F9FD323547325382B833194D44BF4CF0F83','<<a mysql sub user>>','theuser'@'theip' IDENTIFIED BY PASSWORD '*AEAF3F9FD323547325382B833194D44BF4CF0F83','<<a mysql sub user>>','theuser'@'theoldserver' IDENTIFIED BY PASSWORD '*AEAF3F9FD323547325382B833194D44BF4CF0F83','<<a mysql sub user>>','theuser'@'theoldip' IDENTIFIED BY PASSWORD '*AEAF3F9FD323547325382B833194D44BF4CF0F83'; /* _addhosts */

Where is this password hash coming from?

*AEAF3F9FD323547325382B833194D44BF4CF0F83 is ultimately the password hash that is left in the mysql user table. But where did that password come from? What is it? Where is this query coming from?

This is on cPanel 11.76.0.20.

 

[sparek-3]

*AEAF3F9FD323547325382B833194D44BF4CF0F83 appears to be the temporary password that is set for the user when restoring databases.

I assume...

In the transfer session log:

Granting “theuser” access to “theuser_database” with temporary password

Is actually this query:

GRANT USAGE ON *.* TO 'theuser'@'theip' IDENTIFIED BY 'thepassword' , 'theuser'@'theserver' IDENTIFIED BY 'thepassword' , 'theuser'@'localhost' IDENTIFIED BY 'thepassword';
GRANT ALL ON `theuser\_database`.* TO 'theuser'@'theip' IDENTIFIED BY 'thepassword' , 'theuser'@'theserver' IDENTIFIED BY 'thepassword' , 'theuser'@'localhost' IDENTIFIED BY 'thepassword' /* _dbowner_to_all_without_ownership_checks */

And for whatever reason

Restoring MySQL grants

restores the password back to the CORRECT password hash. But then

Restoring MySQL access hosts

restores the password back to this temporary password.

/scripts/restorepkg is compiled code, so I can read it. But it would seem to me that it needs some cleaning up in how it's setting temporary passwords and then going back and setting original passwords.

 

[sparek-3]

OK, I think I've finally been able to come up with steps to duplicate this issue. I don't know if these steps will specifically lead to the root cause, but it at least shows the problem in a consistent manner.

Create an account:

# /usr/local/cpanel/bin/whmapi1 createacct username=%user% domain=%domain% password=%password%

Now it's important in this step to include a password, it can be a simple password with no non-alphanumeric characters, just numbers and letters. In my tests, if you don't include a password and let the system create a password automatically, then this doesn't work. I don't know why - this is puzzle #1.

Now create a database for that user and populate it with some test information

# /usr/local/cpanel/bin/uapi --user=%user% Mysql create_database name=%user%_one
# mysql %user%_one -e "create table one (one int(10), two int(10))"
# mysql %user%_one -e "insert into one (one, two) values (1,2), (3,4)"

This is puzzle #2 - the account has to have at least one database WITH information in it.

Now, note the password hash for %user%'s mysql user:

# mysql mysql -e "select password from user where user=\"%user%\""
+-------------------------------------------+
| password |
+-------------------------------------------+
| *DFA2021762E74350DFBA9AE8E8BC5372F36AF126 |
| *DFA2021762E74350DFBA9AE8E8BC5372F36AF126 |
| *DFA2021762E74350DFBA9AE8E8BC5372F36AF126 |
+-------------------------------------------+

Back up this user:

# /scripts/pkgacct %user% /backup backup --nocompress

Delete this user:

# /scripts/removeacct --force %user%

Restore this user:

# /scripts/restorepkg /backup/%user%.tar

Now look at the user's mysql user password hash:

# mysql mysql -e "select password from user where user=\"%user%\""
+-------------------------------------------+
| password |
+-------------------------------------------+
| *2096C6FEC9ECE4CAA311F45103A0A4A101347C94 |
| *2096C6FEC9ECE4CAA311F45103A0A4A101347C94 |
| *2096C6FEC9ECE4CAA311F45103A0A4A101347C94 |
+-------------------------------------------+

This should be the same as it was above, before you deleted it and restored it - but it's not.

Because this only happens when the %user% has databases with information in them, I have to believe that this is something to do with restoring those databases during the restore process - the restore process creates a random password for the user to run the restore with that user.

But what is REALLY strange, if you don't set a password when you create an account (and thereby let the system create create a random password) then all of this works as it should. The database gets restored and the user's MySQL hash gets restored correctly.

Hoping someone from cPanel can duplicate this and give a bit more information.

 

[cPanelLauren]

We do have a case that is somewhat similar to this CPANEL-25656 - "cpapi2 MysqlFE listdbs" changes the cPanel user's MySQL password string if REMOTE_PASSWORD environment variable is set

Though you're not using cpapi2 at any point during this which makes me wonder if its related.

I did try and recreate this below is the hash before and after restore - though my test server is using MariaDB not MySQL

MariaDB [mysql]> select password from user where user='passtest_one';
+-------------------------------------------+
| password                                  |
+-------------------------------------------+
| *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
| *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
| *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
+-------------------------------------------+
3 rows in set (0.00 sec)

MariaDB [mysql]> select password from user where user="passtest_one";
+-------------------------------------------+
| password                                  |
+-------------------------------------------+
| *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
| *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
| *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
+-------------------------------------------+

What MySQL version are you running?

 

[sparek-3]

This is with MariaDB 10.1

Make sure you are checking the system user's hash

select password from user where user='passtest';

not

select password from user where user='passtest_one';

As far as I can tell, SQL sub users (users you create in the user's cPanel.. users with an underscore in them) are not affected by this, their hashes remain the same. It's related to the system user - the cPanel username.

I'm still running all kinds of tests to make sure this is able to be duplicated. I'm in the process of upgrading a server to cPanel 78 to see if that might fix it.

The environment variable angle is interesting, and in some ways that might make some sense... still probably a bug that needs to be cleaned up. But as you say, I'm not really sure if that's the same issue I am seeing.

 

[sparek-3]

There definitely seems to be some credence to the environment variable issue - although, I'm not specifically seeing any environment variables like you mentioned.

But if you change

/scripts/pkgacct %user% /backup backup --nocompress

to

(/scripts/pkgacct %user% /backup backup --nocompress)

and

/scripts/restorepkg /backup/%user%.tar

to

(/scripts/restorepkg /backup/%user%.tar)

essentially executing these commands in their own sub shell. That does seem to resolve the issue. But you have to do it for both commands. I don't know why. And printenv isn't showing anything to suggest any rogue environment issues. Are these commands retrieving stored variables from some where else?

 

[cPanelLauren]

Make sure you are checking the system user's hash

select password from user where user='passtest';

not

select password from user where user='passtest_one';

I think I lost sight of what I was looking for there for a second but I'm still unable to recreate the issue

Database changed
MariaDB [mysql]> select password from user where user="passtest";
+-------------------------------------------+
| password                                  |
+-------------------------------------------+
| *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
| *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
| *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
+-------------------------------------------+
3 rows in set (0.00 sec)

Database changed
MariaDB [mysql]> select password from user where user="passtest";
+-------------------------------------------+
| password                                  |
+-------------------------------------------+
| *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
| *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
| *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
+-------------------------------------------+
3 rows in set (0.00 sec)

Are these commands retrieving stored variables from some where else?

Good point and quite possible it's reading something cached

Could you possibly open a ticket so that we can look into this further I believe there's a good case here if we can replicate it successfully. Once it's open please update this thread with the ticket ID so we can provide the outcome here.


Thanks!

 

[sparek-3]

Well, I don't really have a test server at my disposal right now. I'm testing all of this on a production server so I'm having to be delicate but being that it's me doing the testing I can afford myself that nimbleness.

I was really hoping you'd be able to duplicate this on your server. Are you following the procedure exactly as I layed out? I tried this on a few different servers and it was always replicated.

Are you doing all of the steps within the same shell session?

 

[sparek-3]

Your Support Request ID is: 11491161

 

[cPanelLauren]

I was really hoping you'd be able to duplicate this on your server. Are you following the procedure exactly as I layed out? I tried this on a few different servers and it was always replicated.

I was too, and yes I used your instructions to replicate.

Are you doing all of the steps within the same shell session?

All done in within the same shell session

Your Support Request ID is: 11491161

Thanks for that, I'm going to add some notes there and hopefully we can get to the bottom of it. I'll update here when there's more info.

 

[sparek-3]

With a bit more testing... it looks like the first time you follow these steps everything works correctly.

But deleting the account and performing the steps again shows the issue.

It almost has to be a variable some where that is not being cleared properly.

 

[cPanelLauren]

Hi @sparek-3


The analyst handling the ticket was able to replicate the issue and knowing now that the first time it's done it works as intended I tried it again and I was also able to replicate:

[[email protected] etc]# mysql mysql -e "select password from user where user=\"passtest\""
+-------------------------------------------+
| password                                  |
+-------------------------------------------+
| *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
| *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
| *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
+-------------------------------------------+
[[email protected] etc]#

[[email protected] etc]# mysql mysql -e "select password from user where user=\"passtest\""
+-------------------------------------------+
| password                                  |
+-------------------------------------------+
| *3AC350368F1F3E1B2A642AB5802777EE0637797F |
| *3AC350368F1F3E1B2A642AB5802777EE0637797F |
| *3AC350368F1F3E1B2A642AB5802777EE0637797F |
+-------------------------------------------+

He opened a case for this CPANEL-25785 and used your replication steps/notes in the case. I'll update you as to the progress of the case as it becomes available. Thank you for opening the ticket!

 

[cPanelLauren]

Hi Guys,

I just wanted to let everyone know that this issue is marked as resolved in v78.0.13 of cPanel/WHM per the change logs here: 78 Change Log - Change Logs - cPanel Documentation

Please let us know if you continue to experience any further issues with this.


Thanks!