Script For Cleaning Up invalid users and clients in Bifrost DB

cleanup_bifrost.rb

require 'pg'

add_command_under_category "cleanup-bifrost", "cleanup", "Cleanup orphaned bifrost objects.", 2 do
  erchef_db = setup_erchef_db
  bifrost_db = setup_bifrost_db

  puts "Populating hash of valid clients and users."
  clients_and_users = erchef_db.exec_params("SELECT authz_id FROM clients UNION select authz_id FROM users")
  real_actor_list = []
  clients_and_users.each do |real_actor|
    authz_id = real_actor['authz_id']
    real_actor_list << authz_id
  end

  puts "#{real_actor_list.length} users and valid clients found."
  real_actor_list_string = "'" + real_actor_list.join("','") + "'"

  total_start_time = Time.now
  window = 100000
  total_deleted = window
  total_actors = bifrost_db.exec_params("SELECT count(*) FROM auth_actor").first['count'].to_i
  remaining = "GO"
  sql = "DELETE FROM auth_actor WHERE id in ( SELECT id FROM auth_actor WHERE authz_id NOT in ( #{real_actor_list_string} ) LIMIT #{window})"

  while remaining != "DELETE 0"
    batch_start_time = Time.now
    results = bifrost_db.exec_params(sql)
    puts "#{total_deleted} examined of #{total_actors - real_actor_list.length}. Batch finished in #{Time.now - batch_start_time} seconds. Deleting next batch."
    total_deleted += window
    remaining = results.cmd_status
  end

  puts "#{total_actors - real_actor_list.length} orphaned authz actors deleted in #{Time.now - total_start_time} seconds."
end

def setup_erchef_db
  running_config = JSON.parse(File.read("/etc/opscode/chef-server-running.json"))
  erchef_config = running_config['private_chef']['postgresql']
  pg_config = running_config['private_chef']['postgresql']
  ::PGconn.open('user' => erchef_config['sql_user'],
                'host' => pg_config['vip'],
                'password' => erchef_config['sql_password'],
                'port' => pg_config['port'],
                'dbname' => 'opscode_chef')
end

def setup_bifrost_db
  running_config = JSON.parse(File.read("/etc/opscode/chef-server-running.json"))
  bifrost_config = running_config['private_chef']['oc_bifrost']
  pg_config = running_config['private_chef']['postgresql']
  ::PGconn.open('user' => bifrost_config['sql_user'],
                'host' => pg_config['vip'],
                'password' => bifrost_config['sql_password'],
                'port' => pg_config['port'],
                'dbname' => 'bifrost')
end