site_utils/mysql_bootstrap.py

#!/usr/bin/python
# Copyright (c) 2014 The Chromium OS Authors. All rights reserved.
# Use of this source code is governed by a BSD-style license that can be
# found in the LICENSE file.

"""Bootstrap mysql.

The purpose of this module is to grant access to a new-user/host/password
combination on a remote db server. For example, if we were bootstrapping
a new autotest master A1 with a remote database server A2, the scheduler
running on A1 needs to access the database on A2 with the credentials
specified in the shadow_config of A1 (A1_user, A1_pass). To achieve this
we ssh into A2 and execute the grant privileges command for (A1_user,
A1_pass, A1_host). If OTOH the db server is running locally we only need
to grant permissions for (A1_user, A1_pass, localhost).

The operation to achieve this will look like:
    ssh/become into A2
    Execute mysql -u <default_user> -p<default_pass> -e
        "GRANT privileges on <db> to 'A1_user'@A1 identified by 'A1_pass';"

However this will only grant the right access permissions to A1, so we need
to repeat for all subsequent db clients we add. This will happen through puppet.

In the case of a vagrant cluster, a remote vm cannot ssh into the db server
vm with plain old ssh. However, the entire vm cluster is provisioned at the
same time, so we can grant access to all remote vm clients directly on the
database server without knowing their ips by using the ip of the gateway.
This works because the db server vm redirects its database port (3306) to
a predefined port (defined in the vagrant file, defaults to 8002), and all
other vms in the cluster can only access it through the vm host identified
by the gateway.

The operation to achieve this will look like:
    Provision the vagrant db server
    Execute mysql -u <default_user> -p<default_pass> -e
        "GRANT privileges on <db> to 'A1_user'@(gateway address)
         identified by 'A1_pass';"
This will grant the right access permissions to all vms running on the
host machine as long as they use the right port to access the database.
"""

import argparse
import logging
import socket
import subprocess
import sys

import common

from autotest_lib.client.common_lib import global_config
from autotest_lib.client.common_lib import utils
from autotest_lib.site_utils.lib import infra


class MySQLCommandError(Exception):
    """Generic mysql command execution exception."""


class MySQLCommandExecutor(object):
    """Class to shell out to mysql.

    USE THIS CLASS WITH CARE. It doesn't protect against SQL injection on
    assumption that anyone with access to our servers can run the same
    commands directly instead of through this module. Do not expose it
    through a webserver, it is meant solely as a utility module to allow
    easy database bootstrapping via puppet.
    """

    DEFAULT_USER = global_config.global_config.get_config_value(
            'AUTOTEST_WEB', 'default_db_user', default='root')

    DEFAULT_PASS = global_config.global_config.get_config_value(
            'AUTOTEST_WEB', 'default_db_pass', default='autotest')


    @classmethod
    def mysql_cmd(cls, cmd, user=DEFAULT_USER, password=DEFAULT_PASS,
                  host='localhost', port=3306):
        """Wrap the given mysql command.

        @param cmd: The mysql command to wrap with the --execute option.
        @param host: The host against which to run the command.
        @param user: The user to use in the given command.
        @param password: The password for the user.
        @param port: The port mysql server is listening on.
        """
        return ('mysql -u %s -p%s --host %s --port %s -e "%s"' %
                (user, password, host, port, cmd))


    @staticmethod
    def execute(dest_server, full_cmd):
        """Execute a mysql statement on a remote server by sshing into it.

        @param dest_server: The hostname of the remote mysql server.
        @param full_cmd: The full mysql command to execute.

        @raises MySQLCommandError: If the full_cmd failed on dest_server.
        """
        try:
            return infra.execute_command(dest_server, full_cmd)
        except subprocess.CalledProcessError as e:
            raise MySQLCommandError('Failed to execute %s against %s' %
                                    (full_cmd, dest_server))


    @classmethod
    def ping(cls, db_server, user=DEFAULT_USER, password=DEFAULT_PASS,
             use_ssh=False):
        """Ping the given db server as 'user' using 'password'.

        @param db_server: The host running the mysql server.
        @param user: The user to use in the ping.
        @param password: The password of the user.
        @param use_ssh: If False, the command is executed on localhost
            by supplying --host=db_server in the mysql command. Otherwise we
            ssh/become into the db_server and execute the command with
            --host=localhost.

        @raises MySQLCommandError: If the ping command fails.
        """
        if use_ssh:
            ssh_dest_server = db_server
            mysql_cmd_host = 'localhost'
        else:
            ssh_dest_server = 'localhost'
            mysql_cmd_host = db_server
        ping = cls.mysql_cmd(
                'SELECT version();', host=mysql_cmd_host, user=user,
                password=password)
        cls.execute(ssh_dest_server, ping)


