CD2: MySQL+SSL / Open Source Plans

[mcfa0086]

Howdy all - We're interested in using DAP with a MySQL cluster which requires SSL for connections. From digging in, it seems like there's not a way to make aiomysql do SSL connections when using the connection URI scheme for specifying database connections - sqlalchemy parses the extra params (ssl_ca, ssl_key, etc) but aiomysql doesn't setup an SSL context fully. 

Curious if anyone else is looking at this, or has solutions. Aiomysql does support SSL when using the more explicit parameter based connection setup (you can pass in a pre-created SSL context). 

Related, any news about plans for throwing this project up on Github so we can put forward PRs? 

-Colin

[LeventeHunyadi]

The existing 0.3.x series of the Python client library is now closed for feature requests.

For the upcoming 0.4.x version series of the client library, we are transitioning away from SQLAlchemy in favor of direct connectivity with asyncpg (for PostgreSQL), aiomysql (for MySQL) and pyodbc (for Microsoft SQL Server). In this new setup, we have more control over how the connection is established, and we can incorporate a pre-created SSLContext, and pass that as the ssl parameter of aiomysql.connect.

Many of the dependencies for the upcoming version are already open-source and available on GitHub, e.g. json_strong_typing (which helps us synthesize Python classes from JSON schema) or pysqlsync (which helps us synchronize schema and data with a database server). We have been planning on making the new version of the Python client library available on GitHub as well (once it's ready) but we don't have a date yet.

[SpencerWilliams]

Is there any way to enable SSL for MySQL via the DAP_CONNECTION_STRING uri? For example, something like:

DAP_CONNECTION_STRING=mysql://cd2:password@localhost:3306/cd2?ssl=true

[SpencerWilliams]

@LeventeHunyadi, would you be able to answer this question or do you know anyone else that you may recommend to answer this?

[LeventeHunyadi]

@sgergely is our new Product Manager responsible for all things DAP client library. Behind the scenes, database connections are managed by an open-source library called pysqlsync, and I am more than happy to add a configuration option similar to what you are proposing to set connection parameters (such as SSL) explicitly. However, DAP client library adds a thin wrapper to pysqlsync, and Gergely's team would need to do some development and testing such that the new parameter propagates. He can advise on the product roadmap whether and when this is planned.

[SpencerWilliams]

That would be very much appreciated!

Hello @sgergely. Could you please advise where this feature is on the roadmap? I am needing SSL support for the DAP client (CLI and library). I am using a MySQL connection. Please let me know if there is anything I can do to help!

[sgergely]

Hello,

I'm currently in the process of putting together the roadmap for the DAP API and CLI and this is a great input to add it as a feature.

Unfortunately I cannot give you an estimate when is this going to be added but as soon as I have any info to share I will update the community definitely.

Currently DAP CLI is not opensourced but the Levente mentioned pysqlsync is available as an opensource repository and it is not maintained by Instructure.

[SpencerWilliams]

What is the best way to be informed when there is news about the roadmap or any info related to plans regarding the DAP package/cli (official newsletter, specific forum thread, etc)? Thank you for the reply.

[sgergely]

Regarding the DAP Library and CLI I recommend checking the Release Notes and the Product Blog here in the Community pages because we are crossposting all releases here as well. You can follow me here in the community as I'm the product manager for the Library and the CLI client. I'm collecting your feedback here: https://community.canvaslms.com/t5/Data-and-Analytics-Group/Help-Shaping-Data-Access-Platform-CLI-am...

Regarding the overall roadmap I recommend to follow Shiren our CPO.

[SpencerWilliams]

I followed Shiren here on the Canvas LMS Community site, but I didn't find any roadmap mentioning the DAP CLI.

[sgergely · Accepted solution]

Hey @mcfa0086 and @SpencerWilliams 

just a follow-up on this, we have released DAP CLI 1.2.0 which supports secure connections to MySQL and PostgreSQL. Please update and check it out here: https://data-access-platform-api.s3.amazonaws.com/client/README.html#secure-connection-to-the-databa...

[SpencerWilliams]

This is fantastic news! Thank you for the excellent update.