class Geordi::DBCleaner

Public Class Methods

new(extra_flags, sudo: false) click to toggle source
# File lib/geordi/db_cleaner.rb, line 11
def initialize(extra_flags, sudo: false)
  @sudo = sudo

  if @sudo
    Interaction.note 'Please enter your sudo password when asked.'
    puts "We're going to run `sudo -u postgres psql` for PostgreSQL"
    puts '               and `sudo mysql`            for MariaDB (which uses PAM auth)'
    `sudo true`
    Interaction.fail 'sudo access is required for database operations as database users' if $? != 0
  end

  @derivative_dbname = /_(test\d*|development|cucumber)$/
  @base_directory = ENV['XDG_CONFIG_HOME']
  @base_directory ||= Dir.home.to_s
  @allowlist_directory = File.join(@base_directory, '.config', 'geordi', 'allowlists')
  FileUtils.mkdir_p(@allowlist_directory) unless File.directory? @allowlist_directory
  if File.directory?(legacy_allowlist_directory)
    move_allowlist_files
  end
  @mysql_command = decide_mysql_command(extra_flags['mysql'])
  @postgres_command = decide_postgres_command(extra_flags['postgres'])
end

Public Instance Methods

allowlist_fname(dbtype) click to toggle source
# File lib/geordi/db_cleaner.rb, line 224
def allowlist_fname(dbtype)
  File.join(@allowlist_directory, dbtype) << '.txt'
end
clean_mysql() click to toggle source
# File lib/geordi/db_cleaner.rb, line 197
def clean_mysql
  Interaction.announce 'Checking for MySQL databases'
  database_list = list_all_dbs('mysql')
  # confirm_deletion includes option for allowlist editing
  deletable_dbs = confirm_deletion('mysql', database_list)
  return if deletable_dbs.nil?
  deletable_dbs.each do |db|
    if @mysql_command.include? '-p'
      Interaction.note "Please enter your MySQL/MariaDB account 'root' for: DROP DATABASE #{db}"
    else
      puts "Dropping MySQL/MariaDB database #{db}"
    end
    `#{@mysql_command} -e 'DROP DATABASE \`#{db}\`;'`
  end
end
clean_postgres() click to toggle source
# File lib/geordi/db_cleaner.rb, line 213
def clean_postgres
  Interaction.announce 'Checking for PostgreSQL databases'
  database_list = list_all_dbs('postgres')
  deletable_dbs = confirm_deletion('postgres', database_list)
  return if deletable_dbs.nil?
  deletable_dbs.each do |db|
    Interaction.note "Dropping PostgreSQL database `#{db}`."
    `#{@postgres_command} -c 'DROP DATABASE "#{db}";'`
  end
end
edit_allowlist(dbtype) click to toggle source
# File lib/geordi/db_cleaner.rb, line 34
    def edit_allowlist(dbtype)
      allowlist = allowlist_fname(dbtype)
      allowlisted_dbs = if File.exist? allowlist
        Geordi::Util.stripped_lines(File.read(allowlist))\
          .delete_if { |l| l.start_with? '#' }
      else
        []
      end
      all_dbs = list_all_dbs(dbtype)
      tmp = Tempfile.open("geordi_allowlist_#{dbtype}")
      tmp.write <<~HEREDOC
        # Put each allowlisted database on a new line.
        # System databases will never be deleted.
        # When you allowlist foo, foo_development and foo_test\\d* are allowlisted, too.
        # This works even if foo does not exist. Also, you will only see foo in this list.
        #
        # Syntax: keep foo
        #         drop bar
      HEREDOC
      tmpfile_content = Array.new
      all_dbs.each do |db|
        next if is_allowlisted?(dbtype, db)
        next if is_protected?(dbtype, db)
        db.sub!(@derivative_dbname, '')
        tmpfile_content.push(['drop', db])
      end
      warn_manual_allowlist = false
      allowlisted_dbs.each do |db_name|
        # Remove 'keep' word from allowlist entries. This is not normally required since geordi
        # does not save 'keep' or 'drop' to the allowlist file on disk but rather saves a list
        # of all allowlisted db names and just presents the keep/drop information while editing
        # the allowlist to supply users a list of databases they can allowlist by changing the
        # prefix to 'keep'. Everything prefixed 'drop' is not considered allowlisted and thus
        # not written to the allowlist file on disk.
        #
        # However, if users manually edit their allowlist files they might use the keep/drop
        # syntax they're familiar with.
        if db_name.start_with? 'keep '
          db_name.gsub!(/keep /, '')
          db_name = db_name.split[1..-1].join(' ')
          warn_manual_allowlist = true
        end
        tmpfile_content.push(['keep', db_name]) unless db_name.empty?
      end
      if warn_manual_allowlist
        Interaction.warn <<~ERROR_MSG
          Your allowlist #{allowlist} seems to have been generated manually.
          In that case, make sure to use only one database name per line and omit the 'keep' prefix."

          Launching the editor.
        ERROR_MSG
      end
      tmpfile_content.sort_by! { |k| k[1] }
      tmpfile_content.uniq!
      tmpfile_content.each do |line|
        tmp.write("#{line[0]} #{line[1]}\n")
      end
      tmp.close
      texteditor = Geordi::Util.decide_texteditor
      system("#{texteditor} #{tmp.path}")
      File.open(tmp.path, 'r') do |wl_edited|
        allowlisted_dbs = []
        allowlist_storage = File.open(allowlist, 'w')
        lines = Geordi::Util.stripped_lines(wl_edited.read)
        lines.each do |line|
          next if line.start_with?('#')
          unless line.split.length == 2
            Interaction.fail "Invalid edit to allowlist file: \`#{line}\` - Syntax is: ^[keep|drop] dbname$"
          end
          unless %w[keep drop k d].include? line.split.first
            Interaction.fail "Invalid edit to allowlist file: \`#{line}\` - must start with either drop or keep."
          end
          db_status, db_name = line.split
          if db_status == 'keep'
            allowlisted_dbs.push db_name
            allowlist_storage.write(db_name << "\n")
          end
        end
        allowlist_storage.close
      end
    end
