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