Managing MySQL Users and Permissions with Puppet

[Jonathan Preston]

I've searched for answers, but couldn't find anything quite matching my use case.

I have a series of servers, managed with Puppet and Foreman. These make up my company's development, staging, and production environments, plus a few small stand-alone servers for side projects.

I want to manage MySQL permissions on these servers, but the trick is that each server environment may have some different accounts.

We use scripts based around Percona XtraBackup to copy production databases to staging and development environments for testing purposes. If I restore a backup from our production environment to our development environment, it copies all the data, which is good, but it also sets the users and permissions to match the production environment as well. This is a problem in our case.

What I want to do is configure, in Puppet/Foreman, all of the credentials that should be present on a given server, and have the Puppet client not only set up whatever the Puppet master says, but also purge any credentials that aren't in the list. In other words, the accounts I specify, and only those, should end up on the Puppet client machine, and Puppet should be responsible for purging anything else there.

Naturally, I don't want to rebuild the grant tables every time Puppet runs, so I presume I'd need a way to intelligently read and parse what's already there.

Is anyone aware of someone having done something like this? Any ideas at all on how I might accomplish it?

Thanks in advance.

[Michael Watters]

Use the puppetlabs-mysql module to manage the grants for each user/db.  You can have different values for each environment stored in hiera.