is_allowlisted?(dbtype, database_name) click to toggle source
# File lib/geordi/db_cleaner.rb, line 270
def is_allowlisted?(dbtype, database_name)
  allowlist_content = if File.exist? allowlist_fname(dbtype)
    Geordi::Util.stripped_lines(File.open(allowlist_fname(dbtype), 'r').read)
  else
    []
  end
  # Allow explicit allowlisting of derivative databases like projectname_test2
  if allowlist_content.include? database_name
    true
  # allowlisting `projectname` also allowlists `projectname_test\d*`, `projectname_development`
  elsif allowlist_content.include? database_name.sub(@derivative_dbname, '')
    true
  else
    false
  end
end
is_protected?(dbtype, database_name) click to toggle source
# File lib/geordi/db_cleaner.rb, line 262
def is_protected?(dbtype, database_name)
  protected = {
    'mysql'    => %w[mysql information_schema performance_schema sys],
    'postgres' => ['postgres'],
  }
  protected[dbtype].include? database_name
end
legacy_allowlist_directory() click to toggle source
# File lib/geordi/db_cleaner.rb, line 297
def legacy_allowlist_directory
  @legacy_allowlist_directory ||= File.join(@base_directory, '.config', 'geordi', 'whitelists')
end
list_all_dbs(dbtype) click to toggle source
# File lib/geordi/db_cleaner.rb, line 168
def list_all_dbs(dbtype)
  if dbtype == 'postgres'
    list_all_postgres_dbs
  else
    list_all_mysql_dbs
  end
rescue DatabaseError
  Interaction.fail 'Connection to database could not be established. Try running again with --sudo.'
end
list_all_mysql_dbs() click to toggle source
# File lib/geordi/db_cleaner.rb, line 186
def list_all_mysql_dbs
  if @mysql_command.include? '-p'
    Interaction.note "Please enter your MySQL/MariaDB account 'root' for: list all databases"
  end
  output, _error, status = Open3.capture3("#{@mysql_command} -B -N -e 'show databases'")

  raise DatabaseError unless status.success?

  output.split
end
list_all_postgres_dbs() click to toggle source
# File lib/geordi/db_cleaner.rb, line 178
def list_all_postgres_dbs
  output, _error, status = Open3.capture3("#{@postgres_command} -t -A -c 'SELECT DATNAME FROM pg_database WHERE datistemplate = false'")

  raise DatabaseError unless status.success?

  output.split
end
move_allowlist_files() click to toggle source
# File lib/geordi/db_cleaner.rb, line 301
def move_allowlist_files
  %w[postgres mysql].each do |dbtype|
    new_path = allowlist_fname(dbtype)
    next if File.exist?(new_path)

    legacy_path = File.join(legacy_allowlist_directory, dbtype) << '.txt'
    FileUtils.mv(legacy_path, new_path)

    if Dir.exist?(legacy_allowlist_directory) && Dir.empty?(legacy_allowlist_directory)
      Dir.delete(legacy_allowlist_directory)
    end
  end
