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()
