def _AllowlistClientIP(instance_ref, sql_client, sql_messages, resources, minutes=5): """Add CLIENT_IP to the authorized networks list. Makes an API call to add CLIENT_IP to the authorized networks list. The server knows to interpret the string CLIENT_IP as the address with which the client reaches the server. This IP will be allowlisted for 1 minute. Args: instance_ref: resources.Resource, The instance we're connecting to. sql_client: apitools.BaseApiClient, A working client for the sql version to be used. sql_messages: module, The module that defines the messages for the sql version to be used. resources: resources.Registry, The registry that can create resource refs for the sql version to be used. minutes: How long the client IP will be allowlisted for, in minutes. Returns: string, The name of the authorized network rule. Callers can use this name to find out the IP the client reached the server with. Raises: HttpException: An http error response was received while executing api request. ResourceNotFoundError: The SQL instance was not found. """ time_of_connection = network.GetCurrentTime() acl_name = 'sql connect at time {0}'.format(time_of_connection) user_acl = sql_messages.AclEntry( kind='sql#aclEntry', name=acl_name, expirationTime=iso_duration.Duration( minutes=minutes).GetRelativeDateTime(time_of_connection) # TODO(b/122989827): Remove this once the datetime parsing is fixed. # Setting the microseconds component to 10 milliseconds. This complies # with backend formatting restrictions, since backend requires a microsecs # component and anything less than 1 milli will get truncated. .replace(microsecond=10000).isoformat(), value='CLIENT_IP') try: original = sql_client.instances.Get( sql_messages.SqlInstancesGetRequest( project=instance_ref.project, instance=instance_ref.instance)) except apitools_exceptions.HttpError as error: if error.status_code == six.moves.http_client.FORBIDDEN: raise exceptions.ResourceNotFoundError( 'There was no instance found at {} or you are not authorized to ' 'connect to it.'.format(instance_ref.RelativeName())) raise calliope_exceptions.HttpException(error) # TODO(b/122989827): Remove this once the datetime parsing is fixed. original.serverCaCert = None original.settings.ipConfiguration.authorizedNetworks.append(user_acl) try: patch_request = sql_messages.SqlInstancesPatchRequest( databaseInstance=original, project=instance_ref.project, instance=instance_ref.instance) result = sql_client.instances.Patch(patch_request) except apitools_exceptions.HttpError as error: raise calliope_exceptions.HttpException(error) operation_ref = resources.Create('sql.operations', operation=result.name, project=instance_ref.project) message = ('Allowlisting your IP for incoming connection for ' '{0} {1}'.format(minutes, text.Pluralize(minutes, 'minute'))) operations.OperationsV1Beta4.WaitForOperation(sql_client, operation_ref, message) return acl_name
def _WhitelistClientIP(instance_ref, sql_client, sql_messages, resources, minutes=5): """Add CLIENT_IP to the authorized networks list. Makes an API call to add CLIENT_IP to the authorized networks list. The server knows to interpret the string CLIENT_IP as the address with which the client reaches the server. This IP will be whitelisted for 1 minute. Args: instance_ref: resources.Resource, The instance we're connecting to. sql_client: apitools.BaseApiClient, A working client for the sql version to be used. sql_messages: module, The module that defines the messages for the sql version to be used. resources: resources.Registry, The registry that can create resource refs for the sql version to be used. minutes: How long the client IP will be whitelisted for, in minutes. Returns: string, The name of the authorized network rule. Callers can use this name to find out the IP the client reached the server with. Raises: HttpException: An http error response was received while executing api request. ToolException: Server did not complete the whitelisting operation in time. """ time_of_connection = network.GetCurrentTime() acl_name = 'sql connect at time {0}'.format(time_of_connection) user_acl = sql_messages.AclEntry( name=acl_name, expirationTime=iso_duration.Duration( minutes=minutes).GetRelativeDateTime(time_of_connection), value='CLIENT_IP') try: original = sql_client.instances.Get( sql_messages.SqlInstancesGetRequest( project=instance_ref.project, instance=instance_ref.instance)) except apitools_exceptions.HttpError as error: raise exceptions.HttpException(error) original.settings.ipConfiguration.authorizedNetworks.append(user_acl) try: patch_request = sql_messages.SqlInstancesPatchRequest( databaseInstance=original, project=instance_ref.project, instance=instance_ref.instance) result = sql_client.instances.Patch(patch_request) except apitools_exceptions.HttpError as error: raise exceptions.HttpException(error) operation_ref = resources.Create( 'sql.operations', operation=result.name, project=instance_ref.project) message = ('Whitelisting your IP for incoming connection for ' '{0} {1}'.format(minutes, text.Pluralize(minutes, 'minute'))) operations.OperationsV1Beta4.WaitForOperation( sql_client, operation_ref, message) return acl_name
class _BaseConnectTest(object): time_of_connection = network.GetCurrentTime() def ExpectInstanceGet(self): self.mocked_client.instances.Get.Expect( self.messages.SqlInstancesGetRequest( instance=self.instance['id'], project=self.Project(), ), self.messages.DatabaseInstance( # pylint:disable=line-too-long backendType=self.instance['backendType'], connectionName='{0}:us-central1:{1}'.format( self.Project(), self.instance['id']), currentDiskSize=None, databaseVersion=self.instance['databaseVersion'], etag='"DlgRosmIegBpXj_rR5uyhdXAbP8/MQ"', failoverReplica=None, instanceType=self.messages.DatabaseInstance. InstanceTypeValueValuesEnum.CLOUD_SQL_INSTANCE, ipAddresses=[ self.messages.IpMapping( ipAddress='104.154.166.249', timeToRetire=None, type=self.messages.IpMapping.TypeValueValuesEnum. PRIMARY, ), ], ipv6Address=None, kind='sql#instance', masterInstanceName=None, maxDiskSize=None, name=self.instance['id'], onPremisesConfiguration=None, project=self.Project(), region='us-central1', replicaConfiguration=None, replicaNames=[], selfLink= 'https://sqladmin.googleapis.com/sql/v1beta4/projects/{0}/instances/{1}' .format(self.Project(), self.instance['id']), serverCaCert=self.messages.SslCert( cert= '-----BEGIN CERTIFICATE-----\nMIIDITCCAgmgAwIBAgIBADANBgkqhkiG9w0BAQUFADBIMSMwIQYDVQQDExpHb29n\nbGUgQ2x', certSerialNumber='0', commonName= 'C=US,O=Google\\, Inc,CN=Google Cloud SQL Server CA', createTime=datetime.datetime( 2017, 5, 12, 21, 33, 4, 844000, tzinfo=protorpc_util.TimeZoneOffset( datetime.timedelta(0))).isoformat(), expirationTime=datetime.datetime( 2019, 5, 12, 21, 34, 4, 844000, tzinfo=protorpc_util.TimeZoneOffset( datetime.timedelta(0))).isoformat(), instance=self.instance['id'], kind='sql#sslCert', selfLink=None, sha1Fingerprint='fcddb49c4a00ff8796ba099933dbeb208b8599bd', ), serviceAccountEmailAddress= '*****@*****.**', settings=self.messages.Settings( activationPolicy=self.messages.Settings. ActivationPolicyValueValuesEnum.ALWAYS, authorizedGaeApplications=[], availabilityType=None, backupConfiguration=self.messages.BackupConfiguration( binaryLogEnabled=None, enabled=False, kind='sql#backupConfiguration', startTime='10:00', ), crashSafeReplicationEnabled=None, dataDiskSizeGb=10, dataDiskType=self.messages.Settings. DataDiskTypeValueValuesEnum.PD_SSD, databaseFlags=[], databaseReplicationEnabled=None, ipConfiguration=self.messages.IpConfiguration( authorizedNetworks=[], ipv4Enabled=True, requireSsl=None, ), kind='sql#settings', locationPreference=None, maintenanceWindow=None, pricingPlan=self.messages.Settings. PricingPlanValueValuesEnum.PER_USE, replicationType=self.messages.Settings. ReplicationTypeValueValuesEnum.SYNCHRONOUS, settingsVersion=1, storageAutoResize=None, storageAutoResizeLimit=None, tier=self.instance['tier'], ), state=self.messages.DatabaseInstance.StateValueValuesEnum. RUNNABLE, suspensionReason=[], )) def MockIPWhitelisting(self, error=False): # Mock the connection time. self.StartPatch('googlecloudsdk.api_lib.sql.network.GetCurrentTime', return_value=self.time_of_connection) # Mock GET and PATCH endpoints self.ExpectInstanceGet() patch_request = self.messages.SqlInstancesPatchRequest( databaseInstance=self.messages.DatabaseInstance( # pylint:disable=line-too-long backendType=self.instance['backendType'], connectionName='{0}:us-central1:{1}'.format( self.Project(), self.instance['id']), currentDiskSize=None, databaseVersion=self.instance['databaseVersion'], etag='"DlgRosmIegBpXj_rR5uyhdXAbP8/MQ"', failoverReplica=None, instanceType=self.messages.DatabaseInstance. InstanceTypeValueValuesEnum.CLOUD_SQL_INSTANCE, ipAddresses=[ self.messages.IpMapping( ipAddress='104.154.166.249', timeToRetire=None, type=self.messages.IpMapping.TypeValueValuesEnum. PRIMARY, ), ], ipv6Address=None, kind='sql#instance', masterInstanceName=None, maxDiskSize=None, name=self.instance['id'], onPremisesConfiguration=None, project=self.Project(), region='us-central1', replicaConfiguration=None, replicaNames=[], selfLink= 'https://sqladmin.googleapis.com/sql/v1beta4/projects/{0}/instances/{1}' .format(self.Project(), self.instance['id']), serverCaCert=None, serviceAccountEmailAddress= '*****@*****.**', settings=self.messages.Settings( activationPolicy=self.messages.Settings. ActivationPolicyValueValuesEnum.ALWAYS, authorizedGaeApplications=[], availabilityType=None, backupConfiguration=self.messages.BackupConfiguration( binaryLogEnabled=None, enabled=False, kind='sql#backupConfiguration', startTime='10:00', ), crashSafeReplicationEnabled=None, dataDiskSizeGb=10, dataDiskType=self.messages.Settings. DataDiskTypeValueValuesEnum.PD_SSD, databaseFlags=[], databaseReplicationEnabled=None, ipConfiguration=self.messages.IpConfiguration( authorizedNetworks=[ self.messages.AclEntry( expirationTime=( self.time_of_connection + datetime.timedelta(minutes=5)).replace( microsecond=10000).isoformat(), kind='sql#aclEntry', name='sql connect at time {0}'.format( str(self.time_of_connection)), value='CLIENT_IP', ), ], ipv4Enabled=True, requireSsl=None, ), kind='sql#settings', locationPreference=None, maintenanceWindow=None, pricingPlan=self.messages.Settings. PricingPlanValueValuesEnum.PER_USE, replicationType=self.messages.Settings. ReplicationTypeValueValuesEnum.SYNCHRONOUS, settingsVersion=1, storageAutoResize=None, storageAutoResizeLimit=None, tier=self.instance['tier'], ), state=self.messages.DatabaseInstance.StateValueValuesEnum. RUNNABLE, suspensionReason=[], ), instance=self.instance['id'], project=self.Project(), ) if error: self.mocked_client.instances.Patch.Expect( patch_request, exception=http_error.MakeHttpError( code=400, message='invalidInstanceProperty', reason='Invalid instance property.', )) else: self.mocked_client.instances.Patch.Expect( patch_request, self.messages.Operation( # pylint:disable=line-too-long endTime=None, error=None, exportContext=None, importContext=None, insertTime=datetime.datetime( 2017, 5, 15, 23, 3, 50, 514000, tzinfo=protorpc_util.TimeZoneOffset( datetime.timedelta(0))).isoformat(), kind='sql#operation', name='8b7ffa62-e950-45c0-bdad-1d366ad8b964', operationType=self.messages.Operation. OperationTypeValueValuesEnum.UPDATE, selfLink= 'https://sqladmin.googleapis.com/sql/v1beta4/projects/{0}/operations/8b7ffa62-e9' .format(self.Project()), startTime=None, status=self.messages.Operation.StatusValueValuesEnum. PENDING, targetId=self.instance['id'], targetLink= 'https://sqladmin.googleapis.com/sql/v1beta4/projects/{0}/instances/{1}' .format(self.Project(), self.instance['id']), targetProject=self.Project(), user= '******', )) self.mocked_client.operations.Get.Expect( self.messages.SqlOperationsGetRequest( operation='8b7ffa62-e950-45c0-bdad-1d366ad8b964', project=self.Project(), ), self.messages.Operation( # pylint:disable=line-too-long endTime=datetime.datetime( 2017, 5, 15, 23, 5, 4, 809000, tzinfo=protorpc_util.TimeZoneOffset( datetime.timedelta(0))).isoformat(), error=None, exportContext=None, importContext=None, insertTime=datetime.datetime( 2017, 5, 15, 23, 3, 50, 514000, tzinfo=protorpc_util.TimeZoneOffset( datetime.timedelta(0))).isoformat(), kind='sql#operation', name='8b7ffa62-e950-45c0-bdad-1d366ad8b964', operationType=self.messages.Operation. OperationTypeValueValuesEnum.UPDATE, selfLink= 'https://sqladmin.googleapis.com/sql/v1beta4/projects/{0}/operations/8b7ffa62-e9' .format(self.Project()), startTime=datetime.datetime( 2017, 5, 15, 23, 3, 50, 707000, tzinfo=protorpc_util.TimeZoneOffset( datetime.timedelta(0))).isoformat(), status=self.messages.Operation.StatusValueValuesEnum.DONE, targetId=self.instance['id'], targetLink= 'https://sqladmin.googleapis.com/sql/v1beta4/projects/{0}/instances/{1}' .format(self.Project(), self.instance['id']), targetProject=self.Project(), user= '******', )) self.mocked_client.instances.Get.Expect( self.messages.SqlInstancesGetRequest( instance=self.instance['id'], project=self.Project(), ), self.messages.DatabaseInstance( # pylint:disable=line-too-long backendType=self.instance['backendType'], connectionName='{0}:us-central1:{1}'.format( self.Project(), self.instance['id']), currentDiskSize=None, databaseVersion=self.instance['databaseVersion'], etag='"DlgRosmIegBpXj_rR5uyhdXAbP8/Mw"', failoverReplica=None, instanceType=self.messages.DatabaseInstance. InstanceTypeValueValuesEnum.CLOUD_SQL_INSTANCE, ipAddresses=[ self.messages.IpMapping( ipAddress='104.154.166.249', timeToRetire=None, type=self.messages.IpMapping.TypeValueValuesEnum. PRIMARY, ), ], ipv6Address=None, kind='sql#instance', masterInstanceName=None, maxDiskSize=None, name=self.instance['id'], onPremisesConfiguration=None, project=self.Project(), region='us-central1', replicaConfiguration=None, replicaNames=[], selfLink= 'https://sqladmin.googleapis.com/sql/v1beta4/projects/{0}/instances/{1}' .format(self.Project(), self.instance['id']), serverCaCert=self.messages.SslCert( cert= '-----BEGIN CERTIFICATE-----\nMIIDITCCAgmgAwIBAgIBADANBgkqhkiG9w0BAQUFADBIMSMwIQYDVQQDExpHb29n\nbGUgQ2x', certSerialNumber='0', commonName= 'C=US,O=Google\\, Inc,CN=Google Cloud SQL Server CA', createTime=datetime.datetime( 2017, 5, 12, 21, 33, 4, 844000, tzinfo=protorpc_util.TimeZoneOffset( datetime.timedelta(0))).isoformat(), expirationTime=datetime.datetime( 2019, 5, 12, 21, 34, 4, 844000, tzinfo=protorpc_util.TimeZoneOffset( datetime.timedelta(0))).isoformat(), instance=self.instance['id'], kind='sql#sslCert', selfLink=None, sha1Fingerprint= 'fcddb49c4a00ff8796ba099933dbeb208b8599bd', ), serviceAccountEmailAddress= '*****@*****.**', settings=self.messages.Settings( activationPolicy=self.messages.Settings. ActivationPolicyValueValuesEnum.ALWAYS, authorizedGaeApplications=[], availabilityType=None, backupConfiguration=self.messages.BackupConfiguration( binaryLogEnabled=None, enabled=False, kind='sql#backupConfiguration', startTime='10:00', ), crashSafeReplicationEnabled=None, dataDiskSizeGb=10, dataDiskType=self.messages.Settings. DataDiskTypeValueValuesEnum.PD_SSD, databaseFlags=[], databaseReplicationEnabled=None, ipConfiguration=self.messages.IpConfiguration( authorizedNetworks=[ self.messages.AclEntry( expirationTime=( self.time_of_connection + datetime.timedelta(minutes=5) ).isoformat(), kind='sql#aclEntry', name='sql connect at time {0}'.format( str(self.time_of_connection)), value='192.0.0.1', ), ], ipv4Enabled=True, requireSsl=None, ), kind='sql#settings', locationPreference=None, maintenanceWindow=None, pricingPlan=self.messages.Settings. PricingPlanValueValuesEnum.PER_USE, replicationType=self.messages.Settings. ReplicationTypeValueValuesEnum.SYNCHRONOUS, settingsVersion=3, storageAutoResize=True, storageAutoResizeLimit=0, tier=self.instance['tier'], ), state=self.messages.DatabaseInstance.StateValueValuesEnum. RUNNABLE, suspensionReason=[], )) def MockProxyStartAndInstanceGet(self): # Mock checking that the Cloud SQL Proxy binary is installed. self.StartPatch('googlecloudsdk.core.util.files.FindExecutableOnPath', return_value='cloud_sql_proxy') # Mock starting the proxy. self.StartPatch( 'googlecloudsdk.api_lib.sql.instances.StartCloudSqlProxy', return_value=mock.Mock()) self.ExpectInstanceGet()