Repository: GoogleCloudPlatform/cloud-sql-python-connector
Pull request #766 (2023-06-19 18:29:35)

feat: add support for PSC connections
Add a new IP type `IPTypes.PSC` to support connections to Cloud SQL using [private service connect](https://cloud.google.com/vpc/docs/private-service-connect).


Patch:
diff --git a/.github/workflows/codeql.yml b/.github/workflows/codeql.yml
index 89ca41b..b7399a3 100644
--- a/.github/workflows/codeql.yml
+++ b/.github/workflows/codeql.yml
@@ -48,7 +48,7 @@ jobs:
 
     steps:
     - name: Checkout repository
-      uses: actions/checkout@8e5e7e5ab8b370d6c329ec480221332ada57f0ab # v3.5.2
+      uses: actions/checkout@c85c95e3d7251135ab7dc9ce3241c5835cc595a9 # v3.5.3
       with:
         ref: ${{ github.event.pull_request.head.sha }}
         repository: ${{ github.event.pull_request.head.repo.full_name }}
diff --git a/.github/workflows/coverage.yml b/.github/workflows/coverage.yml
index 9d280b3..21f964f 100644
--- a/.github/workflows/coverage.yml
+++ b/.github/workflows/coverage.yml
@@ -54,7 +54,7 @@ jobs:
       - run: pip install nox coverage
 
       - name: Checkout base branch
-        uses: actions/checkout@8e5e7e5ab8b370d6c329ec480221332ada57f0ab # v3.5.2
+        uses: actions/checkout@c85c95e3d7251135ab7dc9ce3241c5835cc595a9 # v3.5.3
         with:
           ref: ${{ github.base_ref }}
 
@@ -67,7 +67,7 @@ jobs:
           coverage erase
 
       - name: Checkout PR branch
-        uses: actions/checkout@8e5e7e5ab8b370d6c329ec480221332ada57f0ab # v3.5.2
+        uses: actions/checkout@c85c95e3d7251135ab7dc9ce3241c5835cc595a9 # v3.5.3
         with:
           ref: ${{ github.event.pull_request.head.sha }}
           repository: ${{ github.event.pull_request.head.repo.full_name }}
diff --git a/.github/workflows/labels.yaml b/.github/workflows/labels.yaml
index 441ab68..2f39aea 100644
--- a/.github/workflows/labels.yaml
+++ b/.github/workflows/labels.yaml
@@ -28,7 +28,7 @@ jobs:
       issues: write
       pull-requests: write
     steps:
-      - uses: actions/checkout@8e5e7e5ab8b370d6c329ec480221332ada57f0ab # v3.5.2
+      - uses: actions/checkout@c85c95e3d7251135ab7dc9ce3241c5835cc595a9 # v3.5.3
       - uses: micnncim/action-label-syncer@3abd5ab72fda571e69fffd97bd4e0033dd5f495c # v1.3.0
         env:
           GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }}
diff --git a/.github/workflows/lint.yml b/.github/workflows/lint.yml
index 475a646..f53f3f9 100644
--- a/.github/workflows/lint.yml
+++ b/.github/workflows/lint.yml
@@ -56,7 +56,7 @@ jobs:
         run: pip install nox
 
       - name: Checkout code
-        uses: actions/checkout@8e5e7e5ab8b370d6c329ec480221332ada57f0ab # v3.5.2
+        uses: actions/checkout@c85c95e3d7251135ab7dc9ce3241c5835cc595a9 # v3.5.3
         with:
           ref: ${{ github.event.pull_request.head.sha }}
           repository: ${{ github.event.pull_request.head.repo.full_name }}
diff --git a/.github/workflows/scorecard.yml b/.github/workflows/scorecard.yml
index 17807f3..12ac0de 100644
--- a/.github/workflows/scorecard.yml
+++ b/.github/workflows/scorecard.yml
@@ -35,7 +35,7 @@ jobs:
 
     steps:
       - name: "Checkout code"
-        uses: actions/checkout@8e5e7e5ab8b370d6c329ec480221332ada57f0ab # v3.5.2
+        uses: actions/checkout@c85c95e3d7251135ab7dc9ce3241c5835cc595a9 # v3.5.3
         with:
           persist-credentials: false
 
diff --git a/README.md b/README.md
index 2198ffe..b866e40 100644
--- a/README.md
+++ b/README.md
@@ -252,9 +252,17 @@ with Connector() as connector:
             print(row)
 ```
 
-### Specifying Public or Private IP
+### Specifying IP Address Type
+
+The Cloud SQL Python Connector can be used to connect to Cloud SQL instances
+using both public and private IP addresses, as well as
+[Private Service Connect][psc] (PSC). To specify which IP address type to connect
+with, set the `ip_type` keyword argument when initializing a `Connector()` or when
+calling `connector.connect()`.
+
+Possible values for `ip_type` are `IPTypes.PUBLIC` (default value),
+`IPTypes.PRIVATE`, and `IPTypes.PSC`.
 
-The Cloud SQL Connector for Python can be used to connect to Cloud SQL instances using both public and private IP addresses. To specify which IP address to use to connect, set the `ip_type` keyword argument Possible values are `IPTypes.PUBLIC` and `IPTypes.PRIVATE`.
 Example:
 
 ```python
