Issue: Suport SSL connections
Author: mgalvey
State: closed
Created: 2017-08-22 17:36:44

At present, it appears that peewee / pwiz has no support for connecting to MySQL or Postgres using SSL. Such a feature would be useful in environments where the database is configured to only allow encrypted connections.

I'm not as familiar with Postgres, but I know that at least two modes should be available for MySQL: require SSL, and require SSL + verify CA.

Peewee operates at a higher-level than the database drivers, so any SSL support would need to be implemented at the `pymysql`/`psycopg2` layer instead of Peewee.

It is implemented in PyMySQL (http://pymysql.readthedocs.io/en/latest/modules/connections.html?highlight=ssl), but I'm not sure how I would make use of that in pwiz.

I haven't tested, but it seems like the recommended way to do this in peewee proper would be to use connect_kwargs on instantiation of a Database object, correct?

You would just:

    db = MySQLDatabase('db_name', ssl='whatever')

Any keyword arguments that peewee doesn't recognize are passed back to the db driver, so `ssl` would get passed to pymysql in this case.

Right. That makes sense for peewee proper and is in line with what I expected I'd have to do from your initial response.

Is there any way to do something similar with pwiz, or is that just something I'd have to make modifications to the code for? It looks from the current pwiz code like there's not an obvious way to get arbitrary options into the database constructor. Would a PR to add a convenience flag to pwiz (--ssl, for instance) be welcome?

Oh, I understand now what you're asking -- you may need to make some changes to pwiz or just write a wrapper script to do the same thing pwiz does.

@coleifer Also mentioning in this Issue. With the sensitive data we're dealing with and the MySQL DB that enforces SSL connections, we need a way to connect.

Something you would consider supporting?

Same advise still applies:

> you may need to make some changes to pwiz or just write a wrapper script to do the same thing pwiz does.

What is with putting SSL on MySQL? Is your db exposed to the internet or something? Clustered across multiple data-centers?

So I'm not quite sure how pwiz works yet. Still working through the code and looking.

But to answer your question first, yeah, the DB is exposed to the internet. It's not clustered across multiple datacenters or anything but it does need to be accessed by multiple parties over the public internet.

Azure even has a management portal setting in the Azure MySQL server product that defaults to enforcing it. It seems like it's an important enough use case for them to care about it and even go so far as to default to it for all customers.

![image](https://user-images.githubusercontent.com/29720335/59741577-36f2c780-9220-11e9-9ca0-83835cea20a0.png)


> But to answer your question first, yeah, the DB is exposed to the internet. It's not clustered across multiple datacenters or anything but it does need to be accessed by multiple parties over the public internet.

What in the fuck. If its in Azure, presumably you have it only visible on your virtual LAN, I hope (pray)? Or that at the very least you're not handling any important data...

Nope, it's exposed to the internet internet. It's not that uncommon of a use case. Azure has other protections of course like only allowing source IPs that are explicitly specified by the user to connect. By default SSL is enforced and no source IPs are allowed through.

There's even a dev page to describe how to do it right.

https://docs.microsoft.com/en-us/azure/mysql/concepts-firewall-rules

Not everyone has a corporate VPN that they can hide all this behind and for not-so-sensitive data these protections are enough but that doesn't mean you want snooping obviously.

In our particular use case at this very moment it's just development so all IPs are known and the data isn't particularly sensitive but that doesn't mean I'd want people listening in. SSL in everything I'd think should be standard. Even within a vlan I'd use it.