Create database connections from pre-configured SSL Socket

[jackwotherspoon]

Is there the ability in asyncmy to create database connections from a pre-configured SSL/TLS Socket?

Pymysql allows a sock argument to it's connect method (code) that allows passing in a pre-configured socket, wondering if asyncmy has something equivalent in the asyncio context?

I ask because the Cloud SQL Python Connector would like to support asyncmy as a driver option for connecting to Google Cloud SQL instances. Currently the only MySQL driver we support is pymysql (code) as we need the ability to pass in a pre-configured SSL socket/connection. I wonder if this is possible with asyncmy?

Let me know, happy to give further details 😄

[long2ice]

What about ssl argument for connect? See https://github.com/long2ice/asyncmy/blob/dev/asyncmy/connection.pyx#L131

[jackwotherspoon]

What about ssl argument for connect?

The ssl argument does not work for our use-case.

Cloud SQL connections are made via connecting to a server side proxy which then establishes the connection to the MySQL database itself. Because of this we need to skip the startTLS database protocol and connect directly with TLS outside of the driver code. Originally I was going to support aiomysql and there is a discussion on an open issue aio-libs/aiomysql#757 about a slightly different approach of having a direct_tls argument that tell the driver code to skip the STARTTLS negotiation.

However, after thinking it through for the past few months I think a better approach is to use a sock argument similar to pymysql which we already support with the Cloud SQL Python Connector (implementation code).

Maybe for asyncmy we could do something similar by allowing a reader and writer to be passed in (maybe combined as a tuple)? This would allow the Cloud SQL Python Connector to establish a direct TLS connection outside the driver code and pass it into the connect() method.

That way the Cloud SQL Python Connector could have the implementation code as follows:

import asyncio
import ssl
from typing import Any, TYPE_CHECKING

SERVER_PROXY_PORT = 3307

if TYPE_CHECKING:
    import asyncmy


async def connect(
    ip_address: str, ctx: ssl.SSLContext, **kwargs: Any
) -> "asyncmy.Connection":
    """Helper function to create an asyncmy DB-API connection object.

    :type ip_address: str
    :param ip_address: A string containing an IP address for the Cloud SQL
        instance.

    :type ctx: ssl.SSLContext
    :param ctx: An SSLContext object created from the Cloud SQL server CA
        cert and ephemeral cert.

    :type kwargs: Any
    :param kwargs: Keyword arguments for establishing asyncmy connection
        object to Cloud SQL instance.

    :rtype: asyncmy.Connection
    :returns: An asyncmy.Connection object to a Cloud SQL instance.
    """
    try:
        import asyncmy
    except ImportError:
        raise ImportError(
            'Unable to import module "asyncmy." Please install and try again.'
        )
    
    user = kwargs.pop("user")
    db = kwargs.pop("db")
    # allow automatic IAM database authentication to not require password
    passwd = kwargs.pop("password", None)
    
    reader, writer = await asyncio.open_connection(ip_address, SERVER_PROXY_PORT, server_hostname=ip_address, ssl=ctx)
    
    return await asyncmy.connect(
        user=user,
        database=db,
        password=passwd,
        sock=(reader, writer),
        **kwargs,
    )

Is this something that seems like a viable feature request to you? Happy to begin working on it if so 😄

[kurtisvg]

hey @long2ice -- is this something you'd be interested in taking as a PR? We'd love to add support for this driver in the Cloud SQL python connector