1 #!/usr/bin/python 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 8 The purpose of this module is to grant access to a new-user/host/password 9 combination on a remote db server. For example, if we were bootstrapping 10 a new autotest master A1 with a remote database server A2, the scheduler 11 running on A1 needs to access the database on A2 with the credentials 12 specified in the shadow_config of A1 (A1_user, A1_pass). To achieve this 13 we ssh into A2 and execute the grant privileges command for (A1_user, 14 A1_pass, A1_host). If OTOH the db server is running locally we only need 15 to grant permissions for (A1_user, A1_pass, localhost). 16 17 The 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 22 However this will only grant the right access permissions to A1, so we need 23 to repeat for all subsequent db clients we add. This will happen through puppet. 24 25 In the case of a vagrant cluster, a remote vm cannot ssh into the db server 26 vm with plain old ssh. However, the entire vm cluster is provisioned at the 27 same time, so we can grant access to all remote vm clients directly on the 28 database server without knowing their ips by using the ip of the gateway. 29 This works because the db server vm redirects its database port (3306) to 30 a predefined port (defined in the vagrant file, defaults to 8002), and all 31 other vms in the cluster can only access it through the vm host identified 32 by the gateway. 33 34 The 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';" 39 This will grant the right access permissions to all vms running on the 40 host machine as long as they use the right port to access the database. 41 """ 42 43 import argparse 44 import logging 45 import socket 46 import subprocess 47 import sys 48 49 import common 50 51 from autotest_lib.client.common_lib import global_config 52 from autotest_lib.client.common_lib import utils 53 from autotest_lib.site_utils.lib import infra 54 55 56 class MySQLCommandError(Exception): 57 """Generic mysql command execution exception.""" 58 59 60 class 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 135 def 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 183 def 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 197 def _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 206 def 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 235 if __name__ == '__main__': 236 sys.exit(main(sys.argv[1:])) 237