Amazon Aurora - cannot set password

I've been experimenting with using Amazon Aurora as a remote MySQL database for Virtualmin. There seems to be an incompatibility, based around the way Virtualmin creates and updates MySQL users.

If I set the Aurora details in Webmin > Servers > MySQL Database Server > Config, Virtualmin correctly tries to create the database and users when I create a new Virtual Server.

However, upon creation, and at modification time, I get an error "set password for 'example'@'hostname.com'=password('greatpassword') failed: Can't find any matching row in the user table"

The server is created, and the MySQL user is added to the database on Aurora, but no password is set. Attempts to modify the server result in the same error, as do the same efforts from the virtualmin CLI tool ("virtualmin modify-database-pass --domain mydomain.com --type mysql --pass "greatpassword"")

Trying to run the same SQL ("set password for....") directly on the Aurora DB also fails with the same error, even though the user definitely exists ("select * from user where User='example' and host='hostname.com'" returns the result).

A simple fix is to change the way Virtualmin sets and updates the password in /usr/share/webmin/virtual-server/feature-mysql.pl

diff --git a/feature-mysql.pl b/feature-mysql.pl
index 33c52dd..cf64c82 100755
--- a/feature-mysql.pl
+++ b/feature-mysql.pl
@@ -2637,7 +2637,7 @@ if (&compare_versions($remote_mysql_version, "5.7.6") >= 0) {
        return ("insert into user (host, user, ssl_type, ssl_cipher, x509_issuer, x509_subject, plugin, authentication_string) values ('$host', '$user', '', '', '', '', 'mysql_native_password', $encpass)");
        }
elsif (&compare_versions($remote_mysql_version, 5) >= 0) {
-       return ("insert into user (host, user, ssl_type, ssl_cipher, x509_issuer, x509_subject) values ('$host', '$user', '', '', '', '')", "flush privileges", "set password for '$user'\@'$host' = $encpass");
+       return ("insert into user (host, user, ssl_type, ssl_cipher, x509_issuer, x509_subject) values ('$host', '$user', '', '', '', '')", "flush privileges", "update user set password=$encpass where User='$user' and Host='$host'");
        }
else {
        return ("insert into user (host, user, password) values ('$host', '$user', $encpass)");
@@ -2665,7 +2665,7 @@ foreach my $host (&unique(map { $_->[0] } @{$d->{'data'}})) {
                $flush++;
                }
        else {
-               $sql = "set password for '$user'\@'$host' = $encpass";
+           $sql = "update user set password=$encpass where User='$user' and Host='$host'";
                }
        &mysql::execute_sql_logged($mysql::master_db, $sql);
        }
Status: 
Closed (fixed)

Comments

We've seen this before on MySQL servers that have an option enabled to resolve hostnames to IPs. Does it work if you manually run the set password command, but with your IP instead of hostname?

Unfortunately your patch isn't generally applicable, as only the set password command reliably does password hashing correctly.

THanks for the info. I knew it couldn't be this easy :-D

Yes, it does work if I manually run the set password command with an IP instead of a hostname. Is there a way to configure Virtualmin to use my external IP address when sending the set password command to MySQL.

What I'll do in the next (6.00) Virtualmin release is try set password with both the hostname AND IP address. That will cover both possible cases.

Status: Active » Fixed

Great. Remember that in this case the IP that I want to set is not the same as the IP of my machine. My machine and the virtual servers are on a local network (192.168.xxx.xxx) behind a firewall. Aurora (presumably) will see the external IP (82.5.xxx.xxx).

When Aurora resolves your hostname, will it get the same IP as if you resolved that hostname locally?

I don't think so, no. I'm on a dynamic IP, and anyway my local hostname is set to something like 'versantus.dev", which Aurora wouldn't be able to resolve.

If I can specify an IP in virtualmin to use for the "set password" command, that would work. Otherwise, is there a way around this to use the "update user.. " form of the password command? Can you tell me more about why that doesn't work?

What I'd recommend is making sure that your system's hostname resolves to the external IP, such as via an entry in /etc/hosts . Otherwise there's no way for Virtualmin to know what your external IP is.

I've personally spend the last 48 hours working on this exact issue. My investigation involved more that 20 horus (8 of that spent with a certified Amazon solutions architect that also escalated the issue to Amazon Enterprise.)

Here's the long and short of it. When using an external DB in webmin, for every virtual server webmin is attempting to create 3 user entries in mysql user

