1# -*- coding: utf-8 -*- # 2# Copyright 2013 Google LLC. All Rights Reserved. 3# 4# Licensed under the Apache License, Version 2.0 (the "License"); 5# you may not use this file except in compliance with the License. 6# You may obtain a copy of the License at 7# 8# http://www.apache.org/licenses/LICENSE-2.0 9# 10# Unless required by applicable law or agreed to in writing, software 11# distributed under the License is distributed on an "AS IS" BASIS, 12# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13# See the License for the specific language governing permissions and 14# limitations under the License. 15"""Connects to a Cloud SQL instance.""" 16 17from __future__ import absolute_import 18from __future__ import division 19from __future__ import unicode_literals 20 21import atexit 22from apitools.base.py import exceptions as apitools_exceptions 23 24from googlecloudsdk.api_lib.sql import api_util 25from googlecloudsdk.api_lib.sql import constants 26from googlecloudsdk.api_lib.sql import exceptions 27from googlecloudsdk.api_lib.sql import instances as instances_api_util 28from googlecloudsdk.api_lib.sql import network 29from googlecloudsdk.api_lib.sql import operations 30from googlecloudsdk.calliope import arg_parsers 31from googlecloudsdk.calliope import base 32from googlecloudsdk.calliope import exceptions as calliope_exceptions 33from googlecloudsdk.command_lib.sql import flags as sql_flags 34from googlecloudsdk.command_lib.sql import instances as instances_command_util 35from googlecloudsdk.core.util import files 36from googlecloudsdk.core.util import iso_duration 37from googlecloudsdk.core.util import retry 38from googlecloudsdk.core.util import text 39import six 40import six.moves.http_client 41 42EXAMPLES = ( 43 """\ 44 To connect to a Cloud SQL instance, run: 45 46 $ {command} my-instance --user=root 47 """ 48) 49 50DETAILED_GA_HELP = { 51 'DESCRIPTION': 52 """ 53 Connects to a Cloud SQL instance. 54 55 NOTE: If you're connecting from an IPv6 address, or are constrained by 56 certain organization policies (restrictPublicIP, 57 restrictAuthorizedNetworks), consider running the beta version of this 58 command to avoid error by connecting through the Cloud SQL proxy: 59 *gcloud beta sql connect* 60 """, 61 'EXAMPLES': EXAMPLES, 62} 63 64DETAILED_ALPHA_BETA_HELP = { 65 'DESCRIPTION': 66 """ 67 Connects to Cloud SQL V2 instances through the Cloud SQL Proxy. 68 Connects to Cloud SQL V1 instances directly. 69 """, 70 'EXAMPLES': EXAMPLES, 71} 72 73# TODO(b/62055574): Improve test coverage in this file. 74 75 76def _AllowlistClientIP(instance_ref, 77 sql_client, 78 sql_messages, 79 resources, 80 minutes=5): 81 """Add CLIENT_IP to the authorized networks list. 82 83 Makes an API call to add CLIENT_IP to the authorized networks list. 84 The server knows to interpret the string CLIENT_IP as the address with which 85 the client reaches the server. This IP will be allowlisted for 1 minute. 86 87 Args: 88 instance_ref: resources.Resource, The instance we're connecting to. 89 sql_client: apitools.BaseApiClient, A working client for the sql version to 90 be used. 91 sql_messages: module, The module that defines the messages for the sql 92 version to be used. 93 resources: resources.Registry, The registry that can create resource refs 94 for the sql version to be used. 95 minutes: How long the client IP will be allowlisted for, in minutes. 96 97 Returns: 98 string, The name of the authorized network rule. Callers can use this name 99 to find out the IP the client reached the server with. 100 Raises: 101 HttpException: An http error response was received while executing api 102 request. 103 ResourceNotFoundError: The SQL instance was not found. 104 """ 105 time_of_connection = network.GetCurrentTime() 106 107 acl_name = 'sql connect at time {0}'.format(time_of_connection) 108 user_acl = sql_messages.AclEntry( 109 kind='sql#aclEntry', 110 name=acl_name, 111 expirationTime=iso_duration.Duration( 112 minutes=minutes).GetRelativeDateTime(time_of_connection) 113 # TODO(b/122989827): Remove this once the datetime parsing is fixed. 114 # Setting the microseconds component to 10 milliseconds. This complies 115 # with backend formatting restrictions, since backend requires a microsecs 116 # component and anything less than 1 milli will get truncated. 117 .replace(microsecond=10000).isoformat(), 118 value='CLIENT_IP') 119 120 try: 121 original = sql_client.instances.Get( 122 sql_messages.SqlInstancesGetRequest( 123 project=instance_ref.project, instance=instance_ref.instance)) 124 except apitools_exceptions.HttpError as error: 125 if error.status_code == six.moves.http_client.FORBIDDEN: 126 raise exceptions.ResourceNotFoundError( 127 'There was no instance found at {} or you are not authorized to ' 128 'connect to it.'.format(instance_ref.RelativeName())) 129 raise calliope_exceptions.HttpException(error) 130 131 # TODO(b/122989827): Remove this once the datetime parsing is fixed. 132 original.serverCaCert = None 133 134 original.settings.ipConfiguration.authorizedNetworks.append(user_acl) 135 try: 136 patch_request = sql_messages.SqlInstancesPatchRequest( 137 databaseInstance=original, 138 project=instance_ref.project, 139 instance=instance_ref.instance) 140 result = sql_client.instances.Patch(patch_request) 141 except apitools_exceptions.HttpError as error: 142 raise calliope_exceptions.HttpException(error) 143 144 operation_ref = resources.Create( 145 'sql.operations', operation=result.name, project=instance_ref.project) 146 message = ('Allowlisting your IP for incoming connection for ' 147 '{0} {1}'.format(minutes, text.Pluralize(minutes, 'minute'))) 148 149 operations.OperationsV1Beta4.WaitForOperation(sql_client, operation_ref, 150 message) 151 152 return acl_name 153 154 155def _GetClientIP(instance_ref, sql_client, acl_name): 156 """Retrieves given instance and extracts its client ip.""" 157 instance_info = sql_client.instances.Get( 158 sql_client.MESSAGES_MODULE.SqlInstancesGetRequest( 159 project=instance_ref.project, instance=instance_ref.instance)) 160 networks = instance_info.settings.ipConfiguration.authorizedNetworks 161 client_ip = None 162 for net in networks: 163 if net.name == acl_name: 164 client_ip = net.value 165 break 166 return instance_info, client_ip 167 168 169def AddBaseArgs(parser): 170 """Declare flag and positional arguments for this command parser. 171 172 Args: 173 parser: An argparse parser that you can use it to add arguments that go on 174 the command line after this command. Positional arguments are allowed. 175 """ 176 parser.add_argument( 177 'instance', 178 completer=sql_flags.InstanceCompleter, 179 help='Cloud SQL instance ID.') 180 181 parser.add_argument( 182 '--user', 183 '-u', 184 required=False, 185 help='Cloud SQL instance user to connect as.') 186 187 188def AddBetaArgs(parser): 189 """Declare beta flag arguments for this command parser. 190 191 Args: 192 parser: An argparse parser that you can use it to add arguments that go on 193 the command line after this command. Positional arguments are allowed. 194 """ 195 parser.add_argument( 196 '--port', 197 type=arg_parsers.BoundedInt(lower_bound=1, upper_bound=65535), 198 default=constants.DEFAULT_PROXY_PORT_NUMBER, 199 help=('Port number that gcloud will use to connect to the Cloud SQL ' 200 'Proxy through localhost.')) 201 202 203def RunConnectCommand(args, supports_database=False): 204 """Connects to a Cloud SQL instance directly. 205 206 Args: 207 args: argparse.Namespace, The arguments that this command was invoked with. 208 supports_database: Whether or not the `--database` flag needs to be 209 accounted for. 210 211 Returns: 212 If no exception is raised this method does not return. A new process is 213 started and the original one is killed. 214 Raises: 215 HttpException: An http error response was received while executing api 216 request. 217 UpdateError: An error occurred while updating an instance. 218 SqlClientNotFoundError: A local SQL client could not be found. 219 ConnectionError: An error occurred while trying to connect to the instance. 220 """ 221 client = api_util.SqlClient(api_util.API_VERSION_DEFAULT) 222 sql_client = client.sql_client 223 sql_messages = client.sql_messages 224 225 instance_ref = instances_command_util.GetInstanceRef(args, client) 226 227 acl_name = _AllowlistClientIP(instance_ref, sql_client, sql_messages, 228 client.resource_parser) 229 230 # Get the client IP that the server sees. Sadly we can only do this by 231 # checking the name of the authorized network rule. 232 retryer = retry.Retryer(max_retrials=2, exponential_sleep_multiplier=2) 233 try: 234 instance_info, client_ip = retryer.RetryOnResult( 235 _GetClientIP, 236 [instance_ref, sql_client, acl_name], 237 should_retry_if=lambda x, s: x[1] is None, # client_ip is None 238 sleep_ms=500) 239 except retry.RetryException: 240 raise exceptions.UpdateError('Could not allowlist client IP. Server did ' 241 'not reply with the allowlisted IP.') 242 243 # Check for the mysql or psql executable based on the db version. 244 db_type = instance_info.databaseVersion.name.split('_')[0] 245 exe_name = constants.DB_EXE.get(db_type, 'mysql') 246 exe = files.FindExecutableOnPath(exe_name) 247 if not exe: 248 raise exceptions.SqlClientNotFoundError( 249 '{0} client not found. Please install a {1} client and make sure ' 250 'it is in PATH to be able to connect to the database instance.'.format( 251 exe_name.title(), exe_name)) 252 253 # Check the version of IP and decide if we need to add ipv4 support. 254 ip_type = network.GetIpVersion(client_ip) 255 if ip_type == network.IP_VERSION_4: 256 if instance_info.settings.ipConfiguration.ipv4Enabled: 257 ip_address = instance_info.ipAddresses[0].ipAddress 258 else: 259 # TODO(b/36049930): ask user if we should enable ipv4 addressing 260 message = ('It seems your client does not have ipv6 connectivity and ' 261 'the database instance does not have an ipv4 address. ' 262 'Please request an ipv4 address for this database instance.') 263 raise exceptions.ConnectionError(message) 264 elif ip_type == network.IP_VERSION_6: 265 ip_address = instance_info.ipv6Address 266 else: 267 raise exceptions.ConnectionError('Could not connect to SQL server.') 268 269 # Determine what SQL user to connect with. 270 sql_user = constants.DEFAULT_SQL_USER[exe_name] 271 if args.user: 272 sql_user = args.user 273 274 # We have everything we need, time to party! 275 flags = constants.EXE_FLAGS[exe_name] 276 sql_args = [exe_name, flags['hostname'], ip_address] 277 sql_args.extend([flags['user'], sql_user]) 278 if 'password' in flags: 279 sql_args.append(flags['password']) 280 281 if supports_database: 282 sql_args.extend(instances_command_util.GetDatabaseArgs(args, flags)) 283 284 instances_command_util.ConnectToInstance(sql_args, sql_user) 285 286 287def RunProxyConnectCommand(args, 288 supports_database=False): 289 """Connects to a Cloud SQL instance through the Cloud SQL Proxy. 290 291 Args: 292 args: argparse.Namespace, The arguments that this command was invoked with. 293 supports_database: Whether or not the `--database` flag needs to be 294 accounted for. 295 296 Returns: 297 If no exception is raised this method does not return. A new process is 298 started and the original one is killed. 299 Raises: 300 HttpException: An http error response was received while executing api 301 request. 302 CloudSqlProxyError: Cloud SQL Proxy could not be found. 303 SqlClientNotFoundError: A local SQL client could not be found. 304 ConnectionError: An error occurred while trying to connect to the instance. 305 """ 306 client = api_util.SqlClient(api_util.API_VERSION_DEFAULT) 307 sql_client = client.sql_client 308 sql_messages = client.sql_messages 309 310 instance_ref = instances_command_util.GetInstanceRef(args, client) 311 312 instance_info = sql_client.instances.Get( 313 sql_messages.SqlInstancesGetRequest( 314 project=instance_ref.project, instance=instance_ref.instance)) 315 316 if not instances_api_util.IsInstanceV2(sql_messages, instance_info): 317 # The Cloud SQL Proxy does not support V1 instances. 318 return RunConnectCommand(args, supports_database) 319 320 # If the instance is V2, keep going with the proxy. 321 exe = files.FindExecutableOnPath('cloud_sql_proxy') 322 if not exe: 323 raise exceptions.CloudSqlProxyError( 324 'Cloud SQL Proxy could not be found in PATH. See ' 325 'https://cloud.google.com/sql/docs/mysql/sql-proxy#install for ' 326 'information on installing.') 327 328 # Check for the executable based on the db version. 329 db_type = instance_info.databaseVersion.name.split('_')[0] 330 exe_name = constants.DB_EXE.get(db_type, 'mysql') 331 exe = files.FindExecutableOnPath(exe_name) 332 if not exe: 333 raise exceptions.SqlClientNotFoundError( 334 '{0} client not found. Please install a {1} client and make sure ' 335 'it is in PATH to be able to connect to the database instance.'.format( 336 exe_name.title(), exe_name)) 337 338 # Start the Cloud SQL Proxy and wait for it to be ready to accept connections. 339 port = six.text_type(args.port) 340 proxy_process = instances_api_util.StartCloudSqlProxy(instance_info, port) 341 atexit.register(proxy_process.kill) 342 343 # Determine what SQL user to connect with. 344 sql_user = constants.DEFAULT_SQL_USER[exe_name] 345 if args.user: 346 sql_user = args.user 347 348 # We have everything we need, time to party! 349 flags = constants.EXE_FLAGS[exe_name] 350 sql_args = [exe_name] 351 if exe_name == 'mssql-cli': 352 # mssql-cli merges hostname and port into a single argument 353 hostname = 'tcp:127.0.0.1,{0}'.format(port) 354 sql_args.extend([flags['hostname'], hostname]) 355 else: 356 sql_args.extend([flags['hostname'], '127.0.0.1', flags['port'], port]) 357 sql_args.extend([flags['user'], sql_user]) 358 if 'password' in flags: 359 sql_args.append(flags['password']) 360 361 if supports_database: 362 sql_args.extend(instances_command_util.GetDatabaseArgs(args, flags)) 363 364 instances_command_util.ConnectToInstance(sql_args, sql_user) 365 proxy_process.kill() 366 367 368@base.ReleaseTracks(base.ReleaseTrack.GA) 369class Connect(base.Command): 370 """Connects to a Cloud SQL instance.""" 371 372 detailed_help = DETAILED_GA_HELP 373 374 @staticmethod 375 def Args(parser): 376 """Args is called by calliope to gather arguments for this command.""" 377 AddBaseArgs(parser) 378 sql_flags.AddDatabase( 379 parser, 'The SQL Server database to connect to.') 380 381 def Run(self, args): 382 """Connects to a Cloud SQL instance.""" 383 return RunConnectCommand(args, supports_database=True) 384 385 386@base.ReleaseTracks(base.ReleaseTrack.BETA, base.ReleaseTrack.ALPHA) 387class ConnectBeta(base.Command): 388 """Connects to a Cloud SQL instance.""" 389 390 detailed_help = DETAILED_ALPHA_BETA_HELP 391 392 @staticmethod 393 def Args(parser): 394 """Args is called by calliope to gather arguments for this command.""" 395 AddBaseArgs(parser) 396 AddBetaArgs(parser) 397 sql_flags.AddDatabase( 398 parser, 'The PostgreSQL or SQL Server database to connect to.') 399 400 def Run(self, args): 401 """Connects to a Cloud SQL instance.""" 402 return RunProxyConnectCommand(args, supports_database=True) 403