allow configuration of the 'host' part of the created mysql users

Trying to push this through the agenda, by trying to make the allowed host customizable per database server. This would be an extra field in the dbserver configuration and would be implemented by 0.2.

Yeah well, that's step 4. I'm merely proposing we implement step 2, in the webserver, right now, so that admins can configure it manually for now. Then, we can make that list automatically generated.

In which context would this be stored? In what would would that be passed to the context the first time round?

I'm going to start work, at least on the frontend. I would welcome advice on where to pass this to the backend.

So here's my first stab at it. I was about to hack at the hook_pre_hosting_task() (in hosting_db_server.drush.inc) to propagate db_clients down to the backend, but actually, this is too messy. I think it's much cleaner to do this the proper way and create the users based on the webserver hostname... So I'm just uploading this to share a temporary patch, but I'm basically not taking the path of option 2 here.

So we will be creating grants based on the webserver hostname or the IP, depending on wether or not the DB server supports DNS, which should be a setting that gets propagated to the backend. Right now I think it's safer to assume that the db server does *not* support DNS and resolve the webserver hostname (gethostbyname()) before creating the GRANT.

Basically, then, when a site_N is created on webserver A (ip 10.0.0.1) and db server D (ip 10.0.0.10), the following GRANT is created on D:

GRANT SELECT, [...] ON site_N.* TO `site_N`@`10.0.0.1` IDENTIFIED BY '....';

Then we will stumble again upon this issue in 0.3, with multiple server support. The typical problematic use cases are this:

migrating from a single-server to multiple-server setup

By default, the first "webserver" discovered by Aegir is called "localhost". This will be highly problematic if we base GRANT on the webserver hostname in split web/db server environment.

So say we're migrating the database from localhost to dbserver D, we'll first need to rename the webserver node to its FQDN (`hostname -f`). Then we need to fix the following grants on D:

GRANT SELECT, [...] ON site_N.* TO `site_N`@`10.0.0.1` IDENTIFIED BY ...

And remove the grants on localhost:

REVOKE ALL PRIVILEGES ON site_N.* TO `site_N`@`10.0.0.1` IDENTIFIED BY '....';

migrating a site to another web server

Apart from the regular migrate, we'll also need to REVOKE the old GRANT and create a new one. Maybe a direct manipulation of the "mysql" database would be simpler.

So say we're migrating from webserver A to webserver B (10.0.0.2), the following will need to be ran on dbserver D:

REVOKE ALL PRIVILEGES ON site_N.* TO `site_N`@`10.0.0.1` IDENTIFIED BY '....';
GRANT SELECT, [...] ON site_N.* TO `site_N`@`10.0.0.2` IDENTIFIED BY '....';

migrating a site to another database server

Less common, but still possible, this will be more tricky. A full database dump will be required, which should include GRANTs for the new site. Furthermore, the old GRANT should be removed.

So say we're migrating site N from dbserver D to E, this will need to be ran on E (assuming the site is now on webserver B):

GRANT SELECT, [...] ON site_N.* TO `site_N`@`10.0.0.2` IDENTIFIED BY ...

This will need to be ran on D:

REVOKE ALL PRIVILEGES ON site_N.* TO `site_N`@`10.0.0.2` IDENTIFIED BY '....';

So here's another way to put it. This patch will create grants solely for the webserver associated with the site (the "web_host"). I tested it on my localhost server, I still need to test it on a split server.

The trick here is that some database servers (most highend servers in fact) will not resolve hostnames so while "localhost" will work "www.example.com" will not and we need to create grants based on the IP. I have code for that in the frontend and the backend, but I still have to figure out how to pass it in between. I know it's somewhere in hosting_db_server.drush.inc but I don't think i can access the DB settings from there...

Actually, i think i figured it out. The patch on provision is unchanged.

Actually, I messed up both patches, so I'm uploading new clean copies.

Adrian brought up the good point that the webserver IP should probably be stored in the webserver itself and passed along, but I consider that an optimization that could be outside this patch.

So i was wrong: mysql doesn't care about DNS if you provide the IP. So all that resolve_dns cruft should be removed. The lookups then can be moved upwards to platform verification.

So here's a reroll of the patch, without the resolvedns kludges. It only applies to provision now, and assumes that the webserver will somehow resolve the IP eventually, which would need to be rolled into another patch.

Actually, there's already a web_ip field, oddly enough. We just need to make the default resolve it. I have made it resolve in the hook_load() instead of the hook_validate() (because the latter wasn't working, for some reason). This has the disadvantage of trying to resolve the hostname numerous times if it doesn't resolve the first time the webserver is configured, but it's a good stopgap measure for now.

So this is becoming too trivial. I just committed a fix. Needs testing.

http://drupal.org/cvs?commit=205194
http://drupal.org/cvs?commit=205198

tagging this old issue