user@localhost (or user@whatever-custom-setting-is-in-server-template user@hostname user@IP

When using RDS Aurora - it fails on the set password as mentioned in the initial topic of this post. This is a explicit limitation that applies only to RDS Aurora databases (if you're using Amazon RDS MySQL or Maria DB - everything works as planned).

In fact, amazon Aurora DB doesn't even contain a mysql.host table at all. You can use set password for 'user'@'ipaddress' and that is fine

So it occurred to me - a very simple solution to this problem that will maintain compatibility with other systems not using Aurora is to NOT create the user@hostname at all based on the server template > mysql database settings.

As you know, if you're using a localhost database, webmin only inserts one user@localhost record. As an option, on the server template, you can set the "allowed mysql client hosts" - one of the options here is to specify % (i.e. Any). That is how Aurora itself inserts mysql users for Aurora. Well, if you change the server template from "none" to where you're specifying the hostnames, simple stop automatically inserting the extra user@hostname and user@ipaddress. i.e. the user setting up webmin already went through the trouble of going in the template and explicitly stating what client host they want allowed - so why over-ride their choice of % (or whatever else they set) and also insert the auto-generated user@hostname and user@ipaddress. Heck, if the user choose to set it to Any - then the other 2 entries for hostname and ip address are totally redundant and useless for extra security - it's already wide open at that point (and security is handled by the aurora security policy).

This approach would keep complete compatibility with all other setups where the user left the "none" option in place. And if the user knows enough to explicitly customize the template to choose the hostnames they want specified - then why ignore their choice and continue creating not only the custom hostnames the user set in the template, but also your automatic user@hostname and user@ipaddress entries for that same user? Doesn't it totally defeat the purpose of that setting when you're in affect ignoring it, and writing in whatever you want after the user defined entries anyway?

And....as a second option...if you really don't like the first choice..just add a new option to the server template where a user can explicitly say "don't create user@hostname entry" and "don't create user@ipaddress" on remote mysql systems.

Both options are simple, keeps the security and integrity intact for other systems/server setups - while honoring the users choice in the config and allowing full compatibility with Aurora.

That's a good suggestion. Alternately, if the admin has configured % as a hostname to allow, Virtualmin could just skip adding localhost or 127.0.0.1 or anything else, as they are redundant.

Yes - that would work too. I manged to get another huge chunk of hours on this today just looking for any other possible solution. And any way you slice it, if you use insert 'user'@'host' with Aurora (or really any AWS virtual instance) you run into a problem eventually. Create user is the only way I could find that consistently allows Aurora to accept a user@host and keep from hitting errors with set password. Even if it's not the password issue directly - you run into other problems later when restoring snapshots, etc. Just the mere fact of having mysql users added to anything amazon that are tied to a specific hostname or private IP ends up causing issues further down the chain at some point as it takes away the "virtual" nature of the AWS environment.

In the end, the best possible solution is if % is set at the "allowed mysql client hosts", just don't insert any other user entries at all. Because if if you did some magic to detect it was aurora and used the create instead of insert - you'd still end up inserting a hostname and IP user entry - and that will change the next time the EC2 instance is re-created and leave you with invalid user entries in the table anyway (hence the reason I set the desired allowed mysql client hosts in the virtual server template to % to begin with and relied on AWS security policies to secure Aurora.

I can see your point of not wanting to trust the end user to know any better when they set that host, and your trying to ensure they don't lock themselves out of their own database.

That being said, your suggestion of looking for % and then don't set anything, otherwise do the normal magic, would still solve the issue in all use-cases I can think of.

Any idea of how long a patch like suggested might take to see release?

P.S. This one has been an extremely painful point for me as I'm currently trying to migrate from physical servers to AWS EC2 and RDS Aurora. And getting this working right just makes my life so much easier. As part of this migration, I'm setting up a mirror image sandbox environment. One of the tricks we do just to make things faster when developing on our end, is we have our production system running virutualmin backups and then pushing those backups off to s3 buckets. Then, whenever we need to refresh our sandbox with data from our production environment, we just restore the virtualmin backup of that particular virtual server into the sandbox. And with this user-creation issue we can't directly enable mysql for any specific virtual server, and must manually add the users/databases. So our virtual server backups don't have the users/databases directly associated with the specific virtual server. It's not like we can't manually work around it, but life is just so much faster and easier when we can grab a virtual server backup out of our S3 bucket, and restore it straight into our sandbox server and have everything set to go.

If we have to migrate before this is patched - I'll have to figure out at a later point how to manually associate the created users/database with the virtual servers on the system to get the backups, password syncing, etc. all back working again in the future.

I had a thought to work around this and still keep all passwords/sql users/databases in sync until the hosts issue can be resolved permanently.

1) shut off the option to create a default DB (only let it create the users)

2) I make a temporary edit to feature-mysql.pl so when the virtual server is first created - it either skips the set-password - or it skips creating the extra user@host and user@ip entries in mysql

3) now that the virtual server is created AND mysql is enabled for that virtual server - i go to webmin > mysql and manually delete the user@host and user@ip mysql users, leaving behind only the user@any

4) restore feature-mysql.pl back to it's orginal state - and go update the password for that virtual server.

My theory (and I haven't read through all of the code in enough detail to know without just testing this - the 3 user entries will be created without error due to my edit to feature-mysql.pl - then the extra's are deleted through webmin - at which point webmin knows those extra users are no longer there and no longer need to be updated for that virtual server in future password changes. And I can just go about my business from here without breaking anything else. Of course, I can't add any new virtual servers or new mysql users until the patch is released (unless I make the temp edit to the scripts again) - but the user@any would be intact, linked to the virtual server, and kept in sync - and backups would contain the correct info to restore production virtual server backups to my sandbox virtual server without running into any issues?

Unless there is something in a part of the code I haven't found yet - I'm optimistic this could be a temporary solution so I can complete the migration to EC2 and RDS Aurora without breaking any functionality of webmin, virtualmin, backups, restore from backup, etc.

Ok, I will update Virtualmin to not add other host entries if % is used.

I can send you a beta version with the fix if you like?

Thanks - I can apply the patch - it's better than my lazy patch where i just commented out the extra hosts from being set. I like yours better!

Yup...works perfectly. I end up with 2 user entries as expected - user@any and user@localhost. Fully compatible and tested with the three main DB types offered by amazon RDS - Aurora, MySQL and MariaDB

Thanks again.

Great - this fix will be in the next release.

That's awesome. I'm honestly amazed how fast you were able to resolve this. Developers like you make it so much easier to want to invest time into tracking down issues. It's really refreshing to know that if we (the end users) invest our time to help track down a solution, that effort doesn't fall on deaf ears.

Thank you for that!

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.