MySQL: Support SSL with system certificate store

[janpio]

As described in #8697 there is currently no way to use a MySQL database via SSL with a certificate validation via system certificate store.

After a longer internal discussion, we concluded the following change to have the least existing user impact and still giving a great user experience for that use case:

1. Add a magic string sslcert=system that only enables SSL, but does not set a certificate location internally. This will trigger cert validation from the system store as PlanetScale requires it. (If we ever clean up the API completely, we can easily keep this special string working.)
2. We fix sslaccept to trigger SSL for MySQL as well, so just supplying sslaccept=strict would also work for a certificate that should be strictly enforced (vs. our default of accepting any)

Both changes need to be implemented in Quaint.
This can partially build on top of https://github.com/prisma/quaint/pull/312/files (see comments).

[dbussink]

1. Add a magic string sslcert=system that only enables SSL, but does not set a certificate location internally.

In which cases is this option useful / needed? I think adding this is only confusing and not needed in any case where it makes sense as an argument?

The default setup if sslaccept is provided is ssl_opts.with_danger_accept_invalid_certs. This means that any certificate will be accepted already. This means that that the sslcert=system is a very confusing way to enable SSL, because no certificate is actually used so the setting seems irrelevant looking at the explicit name and the value it sets.

2. We fix sslaccept to trigger SSL for MySQL as well, so just supplying sslaccept=strict would also work for a certificate that should be strictly enforced (vs. our default of accepting any)

I think only this and not doing the other option makes much more sense as the simplest solution here. Anyone who wants SSL with no validation can set sslaccept=accept_invalid_certs which conveys imho the meaning of the option much more than sslcert=system.

For those that do want SSL validation, sslaccept=strict would then be enough to add.

[dbussink]

This means that that the sslcert=system is a very confusing way to enable SSL, because no certificate is actually used so the setting seems irrelevant looking at the explicit name and the value it sets.

I think this is also actively harmful for security. If I see sslcert=system I'd expect my certificates to be validated agains the system roots, but that's not the behavior that happens if no sslaccept is provided. That seems very surprising and makes it less secure for people. Only changing sslaccept seems like a more clear and explicit change.

[dbussink]

1. This will trigger cert validation from the system store as PlanetScale requires it.

This part isn't needed, since the driver already uses system roots by default if nothing is specified. So no option is needed at all to use the system roots (and as mentioned above, I think it's harmful for security as it's confusing and doesn't do what the option would seem to imply).

I have updated prisma/quaint#312 to include the simplest possible change, only adding the use_ssl change when sslaccept is set. I think that's the best path forward here to be honest with the smallest possible change that also should not break any existing users.

[dbussink]

@pimeys Am I reading the milestones correctly that the first version to be released with this fix would be 2.31.0?

[pimeys]

The next one, which is either 2.31.0 or 3.x. Out hopefully on 7.9.2021.

[dbussink]

The next one, which is either 2.31.0 or 3.x. Out hopefully on 7.9.2021.

@pimeys Do you all do things like back porting for security fixes etc? I don't know how easy / often people usually upgrade their versions of Prisma and if a new major release would be harder to adopt?

Mostly asking since I'm coming from the perspective of how to make it as easy as possible for everyone who uses PlanetScale to get the update and improvement into their deployment 😄.

[pimeys]

I'm probably not the best person answering to questions about back porting. We are going to follow semver much more carefully soon, and there will be some breaking changes on the next cycle that require us on going to 3.x.

This is more of a product manager question, so I shared the link on Slack.

[janpio]

I can speak to that:

Until now we never had the need to backport anything. Now with us adopting real SemVer (we did breaking changes before, just did no follow the versioning scheme) that might become more relevant - but we do not think that is already the case with 3.x.

Generally our userbase fortunately is upgrading and moving forward rather quickly and confidently (exceptions apply if we introduce new bugs or change behavior in ways that prevents special setups or configurations, but we are working hard to remove these as fast as possible - or help the users through the migration or adaptation hands-on) so these problems usually only apply to people that just forgot to upgrade or were not aware that there was a new version, and that upgrading to them is a good idea and what we always recommend to users.

3.x will be non breaking for 99% of our users, so a pretty painless upgrade. We expect the upgrade rate to not be much lower than before (besides automated tooling requiring an explicit merge from users). We will listen to our users of course, and see if our assumptions need to be updated. For now we think and hope that will not be the case and we can keep with our "always forward" strategy for some more.