 Hello everybody. I'm Otto from the MariaDB Foundation. I'm here to tell you about some basic security issues related to MariaDB. I find it that most of the time when there are security breaches on a massive scale, most of the time they are related to people doing the basics wrong. So now I'm going through the basics, what you should do if you maintain a... MariaDB database. And there are a few cool things that are new, which you should know about. And mainly socket authentication. I'm also going to touch about the other basic things everybody should do. And the nice thing about this is that socket authentication is by default enabled in Debian. So if you install a new MariaDB in Debian, you will be using socket authentication directly. You don't need to do anything special. And also for example, the settings on what connections it listens to are secure by default in Debian. But still you might want to lax them if you have a setup where you have remote connections. So it's good to know the basics about them. So let's first check how many of you administer MariaDB service. So you know that MariaDB is a fork from MySQL, made by the original authors of MySQL, namely Monty Videnius, who started MySQL. And then after Oracle acquired Sun and got MySQL that way, then Monty decided to fork it. So it's now using the name MariaDB. But it couldn't use the name MySQL because Oracle owns the trademark for it. And the fork happened at the 5.5 versions. So MySQL 5.5, MRD 5.5 are very close to each other. And then for every release, the feature set diverges a little bit. But all the basic things stay the same. And there's a company called MariaDB that provides commercial services. And there's also a foundation, and I am from the foundation. And the foundation's task is just to take care of the open source project that it can't be sold or anything harmful to its success. And the foundation runs on donations. And we have many big companies who sponsor the foundation. So we can do what we do. We are seven people. And from the foundation we have here me and Ian there. And Sergei, who had a talk earlier today, is there. And he's working for the corporation. All right. So if you've ever installed MySQL or MariaDB on Debian, I'm sure you've seen the screen. So when you do upget install, it will ask your root password. You need to set it traditionally. Is this familiar to you? Yes. So when you have lots of hosts to maintain, then this becomes a problem, because first you have some credentials to the host itself so that you can get through SSH or something else to the host. And then you have a second set of credentials to get into the database. And the more hosts you have, the more and more passwords you have. For SSH you maybe use SSH keys, so that solves it. You don't need a root password. But for MySQL demo you traditionally have a root account and a root password. And you need to manage that password somehow. And then if you have tens or hundreds of thousands of hosts, then you use some kind of automation, like puppet or Ansible or salt or chef or whatever. And here's an example from Ansible, from a Galera cluster. And you actually need to set three different passwords. You need to set one for the cluster root password. And you need to set one for the Debian maintainer user. And then you need to set one password for the account that is used to make backups from the data. So you need to manage three passwords. And then you have all these configuration files that go on every host. And in that configuration file are these passwords. And those configuration files are basically protected only by Unix file permissions. So it's kind of a brittle thing. And not only is the risk of leaking the passwords in this kind of situation, you also have the problem that the passwords might get out of sync if you change the password on your system. Then you need to figure out how to update the configuration everywhere to get the correct password out there. So all of this is, and then of course you have all these Ansible scripts or puppet or whatever. And you have that in some Git repository and you can't have passwords in that Git repository. Maybe you have, maybe not, but if you don't have, then you have some kind of secrets management that then does some lookups or something into a separate place where the secrets are. So all of this suddenly makes everything quite complex to manage. How many of you use Ansible to deploy MariaDB hosts? How about puppet, chef, salt? All right. So if you go to GitHub and for example Google for MySQL password, you will notice there's a lot of Ansible and salt and whatever files that people have committed to the version control. And then some of them might be real passwords and some are not. But anyway, there's a risk that you accidentally leak your password when it's part of the configuration and the configuration is managed. So in some repository which maybe was secret initially, but somebody accidentally published it or something like that. How many of you use Docker 1? So are you familiar with this pattern that in Docker you send in as environment variables the password? Yeah, you're weeping. How secure is it? For example, it will be visible in the process list on the host and as it is on the common line it will be in the bash history. So of course it means that you need to have access to the host machine, but anyway it's just more and more places where the password can potentially leak. So there's lots of problems related to password management. You need to design systems how to keep the secrets and configuration separately and against leaks. You are afraid that the password might leak. You should change the password maybe once a year or something like that and so on. So there's a lot of inconvenience related to that. So good news, you don't need to use passwords always. There are other ways you can do the authentication. And also by the way, even if you don't have the password to the MariaDB server, but if you have root you have anyway access to it, you can always go directly to the files and either delete them or copy them somewhere else and then open the data. So actually the password, if you have root on the machine, then the password doesn't actually protect anything. It's only useful for remote access. So root on a Debian system anyway has access to everything. So the goal is to eliminate the passwords. How many of you have noticed that there's this Debian.cnf file which has this maintenance user and its password in clear text? Yes. So for example, if there are some cron jobs or even every time the init script is triggered, this is both for MySQL and MariaDB because of historical reasons. There is a maintenance account like this so that the init script can do certain things. And there's a place where it can find the password. So not only do we want to get rid of the root password which you as an administrator yourself set when you install MariaDB, we also want to get rid of this password of this maintenance user account which is automatic and most people haven't noticed it exists. So the solution is Unix socket authentication. So Unix socket authentication is based on the fact that if you access your MariaDB server, not remotely but locally, you access it via a Unix socket which is basically a file as in Unix everything is a file. And then that file when you access MariaDB through that file it will see what username you have. And simply by installing this Unix socket plugin and then granting that root on local host can be identified via Unix socket, you can allow that the root can come in without giving a password because the MySQL demon will see that it was root who is coming in. It will see the Unix user name of that user. So here are the commands to install it. But on Debian you don't need to do this, it's done automatically. So here's an example, if you want to get in, if you're the root user you just type MySQL, you actually don't need to specify username and it will immediately let you in. And if you are not root then yeah, if you are not root you can still with sudo get into MySQL because when you run sudo the Unix socket will see that this is done as the root user. But then if you are, yeah, but other users you can't get in and also some corner cases that if you have socket authentication enabled and you give a password it ignores the password completely and lets you in based on the fact that it notices that you are root already. And it's only for root access so the root user cannot enter the database as some other user. That's the third example here. Does this look convenient to you? Cool. All right, but there is one drawback that how many of you use PHP MyAdmin? Yeah, so if you use to have the system that you install a new server and you set a root user and a password then you can use tools like PHP MyAdmin to log in or you can remotely log in anywhere but after you start using this Unix socket authentication remote connections will not work because it's not actually the root logging in but it's the PHP process logging in so MySQL won't let the PHP process access the database. So this is the drawback. But this is actually an intended consequence because we want you to have a more secure setup. And the solution for this PHP MyAdmin kind of scenario is that you create a separate user with a separate username and password and then you can maybe give it full access to your database or limited access to your database which is much better. All right, so the Unix socket authentication plugin and the root account is installed, activated and installed by default in Debian since Debian 8 and also in recent Ubuntu. And this is actually something that has been implemented specifically in Debian and it's not globally by default in all MariaDB installations in other distributions but we think that we are going to make it default globally universally for all MariaDB releases at some point. And also note that this applies for new installations. If you already have an existing installation you already have set a root user password and we won't clear that out. It will continue to be as it was. So if you have an existing installation and you want to start using socket authentication then you need to manually do this and the other things that's related to the fact that your old password stops working. Yeah, so this was done. The packaging in Debian which enables this in post scripts and other installation stuff was done by me and Daniel Black and it's only available in Debian at the moment and Ubuntu but not in Red Hat and Suze. All right, so this covers the root case and root, now you have a passwordless root and all your Ansible and Salt scripts and everything you don't need to define any passwords there because Ansible and Salt scripts are run as root so they automatically have access to the MySQL Demon and can do the tricks and things they need to do. Okay, so and this way every time you do something on the local machine as root or using sudo it will work but if you need remote access or something else then you need to learn how to make new accounts. And anyway it's pretty obvious that if everybody are always accessing the database as root using the root password then there's something wrong with your security configuration. You should definitely think about creating accounts for individual users or most of the time individual applications because the accounts are used by all kinds of web applications and others. So you should create a separate database for every application and a separate user for every application and here is the syntax how to do it in SQL and I will post a link to my slides on my Twitter account after this talk so you can check out the syntax there or you can just Google it from the MariaDB Knowledgebase and then if you want to have additional security then you could do multiple users who have access to the same database but some users have less privileges and you only grant them the privileges that they actually need that kind of protects you against for example SQL injections and other stuff that might be in applications despite all other protections. And also note that when you create a user you define a username and a password but you also define where the user is coming from so you can allow them users that they only come from local host or then you can define some network address and if you use this present tile sign that's a wild card it means that anybody from this space can access and if you put only a wild card after the user name then it means that the user can access from anywhere and then always remember to run flush privileges so that the user data is updated after you made changes to the table. You can also use MySQL admin and other tools to manage these users. The point here is that you should not use root to do everything in the database create separate databases and users for each case. In 10.1 there are more additional security features related to this. You can for example put policies on the passwords that they need to be of certain length or certain complexity or need to be changed at a certain interval and stuff like that. If you're in an enterprise use case you might have some security policy that dictates something so you can implement it in MariaDB. In this example I'm using the grant command to create the user and grant them permissions in one single go but there's also a create user command available so you can do it in separate steps and here's a cool thing which is available in grant and now also in create user in 10.2 onwards that you can put that you require SSL. In this example we create a user that is allowed to access the database over the network and then we also set the password but we also require that it must come through an SSL connection so this is additional security. Any questions about this one? Then another thing to restrict connections so there are still plenty despite all firewalls and everything that people should know the basics about administering servers the internet seems to be full of all kinds of open services all around and people are unfortunately running way too many MySQL and MariaDB servers so that they are listening to connections from anywhere that's stupid So what we have in Debian is that we have this bind address to local host by default So that solved that problem and as an administrator you might want to change this so there are few things you can do there is this option called skip networking that will turn off whatever is in bind address it will not bind to any TCP address at all it will only listen on Unix socket that's the most secure option you have but we have this bind address to local host enabled because that's slightly more compatible in certain situations when there's TCP connections coming from local host and if you want the server to accept connections from anywhere then you can comment out this bind address and then the default will set in which is 00000 so that the daemon listens on all interfaces and addresses you can also put here certain rules or you can do that on your firewall which firewall might be a better place to restrict certain networks and address spaces Do you have questions about this one? No, this is pretty basic but this is a very basic thing for anybody who is administering servers that you need to do this but for some reason people seem to forget and neglect even basics so it's good to discuss this quickly Alright, then the second thing this is not enabled by default in Daemon so just like HTTP is an unencrypted connection if you have remote connections coming in to a MySQL or MariaDB server they are completely unencrypted so most of the time those connections are inside the data center and in internal networks so it doesn't matter but anyway if you have a cloud environment and maybe virtual servers and you're not quite sure where their traffic are routed and so on it might be a good idea to enable SSL on these connections and after that they will be encrypted This is a slightly difficult thing to do because you need to use open SSL command line or some graphical tool like for example TinyCA to create your own certificate authority and then you need to create a key pair for the server and then you need to create a key pair for the client and then you need to sign this with your certificate authority so that they are all trusted within your network so this is what it looks like when you have all these keys set up and enabled on your server and this is how it looks like on the client configuration on the remote machine that's connecting to your server and if you're unsure if the connection is secured or not you can give this command backslash s and it will show the server status and there's a line about SSL it will tell you if it's in use or not and since 10.015 MariaDB has had support for TLS 1.2 which is the only protocol that doesn't have any known vulnerabilities at the moment so everybody should use it and you can put in your configuration this SSL cipher line to enforce that TLS 1.2 is always used unfortunately for licensing reasons some people think that OpenSSL is not okay to use with MariaDB in Debian even if it's okay to use with MySQL so we don't in Debian have OpenSSL in use but we have YSSL the security team might want to review this if there's something that we would be allowed to enable in Debian do you have questions about securing the connection? Sergei MySQL packages in Debian that use OpenSSL yes MySQL packages no they don't well we need to there should have been the same issue people shaking their head I think they are using OpenSSL because they have the OpenSSL exception but the OpenSSL exception hasn't been explicitly given to MariaDB so some people who are very strict on interpreting these license things said that we can't use it for MariaDB but maybe the security team wants to use a veto here or something like that if it should be fixed in other ways then to the last part of my security talk so now you have got rid of the passwords so your management is secure then you have the network connections encrypted and you have your daemon that it doesn't listen listen to connections which are not supposed to happen in the first place then what remains is encrypting the data at rest so what you can do you can for example encrypt the entire hard disk that protects you against if somebody steals the physical hardware or you can do some things in your application to encrypt the data and then only store encrypt the data in the database but that's kind of stupid because then the database doesn't understand what data is inside it and it can for example give it incorrect order or make or do selects on the content or something like that so the smart thing to do and if you have encrypted the entire hard disk that doesn't protect you in any way against for example if you have backups that go to some other machine or something like that then the person administering the database can't be sure if all the backups and all the hard disks everywhere are encrypted so the optimal thing to do is to have database level encryption that means that the database sees the data and it can sort it and read the values and do the calculations everything but the operating system does not see the data it's encrypted from the operating system point of view and the files on the disk are encrypted and all backups and everything is encrypted so the database administrator can be rest assured that the data is always encrypted and this is available in MariaDB since 10.1 and there's a lot of settings you need to put on to enable it so we have this shortcut that you can write that include this preset file and then those settings go on however that's not enough you also need to consider key management will you have the key in a file what is the password going to be and or will you have a separate key server and so on and you need to choose if you want to have maybe logs encrypted or not encrypted do you want to leak something in the logs to make it easier to debug and how about different kinds of things so it's nice to know this exists and if you're interested you really need to read up a lot and you really need to read carefully everything because in the worst case you shut yourself out of the data if you screw up the keys or something like that and this was contributed by Google so it's in production use at least at Google alright thank you and I will post the slides on my twitter account so you can follow me there to get them do you have questions well do we have a round of applause thank you very much so after this talk we're going to have a BOF session about MariaDB and MySQL in Debian so if you're interested you should stay around here for that session too come on do you have any questions my presentation can't be so complete that you don't have anything you wonder I called MariaDB on any installation and the socket authentication functionality isn't there do I need to enable anything or do I enable it on jessi you say 10.0.25 I need to double check where it is available if I get my network working I don't get my network working now but it might be that I remember incorrectly and it's not in jessi yet well then it will be in stretch it has at least been in unstable for more than a year so I don't remember did it miss last freeze or not it's in unstable and testing for over a year and it's been in Ubuntu for a long time because Ubuntu a question about connecting with SSL to MariaDB but I guess my question is also to MySQL is that around long already because I remember that there was a bug with that but I'm not quite up so you showed how to enable it now but has it been around how long is that around it has been around for a long time I don't remember exactly maybe Sergei knows better how long has SSL been available for MySQL and MariaDB if the question how long SSL was available in MySQL and MariaDB at least since the year 2000 so for a while actually what you might remember is that there was a bug that the client connecting to the server could downgrade the protocol so that it doesn't use SSL so that's why you should use this in your configuration on the client that it enforces that the connection must be SSL otherwise it will refuse to connect to the server any other questions Thank you Please stay around for the buff session