How to manage MySQL users in Chef

In Chef, how do you manage MySQL users? How would you set different privileges and passwords based on different environments? Would you use data bags or is there any other way to manage these?

We have a Chef repo for dev and a separate repo for prod.

Solution

In Chef, how do you manage MySQL users?

In order to manage mysql users you can use mysql_database_user resource or mysql_database with action query when some things are not covered by mysql_database_user resource.

manage user with mysql_database_user resource

mysql_database_user 'app-user' do
  connection mysql_connection_info
  password data_bag_item['password']
  host 'localhost'
  privileges [:all]
  action [:create, :grant]
end

where data_bag_item['password'] is password stored in specific data bag.

manage user with mysql_database resource

mysql_database 'create slave user' do
  connection mysql_connection_info
  sql "GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY   '#{slave_password_data_bag_item['password']}'"
  action :query
end

You'll need this cookbook https://github.com/chef-cookbooks/database. It is possible to have differences in resource's attributes because this examples are not from latest version.

How would you set different privileges and passwords based on different environments

For this you can use env options in chef and/or combine them with chef roles.

To have one resource executed specifically per environment you can use not_if or only_if attributes applied to resource:

mysql_database_user 'app-user' do
  not_if { node.environment == 'devel' }
end

and using helpers with it:

ENV_DEV ||= 'dev'

def dev_env?
        node.environment == ENV_DEV
end

mysql_database_user 'app-user' do
  not_if { dev_env? }
end

You can combine this with env_roles and have specific roles for your environment. Those roles will include recipes that will manage your credentials. In that way you will ave not_if and if options.

More about env and roles - https://www.digitalocean.com/community/tutorials/how-to-use-roles-and-environments-in-chef-to-control-server-configurations

Would you use data bags or is there any other way to manage these?

Yes I would use that with proper abstraction in libraries to minimize duplicate code to call something out from databag.

There is a way to hide decrypted output in chef log, in that way your log will not contain sensitive info.

We have a Chef repo for dev and a separate repo for prod.

To me is ok to start with one chef cookbook with recipes, roles, env e.g. one codebase to manage all your nodes.

You can expand this incrementally when your requirements got up to base cookbook and higher cookbooks that will use base cookbook as dependency. In that way you will overcome big cookbooks with lots of recipes and libraries.