 Thank you for the introduction. It's good to see all of you here. I'm right before the lunch break So I'll try to be as quick as possible How many of you here use a database server? Okay, that's pretty much all of you. Okay. That's good. How many use my esquial of MariaDB? Okay postgres MongoDB Something else well I can't hear anyway Good to see all of you here I currently work at Pricona for the I've been there for almost two years and before that I was on the founding team of MariaDB server which is fairly popular it's got over 12 million users now and before that I was at this company called MySQL and We we made MySQL and we sold the company to Sun and I've also been involved with other open-source projects and if you're wondering where I come from I come from Malaysia and Today of all days. I'm extremely proud to be in Malaysia because we love you government These slides will be released under creative commons So you can get them. Also, it should be Quite verbose that if you download them later, you can try pretty much everything fairly easily by copying and pasting in theory So Let's talk a little bit about attack vectors and what we're what the focus of today's talk really is about I'm gonna focus a lot about external attack vectors where you know, you may have trust based authentication turned on People grab your passwords or your authentication method gets broken into things like network snooping and or spoofing theft of a physical server I put physical in italics largely because maybe a lot of you use the cloud as well But you know, you could also get stuff stolen from cloud server And also how we protect yourself against administrators. So, you know earlier we've heard examples of admins that leave who get annoyed maybe you fire your sys admin or Or if you come from the internet from a long time ago, the famous term is the bofh So maybe you've got an admin that's a bofh and I can't probably say that out loud But I'm sure you know how to find out what it means What we won't focus so much about today is permissions, right? So permissions in the mysql world is like brands That it's very hard to master grants, but I highly recommend you to master it So you don't grant all on everything to everybody Obviously can't cover SQL injection. There are plenty of frameworks out there that you could use to Figure out why you shouldn't have SQL injections in your code Your application itself might be vulnerable So, you know, not to pick on WordPress, but you know, it powers nearly 30% of the internet If you don't update WordPress, you may get broken into sometimes and it may happen at the most inconvenient times like, you know Right after Christmas right before New Year's So turn on automatic updates, maybe now, of course operating system vulnerabilities where possible you should always obviously update your OS Naturally this talk is also very Linux centric because I don't know anyone around the production database on Microsoft Windows Probably you don't either So let's let's give you attack vectors from a much higher level before we go down into the weeds So trust base security Postgres calls this trust authentication and you can enable this for local connections as well as external connections in theory and this actually happens via socket authentication and you can find this fairly easily when you When you start up the server and you can configure this in your PG HBA con MariaDB server also provides this by default authentication via unique socket and MySQL called the socket peer credential authentication now In MariaDB server, it's turned on by default Why do you think this is probably not a good thing? Largely, this also means that if your operating system user Manages to get broken into so let's say you've created a database user and the password for that The Linux password for that has been leaked somehow and that person manages to log in that but now that person also Manages to access your database So trust base security is probably not a good thing to have as a default Actually, it's easy to get started. So when you install MariaDB server, it's awesome You don't have to enter a password, but it's not something you want to run in production When it comes to password snooping In Postgres, you know, it's stored basically is MD5 But you've also got the ability to have these scrams char256 now Which will actually avoid the risk of duplicate solves being replayed It's highly recommended that you use scrams char256 If you're not already doing that and if you're following the development around Postgres 11 You'll realize that they've also got channel channel binding coming Which will allow your authentication to be very similar to certificate-based authentication going forward In MySQL today, you want to use the caching char256 password So I'm very proudly wearing a MySQL 8 t-shirt today Would have thought that I'd actually put on something with an orca logo on it But MySQL 8 got released maybe two weeks ago as a generally available release It's it's got a lot of security features baked in it's highly recommended you you try it and I'm gonna talk quite extensively about how it's made security better And in MariaDB server, there's this thing called added25519 Which is similar but not the same Created by Daniel J. Bernstein and it's not turned on by default, but it's highly recommended you do And we'll talk about why MySQL native password is Not secure anymore because it basically uses Sha one double Sha ones When it comes to password attacks, this could be a weak password like test123 or ABCDEFG I'm sure you know many of you have got users that do this The other one is reusing old passwords. So many Many compliance, I don't know what it's like in India for compliance But overseas you have things like HIPAA and so forth that you know force you to have compliance and I think the biggest deal now maybe is the EU GDPR and One way to protect against Compliance is they tell you to change your password every 90 days or something. I personally hate that If I have already got a good password fire password manager, but it turns out that you know many to follow compliance you have to reuse old passwords and People tend to well you don't want to use old passwords You have to change passwords and then people tend to reuse old passwords So you need to track changes of passwords that have been changed And then of course this brute force password attacks So you can actually have the ability to increase the time before someone logs it logs in So if you get a failed authentication three times, maybe you time it out by say a minute and another three times five minutes another three times you lock that down and by far the best in terms of Preventing password attacks is mysql8. It's really got a lot beyond weak passwords as well as reusing old passwords Three DB server can definitely help you against weak passwords postgres itself. It doesn't have anything Built-in so far, but you can use external authentication like LDAP SSPI SSPI is another nice way of saying active directory and or Kerberos right GSS API and MongoDB also has external authentication By LDAP but only available either in the MongoDB Enterprise Edition Which you pay some money for or persona server for MongoDB, which you get free as persona makes all the software open source network operations When you connect to your database server You want to connect in you know Plain text or you want to make sure you're doing it over as a cell You definitely want to do it over as a cell if you're replicating you definitely want to do it over as a cell A lot of people tend to say oh Every time I make it a connection with SSL. It's it gets really expensive Not true. The overheads are definitely not as bad as you think You never ever want to replicate in plain text in the cloud now replication by default tends to be in plain text So you definitely want to turn SSL on you don't only want to prefer or require SSL, right? You want to also make sure that you're verifying the certificate of that Authority to make sure that this is so we actually talking to I've got a bunch of references here, and I also talked a little bit about using a proxy called the proxy SQL so bunch of references where you can get some pretty graphs now Basically in 2017 You will actually notice that there is Yazzle as well as open SSL So Yazzle is a is a SL library now also commonly known as Wolf SSL Previously it was known Something else to keeps on renaming itself every few years There's also open SSL with the fairly famous library In fact, most modern my SQLs you get today are fully based on Against open SSL and you realize that the connection time is actually not not very high the latency with having Cell is not bad, especially once it gets cash Also, if you end up using something like a proxy Like proxy SQL to manage your large amounts of servers You can really leverage the time reduction to complete say 10,000 connections even So it's a huge boost So most large-scale deployments tend to always have a proxy between the application servers and the database It comes to data theft You can obviously encrypt your entire disk which makes a lot of sense Postgres has column encryption, which is kind of nice. A lot of people ask for this inside of my SQL as well So you could you know, you wanted to saw credit card detail you could in theory and that column and Both MariaDB server as well as my SQL have at rest table encryption Which means you can encrypt everything and you can use an entire you can use an external key server for this Now data theft is kind of interesting right because it's such that if it's encrypted If someone got access to your server, but not your keys and you did a minus QL dump for example You can't you get encrypted data, which is useless to most people now I can think of many examples of of how this is it's been bad So, you know, we've had many breakings like the Philippine voter data of 55 million people You know end up being released. We've seen Every time a patreon gets broken into and that involves people's credit card numbers getting stolen That's this website for cheating on your partner that you know That also got broken into and all the data gets put on the internet now if they only had encryption it turns out that Well, you you have a nice big dump of data that you can't do much much with unless you have the key to decrypt the data Now we can focus a little bit more on on my SQL is something I've spent an access of a decade working on So I'm not going to cover all the older my SQLs. I mean maybe some of you are still using my SQL 50 or 51 I'd be surprised if anyone said they were still using 323 In fact with my SQL 8 being released 5756 get supported and 55 is like really on the really on the end of life I'm not going to talk about my skill enterprise edition because you got to pay money for that There's of course Bacona server for my SQL which is a drop-in replacement for my SQL This MariaDB server which is a branch or well now a fork of my SQL And we'll cover basically 55 right up to the 10-3 I'm going to talk to you a little bit about synchronous replication solutions like Galera group replication The X protocol is extremely new which allows you to query my SQL with a new port But you can replace instead of querying my SQL with SQL you can query it with Python Or you can query it with a JavaScript like language So if you were thinking hey I'd like no SQL query methods that's that's the X protocol I won't cover NDB cluster because not many people use it So SQL is a standard but extremely difficult for you to get the standards document for free But you can find it on various FTP sites When you use my SQL or MariaDB or Bacona server very interchangeable Unless I specify it's only for one server You can do something like select added global SQL mode And you will actually find that as my SQL progresses it becomes stricter So all the things you managed to do before because my SQL wasn't SQL standards compliant May stop working when you start upgrading my SQL so it's extremely important to read upgrading guides My SQL keeps on getting better in terms of security And you can see how long it has taken to actually get better So it's actually been an over 15 year journey to get better And I joined the journey around the time when we were able to get drop user and show privileges Before that before 323 you couldn't grant users certain you couldn't grant users privileges Things like show create user which allows you to see what privileges the user has It's actually useful because if you did grant all you can now change it Roles extremely handy because pretty much every database server out there has SQL standard roles But they only came to my SQL officially two weeks ago and in MariaDB I considered useful when default role came out So you could actually assign a default role to users Otherwise you'd have to create roles every time My SQL user table has changed So if you've written scripts that basically select against the host user password You realize that since 5.7 it was removed However it is still present in MariaDB So that's where things start to diverge So if you're a very large user of my SQL where you also play around with MariaDB and my SQL So your scripts now have to become much smarter to do if depth If I detect my SQL I have a different query pattern If I detect MariaDB I have a different query pattern And there's a document that compares server 10.2 and 5.7 So password for example replaced by authentication string and you note which is down here Whereas authentication string also did exist here So that's how it's updated These are pretty normal Also new When was your password last changed? Kind of handy What's the lifetime of the password in this case? It's null but you can set it to say 90 days By when 5.7 first came out the default lifetime was six months Of course you create a default role Things like max statement time will allow you to time out a statement And if the account is locked or not And of course in 8.0 this becomes even longer So it's well worth noting that you could study this And how things progress When it comes to security features by version It was McAfee that first came up with an audit plugin And audit is extremely important Now you want to definitely always be able to know what your users are doing Including your administrators MySQL 5.5 which has been around for an extremely long time Now it came out in 2009 As plugable authentication And MariaDB 5.2 back-forced with it as well It allowed you to have the ability to have a proxy user So it happened to use things like open stack So a lot of people nowadays either deploy their own cloud Say via something like open stack Or they use cloud services And having proxy users is handy Because it allows the ability for you to do administrative tasks But at the same time not have access to the user schema Things like audit and PAM authentication plugins arrive 5.6 actually gives you things like encrypted client credentials That you can save inside your home directory The SHA-26 password finally arrived which is very useful Things like password expiry Also having a random password upon startup Very useful 5.7 made things a little harder upon installation Because you'd have to actually grep for the root password inside the log files You can also of course do things like expiry of passwords You can also lock and unlock user accounts If someone leaves the company You can just lock the account You'd have to delete the user 8.0 brought things like roles Further MySQL user table changes MariaDB of course at that point in time has already diverged as a fork So you still have a different set of roles in 10.0 You can also have at rest table and or table space encryption Highly recommended the table space One very unique thing is the AWS Key Management plugin Which we'll talk about a little later You can also have user limits User limits are present already in MySQL beforehand Dona server brings on the utility user Again tied very closely to the proxy user The super read-only option Which allows you to perform automatic failovers Without allowing the super user also to write the database by accident So when it comes to installation 3.5.7 no passwords 5.7 would give you an expired random password And eventually even the ability for an anonymous user to exist Was removed from the MySQL default installation Now what it means is there's no password It means when you type MySQL you can log in automatically And most people don't like to change their defaults This is why you'll see occasional news reports saying N MySQL servers hacked But you don't see them so often nowadays You see more like N MongoDB servers hacked Because the MongoDB defaults are also meant to be easy But you know upon progression things will get better So how are passwords stored inside of MySQL? Well here for one notice that the plugins Plugin column is not actually displaying anything However in 5.6 you'll actually notice that something called MySQL native password is specified And also now of course the authoring is still not now 5.7 changes this by the fact that you don't even select the password field anymore So here I did select host user password Here I'm doing select host user plugin authoring And passwords replaced by authentication strings And it turns out that you can actually see it here And in 8.0 you'll notice that the plugin has changed From MySQL native password to caching SHA2 password Caching SHA2 password is now the default in MySQL 8 MySQL 5.7 also provides the ability for you to have a minimum password policy So you can set it to low, medium or high And then if you give a fairly easy password it will tell you that you can't use it It also allows you to check So if you wanted to validate if a user's got a good password You could validate a password like for example in this case I use Protona Which gives you 25 and Protona 123 is 100 It ranges from 0 right up to 100 And again Protona 123 works when the validate password policy is set to low You should probably not set it to low, you could set it to medium, you could set it to high MySQL native password format which is deprecated in 5.7 and removed in 8.0 Is definitely not some form of encryption because it's just a checksum using the SHA1 function So contrary to the MySQL manual which will tell you that encryption performed by password is one way not reversible This is not true, MySQL passwords do not use a salt So a generated password on one MySQL server using MySQL native password will match that on another So this is why you definitely don't want to do things like select password as password So things get better over time but this has been the way it's been for many many years So you obviously want to make sure that the root user always has a password You don't want anonymous users and I'll tell you why anonymous users are bad probably on the next slide You never ever want to have the test database And the best part is MySQL made it super easy now So you run MySQL secure installation when you have 8.0 It will actually make sure that even the valid password plugin is turned on by default So by default things get better and it hardens it by just running a one line command So why anonymous users are bad? If I can log in as an anonymous user and I can access the test table I can keep on doing inserts and basically fill your disk So I can just be an annoying destructive person This is just one example of why anonymous users on the test database are bad So 5.6 starts improving password policy And it's important to start around 5.6 because many of you may be using MariaDB And missing out on what MySQL has been improving in 5.6, 5.7 and then 8 Because many of these things only exist in MySQL now For one, password expiry works this way You could also have a password validation plugin which is turned on The configuration editor is great if you're going to be doing scripting And of course having a random root password on install Password expiration will allow you to change passwords back to historical value And this is basically what you'd get in 5.6 But you can track password changes in 8.0 now So you can't actually change it back to historical password value And the bonus is you can in configuration say I'd like to track the last two passwords I'd like to track the last n passwords 5.7 definitely improves the password expiry Because you have an automatic password lifetime that can be set You can also require one to change password every n days You can also have extended SQL around expiration As well as the account locking and unlocking In terms of MariaDB, MariaDB allows you to chain load password plugins You definitely want to turn on the simple password check plugin Which will allow you to enforce a minimum password length Which will basically allow you to ensure that a password is good If it's A, B, C, 1, 2, 3, 4, 5, hash, capital B But that is not a good password So you probably want to also load the cracklib password check validation plugin Which will ensure that even if it passes the simple password check So you got 8 characters, uppercase, lowercase It'll also run it against cracklib Which is provided on pretty much every Linux system out there Because it's via PAM cracklib And if cracklib can crack it, we'll say this is a weak password Authentication So alt socket will basically authenticate against the Unix socket This is a nice way to start But it's also extremely not secure way going forward Definitely want to use the SHA256 password The SHA256 password is kind of expensive Because every time you make a connection it has to validate this long password Whereas the caching SHA256 password makes it much quicker Which is why it's now turned on by default Add a 25519 While not SHA256 Follows the elliptical curve digital signature algorithm That OpenState uses There's a Kerberos plugin as well Which will allow you to log in against Active Directory Or Kerberos There's an Active Directory Enterprise only plugin That exists as well Today when you install MySQL 8 You'll actually notice that it tells you By default it recommends you to use strong password encryption It tells you not to use the legacy authentication system Especially if you install it in Ubuntu And this is largely because You want to be secure out of the box Now, if you say I am a web hosting company And I have thousands of users with the old passwords For example, some people will then say Like MariaDB for example supports the old password format And the native password format The old password format is extremely insecure You can break into a password in a couple hundred tries even Native password Still kind of probably useful But you've probably also read that SHA1 can't have collisions nowadays So double SHA1 Can probably also have collisions Which is again why SHA266 is the recommended method today And going forward we'll see if MariaDB103 also strengthens this To make you use add25519 by default Secure communication is extremely important MySQL of course gives you the capability to have secure as-to-sell connections And you definitely want this turned on You definitely want to start by connecting via an IPS And then you can do select statements So I'm going to give you a few examples so we can go quicker through this as well I use a utility called ngrep I don't know how many of you use this But it basically can do network traffic rep And by default If you notice Yes, you'll notice that Select unencrypted is actually showing you the traffic going through in plain text This is kind of awful because everything goes through in plain text Now with 5.7 When I do select unencrypted You'll notice that I can't actually decrypt it Because basically the client traffic is encrypted already So 5.7 and greater encrypted You decide to for some absurd reason turn it off Like say SSL equals zero Then again it becomes something you can see So not very useful So I highly recommend not turning off SSL And leaving it on Use SSL it makes sense In fact large large scale websites today That are powered by mySQL And that's probably 19 of the 20 websites out there They all use SSL Of course it's well worth noting that things get more complex So with 5.6 and 5.7 These are your SSL server variables And as you can see with 5.7 Things get a bit more interesting Because they actually tell you where your certificate files are by default And then with 8.0 It becomes a little longer because of this thing called the X protocol So this allows you to access mySQL using Not port 3.3.0.6 But 3.3.0.6.0 Secure communication is extremely easy to turn on You put this in your mySQLD You basically need to then after that look and see What SSL version and SSL server you could be using If you are using an app Any app will make it fairly easy for you In this case I picked Python And this is all you need to do In addition to having a user password and host Now you may think that setting up SSL is a bit complex Using the open SSL command Because it involves a manual process Maybe it takes like 5 minutes of your life And if you assist admin you want to save that time You could definitely try this thing called mySQL RSA setup You could actually do it for you Basically one liner And it will be set up in less than 10 seconds even How many people here use the cloud? Google cloud, Rackspace cloud, Amazon cloud Okay so quite a lot of you Now if you are using the cloud and you are not using SSL You are asking for trouble So Rackspace They of course have a grant modifier A grant modifier and they actually require you to have SSL turned on When you do things like select, insert, update, delete and so forth Amazon has this option for SSL verify Server cert So it will verify your database instance endpoint against the endpoint in the SSL cert Again useful Google also has this available And they allow you to have up to a maximum of 10 certificates per instance Now Because MariaDB calls itself MySQL And MySQL calls itself MySQL You may be using client libraries that don't match each other Because when you install MySQL And not MySQL server you may be getting a MySQL client library that comes from Maybe MariaDB For one There were clients pre 573 that would consider SSL just as advice So it would actually fall back And you would think you would be using SSL but you are actually not using it for connections This is not good So this is of course changed And then of course There have been some deprecations So now SSL mode equals required is preferred And make sure that your client matches your server And also make sure that the SSL libraries are using also kind of match Now There was a scenario where MySQL enterprise did link statically against open SSL In the 56 I think it was 5618 Or 17 And when open SSL had that hard bleed bug You actually have to basically recompile the server because it statically linked against open SSL And make a new release Pretty much everything nowadays that you download today is dynamic And also linked against open SSL So this is an example of using stronger passwords Either the shower 56 or caching shower 56 password You'll notice that you get this nice long authentication string This is exactly what you want to aim for going forward Now external authentication via something like PAM Is kind of interesting because Not only does Purcona provide a PAM authentication plugin So does MariaDB And you can fairly easily configure PAM Some of course support the ability to have PAM groups And some don't Now installing the PAM plugin is fairly straightforward And If you want What you could do is You could go on a test server Install Google Authenticator So preferably you've got You know WNL Ubuntu And you can actually even have A PAM true factor authentication With the Google Auth app on your phone Which actually does work We talked a little bit about MariaDB Auth plugins as well And This is a good example of how you'd use Edit25519 Auth And again If you notice The password is A lot more complex Than you'd expect from my escalated password And Edit25519 should also in theory Not allow you to get broken into easily Because if it was Then H would be a problem as well Unix Socket Auth is As I said turned on by default Inside of MariaDB when you install it On Linux nowadays And you want to obviously Turn that off because this trustless authentication mechanism Is not necessarily good For production use cases Audit plugin How many of you here use Twitter? Not many, okay Well for the ones of you that did use Twitter Or follow the news I think last week Twitter did say something like Hey Reset your passwords Largely because We have a system That may have saved your passwords in plain text But everything else is stored nicely encrypted And when I read that I thought I wonder if they were using an audit plugin Because you know some early versions Of say the MariaDB audit plugin Actually recorded the passwords In plain text as well Which was actually later Realized to be kind of a bug Because it's kind of two for both So now the passwords are hashed Of course we don't know If that's the reason Or they had something else But it's kind of interesting to note That You can audit An audit plugin can audit Everything Including what the root user does So It's highly recommended You turn on an audit plugin Because It's extremely useful From a compliance standpoint As well to know If a user touched Someone else's data I think also last week You might have realized That Facebook said They fired someone For accessing A profile of A girl he met on Tinder maybe Or something to that effect Accessing someone's User database is extremely bad And again An audit plugin could help you Because it'll ensure You know exactly What someone's doing Including an admin There's several formats For the audit log You know the Basically XML Or JSON Or CSV And here are examples Of what What formats The old and the new format In XML look like And the JSON CSV format So you could actually Take the audit logs And give it to someone Who doesn't know any SQL And they could also Do audits Now You notice suddenly That the MariaDB audit log format Is actually different From the MySQL one And this actually Becomes a problem Because if you want to Rep the audit log MySQL actually provides A utility called MySQL audit grep That allows you to Grep the audit log By say user Or command And you can't actually Run this utility This utility is part Of MySQL utility's package And you can't run it Against the MariaDB audit Log format Now MariaDB server itself Extended the audit API To allow you to Have user level filtering So you can filter By user Of course If you have The query cache enabled You get No table records Because it doesn't Actually touch a table It's the query cache Now the correct size For the query cache Is zero Because the query cache Is not very useful In fact MySQL 8 Has completely removed The query cache Because it's been Zero for a long time Pracona's audit plugin Supports multiple formats All new JSON CSV You can also filter By user Without extending The audit API You can also filter By a scale command type Database and so forth And Pracona Because we're a company Focused largely On performance We wanted to make sure That we also allowed you To have multiple variants In terms of auditing So you could have The ability to have Asynchronous audit logs Or semi-synchronous Or fully-synchronous audit logs And you'll decide When it's time to use Memory buffers Or drop messages If the buffer is full And when to sync to disk Yes, auditing Can take a hit But you want to make sure That you're getting The most performant Variants available McCarthy Being the first one Great You also degenerate Offsets against the server Which they provide a tool for It's nowadays Probably really deprecated By the MariaDB Of the Pracona audit plugins And if you happen to use Amazon, RDS You'll realize That they also As in the option group Allow you to use An audit plugin Against both MySQL As well as MariaDB, RDS So now we'll talk A little bit about Secure storage You want to encrypt The data at rest This could include Your tables, Table spaces Your binary logs And other logs And e-management Is extremely important As well So MariaDB servers Overhead is typically Less than 1% You can Of course encrypt Also temporary files Which are extremely useful Encryption is extremely Easy to turn on You basically Have to have All those options there Now You want to make things easy For you You don't want to have All those options Because if you miss one Problems occur So use a preset Also plug in For the Amazon Key management server Because key management solutions Tend to be Kind of expensive MySQL 5.7 Also includes encryption You have the ability To encrypt InnoDB table spaces There is also Similar to MariaDB The ability to Store the Key files On your hard disk Now this is of course Good for testing But extremely Poor for implementation Because if I Break in And I do a MySQL dump You have to use InnoDB file per table But that's been the default Since 5.5 So if you're not using InnoDB file per table It's time to change And then you have to Alter the table So you can't actually Just enter data encrypted You have to first alter it It has an external Key management solution Via Oracle Key Vault But I'll talk to you A little bit about how You can not spend Money on Oracle Key Vault So Various releases Came up with Various things So Including inside MySQL 5.7.19 There's also an AWS Key Management Service Plugin Again, it's only available In the Enterprise version So Basically At Precona We want to make everything Nice and open for you So we also decided To include Vault encryption So if you use HashiCops Vault And you already Have a Vault server Set up You can just use The Vault plug-in For both MySQL Or Precona server And you can Store your credentials There as well And this became Generally available Also Two weeks ago It's been Available for a while But Two weeks ago We had this big MySQL conference So Make sense of We announced these things So MySQL Enterprise Transparent data encryption Does have Data address encryption For Encrypting Even the physical files Of the database You can of course Use other things Like locks and so forth Even Basically The DNS Algorithms Precona service Keyring Vault Is something You definitely Want to try today Failing which Of course look at The KMS solution That MariaDB has Against The Amazon Solution Now I don't know How many of you Here use Something like ExtraDB cluster Or Galera cluster Where you Want to have Fully synchronous Replication And Precona spent Some time Making it Easy by default So for one We Write fairly good documentation To ensure You follow it But also We want to make sure It's such that You could roll it out easily So we can't Change your IP tables Rules Or when you do An installation Will tell you These are the Suggested IP tables Rules you have You're probably Also encrypt the traffic When it comes To things like Generating keys Enabling encryption All this Can be done Basically Automatically also Just State snapshot Transfer Basically referring To the full Data transfer that occurs When a new node A joiner node Joins the cluster And receives data From a donor node And you want All that To be encrypted As well Of course We talked about Table space encryption Earlier And you can use Something like Vault As well For external key management So a good Quick how to So that You don't have to Worry Follow Industry standards Then you can Become a security Expert And you know You can have A bunch of things Against a checkbox And that's That's the aim In terms of Writing documentation First I talked about SQL standard roles But there's not Really much I can talk to you About roles Because it's Standard in pretty Much every Database out There Except MariaDB And MySQL Which only Group users into roles So More or less In conclusion You want to Definitely have things Like an audit The ability to have Audit You want to have Log reduction Because you don't Want everything Showing up In your audit logs So you want to Be able to Configure how You can redact stuff You want to Have external Authentication As well Maybe because You don't Want to trust The authentication System Or it's easier To have Authentually Now I promised to talk A little bit about MongoDB And you know For MongoDB includes Things like audit, Log reduction And external auth Amongst other things Also fully Open source And drop in replacement Definitely today I want to have SSL turned on The excuse that It's Heavy on the CPU Is not a good Excuser any longer The CPU time Is much cheaper Than you think You want to turn on Encryption Having a dump On your database And walking away With everything In plain text And embarrassing you And your users In public And I think The other really Really important thing Is you want to Have external Key management And either You use Vault Or use Some other solution That costs money It can Tally up to you And most Importantly Upgrade your Software Because It turns out That It's better And we Make it better Because we want to Make it more secure By default We want to give you More features So upgrading Should not be Something you are Averse to Read the Upgrading guides Run it on Test servers And even though You're running databases It makes sense To Upgrade I guess With that I'm More or less Open to Questions As well And you have So yeah I'm open to questions Questions Any questions Yeah Hi So I have A couple of questions So We see a lot of Software You know Being deployed Inside containers today And So the first Question is What is your Thought of Thoughts about You know Deploying Databases inside Containers Databases inside containers So That's the first question I'll answer it Then we can have A conversation sort of Yeah So databases inside containers So as long So If you want to do Trust-based authentication You can do it Within the container But never outside of the Container And If you think of it As a container Being secure By default As long as the Container itself Doesn't get broken into All should be well You should probably Also be running Things like SC Linux or App Armor But at the same time You know Perfectly okay As long as The similar principles Apply All that said I guess If you're Familiar with Kubernetes Stateful set is probably The only way to have Sort of Stateful applications At the moment And it's not Like super Full proof And yes You can run MySQL with Kubernetes But not many people Do yet Or even Postgres with Now In terms of Best practices Of using Stateful set There's not much Around security Beyond the fact That you should Make sure your containers Are also You know Well secure But At the same time From a Postgres standpoint The Chaps at Zolando Actually Made a very Good presentation About a year ago About how They handle Containers And deployment The DIN DNS service Have also made A good presentation Around how You could handle MySQL Inside of something Like Kubernetes But from a Security standpoint There's nothing Additional beyond Like if you're Going to use trust based You make sure it's In your local Instance Not exported Hope that answers Your question You have Another question Right So What was the second question I kind of Answered it already Okay The databases Another question right there And Hi Hi Charles So We're talking about The audit plug-ins right So what's the overhead On the MySQL server When you're like You know Enable the audit On the plug-in base So the overhead Yeah So that's a very good Question about What the overhead Is like Around the Audit plug-in And Yeah So if you have A very Busy server Worst case scenario So like Actually hit A 10% Hit Now Production use case It probably is never Going to be that way So if you're running Sysbench Maybe you want to do You want to do Asynchronous Or turn on Performance mode If you're using Protonous audit plug-in Whereas if you're using The others which Don't have that option Then Yeah It's going to Write to this All the time So You know If you know Linux You know By up to 10% But Generally speaking Real-life use cases This should not be the case And at the same time You can switch modes Which should also In theory help you Is that your Only question? Yes Okay Yeah, hi So actually One of my Questions was Same as He asked Another question was Like If we can Make the MySQL connections Over a Private network only Is it still So the question is If you have The MySQL only On a private network Should you still be Using SSL? And the answer is Probably yes Yes Because You don't know who Else will Attach traffic To your private network Somehow If you're using The cloud This can leak quite easily If it's Your own Private network Like it's In this room A bad actor Could still come in Like say If I was the bad Actor Using on a cell Probably makes sense And You know All the SSL performance Gains that you've seen Actually come a lot From the work That's been done And improvements In MySQL By companies Like Google and Facebook Because they Published their Cell improvements And You know And actually That's why Google even Wrote the encryption For MariaDB Because they You know They need it So yeah You don't want The equivalent here is But If there's An intelligence agency And they somehow Managed to attach To your network Then everything's still In plain text So you want to protect against Any kind of Data exfiltration So yes You should probably run SSL Even if it's In this room More questions Yep At the back What is log reduction? What is log reduction? Yeah Okay Log reduction means I'll give you an example Of a log Maybe I want to Redact some things Like I want to redact For example Passwords I don't want to Store passwords In the log file I'd like to redact that That's probably useful As an example Maybe I want to Filter You know I want to redact things Like I don't want to know What Something user does That's removing stuff From the log That it won't be Written to a permanent record So You want to definitely Redact By default Passwords And You know most people by When they install something They don't maybe Don't change the default So if the default was to Log passwords And it's a It's a poor default So that needs to change So that's log reduction We had another question here Somebody from this side raised Your hand No longer So one of the issues As an application developer That you generally You know Encounter Is How do you configure The connection to a database From an application Right So typically The easiest way to You know Do that is to hardcore Your username and password Inside the application code Or Externalize it into a You know plain text Configuration file Of course Some of the languages Do support You know Encryption That is supported as Maybe Native In that program Or An external library So my question is Does MySQL or MariaDB Have something out of box That The application developers can use Yes The application doesn't Want to store In this case The user and password Right And yes That's the question You don't want to Store your User credentials inside The application And the answer is Actually yes You can use The mylogin.cnf Inside the Applications User Account And That actually Works That's been available since MySQL 5.6 and greater Not MariaDB though At the moment So I suggest Looking at the documentation Around the MySQL Config editor Another question Yeah More questions You were Showing about Pam and all right How you could Connect using Pam with your MySQL 5.6 How you could Connect using Pam With your MySQL server So can you Actually have multiple Clients Using different Authentication Mechanisms at the same time Multiple kind of Clients Can you Repeat the Question About Pam Yeah So you were Showing about How you could Use Pam As an Authenticator With MySQL and all So can you Actually have Multiple kinds of Authentication Applications and Like Can I have Different Authentication Mechanisms at the Same time Right So can a user Authenticate against Pam and maybe Also authenticate Against Kerberos is Your question Multiple Authentication And yes of course Because So once you Configured Pam It turns out That you Actually Basically Set The Pam Per user I can have Another user Created Authenticating Against Kerberos I can have Another user Authenticating Against Any other plug-in That happens to exist So the answer is Yes It is On a user Basis You can have Multiple Authentication Backends If that was A requirement Of yours Hi So In your Talk you mentioned About Complaints Data Regulations So I have a question It may be May not be related to MySQL In near future Can we expect The databases With MySQL Oracle Postgres SQL Natively support Or At least Through plugins Help the developers Achieve the Complaints In any way At least Masking Or some kind of Attributes Just like Constraints Database Constraints Can we specify some Attributes For specific Fields Or columns Where I can Mask the Data Yeah So Question around Masking Data So Postgres Actually For example You can Mask data via Column Encryption Right And that's A very common Will we make it easier For you to maybe Remove a user For And all phases of the data Now That's actually kind of Hard for us to make One kind of tool Like that Because let's say We remove the user On Say database On your master server But you have a time Delayed replica That Is maybe Delayed by a day In another data center Then Then the data Is still stored there For example Because we don't actually Know What your backup policy Is You may be having Logical backups You may be making A point-in-time recovery Snapshots And we don't know What your backup policy Is So the person of Can we make it much Easier for you Is Very I think Very much depends On how you have Set up your Policies as well But can we Make it easier From a data Masking standpoint Yes Of course So I think A very good Talk Again Might be checking out How proxy SQL can help With data masking Was presented At FOSSTEM earlier this year So we do have Examples where you could Actually do something Like that with proxy SQL So again Still using middleware Not quite Getting it done In the database itself Because the database Will still be The source of truth Hope that Kind of answers Your question