MySQL using AWS Aurora MySQL

ninja0n3 · October 22, 2023, 12:02am

System specs
OS type and version	Ubuntu 22.04
Virtualmin version	7.8.2

Hello,

I’m experiencing some issues when using a remote AWS Aurora MySQL database; I wasn’t able to find any relevant direction online or on this forum about something like this.
The master user on that database does not have direct write permissions to the mysql database on the instance.
When I try to assign a database to a new virtual server, I end up with the following message:
MariaDB database failed! : mysql::execute_sql_logged failed : SQL insert ignore into user (host, user, ssl_type, ssl_cipher, x509_issuer, x509_subject) values ('localhost', 'multisite-mpd', '', '', '', '') failed : Access denied for user 'some_user'@'%' to database 'mysql'

My understanding is that virtualmin is attempting to create a user by performing a direct insert into the mysql.users, which is not possible with an RDS/Aurora instance due to the restrictions on the mysql database - those permissions are exclusive to a user named rdsadmin, which is not directly accessible.

I’m trying to find a workaround that would still allow me to assign a db to a virtual server and subsequently allow me to create wordpress installations while using Aurora or RDS db instances.

Alternatively, I was thinking about editing the mysql creation scripts and replace some of the queries with queries that would work (for example using create user instead of attempting a direct insert on the mysql.usersdb.

I’m not entirely sure where to find those files.

Any guidance to get this working would be greatly appreciated.

Thanks in advance.

stefan1959 · October 22, 2023, 9:42am

So is virtualmin is on the AWS? localhost won’t work if its not.

ninja0n3 · October 22, 2023, 1:58pm

Virtualmin is on an AWS EC2 instance, and that works fine. I’m using a remote database MySQL that runs on AWS Aurora, which doesn’t allow me direct interaction with the mysql schema.

I’m trying to do a workaround or edit the script to be able to replace the direct user inserts with the equivalent queries that don’t rely on inserting/updating/deleting any tables in mysql

stefan1959 · October 22, 2023, 9:57pm

Not sure, this is documented on aws though. Maybe you need to open a firewall port.

ninja0n3 · October 23, 2023, 8:56am

@stephan1959, The Aurora cluster is setup correctly, the issue I have is how it is treated by Virtualmin. The database is operating as expected, connectivity is also correct. The issue is more on the access permissions associated with what I can or can’t do within that db with the “master user”. Virtualmin treats this database as MySQL (based on the reported version by Aurora, MySQL 8.0.28, but it seems to support the MariaDB functionality for create user)

I think I was able to find the relevant script that handles the user creation for a given virtual server under /usr/share/webmin/virtual-server/feature-mysql.pl

I updated some of the code as follows:

sub get_user_creation_sql
{
my ($d, $host, $user, $encpass, $plainpass) = @_;
my ($ver, $variant) = &get_dom_remote_mysql_version($d);
my $plugin = &get_mysql_plugin($d, 1);

# Hash password for setting
if (!$encpass && $plainpass) {
	$encpass = &encrypt_plain_mysql_pass($d, $plainpass)
	}
#------
# Edited the if statement below to support the version for Aurora - Aurora appears as MySQL 8.0.28 on 
# Virtualmin as follows:
# `MySQL version 8.0.28 on mycluster.cluster-some_aws_id.us-region.rds.amazonaws.com`
#------
if ($variant eq "mariadb" && &compare_versions($ver, "10.4") >= 0 ||
    $variant eq "mysql" && &compare_versions($ver, "8.0.28") >= 0) {
	# Need to use new 'create user' command
	return ("create user '$user'\@'$host' identified $plugin by ".
		($plainpass ? "'".&mysql_escape($plainpass)."'"
			    : "password $encpass"));
	}
elsif ($variant eq "mysql" && &compare_versions($ver, "5.7.6") >= 0) {
	my $changepasssql;
	if ($plainpass) {
		$changepasssql = "alter user '$user'\@'$host' identified $plugin by '".&mysql_escape($plainpass)."'";
		}
	else {
		$changepasssql = "update user set authentication_string = $encpass where user = '$user' and host = '$host'";
		}
	return ("insert ignore into user (host, user, ssl_type, ssl_cipher, x509_issuer, x509_subject) values ('$host', '$user', '', '', '', '')", "flush privileges", "$changepasssql", "flush privileges");
	}
elsif (&compare_versions($ver, 5) >= 0) {
	my $setpasssql;
	if ($plainpass) {
		$setpasssql = "set password for '$user'\@'$host' = ".
			      &encrypt_plain_mysql_pass($d, $plainpass);
		}
	else {
		$setpasssql = "set password for '$user'\@'$host' = $encpass";
		}
	return ("insert ignore into user (host, user, ssl_type, ssl_cipher, x509_issuer, x509_subject) values ('$host', '$user', '', '', '', '')", "flush privileges", $setpasssql, "flush privileges");
	}
else {
	return ("insert ignore into user (host, user, password) values ('$host', '$user', $encpass)");
	}
}

However changing this still uses the first elsif (or maybe the else, I’m not sure) when I attempt to enable the database on the virtual edit. I’m not sure if I’m editing the right file or if I’m missing something else…
I even tried to force it by adding an || true to the initial if statement, I still get the query on the elsif condition.
I don’t know if Virtualmin uses a cached version of this file, or if there’s another location where these files are kept.
Not sure what I’m missing.

Any pointers would be greatly appreciated.

stefan1959 · October 23, 2023, 9:02am

Not sure what you mean by that.
What on VM are you connecting to the database.

Is it this?

ninja0n3 · October 23, 2023, 9:15am

I mean this:

stefan1959 · October 23, 2023, 9:19am

so is port 3306 open, that about all I can think that would cause a issue. If I get some time tomorrow I will try.

ninja0n3 · October 23, 2023, 9:32am

I have no problems connecting to the database and getting data from it (host, port and firewall permissions are correct); the issue is when I attempt to associate the database with a virtual server, the database user creation fails because Aurora doesn’t support direct writes to the mysql schema on that database server.
So I end up with the following error:

MariaDB database failed! : mysql::execute_sql_logged failed : SQL insert ignore into user (host, user, ssl_type, ssl_cipher, x509_issuer, x509_subject) values ('localhost', 'some_site', '', '', '', '') failed : Access denied for user 'some_user'@'%' to database 'mysql'

AWS Aurora MySQL does support create user like MariaDB, however, since the server reports itself as a MySQL 8.0.28 server, the db user creation script tries to use insert ignore into user (host, user, ssl_type, ssl_cipher, x509_issuer, x509_subject) values ('$host', '$user', '', '', '', '') (line 2930 of feature-mysql.pl) instead of using create user '$user'\@'$host' identified $plugin by ".($plainpass ? "'".&mysql_escape($plainpass)."'": "password $encpass" (line 2918).

What I’m trying to achieve at this point, is edit the appropriate user creation script to use the behavior in line 2918 defined by

if ($variant eq "mariadb" && &compare_versions($ver, "10.4") >= 0) {
	# Need to use new 'create user' command
	return ("create user '$user'\@'$host' identified $plugin by ".
		($plainpass ? "'".&mysql_escape($plainpass)."'"
			    : "password $encpass"));
	}

to leverage this block instead of the ones below.

I tried this:

if ($variant eq "mariadb" && &compare_versions($ver, "10.4") >= 0 ||
    $variant eq "mysql" && &compare_versions($ver, "8.0.28") >= 0) {
	# Need to use new 'create user' command
	return ("create user '$user'\@'$host' identified $plugin by ".
		($plainpass ? "'".&mysql_escape($plainpass)."'"
			    : "password $encpass"));
	}

and

if ($variant eq "mariadb" && &compare_versions($ver, "10.4") >= 0 ||
    $variant eq "mysql" && &compare_versions($ver, "8.0.28") >= 0 || true) {
	# Need to use new 'create user' command
	return ("create user '$user'\@'$host' identified $plugin by ".
		($plainpass ? "'".&mysql_escape($plainpass)."'"
			    : "password $encpass"));
	}

neither of which seem to be working.

The file I’m editing is /usr/share/webmin/virtual-server/feature-mysql.pl

I’m not sure if I’m editing the correct file to bypass the current behavior of if there’s another file somewhere else that ensures this behavior.

ninja0n3 · October 23, 2023, 10:12am

Quick Update: Turns out I was editing the correct file, I was able to get it working after editing the file and running sudo /etc/webmin/restart

I didn’t know I had to restart the webmin server for the perl file changes to take effect.

After doing this task I was able to get the user creation to work when enabling the database on the virtual server.

In summary:

Using Aurora MySQL as the Database, which behaves like a MariaDB
Edited the if statement on line 2916 to include the mysql db version corresponding to Aurora by adding: $variant eq "mysql" && &compare_versions($ver, "8.0.28") >= 0
restarted the webmin server using sudo /etc/webmin/restart

After doing this the process worked:

system · October 31, 2023, 10:13am

This topic was automatically closed 8 days after the last reply. New replies are no longer allowed.