end

Private Instance Methods

confirm_deletion(dbtype, database_list) click to toggle source
# File lib/geordi/db_cleaner.rb, line 228
def confirm_deletion(dbtype, database_list)
  proceed = ''
  until %w[y n].include? proceed
    deletable_dbs = filter_allowlisted(dbtype, database_list)
    if deletable_dbs.empty?
      Interaction.note "No #{dbtype} databases found that were not allowlisted."
      if Interaction.prompt('Edit the allowlist? [y]es or [n]o') == 'y'
        proceed = 'e'
      else
        return []
      end
    end
    if proceed.empty?
      Interaction.note "The following #{dbtype} databases are not allowlisted and can be deleted:"
      deletable_dbs.sort.each do |db|
        puts db
      end
      Interaction.note "These #{dbtype} databases are not allowlisted and can be deleted."
      proceed = Interaction.prompt('Proceed? [y]es, [n]o or [e]dit allowlist')
    end
    case proceed
    when 'e'
      proceed = '' # reset user selection
      edit_allowlist dbtype
    when 'n'
      Interaction.note 'Nothing deleted.'
      return []
    when 'y'
      return deletable_dbs
    end
  end
end
decide_mysql_command(extra_flags) click to toggle source
# File lib/geordi/db_cleaner.rb, line 116
def decide_mysql_command(extra_flags)
  cmd = @sudo ? 'sudo mysql' : 'mysql'
  unless extra_flags.nil?
    if extra_flags.include? 'port'
      port = Integer(extra_flags.split('=')[1].split[0])
      Interaction.fail "Port #{port} is not open" unless Geordi::Util.is_port_open? port
    end
    cmd << " #{extra_flags}"
  end

  if @sudo
    Open3.popen3("#{cmd} -e 'QUIT'") do |_stdin, _stdout, stderr, thread|
      break if thread.value.exitstatus == 0
      # sudo mysql was not successful, switching to mysql-internal user management
      mysql_error = stderr.read.lines[0].chomp.strip.split[1]
      if %w[1045 1698].include? mysql_error # authentication failed
        cmd = 'mysql -uroot'
        cmd << " #{extra_flags}" unless extra_flags.nil?
        unless File.exist? File.join(Dir.home, '.my.cnf')
          Interaction.note "Please enter your MySQL/MariaDB password for account 'root'."
          Interaction.warn "You should create a ~/.my.cnf file instead, or you'll need to enter your MySQL root password for each db."
          Interaction.note 'See https://makandracards.com/makandra/50813-store-mysql-passwords-for-development for more information.'
          cmd << ' -p' # need to ask for password now
        end
        Open3.popen3("#{cmd} -e 'QUIT'") do |_stdin_2, _stdout_2, _stderr_2, thread_2|
          Interaction.fail 'Could not connect to MySQL/MariaDB' unless thread_2.value.exitstatus == 0
        end
      elsif mysql_error == '2013' # connection to port or socket failed
        Interaction.fail 'MySQL/MariaDB connection failed, is this the correct port?'
      end
    end
  end
  cmd
end
decide_postgres_command(extra_flags) click to toggle source
# File lib/geordi/db_cleaner.rb, line 152
def decide_postgres_command(extra_flags)
  cmd = @sudo ? 'sudo -u postgres psql' : 'psql'
  unless extra_flags.nil?
    begin
      port = Integer(extra_flags.split('=')[1])
      Interaction.fail "Port #{port} is not open" unless Geordi::Util.is_port_open? port
    rescue ArgumentError
      socket = extra_flags.split('=')[1]
      Interaction.fail "Socket #{socket} does not exist" unless File.exist? socket
    end
    cmd << " #{extra_flags}"
  end
  cmd
end
filter_allowlisted(dbtype, database_list) click to toggle source
# File lib/geordi/db_cleaner.rb, line 287
def filter_allowlisted(dbtype, database_list)
  # n.b. `delete` means 'delete from list of dbs that should be deleted in this context
  # i.e. `delete` means 'keep this database'
  deletable_dbs = database_list.dup
  deletable_dbs.delete_if { |db| is_allowlisted?(dbtype, db) if File.exist? allowlist_fname(dbtype) }
  deletable_dbs.delete_if { |db| is_protected?(dbtype, db) }
  deletable_dbs.delete_if { |db| db.start_with? '#' }
end