1#!/usr/bin/python2 2# Copyright (c) 2014 The Chromium OS Authors. All rights reserved. 3# Use of this source code is governed by a BSD-style license that can be 4# found in the LICENSE file. 5 6"""Bootstrap mysql. 7 8The purpose of this module is to grant access to a new-user/host/password 9combination on a remote db server. For example, if we were bootstrapping 10a new autotest master A1 with a remote database server A2, the scheduler 11running on A1 needs to access the database on A2 with the credentials 12specified in the shadow_config of A1 (A1_user, A1_pass). To achieve this 13we ssh into A2 and execute the grant privileges command for (A1_user, 14A1_pass, A1_host). If OTOH the db server is running locally we only need 15to grant permissions for (A1_user, A1_pass, localhost). 16 17The operation to achieve this will look like: 18 ssh/become into A2 19 Execute mysql -u <default_user> -p<default_pass> -e 20 "GRANT privileges on <db> to 'A1_user'@A1 identified by 'A1_pass';" 21 22However this will only grant the right access permissions to A1, so we need 23to repeat for all subsequent db clients we add. This will happen through puppet. 24 25In the case of a vagrant cluster, a remote vm cannot ssh into the db server 26vm with plain old ssh. However, the entire vm cluster is provisioned at the 27same time, so we can grant access to all remote vm clients directly on the 28database server without knowing their ips by using the ip of the gateway. 29This works because the db server vm redirects its database port (3306) to 30a predefined port (defined in the vagrant file, defaults to 8002), and all 31other vms in the cluster can only access it through the vm host identified 32by the gateway. 33 34The operation to achieve this will look like: 35 Provision the vagrant db server 36 Execute mysql -u <default_user> -p<default_pass> -e 37 "GRANT privileges on <db> to 'A1_user'@(gateway address) 38 identified by 'A1_pass';" 39This will grant the right access permissions to all vms running on the 40host machine as long as they use the right port to access the database. 41""" 42 43import argparse 44import logging 45import socket 46import subprocess 47import sys 48 49import common 50 51from autotest_lib.client.common_lib import global_config 52from autotest_lib.client.common_lib import utils 53from autotest_lib.site_utils.lib import infra 54 55 56class MySQLCommandError(Exception): 57 """Generic mysql command execution exception.""" 58 59 60class MySQLCommandExecutor(object): 61 """Class to shell out to mysql. 62 63 USE THIS CLASS WITH CARE. It doesn't protect against SQL injection on 64 assumption that anyone with access to our servers can run the same 65 commands directly instead of through this module. Do not expose it 66 through a webserver, it is meant solely as a utility module to allow 67 easy database bootstrapping via puppet. 68 """ 69 70 DEFAULT_USER = global_config.global_config.get_config_value( 71 'AUTOTEST_WEB', 'default_db_user', default='root') 72 73 DEFAULT_PASS = global_config.global_config.get_config_value( 74 'AUTOTEST_WEB', 'default_db_pass', default='autotest') 75 76 77 @classmethod 78 def mysql_cmd(cls, cmd, user=DEFAULT_USER, password=DEFAULT_PASS, 79 host='localhost', port=3306): 80 """Wrap the given mysql command. 81 82 @param cmd: The mysql command to wrap with the --execute option. 83 @param host: The host against which to run the command. 84 @param user: The user to use in the given command. 85 @param password: The password for the user. 86 @param port: The port mysql server is listening on. 87 """ 88 return ('mysql -u %s -p%s --host %s --port %s -e "%s"' % 89 (user, password, host, port, cmd)) 90 91 92 @staticmethod 93 def execute(dest_server, full_cmd): 94 """Execute a mysql statement on a remote server by sshing into it. 95 96 @param dest_server: The hostname of the remote mysql server. 97 @param full_cmd: The full mysql command to execute. 98 99 @raises MySQLCommandError: If the full_cmd failed on dest_server. 100 """ 101 try: 102 return infra.execute_command(dest_server, full_cmd) 103 except subprocess.CalledProcessError as e: 104 raise MySQLCommandError('Failed to execute %s against %s' % 105 (full_cmd, dest_server)) 106 107 108 @classmethod 109 def ping(cls, db_server, user=DEFAULT_USER, password=DEFAULT_PASS, 110 use_ssh=False): 111 """Ping the given db server as 'user' using 'password'. 112 113 @param db_server: The host running the mysql server. 114 @param user: The user to use in the ping. 115 @param password: The password of the user. 116 @param use_ssh: If False, the command is executed on localhost 117 by supplying --host=db_server in the mysql command. Otherwise we 118 ssh/become into the db_server and execute the command with 119 --host=localhost. 120 121 @raises MySQLCommandError: If the ping command fails. 122 """ 123 if use_ssh: 124 ssh_dest_server = db_server 125 mysql_cmd_host = 'localhost' 126 else: 127 ssh_dest_server = 'localhost' 128 mysql_cmd_host = db_server 129 ping = cls.mysql_cmd( 130 'SELECT version();', host=mysql_cmd_host, user=user, 131 password=password) 132 cls.execute(ssh_dest_server, ping) 133 134 135def bootstrap(user, password, source_host, dest_host): 136 """Bootstrap the given user against dest_host. 137 138 Allow a user from source_host to access the db server running on 139 dest_host. 140 141 @param user: The user to bootstrap. 142 @param password: The password for the user. 143 @param source_host: The host from which the new user will access the db. 144 @param dest_host: The hostname of the remote db server. 145 146 @raises MySQLCommandError: If we can't ping the db server using the default 147 user/password specified in the shadow_config under default_db_*, or 148 we can't ping it with the new credentials after bootstrapping. 149 """ 150 # Confirm ssh/become access. 151 try: 152 infra.execute_command(dest_host, 'echo "hello"') 153 except subprocess.CalledProcessError as e: 154 logging.error("Cannot become/ssh into dest host. You need to bootstrap " 155 "it using fab -H <hostname> bootstrap from the " 156 "chromeos-admin repo.") 157 return 158 # Confirm the default user has at least database read privileges. Note if 159 # the default user has *only* read privileges everything else will still 160 # fail. This is a remote enough case given our current setup that we can 161 # avoid more complicated checking at this level. 162 MySQLCommandExecutor.ping(dest_host, use_ssh=True) 163 164 # Prepare and execute the grant statement for the new user. 165 creds = { 166 'new_user': user, 167 'new_pass': password, 168 'new_host': source_host, 169 } 170 # TODO(beeps): Restrict these permissions. For now we have a couple of 171 # databases which may/may-not exist on various roles that need refactoring. 172 grant_privileges = ( 173 "GRANT ALL PRIVILEGES ON *.* to '%(new_user)s'@'%(new_host)s' " 174 "IDENTIFIED BY '%(new_pass)s'; FLUSH PRIVILEGES;") 175 MySQLCommandExecutor.execute( 176 dest_host, MySQLCommandExecutor.mysql_cmd(grant_privileges % creds)) 177 178 # Confirm the new user can ping the remote database server from localhost. 179 MySQLCommandExecutor.ping( 180 dest_host, user=user, password=password, use_ssh=False) 181 182 183def get_gateway(): 184 """Return the address of the default gateway. 185 186 @raises: subprocess.CalledProcessError: If the address of the gateway 187 cannot be determined via netstat. 188 """ 189 cmd = 'netstat -rn | grep "^0.0.0.0 " | cut -d " " -f10 | head -1' 190 try: 191 return infra.execute_command('localhost', cmd).rstrip('\n') 192 except subprocess.CalledProcessError as e: 193 logging.error('Unable to get gateway: %s', e) 194 raise 195 196 197def _parse_args(args): 198 parser = argparse.ArgumentParser(description='A script to bootstrap mysql ' 199 'with credentials from the shadow_config.') 200 parser.add_argument( 201 '--enable_gateway', action='store_true', dest='enable_gateway', 202 default=False, help='Enable gateway access for vagrant testing.') 203 return parser.parse_args(args) 204 205 206def main(argv): 207 """Main bootstrapper method. 208 209 Grants permissions to the appropriate user on localhost, then enables the 210 access through the gateway if --enable_gateway is specified. 211 """ 212 args = _parse_args(argv) 213 dest_host = global_config.global_config.get_config_value( 214 'AUTOTEST_WEB', 'host') 215 user = global_config.global_config.get_config_value( 216 'AUTOTEST_WEB', 'user') 217 password = global_config.global_config.get_config_value( 218 'AUTOTEST_WEB', 'password') 219 220 # For access via localhost, one needs to specify localhost as the hostname. 221 # Neither the ip or the actual hostname of localhost will suffice in 222 # mysql version 5.5, without complications. 223 local_hostname = ('localhost' if utils.is_localhost(dest_host) 224 else socket.gethostname()) 225 logging.info('Bootstrapping user %s on host %s against db server %s', 226 user, local_hostname, dest_host) 227 bootstrap(user, password, local_hostname, dest_host) 228 229 if args.enable_gateway: 230 gateway = get_gateway() 231 logging.info('Enabling access through gateway %s', gateway) 232 bootstrap(user, password, gateway, dest_host) 233 234 235if __name__ == '__main__': 236 sys.exit(main(sys.argv[1:])) 237