From adc9c0f1168b780e6c8b78f63caa2fb51cc72399 Mon Sep 17 00:00:00 2001 From: Silvio Rhatto Date: Wed, 3 Feb 2010 14:11:18 -0200 Subject: Adding resource types mysql_{user,database,grant} (2) --- lib/facter/mysql.rb | 8 ++ lib/puppet/parser/functions/mysql_password.rb | 9 ++ lib/puppet/provider/mysql_database/mysql.rb | 55 +++++++++ lib/puppet/provider/mysql_grant/mysql.rb | 155 ++++++++++++++++++++++++++ lib/puppet/provider/mysql_user/mysql.rb | 76 +++++++++++++ lib/puppet/type/mysql_database.rb | 11 ++ lib/puppet/type/mysql_grant.rb | 77 +++++++++++++ lib/puppet/type/mysql_user.rb | 22 ++++ tests/001_create_database.pp | 4 + tests/010_create_user.pp | 7 ++ tests/012_change_password.pp | 6 + tests/100_create_user_grant.pp | 9 ++ tests/101_remove_user_privilege.pp | 8 ++ tests/102_add_user_privilege.pp | 8 ++ tests/103_change_user_grant.pp | 8 ++ tests/104_mix_user_grants.pp | 8 ++ tests/150_create_db_grant.pp | 9 ++ tests/151_remove_db_privilege.pp | 8 ++ tests/152_add_db_privilege.pp | 8 ++ tests/153_change_db_priv.pp | 8 ++ tests/154_mix_db_grants.pp | 8 ++ tests/200_give_all_user_privs.pp | 8 ++ tests/201_give_all_db_privs.pp | 8 ++ tests/996_remove_db_grant.pp | 5 + tests/997_remove_user_grant.pp | 5 + tests/998_remove_user.pp | 3 + tests/999_remove_database.pp | 3 + tests/README | 6 + tests/run_tests | 13 +++ 29 files changed, 563 insertions(+) create mode 100644 lib/facter/mysql.rb create mode 100644 lib/puppet/parser/functions/mysql_password.rb create mode 100644 lib/puppet/provider/mysql_database/mysql.rb create mode 100644 lib/puppet/provider/mysql_grant/mysql.rb create mode 100644 lib/puppet/provider/mysql_user/mysql.rb create mode 100644 lib/puppet/type/mysql_database.rb create mode 100644 lib/puppet/type/mysql_grant.rb create mode 100644 lib/puppet/type/mysql_user.rb create mode 100644 tests/001_create_database.pp create mode 100644 tests/010_create_user.pp create mode 100644 tests/012_change_password.pp create mode 100644 tests/100_create_user_grant.pp create mode 100644 tests/101_remove_user_privilege.pp create mode 100644 tests/102_add_user_privilege.pp create mode 100644 tests/103_change_user_grant.pp create mode 100644 tests/104_mix_user_grants.pp create mode 100644 tests/150_create_db_grant.pp create mode 100644 tests/151_remove_db_privilege.pp create mode 100644 tests/152_add_db_privilege.pp create mode 100644 tests/153_change_db_priv.pp create mode 100644 tests/154_mix_db_grants.pp create mode 100644 tests/200_give_all_user_privs.pp create mode 100644 tests/201_give_all_db_privs.pp create mode 100644 tests/996_remove_db_grant.pp create mode 100644 tests/997_remove_user_grant.pp create mode 100644 tests/998_remove_user.pp create mode 100644 tests/999_remove_database.pp create mode 100644 tests/README create mode 100755 tests/run_tests diff --git a/lib/facter/mysql.rb b/lib/facter/mysql.rb new file mode 100644 index 0000000..e262ec1 --- /dev/null +++ b/lib/facter/mysql.rb @@ -0,0 +1,8 @@ +Facter.add("mysql_exists") do + ENV["PATH"]="/bin:/sbin:/usr/bin:/usr/sbin" + + setcode do + mysqlexists = system "which mysql > /dev/null 2>&1" + ($?.exitstatus == 0) + end +end diff --git a/lib/puppet/parser/functions/mysql_password.rb b/lib/puppet/parser/functions/mysql_password.rb new file mode 100644 index 0000000..6443d95 --- /dev/null +++ b/lib/puppet/parser/functions/mysql_password.rb @@ -0,0 +1,9 @@ +# hash a string as mysql's "PASSWORD()" function would do it +require 'digest/sha1' + +module Puppet::Parser::Functions + newfunction(:mysql_password, :type => :rvalue) do |args| + '*' + Digest::SHA1.hexdigest(Digest::SHA1.digest(args[0])).upcase + end +end + diff --git a/lib/puppet/provider/mysql_database/mysql.rb b/lib/puppet/provider/mysql_database/mysql.rb new file mode 100644 index 0000000..2b70e04 --- /dev/null +++ b/lib/puppet/provider/mysql_database/mysql.rb @@ -0,0 +1,55 @@ +require 'puppet/provider/package' + +Puppet::Type.type(:mysql_database).provide(:mysql, + :parent => Puppet::Provider::Package) do + + desc "Use mysql as database." + commands :mysqladmin => '/usr/bin/mysqladmin' + commands :mysql => '/usr/bin/mysql' + + # retrieve the current set of mysql users + def self.instances + dbs = [] + + cmd = "#{command(:mysql)} mysql -NBe 'show databases'" + execpipe(cmd) do |process| + process.each do |line| + dbs << new( { :ensure => :present, :name => line.chomp } ) + end + end + return dbs + end + + def query + result = { + :name => @resource[:name], + :ensure => :absent + } + + cmd = "#{command(:mysql)} mysql -NBe 'show databases'" + execpipe(cmd) do |process| + process.each do |line| + if line.chomp.eql?(@resource[:name]) + result[:ensure] = :present + end + end + end + result + end + + def create + mysqladmin "create", @resource[:name] + end + def destroy + mysqladmin "-f", "drop", @resource[:name] + end + + def exists? + if mysql("mysql", "-NBe", "show databases").match(/^#{@resource[:name]}$/) + true + else + false + end + end +end + diff --git a/lib/puppet/provider/mysql_grant/mysql.rb b/lib/puppet/provider/mysql_grant/mysql.rb new file mode 100644 index 0000000..61c32d9 --- /dev/null +++ b/lib/puppet/provider/mysql_grant/mysql.rb @@ -0,0 +1,155 @@ +# A grant is either global or per-db. This can be distinguished by the syntax +# of the name: +# user@host => global +# user@host/db => per-db + +require 'puppet/provider/package' + +MYSQL_USER_PRIVS = [ :select_priv, :insert_priv, :update_priv, :delete_priv, + :create_priv, :drop_priv, :reload_priv, :shutdown_priv, :process_priv, + :file_priv, :grant_priv, :references_priv, :index_priv, :alter_priv, + :show_db_priv, :super_priv, :create_tmp_table_priv, :lock_tables_priv, + :execute_priv, :repl_slave_priv, :repl_client_priv, :create_view_priv, + :show_view_priv, :create_routine_priv, :alter_routine_priv, + :create_user_priv +] + +MYSQL_DB_PRIVS = [ :select_priv, :insert_priv, :update_priv, :delete_priv, + :create_priv, :drop_priv, :grant_priv, :references_priv, :index_priv, + :alter_priv, :create_tmp_table_priv, :lock_tables_priv, :create_view_priv, + :show_view_priv, :create_routine_priv, :alter_routine_priv, :execute_priv +] + +Puppet::Type.type(:mysql_grant).provide(:mysql) do + + desc "Uses mysql as database." + + commands :mysql => '/usr/bin/mysql' + commands :mysqladmin => '/usr/bin/mysqladmin' + + def mysql_flush + mysqladmin "flush-privileges" + end + + # this parses the + def split_name(string) + matches = /^([^@]*)@([^\/]*)(\/(.*))?$/.match(string).captures.compact + case matches.length + when 2 + { + :type => :user, + :user => matches[0], + :host => matches[1] + } + when 4 + { + :type => :db, + :user => matches[0], + :host => matches[1], + :db => matches[3] + } + end + end + + def create_row + unless @resource.should(:privileges).empty? + name = split_name(@resource[:name]) + case name[:type] + when :user + mysql "mysql", "-e", "INSERT INTO user (host, user) VALUES ('%s', '%s')" % [ + name[:host], name[:user], + ] + when :db + mysql "mysql", "-e", "INSERT INTO db (host, user, db) VALUES ('%s', '%s', '%s')" % [ + name[:host], name[:user], name[:db], + ] + end + mysql_flush + end + end + + def destroy + mysql "mysql", "-e", "REVOKE ALL ON '%s'.* FROM '%s@%s'" % [ @resource[:privileges], @resource[:database], @resource[:name], @resource[:host] ] + end + + def row_exists? + name = split_name(@resource[:name]) + fields = [:user, :host] + if name[:type] == :db + fields << :db + end + not mysql( "mysql", "-NBe", 'SELECT "1" FROM %s WHERE %s' % [ name[:type], fields.map do |f| "%s = '%s'" % [f, name[f]] end.join(' AND ')]).empty? + end + + def all_privs_set? + all_privs = case split_name(@resource[:name])[:type] + when :user + MYSQL_USER_PRIVS + when :db + MYSQL_DB_PRIVS + end + all_privs = all_privs.collect do |p| p.to_s end.sort.join("|") + privs = privileges.collect do |p| p.to_s end.sort.join("|") + + all_privs == privs + end + + def privileges + name = split_name(@resource[:name]) + privs = "" + + case name[:type] + when :user + privs = mysql "mysql", "-Be", 'select * from user where user="%s" and host="%s"' % [ name[:user], name[:host] ] + when :db + privs = mysql "mysql", "-Be", 'select * from db where user="%s" and host="%s" and db="%s"' % [ name[:user], name[:host], name[:db] ] + end + + if privs.match(/^$/) + privs = [] # no result, no privs + else + # returns a line with field names and a line with values, each tab-separated + privs = privs.split(/\n/).map! do |l| l.chomp.split(/\t/) end + # transpose the lines, so we have key/value pairs + privs = privs[0].zip(privs[1]) + privs = privs.select do |p| p[0].match(/_priv$/) and p[1] == 'Y' end + end + + privs.collect do |p| symbolize(p[0].downcase) end + end + + def privileges=(privs) + unless row_exists? + create_row + end + + # puts "Setting privs: ", privs.join(", ") + name = split_name(@resource[:name]) + stmt = '' + where = '' + all_privs = [] + case name[:type] + when :user + stmt = 'update user set ' + where = ' where user="%s" and host="%s"' % [ name[:user], name[:host] ] + all_privs = MYSQL_USER_PRIVS + when :db + stmt = 'update db set ' + where = ' where user="%s" and host="%s"' % [ name[:user], name[:host] ] + all_privs = MYSQL_DB_PRIVS + end + + if privs[0] == :all + privs = all_privs + end + + # puts "stmt:", stmt + set = all_privs.collect do |p| "%s = '%s'" % [p, privs.include?(p) ? 'Y' : 'N'] end.join(', ') + # puts "set:", set + stmt = stmt << set << where + + mysql "mysql", "-Be", stmt + mysql_flush + end +end + diff --git a/lib/puppet/provider/mysql_user/mysql.rb b/lib/puppet/provider/mysql_user/mysql.rb new file mode 100644 index 0000000..adc46c3 --- /dev/null +++ b/lib/puppet/provider/mysql_user/mysql.rb @@ -0,0 +1,76 @@ +require 'puppet/provider/package' + +Puppet::Type.type(:mysql_user).provide(:mysql, + # T'is funny business, this code is quite generic + :parent => Puppet::Provider::Package) do + + desc "Use mysql as database." + commands :mysql => '/usr/bin/mysql' + commands :mysqladmin => '/usr/bin/mysqladmin' + + # retrieve the current set of mysql users + def self.instances + users = [] + + cmd = "#{command(:mysql)} mysql -NBe 'select concat(user, \"@\", host), password from user'" + execpipe(cmd) do |process| + process.each do |line| + users << new( query_line_to_hash(line) ) + end + end + return users + end + + def self.query_line_to_hash(line) + fields = line.chomp.split(/\t/) + { + :name => fields[0], + :password_hash => fields[1], + :ensure => :present + } + end + + def mysql_flush + mysqladmin "flush-privileges" + end + + def query + result = {} + + cmd = "#{command(:mysql)} -NBe 'select concat(user, \"@\", host), password from user where concat(user, \"@\", host) = \"%s\"'" % @resource[:name] + execpipe(cmd) do |process| + process.each do |line| + unless result.empty? + raise Puppet::Error, + "Got multiple results for user '%s'" % @resource[:name] + end + result = query_line_to_hash(line) + end + end + result + end + + def create + mysql "mysql", "-e", "create user '%s' identified by PASSWORD '%s'" % [ @resource[:name].sub("@", "'@'"), @resource.should(:password_hash) ] + mysql_flush + end + + def destroy + mysql "mysql", "-e", "drop user '%s'" % @resource[:name].sub("@", "'@'") + mysql_flush + end + + def exists? + not mysql("mysql", "-NBe", "select '1' from user where CONCAT(user, '@', host) = '%s'" % @resource[:name]).empty? + end + + def password_hash + @property_hash[:password_hash] + end + + def password_hash=(string) + mysql "mysql", "-e", "SET PASSWORD FOR '%s' = '%s'" % [ @resource[:name].sub("@", "'@'"), string ] + mysql_flush + end +end + diff --git a/lib/puppet/type/mysql_database.rb b/lib/puppet/type/mysql_database.rb new file mode 100644 index 0000000..bb25ffa --- /dev/null +++ b/lib/puppet/type/mysql_database.rb @@ -0,0 +1,11 @@ +# This has to be a separate type to enable collecting +Puppet::Type.newtype(:mysql_database) do + @doc = "Manage a database." + ensurable + newparam(:name) do + desc "The name of the database." + + # TODO: only [[:alnum:]_] allowed + end +end + diff --git a/lib/puppet/type/mysql_grant.rb b/lib/puppet/type/mysql_grant.rb new file mode 100644 index 0000000..415f5aa --- /dev/null +++ b/lib/puppet/type/mysql_grant.rb @@ -0,0 +1,77 @@ +# This has to be a separate type to enable collecting +Puppet::Type.newtype(:mysql_grant) do + @doc = "Manage a database user's rights." + #ensurable + + autorequire :mysql_db do + # puts "Starting db autoreq for %s" % self[:name] + reqs = [] + matches = self[:name].match(/^([^@]+)@([^\/]+)\/(.+)$/) + unless matches.nil? + reqs << matches[3] + end + # puts "Autoreq: '%s'" % reqs.join(" ") + reqs + end + + autorequire :mysql_user do + # puts "Starting user autoreq for %s" % self[:name] + reqs = [] + matches = self[:name].match(/^([^@]+)@([^\/]+).*$/) + unless matches.nil? + reqs << "%s@%s" % [ matches[1], matches[2] ] + end + # puts "Autoreq: '%s'" % reqs.join(" ") + reqs + end + + newparam(:name) do + desc "The primary key: either user@host for global privilges or user@host/database for database specific privileges" + end + newproperty(:privileges, :array_matching => :all) do + desc "The privileges the user should have. The possible values are implementation dependent." + munge do |v| + symbolize(v) + end + + def should_to_s(newvalue = @should) + if newvalue + unless newvalue.is_a?(Array) + newvalue = [ newvalue ] + end + newvalue.collect do |v| v.to_s end.sort.join ", " + else + nil + end + end + + def is_to_s(currentvalue = @is) + if currentvalue + unless currentvalue.is_a?(Array) + currentvalue = [ currentvalue ] + end + currentvalue.collect do |v| v.to_s end.sort.join ", " + else + nil + end + end + + # use the sorted outputs for comparison + def insync?(is) + if defined? @should and @should + case self.should_to_s + when "all" + self.provider.all_privs_set? + when self.is_to_s(is) + true + else + false + end + else + true + end + end + + end +end + diff --git a/lib/puppet/type/mysql_user.rb b/lib/puppet/type/mysql_user.rb new file mode 100644 index 0000000..55d97b6 --- /dev/null +++ b/lib/puppet/type/mysql_user.rb @@ -0,0 +1,22 @@ +# This has to be a separate type to enable collecting +Puppet::Type.newtype(:mysql_user) do + @doc = "Manage a database user." + ensurable + newparam(:name) do + desc "The name of the user. This uses the 'username@hostname' form." + + validate do |value| + if value.split('@').first.size > 16 + raise ArgumentError, + "MySQL usernames are limited to a maximum of 16 characters" + else + super + end + end + end + + newproperty(:password_hash) do + desc "The password hash of the user. Use mysql_password() for creating such a hash." + end +end + diff --git a/tests/001_create_database.pp b/tests/001_create_database.pp new file mode 100644 index 0000000..4e489cc --- /dev/null +++ b/tests/001_create_database.pp @@ -0,0 +1,4 @@ + +err("Will create 'test_db'") +mysql_database { "test_db": ensure => present } + diff --git a/tests/010_create_user.pp b/tests/010_create_user.pp new file mode 100644 index 0000000..a45ed5b --- /dev/null +++ b/tests/010_create_user.pp @@ -0,0 +1,7 @@ + +err("Will create user 'test_user@%' with password 'blah'") + +mysql_user{ "test_user@%": + password_hash => mysql_password("blah"), + ensure => present +} diff --git a/tests/012_change_password.pp b/tests/012_change_password.pp new file mode 100644 index 0000000..7bf7f02 --- /dev/null +++ b/tests/012_change_password.pp @@ -0,0 +1,6 @@ + +err("Changing password for user 'test_user@%'") +mysql_user{ "test_user@%": + password_hash => mysql_password("foo"), + ensure => present +} diff --git a/tests/100_create_user_grant.pp b/tests/100_create_user_grant.pp new file mode 100644 index 0000000..1d3dca8 --- /dev/null +++ b/tests/100_create_user_grant.pp @@ -0,0 +1,9 @@ +err("Grant SELECT, INSERT and UPDATE to test_user@%") + +mysql_grant { + "test_user@%": + privileges => [ "select_priv", 'insert_priv', 'update_priv' ], + tag => test; +} + + diff --git a/tests/101_remove_user_privilege.pp b/tests/101_remove_user_privilege.pp new file mode 100644 index 0000000..6b7029e --- /dev/null +++ b/tests/101_remove_user_privilege.pp @@ -0,0 +1,8 @@ +err("Revoke UPDATE from test_user@%") + +mysql_grant { + "test_user@%": + privileges => [ "select_priv", 'insert_priv' ], +} + + diff --git a/tests/102_add_user_privilege.pp b/tests/102_add_user_privilege.pp new file mode 100644 index 0000000..849cd3a --- /dev/null +++ b/tests/102_add_user_privilege.pp @@ -0,0 +1,8 @@ +err("Grant DELETE to test_user@%") + +mysql_grant { + "test_user@%": + privileges => [ "select_priv", 'insert_priv', 'delete_priv' ], +} + + diff --git a/tests/103_change_user_grant.pp b/tests/103_change_user_grant.pp new file mode 100644 index 0000000..fa860a3 --- /dev/null +++ b/tests/103_change_user_grant.pp @@ -0,0 +1,8 @@ +err("Replace DELETE with UPDATE grant for test_user@%") + +mysql_grant { + "test_user@%": + privileges => [ "select_priv", 'insert_priv', 'update_priv' ], +} + + diff --git a/tests/104_mix_user_grants.pp b/tests/104_mix_user_grants.pp new file mode 100644 index 0000000..d0dc512 --- /dev/null +++ b/tests/104_mix_user_grants.pp @@ -0,0 +1,8 @@ +err("Change the order of the defined privileges") + +mysql_grant { + "test_user@%": + privileges => [ "update_priv", 'insert_priv', 'select_priv' ], +} + + diff --git a/tests/150_create_db_grant.pp b/tests/150_create_db_grant.pp new file mode 100644 index 0000000..597993d --- /dev/null +++ b/tests/150_create_db_grant.pp @@ -0,0 +1,9 @@ +err("Create a db grant") + +mysql_grant { + "test_user@%test_db": + privileges => [ "select_priv", 'insert_priv', 'update_priv' ], + tag => test; +} + + diff --git a/tests/151_remove_db_privilege.pp b/tests/151_remove_db_privilege.pp new file mode 100644 index 0000000..da3246f --- /dev/null +++ b/tests/151_remove_db_privilege.pp @@ -0,0 +1,8 @@ +err("Revoke UPDATE from test_user@%test_db") + +mysql_grant { + "test_user@%test_db": + privileges => [ "select_priv", 'insert_priv'], +} + + diff --git a/tests/152_add_db_privilege.pp b/tests/152_add_db_privilege.pp new file mode 100644 index 0000000..6dd00d1 --- /dev/null +++ b/tests/152_add_db_privilege.pp @@ -0,0 +1,8 @@ +err("Grant DELETE to test_user@%test_db") + +mysql_grant { + "test_user@%test_db": + privileges => [ "select_priv", 'insert_priv', 'delete_priv'], +} + + diff --git a/tests/153_change_db_priv.pp b/tests/153_change_db_priv.pp new file mode 100644 index 0000000..f72dab8 --- /dev/null +++ b/tests/153_change_db_priv.pp @@ -0,0 +1,8 @@ +err("Change DELETE to UPDATE privilege for test_user@%test_db") + +mysql_grant { + "test_user@%test_db": + privileges => [ "select_priv", 'insert_priv', 'update_priv'], +} + + diff --git a/tests/154_mix_db_grants.pp b/tests/154_mix_db_grants.pp new file mode 100644 index 0000000..408308f --- /dev/null +++ b/tests/154_mix_db_grants.pp @@ -0,0 +1,8 @@ +err("Change privilege order") + +mysql_grant { + "test_user@%test_db": + privileges => [ "update_priv", 'insert_priv', 'select_priv'], +} + + diff --git a/tests/200_give_all_user_privs.pp b/tests/200_give_all_user_privs.pp new file mode 100644 index 0000000..cb59c8d --- /dev/null +++ b/tests/200_give_all_user_privs.pp @@ -0,0 +1,8 @@ +err("Grant ALL to test_user@%") + +mysql_grant { + "test_user@%": + privileges => all +} + + diff --git a/tests/201_give_all_db_privs.pp b/tests/201_give_all_db_privs.pp new file mode 100644 index 0000000..745048f --- /dev/null +++ b/tests/201_give_all_db_privs.pp @@ -0,0 +1,8 @@ +err("Grant ALL to test_user@%/test_db") + +mysql_grant { + "test_user@%/test_db": + privileges => all +} + + diff --git a/tests/996_remove_db_grant.pp b/tests/996_remove_db_grant.pp new file mode 100644 index 0000000..a93c2a3 --- /dev/null +++ b/tests/996_remove_db_grant.pp @@ -0,0 +1,5 @@ +err("Remove the db grant") + +mysql_grant { "test_user@%test_db": privileges => [ ] } + + diff --git a/tests/997_remove_user_grant.pp b/tests/997_remove_user_grant.pp new file mode 100644 index 0000000..fcdc490 --- /dev/null +++ b/tests/997_remove_user_grant.pp @@ -0,0 +1,5 @@ +err("Removing the user grant") + +mysql_grant { "test_user@%": privileges => [] } + + diff --git a/tests/998_remove_user.pp b/tests/998_remove_user.pp new file mode 100644 index 0000000..649e739 --- /dev/null +++ b/tests/998_remove_user.pp @@ -0,0 +1,3 @@ + +err("Removing user 'test_user@%'") +mysql_user{ "test_user@%": ensure => absent } diff --git a/tests/999_remove_database.pp b/tests/999_remove_database.pp new file mode 100644 index 0000000..8a5df3e --- /dev/null +++ b/tests/999_remove_database.pp @@ -0,0 +1,3 @@ +err("Will remove 'test_db'") +mysql_database { "test_db": ensure => absent } + diff --git a/tests/README b/tests/README new file mode 100644 index 0000000..7ef1421 --- /dev/null +++ b/tests/README @@ -0,0 +1,6 @@ +Execute these testfile in asciibetical order to check the functioning of the +types and providers. + +They try to create databases, users, grants, check for their existance, change +attributes, and remove them again. + diff --git a/tests/run_tests b/tests/run_tests new file mode 100755 index 0000000..1ae6c42 --- /dev/null +++ b/tests/run_tests @@ -0,0 +1,13 @@ +#!/bin/bash + +export RUBYLIB=${RUBYLIB:-../plugins} +OPTIONS="$*" +OPTIONS="${OPTIONS:---trace}" + +find -iname \*.pp | sort | while read current; do + echo "Running $current" + puppet $OPTIONS $current + echo "Running $current again" + puppet $OPTIONS $current + echo +done -- cgit v1.2.3