Do MySQL database dumps as root instead of virtual server user

Submitted by aitte on Sun, 06/16/2013 - 09:38

In security-conscious setups (isolating sensitive databases from each other), I do the following:

  • Create a MySQL database using Virtualmin, as part of one of the virtual servers
  • Log into MySQL as root, and revert all access rights from the server-user
  • Create a new MySQL user with full access to that database.

The issue is that when the backup script runs, it is executed as the server-user, which of course has no read access to these special security-conscious tables:

Creating backup for virtual server ExampleServer ..
    Copying virtual server configuration ..
    .. done

    Backing up Cron jobs ..
    .. none defined.

    Saving mail aliases ..
    .. done

    Saving mail and FTP users ..
    .. done

    Backing up mail and FTP user Cron jobs ..
    .. none to backup

    Dumping MySQL database ExampleServer ..
    .. done

    Dumping MySQL database ExampleServer_roundcube ..
    .. dump failed! sh: /home/ExampleServer/.backup/ExampleServer_mysql_ExampleServer_roundcube: No such file or directory


    Dumping MySQL database ExampleServer_pma ..
    .. dump failed! sh: /home/ExampleServer/.backup/ExampleServer_mysql_ExampleServer_pma: No such file or directory


    Dumping MySQL database ExampleServer_piwik ..
    .. dump failed! sh: /home/ExampleServer/.backup/ExampleServer_mysql_ExampleServer_piwik: No such file or directory

Would it be possible for you to change the Virtualmin backup process so that all database dumps run as the root user?

Status: 
Active

Comments

Submitted by JamieCameron on Sun, 06/16/2013 - 13:11

When you say root, do you mean connecting to MySQL as the root user, or running the mysqldump command as the root user?

It looks like the error here is actually a unix permissions problem of some kind that is preventing writes to the .backup directory, which seems unrelated to your table permissions change.

FYI, if you make changes to permissions outside of Virtualmin's control, we can't guarantee that everything will still work.

Submitted by aitte on Sun, 06/16/2013 - 15:44

This isn't related to the .backup directory permissions (which I haven't touched). Every other server and every other database is exported fine (even in the example log above, you see 1 of the 4 databases export fine). The only databases that show this error are the ones where I've reverted the Virtualmin virtserver user's MySQL permissions and created a new user that has solo access to these specific databases (which is the case for the 3 failed exports in the example above).

That lead me to conclude that each dump is being executed with the permissions of the virtualmin virtserver user's MySQL account, and not as the root MySQL account. It's the only thing I can think of to explain why every database exports fine except the ones with retracted permissions.

I am guessing Virtualmin explicitly or implicitly does the equivalent of:

 mysqldump –u[username] –p[password] [database name] > [dump file]
 where username = the virtual server's MySQL user; not root

Would this be fixable by running mysqldump with MySQL-root permissions? Is such a thing possible? I assume it would be, since Virtualmin obviously has root access and knows the password to use (how else would it be creating new MySQL users and so on?).

Had a look in /usr/libexec/webmin/virtual-server/feature-mysql.pl and found a call to &mysql::backup_database() but not sure if that's the place that needs editing to try the backup as MySQL-root.

Submitted by JamieCameron on Sun, 06/16/2013 - 16:02

Actually, the dump is done as the root mysql user.

Is this backup being done on schedule in the background, via the UI as root, or via the UI as the domain owner?

Submitted by aitte on Mon, 06/17/2013 - 09:05

Thanks for that comment - I ran every possible test:

  • The failed log above was the scheduled event (weekly (Sunday) midnight backup).

  • I went back to the UI, clicked the Schedule and chose "Backup now" and did a backup as UI (root) in the foreground and it worked. Each database succeeded.

  • I then went back again, clicked the Schedule, chose "Backup now" and did a backup as UI (root) in the background and it worked. Each database succeeded.

So the only thing that had failed was the Scheduled event.

  • I then went back, set the Schedule to 1 minute into the future and waited... and it worked. Each database succeeded.

    This was the background task's process: root 27863 13.1 5.1 165436 97488 ? R 15:41 0:23 /usr/bin/perl /usr/libexec/webmin/virtual-server/backup.pl --id 13705714333932 --force-email

    This was the scheduled task's process: root 30243 17.7 5.6 189440 105760 ? S 15:48 0:04 /usr/libexec/webmin/virtual-server/backup.pl

I am now at a loss for why the last scheduled backup failed - with the failures affecting JUST the databases with modified SQL permissions. Hmm...

Could it really be a /home/ExampleServer/.backup directory permissions issue? I doubt that too, because that directory doesn't exist. It is created by the backup task, AND the backup task runs as root, so it can't be the file permissions...

Edit: I just discovered that the same scheduled task has actually run successfully on the prior sunday. So it was just this sunday that it failed. It worries me that I can't find why it failed... I mean, it's a sporadic failure despite the exact same backup parameters being executed successfully the prior sunday. Very strange.

Submitted by JamieCameron on Mon, 06/17/2013 - 15:44

I wonder if perhaps there were two scheduled backups running at the same time? This could have caused the .backup directory to be deleted by the first backup before the second one could populate it.

Virtualmin is supposed to have locking to prevent this, but maybe it didn't work in this case.

Submitted by aitte on Mon, 06/17/2013 - 19:09

Very good thinking there. That makes complete sense. I've been a bit worried by my schedule, which is set up as:

  • Full backup: Weekly (Sunday at 0:00)
  • Incremental backup: Daily (at 0:00)

I was just hoping that Virtualmin would see such scheduling conflicts and deal with it somehow, but maybe this time the locking didn't work as it should have.

It's a bit scary to know that an incremental and full update takes place at the same time, and optimally it would only do the full backup since the incremental is totally superfluous.

However, I just realized that I can set the Full backup to custom schedule: 0:00 on Sunday, and the incremental update to custom schedule: 0:00 on Monday-Saturday.

I am quite convinced that this issue was caused by the scheduling conflict and a race condition. I bet the incremental backup finished earlier and deleted the .backup directory right near the end of the full backup, after which the final database backups failed. Pure chance just made it happen at the exact moment that the 3 modified-permissions databases were being exported, hehe. They were the last 3 databases to export.

Submitted by JamieCameron on Mon, 06/17/2013 - 19:48

Yes, I'd recommend those scheduling not to overlap - even if locking is working OK, it is nearly random which one will run first.

Submitted by aitte on Tue, 06/18/2013 - 16:16

Thanks. I see. At the time of setting up the conflicting schedules, I was a bit nervous but assumed that Virtualmin had some code in place to deal with it.

If there's one thing I can suggest then, it's an extra paragraph in the "Help..." section of the Virtualmin Scheduled Backups module, warning against creating overlapping schedules.

Submitted by JamieCameron on Tue, 06/18/2013 - 16:54

I'll add some additional help text around this.