def bootstrap(user, password, source_host, dest_host):
    """Bootstrap the given user against dest_host.

    Allow a user from source_host to access the db server running on
    dest_host.

    @param user: The user to bootstrap.
    @param password: The password for the user.
    @param source_host: The host from which the new user will access the db.
    @param dest_host: The hostname of the remote db server.

    @raises MySQLCommandError: If we can't ping the db server using the default
        user/password specified in the shadow_config under default_db_*, or
        we can't ping it with the new credentials after bootstrapping.
    """
    # Confirm ssh/become access.
    try:
        infra.execute_command(dest_host, 'echo "hello"')
    except subprocess.CalledProcessError as e:
        logging.error("Cannot become/ssh into dest host. You need to bootstrap "
                      "it using fab -H <hostname> bootstrap from the "
                      "chromeos-admin repo.")
        return
    # Confirm the default user has at least database read privileges. Note if
    # the default user has *only* read privileges everything else will still
    # fail. This is a remote enough case given our current setup that we can
    # avoid more complicated checking at this level.
    MySQLCommandExecutor.ping(dest_host, use_ssh=True)

    # Prepare and execute the grant statement for the new user.
    creds = {
        'new_user': user,
        'new_pass': password,
        'new_host': source_host,
    }
    # TODO(beeps): Restrict these permissions. For now we have a couple of
    # databases which may/may-not exist on various roles that need refactoring.
    grant_privileges = (
        "GRANT ALL PRIVILEGES ON *.* to '%(new_user)s'@'%(new_host)s' "
        "IDENTIFIED BY '%(new_pass)s'; FLUSH PRIVILEGES;")
    MySQLCommandExecutor.execute(
            dest_host, MySQLCommandExecutor.mysql_cmd(grant_privileges % creds))

    # Confirm the new user can ping the remote database server from localhost.
    MySQLCommandExecutor.ping(
            dest_host, user=user, password=password, use_ssh=False)


def get_gateway():
    """Return the address of the default gateway.

    @raises: subprocess.CalledProcessError: If the address of the gateway
        cannot be determined via netstat.
    """
    cmd = 'netstat -rn | grep "^0.0.0.0 " | cut -d " " -f10 | head -1'
    try:
        return infra.execute_command('localhost', cmd).rstrip('\n')
    except subprocess.CalledProcessError as e:
        logging.error('Unable to get gateway: %s', e)
        raise


def _parse_args(args):
    parser = argparse.ArgumentParser(description='A script to bootstrap mysql '
                                     'with credentials from the shadow_config.')
    parser.add_argument(
            '--enable_gateway', action='store_true', dest='enable_gateway',
            default=False, help='Enable gateway access for vagrant testing.')
    return parser.parse_args(args)


def main(argv):
    """Main bootstrapper method.

    Grants permissions to the appropriate user on localhost, then enables the
    access through the gateway if --enable_gateway is specified.
    """
    args = _parse_args(argv)
    dest_host = global_config.global_config.get_config_value(
            'AUTOTEST_WEB', 'host')
    user = global_config.global_config.get_config_value(
            'AUTOTEST_WEB', 'user')
    password = global_config.global_config.get_config_value(
            'AUTOTEST_WEB', 'password')

    # For access via localhost, one needs to specify localhost as the hostname.
    # Neither the ip or the actual hostname of localhost will suffice in
    # mysql version 5.5, without complications.
    local_hostname = ('localhost' if utils.is_localhost(dest_host)
                      else socket.gethostname())
    logging.info('Bootstrapping user %s on host %s against db server %s',
                 user, local_hostname, dest_host)
    bootstrap(user, password, local_hostname, dest_host)

    if args.enable_gateway:
        gateway = get_gateway()
        logging.info('Enabling access through gateway %s', gateway)
        bootstrap(user, password, gateway, dest_host)


if __name__ == '__main__':
    sys.exit(main(sys.argv[1:]))