From 813025c80066504345e72ead609075751fbc5b80 Mon Sep 17 00:00:00 2001 From: root Date: Thu, 18 Oct 2007 11:28:00 +0200 Subject: mysql: prefetching, tests, order independent privileges This commit implements prefetching for the mysql_database and the mysql_user types. This enables them to e.g. set the password_hash right when creating the user. There is now a directory with tests. Privileges are now order independent and do not cause spurious notices anymore. --- plugins/puppet/provider/mysql_database/mysql.rb | 41 +++++++++++++++-- plugins/puppet/provider/mysql_grant/mysql.rb | 57 ++++++++++++----------- plugins/puppet/provider/mysql_user/mysql.rb | 61 +++++++++++++++++++++---- plugins/puppet/type/mysql_grant.rb | 44 +++++++++++++++++- plugins/puppet/type/mysql_user.rb | 5 +- 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/996_remove_db_grant.pp | 5 ++ tests/997_remove_user_grant.pp | 5 ++ tests/998_remove_user.pp | 3 ++ tests/999_remove_database.pp | 4 ++ tests/README | 6 +++ tests/run_tests | 13 ++++++ 23 files changed, 290 insertions(+), 45 deletions(-) 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/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/plugins/puppet/provider/mysql_database/mysql.rb b/plugins/puppet/provider/mysql_database/mysql.rb index b12d042..2b70e04 100644 --- a/plugins/puppet/provider/mysql_database/mysql.rb +++ b/plugins/puppet/provider/mysql_database/mysql.rb @@ -1,7 +1,41 @@ -Puppet::Type.type(:mysql_database).provide(:mysql) do +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 :mysqlshow => '/usr/bin/mysqlshow' + 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] @@ -9,8 +43,9 @@ Puppet::Type.type(:mysql_database).provide(:mysql) do def destroy mysqladmin "-f", "drop", @resource[:name] end + def exists? - if /\| #{@resource[:name]} /.match(mysqlshow) + if mysql("mysql", "-NBe", "show databases").match(/^#{@resource[:name]}$/) true else false diff --git a/plugins/puppet/provider/mysql_grant/mysql.rb b/plugins/puppet/provider/mysql_grant/mysql.rb index 5122d2e..9d900ec 100644 --- a/plugins/puppet/provider/mysql_grant/mysql.rb +++ b/plugins/puppet/provider/mysql_grant/mysql.rb @@ -3,8 +3,11 @@ # user@host => global # user@host/db => per-db +require 'puppet/provider/package' + Puppet::Type.type(:mysql_grant).provide(:mysql) do - desc "Use mysql as database." + + desc "Uses mysql as database." commands :mysql => '/usr/bin/mysql' @@ -28,36 +31,38 @@ Puppet::Type.type(:mysql_grant).provide(:mysql) do end end - def create - name = split_name(@resource[:name]) - case name[:type] - when :user - mysql "mysql", "-e", "INSERT INTO user (host, user, %s) VALUES ('%s', '%s', %s)" % [ - @resource.should(:privileges).join(","), - name[:host], name[:user], - @resource.should(:privileges).map do |m| "'Y'" end.join(",") - ] - when :db - mysql "mysql", "-e", "INSERT INTO db (host, user, db, %s) VALUES ('%s', '%s', '%s', %s)" % [ - @resource.should(:privileges).join(","), - name[:host], name[:user], name[:db], - @resource.should(:privileges).map do |m| "'Y'" end.join(",") - ] + 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 end end + def destroy - #mysql "mysql", "-e", "REVOKE %s ON '%s'.* FROM '%s@%s'" % [ @resource[:privileges], @resource[:database], @resource[:name], @resource[:host] ] + mysql "mysql", "-e", "REVOKE ALL ON '%s'.* FROM '%s@%s'" % [ @resource[:privileges], @resource[:database], @resource[:name], @resource[:host] ] end - def exists? + def row_exists? name = split_name(@resource[:name]) fields = [:user, :host] if name[:type] == :db fields << :db end - mysql( "mysql", "-Be", 'SELECT user FROM %s WHERE %s' % [ name[:type], fields.map do |f| "%s = '%s'" % [f, name[f]] end.join(' AND ')]) + 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 + # privileges "exist" always, it's just the setting we are interested in + # def exists? @resource.should( end + def privileges name = split_name(@resource[:name]) privs = "" @@ -98,9 +103,11 @@ Puppet::Type.type(:mysql_grant).provide(:mysql) do :create_view_priv, :show_view_priv, :create_routine_priv, :alter_routine_priv, :execute_priv ] - begin + unless row_exists? + create_row + end - puts "Setting privs: ", privs.join(", ") + # puts "Setting privs: ", privs.join(", ") name = split_name(@resource[:name]) stmt = '' where = '' @@ -116,16 +123,12 @@ Puppet::Type.type(:mysql_grant).provide(:mysql) do all_privs = db_privs end - puts "stmt:", stmt + # puts "stmt:", stmt set = all_privs.collect do |p| "%s = '%s'" % [p, privs.include?(p) ? 'Y' : 'N'] end.join(', ') - puts "set:", set + # puts "set:", set stmt = stmt << set << where mysql "mysql", "-Be", stmt - rescue NoMethodError => detail - puts detail.backtrace - puts detail.to_s - end end end diff --git a/plugins/puppet/provider/mysql_user/mysql.rb b/plugins/puppet/provider/mysql_user/mysql.rb index 1c48d71..8238620 100644 --- a/plugins/puppet/provider/mysql_user/mysql.rb +++ b/plugins/puppet/provider/mysql_user/mysql.rb @@ -1,27 +1,68 @@ -Puppet::Type.type(:mysql_user).provide(:mysql) do +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' + # 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 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@%s' identified by '%s'" % [ @resource[:name], @resource[:host], @resource[:password] ] + mysql "mysql", "-e", "create user '%s' identified by PASSWORD '%s'" % [ @resource[:name].sub("@", "'@'"), @resource.should(:password_hash) ] end + def destroy - mysql "mysql", "-e", "drop user '%s@%s'" % [ @resource[:name], @resource[:host] ] + mysql "mysql", "-e", "drop user '%s'" % @resource[:name].sub("@", "'@'") end + def exists? - if /^#{@resource[:name]}@#{@resource[:host]}$/.match( mysql( "mysql", "-Be", 'SELECT CONCAT(user, "@", host) FROM user' ) ) - true - else - false - end + not mysql("mysql", "-NBe", "select '1' from user where CONCAT(user, '@', host) = '%s'" % @resource[:name]).empty? end def password_hash - mysql("mysql", "-NBe", "select password from user where user='#{@resource[:name]}' and host='#{@resource[:host]}'").chomp + @property_hash[:password_hash] end def password_hash=(string) - mysql "mysql", "-e", "SET PASSWORD FOR '#{@resource[:name]}'@'#{@resource[:host]}' = '#{string}'" + mysql "mysql", "-e", "SET PASSWORD FOR '%s' = '%s'" % [ @resource[:name].sub("@", "'@'"), string ] end end diff --git a/plugins/puppet/type/mysql_grant.rb b/plugins/puppet/type/mysql_grant.rb index cc0c0e8..8a858e6 100644 --- a/plugins/puppet/type/mysql_grant.rb +++ b/plugins/puppet/type/mysql_grant.rb @@ -1,7 +1,17 @@ # This has to be a separate type to enable collecting Puppet::Type.newtype(:mysql_grant) do @doc = "Manage a database user's rights." - ensurable + #ensurable + + autorequire :mysql_user do + reqs = [] + matches = self[:name].match(/^([^@]+)@([^\/]+)$/) + unless matches.nil? + reqs << "%s@%s" % [ matches[0], matches[1] ] + end + reqs + end + newparam(:name) do desc "The primary key: either user@host for global privilges or user@host/database for database specific privileges" end @@ -10,6 +20,38 @@ Puppet::Type.newtype(:mysql_grant) do 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 + self.is_to_s(is) == self.should_to_s + else + true + end + end + end end diff --git a/plugins/puppet/type/mysql_user.rb b/plugins/puppet/type/mysql_user.rb index d0756de..d92b610 100644 --- a/plugins/puppet/type/mysql_user.rb +++ b/plugins/puppet/type/mysql_user.rb @@ -3,10 +3,7 @@ Puppet::Type.newtype(:mysql_user) do @doc = "Manage a database user." ensurable newparam(:name) do - desc "The name of the user." - end - newparam(:host) do - desc "The host from where to connect." + desc "The name of the user. This uses the 'username@hostname' form." end newproperty(:password_hash) do desc "The password hash of the user. Use mysql_password() for creating such a hash." 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..f2b52f9 --- /dev/null +++ b/tests/150_create_db_grant.pp @@ -0,0 +1,9 @@ +err("Create a db grant") + +mysql_grant { + "test_user@%/test_user": + 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..4eae44b --- /dev/null +++ b/tests/151_remove_db_privilege.pp @@ -0,0 +1,8 @@ +err("Revoke UPDATE from test_user@%/test_user") + +mysql_grant { + "test_user@%/test_user": + 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..21dae54 --- /dev/null +++ b/tests/152_add_db_privilege.pp @@ -0,0 +1,8 @@ +err("Grant DELETE to test_user@%/test_user") + +mysql_grant { + "test_user@%/test_user": + 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..a317052 --- /dev/null +++ b/tests/153_change_db_priv.pp @@ -0,0 +1,8 @@ +err("Change DELETE to UPDATE privilege for test_user@%/test_user") + +mysql_grant { + "test_user@%/test_user": + privileges => [ "select_priv", 'insert_priv', 'update_priv'], +} + + diff --git a/tests/996_remove_db_grant.pp b/tests/996_remove_db_grant.pp new file mode 100644 index 0000000..e05aea8 --- /dev/null +++ b/tests/996_remove_db_grant.pp @@ -0,0 +1,5 @@ +err("Remove the db grant") + +mysql_grant { "test_user@%/test_user": 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..34e224d --- /dev/null +++ b/tests/999_remove_database.pp @@ -0,0 +1,4 @@ + +err("Will remove 'test_db' again") +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