Revert "Revert "[autotest] Bootstrapping mysql during server bringup.""
This reverts commit bd92448fc67407d67e07d8bbebb120c7016b70a2.
We think the CQ failure was an anomaly caused by pushing to
prod, and not a bug in the CL.
Change-Id: Ic46332d28d21969e6546a5a1ab1c1af8120583b2
Reviewed-on: https://chromium-review.googlesource.com/228362
Reviewed-by: Richard Barnette <jrbarnette@chromium.org>
Tested-by: Richard Barnette <jrbarnette@chromium.org>
diff --git a/site_utils/mysql_bootstrap.py b/site_utils/mysql_bootstrap.py
new file mode 100755
index 0000000..9383121
--- /dev/null
+++ b/site_utils/mysql_bootstrap.py
@@ -0,0 +1,236 @@
+#!/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:]))
diff --git a/tko/db.py b/tko/db.py
index 35efbc1..2336490 100644
--- a/tko/db.py
+++ b/tko/db.py
@@ -81,6 +81,22 @@
self.max_delay = get_value("AUTOTEST_WEB", "global_db_max_retry_delay",
"max_retry_delay", type=int, default=60)
+ # TODO(beeps): Move this to django settings once we have routers.
+ # On test instances mysql connects through a different port. No point
+ # piping this through our entire infrastructure when it is only really
+ # used for testing; Ideally we would specify this through django
+ # settings and default it to the empty string so django will figure out
+ # the default based on the database backend (eg: mysql, 3306), but until
+ # we have database routers in place any django settings will apply to
+ # both tko and afe.
+ # The intended use of this port is to allow a testing shard vm to
+ # update the master vm's database with test results. Specifying
+ # and empty string will fallback to not even specifying the port
+ # to the backend in tko/db.py. Unfortunately this means retries
+ # won't work on the test cluster till we've migrated to routers.
+ self.port = global_config.global_config.get_config_value(
+ "AUTOTEST_WEB", "global_db_port", type=str, default='')
+
def _init_db(self):
# make sure we clean up any existing connection
@@ -90,7 +106,7 @@
# create the db connection and cursor
self.con = self.connect(self.host, self.database,
- self.user, self.password)
+ self.user, self.password, self.port)
self.cur = self.con.cursor()
diff --git a/tko/db_mysql.py b/tko/db_mysql.py
index 02bf269..edd6863 100644
--- a/tko/db_mysql.py
+++ b/tko/db_mysql.py
@@ -3,6 +3,13 @@
import db
class db_mysql(db.db_sql):
- def connect(self, host, database, user, password):
- return driver.connect(host=host, user=user,
- passwd=password, db=database)
+ def connect(self, host, database, user, password, port):
+ connection_args = {
+ 'host': host,
+ 'user': user,
+ 'db': database,
+ 'passwd': password,
+ }
+ if port:
+ connection_args['port'] = int(port)
+ return driver.connect(**connection_args)