@@ -268,9 +276,14 @@ conn = connector.connect(
 )
 ```
 
-Note: If specifying Private IP, your application must already be in the same VPC network as your Cloud SQL Instance.
+Note: If specifying Private IP or Private Service Connect, your application must be
+attached to the proper VPC network to connect to your Cloud SQL instance. For most
+applications this will require the use of a [VPC Connector][vpc-connector].
+
+[psc]: https://cloud.google.com/vpc/docs/private-service-connect
+[vpc-connector]: https://cloud.google.com/vpc/docs/configure-serverless-vpc-access#create-connector
 
-### IAM Authentication
+### Automatic IAM Database Authentication
 
 Connections using [Automatic IAM database authentication](https://cloud.google.com/sql/docs/postgres/authentication#automatic) are supported when using Postgres or MySQL drivers.
 First, make sure to [configure your Cloud SQL Instance to allow IAM authentication](https://cloud.google.com/sql/docs/postgres/create-edit-iam-instances#configure-iam-db-instance)
diff --git a/google/cloud/sql/connector/connector.py b/google/cloud/sql/connector/connector.py
index cf5df50..8bed28c 100755
--- a/google/cloud/sql/connector/connector.py
+++ b/google/cloud/sql/connector/connector.py
@@ -18,12 +18,16 @@ from __future__ import annotations
 import asyncio
 from functools import partial
 import logging
+import socket
 from threading import Thread
 from types import TracebackType
 from typing import Any, Dict, Optional, Type, TYPE_CHECKING
 
 import google.cloud.sql.connector.asyncpg as asyncpg
-from google.cloud.sql.connector.exceptions import ConnectorLoopError
+from google.cloud.sql.connector.exceptions import (
+    ConnectorLoopError,
+    DnsNameResolutionError,
+)
 from google.cloud.sql.connector.instance import (
     Instance,
     IPTypes,
@@ -238,6 +242,21 @@ class Connector:
         # attempt to make connection to Cloud SQL instance
         try:
             instance_data, ip_address = await instance.connect_info(ip_type)
+            # resolve DNS name into IP address for PSC
+            if ip_type.value == "PSC":
+                addr_info = await self._loop.getaddrinfo(
+                    ip_address, None, family=socket.AF_INET, type=socket.SOCK_STREAM
+                )
+                # getaddrinfo returns a list of 5-tuples that contain socket
+                # connection info in the form
+                # (family, type, proto, canonname, sockaddr), where sockaddr is a
+                # 2-tuple in the form (ip_address, port)
+                try:
+                    ip_address = addr_info[0][4][0]
+                except IndexError as e:
+                    raise DnsNameResolutionError(
+                        f"['{instance_connection_string}']: DNS name could not be resolved into IP address"
+                    ) from e
 
             # format `user` param for automatic IAM database authn
             if enable_iam_auth:
diff --git a/google/cloud/sql/connector/exceptions.py b/google/cloud/sql/connector/exceptions.py
index cc3135f..33b9fda 100644
--- a/google/cloud/sql/connector/exceptions.py
+++ b/google/cloud/sql/connector/exceptions.py
@@ -63,3 +63,10 @@ class AutoIAMAuthNotSupported(Exception):
     """
 
     pass
+
+
+class DnsNameResolutionError(Exception):
+    """
+    Exception to be raised when the DnsName of a PSC connection to a
+    Cloud SQL instance can not be resolved to a proper IP address.
+    """
diff --git a/google/cloud/sql/connector/instance.py b/google/cloud/sql/connector/instance.py
index b45d79a..59f596e 100644
--- a/google/cloud/sql/connector/instance.py
+++ b/google/cloud/sql/connector/instance.py
@@ -60,6 +60,7 @@ APPLICATION_NAME = "cloud-sql-python-connector"
 class IPTypes(Enum):
     PUBLIC: str = "PRIMARY"
     PRIVATE: str = "PRIVATE"
+    PSC: str = "PSC"
 
 
 class InstanceMetadata:
diff --git a/google/cloud/sql/connector/refresh_utils.py b/google/cloud/sql/connector/refresh_utils.py
index 1966bab..2a85d14 100644
--- a/google/cloud/sql/connector/refresh_utils.py
+++ b/google/cloud/sql/connector/refresh_utils.py
@@ -108,8 +108,16 @@ async def _get_metadata(
             f'[{project}:{region}:{instance}]: Provided region was mismatched - got region {region}, expected {ret_dict["region"]}.'
         )
 
+    ip_addresses = (
+        {ip["type"]: ip["ipAddress"] for ip in ret_dict["ipAddresses"]}
+        if "ipAddresses" in ret_dict
+        else {}
+    )
+    if "dnsName" in ret_dict:
+        ip_addresses["PSC"] = ret_dict["dnsName"]
+
     metadata = {
-        "ip_addresses": {ip["type"]: ip["ipAddress"] for ip in ret_dict["ipAddresses"]},
+        "ip_addresses": ip_addresses,
         "server_ca_cert": ret_dict["serverCaCert"]["cert"],
         "database_version": ret_dict["databaseVersion"],
     }
diff --git a/requirements.txt b/requirements.txt
index 45790c9..9e0a1ff 100644
--- a/requirements.txt
+++ b/requirements.txt
@@ -1,4 +1,4 @@
 aiohttp==3.8.4
 cryptography==41.0.1
 Requests==2.31.0
-google-auth==2.20.0
+google-auth==2.21.0
diff --git a/setup.py b/setup.py
index 9b18e5c..53b352b 100644
--- a/setup.py
+++ b/setup.py
@@ -74,7 +74,7 @@ setup(
     namespace_packages=namespaces,
     install_requires=core_dependencies,
     extras_require={
-        "pymysql": ["PyMySQL==1.0.3"],
+        "pymysql": ["PyMySQL==1.1.0"],
         "pg8000": ["pg8000==1.29.8"],
         "pytds": ["python-tds==1.12.0"],
         "asyncpg": ["asyncpg==0.27.0"]


Test patch:
diff --git a/.github/workflows/tests.yml b/.github/workflows/tests.yml
index 91378bd..84c4e25 100644
--- a/.github/workflows/tests.yml
+++ b/.github/workflows/tests.yml
@@ -61,7 +61,7 @@ jobs:
             }
 
       - name: Checkout code
-        uses: actions/checkout@8e5e7e5ab8b370d6c329ec480221332ada57f0ab # v3.5.2
+        uses: actions/checkout@c85c95e3d7251135ab7dc9ce3241c5835cc595a9 # v3.5.3
         with:
           ref: ${{ github.event.pull_request.head.sha }}
           repository: ${{ github.event.pull_request.head.repo.full_name }}
@@ -179,7 +179,7 @@ jobs:
             }
 
       - name: Checkout code
-        uses: actions/checkout@8e5e7e5ab8b370d6c329ec480221332ada57f0ab # v3.5.2
+        uses: actions/checkout@c85c95e3d7251135ab7dc9ce3241c5835cc595a9 # v3.5.3
         with:
           ref: ${{ github.event.pull_request.head.sha }}
           repository: ${{ github.event.pull_request.head.repo.full_name }}
diff --git a/requirements-test.txt b/requirements-test.txt
index 7d0b5a3..cbd83e0 100644
--- a/requirements-test.txt
+++ b/requirements-test.txt
@@ -1,8 +1,8 @@
-pytest==7.3.1
+pytest==7.4.0
 mock==5.0.2
 pytest-cov==4.1.0
 pytest-asyncio==0.21.0
-SQLAlchemy==2.0.16
+SQLAlchemy==2.0.18
 sqlalchemy-pytds==1.0.0
 flake8==5.0.4
 flake8-annotations==2.9.1
@@ -11,9 +11,9 @@ mypy==0.982
 sqlalchemy-stubs==0.4
 types-pkg-resources==0.1.3
 types-PyMySQL==1.0.19.7
-types-mock==5.0.0.6
+types-mock==5.0.0.7
 twine==4.0.2
-PyMySQL==1.0.3
+PyMySQL==1.1.0
 pg8000==1.29.8
 asyncpg==0.27.0
 python-tds==1.12.0
diff --git a/tests/unit/mocks.py b/tests/unit/mocks.py
index 0610c67..47b4605 100644
--- a/tests/unit/mocks.py
+++ b/tests/unit/mocks.py
@@ -219,6 +219,7 @@ class FakeCSQLInstance:
                         datetime.datetime.utcnow() + datetime.timedelta(minutes=10)
                     ),
                 },
+                "dnsName": "abcde.12345.us-central1.sql.goog",
                 "ipAddresses": ip_addresses,
                 "region": self.region,
                 "databaseVersion": self.db_version,
diff --git a/tests/unit/test_instance.py b/tests/unit/test_instance.py
index dcce7a1..0886410 100644
--- a/tests/unit/test_instance.py
+++ b/tests/unit/test_instance.py
@@ -301,6 +301,11 @@ async def test_get_preferred_ip(instance: Instance) -> None:
     # verify private ip address is preferred
     assert ip_addr == "1.1.1.1"
 
+    # test PSC as preferred IP type for connection
+    ip_addr = instance_metadata.get_preferred_ip(IPTypes.PSC)
+    # verify PSC ip address is preferred
+    assert ip_addr == "abcde.12345.us-central1.sql.goog"
+
 
 @pytest.mark.asyncio
 async def test_get_preferred_ip_CloudSQLIPTypeError(instance: Instance) -> None:
@@ -319,6 +324,10 @@ async def test_get_preferred_ip_CloudSQLIPTypeError(instance: Instance) -> None:
     with pytest.raises(CloudSQLIPTypeError):
         instance_metadata.get_preferred_ip(IPTypes.PRIVATE)
 
+    # test error when PSC is missing
+    with pytest.raises(CloudSQLIPTypeError):
+        instance_metadata.get_preferred_ip(IPTypes.PSC)
+
 
 @pytest.mark.asyncio
 async def test_ClientResponseError(