 So are you enjoying for them so far? Okay, I can do that because So this we have not yet started. We're just making time for everybody, right? So I work for the Wikimedia Foundation. I Don't know if everybody's familiar with it Do you know what I'm talking about? Did I say Wikimedia? People usually recognize better the brand Wikimedia So we at the foundation are a non-profit organization We are funded just by very generous donors and our mission is to support all the open knowledge projects That are maintained by the community. So we are just The foundation to support them. We don't own Wikimedia. We don't Have a decision on what's being edited or not what we try to do is to support Financially in terms of software development so a media wiki mostly another software related to run the wikis and In particular our team is in charge of maintaining the servers to receive a huge amount of traffic that We have to to be able to serve not only wikipedia Wikipedia is the most famous project But there are a lot of other projects not as well known. I don't know if you're familiar with wikimedia commons It's a repository of files mostly images sounds and video, but also other Other files are actually just uploaded The slides to wikimedia commons. Yes, I Configure this Okay I had this very good idea of disabling the touchpad when Mouses on and it doesn't work And now there's another project and I will start Soon, there's another Very successful project which I personally was quite surprised which is wiki data You familiar with it, which is not just having text and Images on wiki pages, but actually have a repository of a free database a database of everything So it's a structured data that can be queried by Anyone so if you're interested on knowing more Please visit there's a wikimedia.org and the wikimedia foundation or websites and you can check all our projects and Of course, I will Encourage you to edit wikipedia. Let me ask who who part is it a party It makes some content or edits or has ever edited on wikipedia or any Okay, the rest. What are you waiting for? We need your contributions Please please it takes just a mobile phone to edit Something and with things like wiki data Photograph an image it takes very little to contribute the other thing Just before we properly start is We are hiring on the Cyrela ability engineering team. So if someone was to work with us after even after everything I will tell you Please contact me or you can go to this website. Yeah, you can trust him on that What's this job? My school enters in a bar and says goes to a table and says can I join you? As you can see I'm horrible I said I need to try Okay, let's go so I did the Long introduction before I Will present now myself properly and Jaime Crespo. I work as a DBA database administrator in the technology team for the wikimedia foundation and I'm going to talk about TLS TLS I will be talking sometimes. I will mention in TLS sometimes. I will be saying SSL In all cases I will really mean TLS but because my school and Marielle is Still referring on configuration and many other projects as SSL sometimes. I will say SSL and one of the myths I have been found around TLS is that it is hard and it is it doesn't work because I have a lot of load So I cannot enable it. So I need to run in a less secure environment For the first thing which is TLS is hard. I have to say no it is not This is an actual commit of our public Puppet repository we use puppet for configuration management and this is a Commit that I personally did I think a couple of years ago and Literally, this is everything that it is to Enable TLS is literally three lines of code three. This is on the client This is on the server. This is an actual the actual production configuration that we use for for media wiki and Yeah, well, I added some flowers so we can enable and disable that will but that's it so That's how you enable TLS. I'm done. Thank you very much. That's all The thing is The rest of the talk I won't be telling you about TLS. I actually don't know that much I'm not a security expert. I'm not a traffic expert. We have people that knows too much about knows enough about cryptography about a security what I'm going to talk to you is about the operational pains as a TVA to Enable this on a relatively large scale. So I will focus now on the large-scale part I'm not actually sure if we are that large And let me ask you for example how many of you on your own are Responsible for handling at least one MySQL Maria Dior per Kona server Okay, keep your hand up if you have more than 10 if you handle on your own more than 100 More than 1000 Okay, we can see that those people are the ones that are really Large-scale the thing I decided to call out ourselves large-scale is because If I show you Things like this Is that we one of the great things of being able to work on the Wikimedia Foundation is that I can actually show you this this is my and wiki installation on over two data centers and We have a few of this and I can keep scrolling If you ask some of those people that have thousands and I know they're much larger than us they probably can't show you this I can show you in real time. This is actually real time of how many queries how many servers So my actual My actual What I want to show you is How I fail to actually enable TLS it's actually an ongoing project because I can all open speak openly about Everything that I did and I felt so maybe you don't have to commit the same mistakes that I did and This is basically a summary of Everything that we have on our tracking system, which is open for everyone to to see The best of the biggest failure probably was that we rush to production I was when I was working. I was at the time the only DBA. I was in charge of maintaining all those databases Putting out fires. There was very little automation. I will talk about that later But we were going to do a data center failover for the first time We needed encryption on the replication channels on the my score replication channels. So what did I do? What did I do? I did that commit? I showed you before I enabled TLS and I said, well I mean, this is just for a test. So I will just create a test certificate What happens with test configuration? Test deployment that test things become permanent But I was wise I knew I had to for myself to know something that is temporary to be permanent So what did I do? I generate a temporary certificate for it was just one year in one year. It will expire Do you know where I'm going to? Do you know who was restarting servers in a rush because the TLS was about to expire Yes that So That's a lesson TLS is not difficult to do it But probably it was my fault to not push back and say hey if we need this to be done with a specific schedule Maybe we can do other kind of encryption that is easier to switch and to manage An SHH tunnel Big BN encryption anything that we can like change quickly and later we will do What I had to do which is in this case work three times as much first to enable things Do a full fleet restart then and do the things and then redo the things properly The other point was resources and happily after that I bought at least Some help to manage that I'm not longer male The other problem we face is Orchestration that's something that again at the time we didn't have the proper resources so We weren't able to do very quick full fleet restarts and That's something that I think is One of the other big problems with my school And I will talk later about things I would like I would like to change on my school to make things easier And is that every time that you have to enable for the first time? encryption or You have to change the certificates which was the case on the second roll over change the certificates to In this case we started using The our the same puppets Certificates that we were using for for our configuration management. We reuse them for my school too But every time there was an open SSL back You were supposed to also restart my school in this case not because of a change of a certificate But also because well you have to upgrade open SSL upgrade open SSL is quite easy But my school has to get the new serve library and for that you need to do a restart and there was a problem in terms of Server and client coordination because At that time we enable only Encryption just for replication But if we wanted to change the CA it was always an internal CA The cross DC replication from that doesn't everyone to that does that you it was not easy to Coordinate that because if you change it here now Replication is broken. So of course you could just disable replication, but at that point the point was no no once we have an able Encrypted replication we cannot go back to plain text so we had to do Complex topology changes to be able to replicate from as different from a replica that had a different CA Do failover? Most of these would have been much easier if we had what the tools that we have right now in terms of orchestration to Handle these easy. You want to know more about orchestration. There is a talk on the configuration management track by Ricardo, and I recommend you to go there and check what some OpenShort tools we have developed. I Already mentioned this server support We hit bugs with Encryption not working literally some other versions weren't compatible with the new standard of TLS and I Don't know exactly what was the issue, but The version that we were at the time a couple of years ago We were still on 5.5 didn't absolutely work for the kind of creation that we want. Okay. We just have great The other issue that we face is that Only OpenSSL linked packages of MySQL works with modern versions of TLS I'm talking 1.2 and in the future 1.3 Which means at that time they both the upstream packages and Once that were provided by our distribution either Ubuntu or Debian they didn't work in in fact Debian considers OpenSSL license the old one or the current one and not compatible with EPL So it's not about and another question of this doesn't work No, they at least for what I've told there don't plan to link it because of Some technicality on on the license which I'm not against but we definitely needed strong eclipsing Here it will want to do it. So not that was not the only reason. We also wanted to Have a custom applied patches security backports and other stuff but we one of the things we had to do is to Create our own packaging of MySQL Well in this case MariaDB. We're currently using MariaDB 10.0 And we're migrating to MariaDB 10.1 More pain Client support third-party support Some of the developers may also be here We have problems with PHP 55 that wasn't fully compatible with OpenSSL 1.2 And this is probably in general the biggest pain most client libraries connectors Etc have some kind of support for open for sorry TLS But not 1.2 and the problem is TLS 1.0 and 1.1 has Protocols that are vulnerable to security problems To security exploit so we definitely wanted to go for the secure For the secure Protocol for the one that at this time doesn't have no bugs at least there Depending on the end of Christian algorithm The other thing is we have been starting paying with things like Proxy SQL and that's something that René I have been sending him bugs and And The problem with Proxy SQL for example, which is not a client or a connector Is that because he was using an older client the client that Proxy SQL integrated Didn't support 1.2 and that's kind of a blocker as you can see for for us and then Something that is a bit curious people were starting to see that they tried colleagues MySQL and they that stopped working and it says something like TLS or something like that. Why was that? Well one of the biggest problems that I have To work to make me understand is that Handling TLS for my school is a complete different problem to handle or enabling TLS for something like a Pasi a Pasi has the concept of virtual host The way you use a Pasi is normally with the public certificates that is Validated by the external CA in our case we handle the whole stuff with our own internal CA and Typically you connect through an IP address, but the certificates are based on a domain So if you go through local hosts for example through a socket and we have enabled validate the domain You cannot connect because the domain and local hosts doesn't match So that's a silly thing, but something to to have into account when enabled this There were some things that were well, I have to say one of the things that I think we did well is Rolling it when we roll it into production we did opt-in which means that we enabled the support But every single client can be Slowly enabled on the use of TLS For example we started with the replication channel because it's really easy if you want to use encryption Starting to enable TLS through the replication channel. It has almost no performance impact. I will tell you later Why but it's mostly that it's a single connection that is ongoing all the time There's no a lot of connects and disconnects and the impact on performance is almost at least in our case No, no performance back there. The other the second step is enabling for administration channels or things that are not pure client heavy hitters things like administration things like Schema changes things like things that don't really need the performance It doesn't matter if connecting takes one second when the connection takes one second because you know, you can wait Also going from through TLS 2.2 because if We had to we had enabled it with the with the non open SSL Yeah, SSL or one of these products that doesn't provide you full compatibility We will be doing again even more work to now enable TLS.2 or in the future doctoring And well support in terms of we are in a place that we people are really concerned about privacy and security so having the support from management on You know, yes, spend time on this because this is important. It's nothing that you really want to have You don't want to say hey, don't waste time on See that security stuff because it takes a toll on you We are not aiming for 100% coverage right now When I created this proposal I was hoping that we will already have the 100% coverage, but It will eventually come and most cases in our case it It is due to Application changes that are needed and we are now on to my great from HHVM to PHP 7. So There's a lot of work that gets there and You know, there's only two DBAs. So we work on this on our free time. Let's say This is a couple of things I wanted to show you for the idea of I'm worried about how much of a performance hits this will have on it on us And I think you can trust me that we have quite some performance if I have time I will show you our real-time monitoring that is public of how many queries how many reasons you have This is some test that we don't idle host. So it's not fully representative of an actual Full load but what forget about so many numbers and I want to remark that the difference between SSL or TLS connections is What is Can be problematic. We are talking about 20 times to 50 times slower of the time For connecting but in terms of actual query performance the impact is less than five percent so What what does this tell us just fix the client library so they use persistent connection and that's the whole point of our interest on A proxy SQL so we can have prepared there's some kind of pre-using of connections at application level right now but we need full Pre-creation of connections so that that overhead is already Already forgotten by the time you need a query ready. So you could do the same to In our case there could change the architecture takes a bit of time And everything's like proxy as well We are not in a rush because we wanted these for mostly Cross DC queries which right now we don't do but we may be and do entirely doing But we may fix that in a different way in which you only have we may only need to do Same DC queries, which is what we want to do The last thing is okay. How could you ease my pain the rest of you? There's literally and probably more Free a box the last one is mine the first one I think is by Simon is Simon here And the second one is by Eric and this is our box already to please allow us to change certificates in a hallway So if there's any people from my school over there, please have a look at those bars. Maybe like Marcus duplicates From the people that are not part of my school But that part of the my school community support of proper TLS Would be rates Renier is going for example in case of proxy SQL super Helpful in that case I'm proper open SSL one point one support which I Think it's a valuable in both my school and Maria B on the latest versions But we cannot all be on the latest my school or Maria DB versions So it would be helpful to kind of support all their versions of Of my school or Maria DB. Oh, well, if you have metrics your test, please share them I will that's exactly what I'm doing here. I already talk about pending work But that's that's it Basically finishing the immigration one thing I didn't mention is TLS is not only for Security from the point of view of someone is in the middle trying to Get queries that are going or private data. The other thing is Enabling more secure authentication methods, which until 8.0 It was only possible if you were using TLS and the connection so That's it. Thank you very much If you have any questions We may have one minute for that a couple of minutes. Yes so There's there's several components because so I will repeat the question. I just want to show you the This is our load of most of all that is so the question is what do we use for our crustacean? So it's a combination of things depending on What you call our crustacean one of the things that has been introduced recently is a custom made tool which was called Cummin, which basically is a replace a proper replacement for salt Which was what was used for executing remote commands in a parallel way? And this is a much better replacement. You won't know about that. That's that the talk I I mentioned In terms of my score crustacean We are kind of trying to build a custom library because things like orchestrator It doesn't fit very well our own case use cases in terms of Pulling and the pulling servers In the past we were not that was not a tool that was not something we were wanted. We had to go through the database code commits which was horrible the the current work right now is to have some kind of dynamic configuration Object server and probably is going to be something like a TCD so we can change much more dynamically the weights and the pull servers once we have a TCD and With the help of Cummin, it will be some things but That are left like automatic master slave switch But that I think will be much easier once the other things are fully on on place I don't know if that answers your question. So a lot of different things and not something like Commercial in terms of something that already exists more questions Yes, so Our full stack is Maria TV, sorry PHP Maria TV Varnish, well, there's a lot of technology there as a rule. We just use open source or free software Around the whole so we don't let anything anything to be installed on our service if it's not open source I think there are some exceptions like people in the office may use windows or something like that But in terms of servers, yes, it's fully open source Yes Yeah, yeah So the problems that the the idea is will have a double certification you can migrate easily yes, and Definitely to be fair right now. That's not a huge problem because the problem was Migrating from the bad testing stuff to the proper stuff And definitely there's ways to solve that I may not be using double certificate because it doesn't actually solve the possible compromise of the CA authority so you still have to have a way to Kind of restart the server to introduce Like a new non-existing CA and in terms of for example replication is actually much easier than I said because The certificates and the combination for replication is purely dynamic already. So it's not really a huge concern It was just at the time Show me like let's talk outside because it's a much larger question