MariaDB SQL over SSL/TLS

hello everyone i'm jacob kaufman i'm the nerd in the street and today we are setting up tls encryption for our sql server [Music] all right everyone so sql structured query language is a language that's used by some of the most popular database programs out there mysql mariadb these are database programs that are used by some of the most popular web applications on the internet like wordpress nextcloud drupal mybb phpb composer cms there are all kinds of applications that use mysql for their databases now i've shown how to set up some of these web apps in previous videos and normally it's not necessary to actually secure your mysql server with ssl tls encryption we normally set up ssl on the web server so apache or nginx or whatever we're using and that secures communications between the web server and the client which is usually somebody's computer running a web browser where they're accessing that web application but the actual back-end communication between the front-end web server and the database server is usually happening on a single machine at least in the configurations that i usually show on this channel if you're running apache and mysql or if you're running nginx and mariodb on the same physical server or if you're running them on the same virtual private server those applications can communicate with each other locally with unix sockets they don't need to send any information out onto the network so that information doesn't need to be encrypted because it's not going anywhere it's all staying locally on that box however if you're setting up a more complex environment maybe you're expecting workloads great enough that you want to have a separate backend database server from the front-end web server or maybe you've got several front-end web servers maybe they're running different php applications or applications of any language but they all need to access the same database server because they're all using common information well then any of those cases where you're setting up your mysql server to accept remote connections encryption suddenly becomes very important because there's data that you're going to be sending to and receiving from that database that you probably don't want to be sent unencrypted out on the internet it's the same stuff that we think about when we set up ssl for our website passwords usually in the database they are assaulted and hashed but we still don't want those just floating around email addresses maybe unpublished posts on your blog or metadata for files on your next cloud server that's supposed to be private if we're going to be sending those things between several different servers on the internet we need to have that information between your web server and the database server encrypted because if it's not any queries that you're making to the database and the information that gets sent back is going to be sent in clear text unencrypted which means that the internet service providers in between those two servers probably your government and possibly even people on your home or business network will all be able to read that information very easily so to avoid that we want to set up tls encryption for our sql server now this is a little bit more involved than setting up tls just for a web server with a web server we can normally just run certbot and it goes and gets a publicly signed certificate from a public certificate authority and it configures everything for us when we run certbot with sql we actually need to perform configuration on both the server and the client both of them need to have certificates to use and there is some extra setup required to make sure that ssl is actually being used on that connection and to enforce that it's always used so that's what i'm going to show you how to do today i'm using a mariodb server and client for my example the process should be almost exactly the same if you're using a classic mysql server i'll put a link in the description of this video to a page with all of the commands i'm going to type in this video so if you want to copy and paste instead of typing along take a look in the description for that and finally for this video i am assuming that you are setting this up between two linux servers one of them is going to be our sql server and the other one is going to be an sql client but still assuming it's a server of some kind so not running a gui or anything so that's what we're doing today without further ado let's cut the desktop and get started all right guys and here we are on the desktop i am going to go ahead and create two servers here on digitalocean if you're looking for a server for yourself i would highly recommend you go to lynnode.nots.com and create some lynnode servers but i happen to have a lot of credit built up on digitalocean that's the only reason i'm using it and you can go to digitalocean.nots.com to get some credit for yourself if you don't have an account there yet both of my servers today are going to be running debian 10 this should work exactly the same if you're on ubuntu it should be very similar if you're on centos or red hat based distributions but i'm using debian as an example today and my first server is going to be in new york i'm assigning an ssh key to it so i don't need to use a password to log into the server if you don't have an ssh key on your digitalocean account yet you can just select the password option and the host name for this first server i'm going to make it examplesql server because this is going to be where our mario db server is running so i'll go ahead and create that and while that's being created here i'll go ahead and create our second server at the same time so once again debian 10. this one is going to be in san francisco california once again going to select our ssh key and i'm going to make this one called example sql client so we will create that and we'll wait for both of those to get ip addresses on digitalocean here so we've got two servers here we've got our sql server in new york and our sql client in california these two servers physically are very far apart from each other i'm kind of in the middle between them but like i said in the introduction if we don't set up ssl tls for this connection between these two servers that's going to be going all the way across the country unencrypted so lots of opportunities for many isps routers in between these two servers to be compromised by third parties now that both of these have ip addresses i am going to assign domain names to both of them i've got my dns being managed over on linux here so i'm going to add exampleone.netstreet.com for our sql server and then i'll copy our client ip address and that is going to be example2.nernonthestreet.com so both of these a records are in place here i am just going to wait a couple of minutes for those to take effect all right and that dns change has taken effect so i'm going to open up a terminal here and we will connect to our first server so i'm going to ssh into the root account at example1.nernonthestreet.com once again this is our sql server and the first thing we need to do since this is a fresh server is we need to actually set up mariadb so i'm going to do an apt update first it's always good to have up-to-date package lists before we start doing anything on a server and it looks like we do have 28 packages that can be upgraded so i'm going to run apt full upgrade to install those i do see that some of the packages being updated are our kernel image linux image cloud so i am going to reboot the server after these updates are done you can see it generating our new initial ram disk right here so we do want a reboot so we're running that newer kernel version all right i'm logged back in here now that we are up to date we can go ahead and install mariodb server and just like we always do when we're setting up a mario db server we also want to run the mysql secure installation script that's just going to run through some things it's going to set a root password for the mario db server itself for us here and then we'll just remove some default users and databases to make the server a little bit more secure at this point we can connect to mariodb we'll just run mariadb to start out with and i am going to create a database if we do show databases right now here in mariadb you can see there are just our default databases but i'm going to make one for us to use here for testing so i'm going to run create database test database and then i'm going to create a user and granted privileges for that particular database so i'm going to run grant all privileges on test database all tables two and we're going to specify our new user here so this is going to be test underscore user at now at this point if you've done this before if you've set up a mariadb server before you would probably want to write localhost here that's what we normally do when we're setting up our mariodb server on the same server as our web server however if we run this this would specify that our test user is only allowed to log in from this particular host from the server that we're running mariodb on we would not be able to connect from our external server since we know we're going to be connecting from a different server i'm actually going to run this command specifying the client's ip address that way this user will only be allowed to connect from this particular client's ip address you know if people are at home or if they're on other machines they shouldn't be allowed to connect directly to the mysql server they should only be connecting to the client server and then the client server should be connecting in the back end to the server server now if you're going to be connecting to the mariodb database from many different clients or if you don't know what the client ip address is going to be you can specify a wildcard with a percentage sign here that is less secure once again because that means anybody can attempt to connect from any machine anywhere on the internet so once again i'm just going to specify that we can only connect this user can only connect to this server from this particular client's ip address and then we're going to of course add a password so identified by and i'll use our super secure test password here we'll run flush privileges to apply that and then we will quit out of the server now if i run mariadb dash u test user-p right here this should attempt to connect to mariadb locally using this user account and then it's going to prompt me for a password i'm going to enter the password and we are going to get access denied once again because we're not allowed to connect from localhost we're only allowed to connect from the client ip address which we're not on right now right now we're on the server now in order to actually allow access from that server globally in mariodb right now the user is allowed to connect from that server but mariodb is not actually listening for external connections so in order to make that happen i'm going to just list out all of our configuration files in etsy mysql and i'm doing this because i want to show you how the configuration is set up on a debian system by default because it's a little confusing the standard place for mysql configuration is at etsy mysql my.cnf however you can see that my.cnf is pointing to etsy alternatives my.cnf that's a sim link if i do an ls-al on sc alternatives my.cnf it's pointing back to etsy mysql mariodb.cnf the reason why debian does this is because their older mysql package uses the same etsy mysql my.cnf path for its configuration the alternative system on debian takes care of those situations where you've got multiple packages that provide alternative configurations or alternative files in general now if we take a look inside of etsy mysql mariodb.cnf if we nano into that you can see there's not actually a lot going on in here basically all that this main configuration file is doing is including two subdirectories comp.d and mariodb.com.d once again if you were to install regular mysql this top one would be here and the bottom one wouldn't so we'll exit out of that file and i happen to already know that we need to nano into etsy mysql mariodb.com.d 50-server.cnf there aren't that many files in those two directories so you can find that out pretty quickly if you're not you know watching this video but this is the file that we want to edit here because this is where our bind address is currently set you can see it right down here now the bind address is what mariadb is going to bind itself to it's the address that it's going to listen on when it starts up by default it is set to 127.0.0.1 which is our localhost ip address it's only going to listen on localhost so once again even if we give a user account permission to connect if a user tries to connect it's not even going to make it far enough for that permission to be relevant because the mariodb server isn't listening for external connections at all right now so in order to fix that we're going to replace that with 0.0.0.0 this means that we want to bind to any network interfaces that are on the system any ip addresses that we have now if you want to you could actually just comment out or remove the entire bind address line there are also several other ways to accomplish listening on any ip address we could even specify our public ip address for the server but in this case i do want to listen locally if i want to connect using the root user on this machine i just also want to listen externally so that our other user can connect from the client so we're going to set this to 0.0.0.0 we'll save that and we'll restart mariodb so that that configuration change takes effect all right so now our mario db server is set up pretty easy so far and we're going to open up a new tab in console here and i'm going to connect to our sql client so once again i will do ssh root at example 2.nerdonthestreet.com it's going to ask for my ssh key passphrase and now we are connected and just like we did on the server the first thing i'm going to do is run an app update and then an apt full upgrade just making sure that our server is up to date when we start the process here and once again since our kernel has been updated i am just going to reboot that server before we start so that we're running that updated kernel okay and on this server we're not going to install mariodb server instead we are going to install mariodb client now the mariodb client core gets installed by default as a dependency when we install mariodb servers so you don't normally see me explicitly installing this client package in my videos but in this case i am installing it since the purpose of this machine is just to be a mariadb client okay and at this point we can connect to our database we're going to run mariadb dash u test user dash p dash dash host equals example1.nernonthestreet so our client is going to connect to our server right now it's going to ask for our password and when i run that you can see we are connected once again we know we're connected to our remote database because i didn't install the mario db server on this machine that we're on i only installed the client but i can run show databases and you can see we've got our test database right there you can see i've got less here in this database list than i did on the other machine because there are some system databases that the root user has access to that our other user obviously won't have access to by default but that test database that i made on the other machine since we gave this user full access to it we can see it listed here now i'm going to go into our test database and i am going to just put some data in here that we have to test with now once again right now everything that i'm doing these commands that i am typing they are being transmitted across the entire continental united states unencrypted from this client to the server but with that in mind i am going to take a look here if i do show tables there's nothing in this database yet so let's create a table we'll call it test table and that test table is going to have two fields in it two columns we're going to have record id and that will be an integer and we'll have record text and that's going to be a long text data type so i'll run that and then i'm going to insert some data into that table so we're going to run insert into test table values and we'll make the first one we'll give it an id of zero and we'll say this is my first record and then we'll do that again but we'll give it an id of one and we'll say this is my second record all right and we will select all from our test table just to see that information that we just inserted and you can see we've got record id 0 first record and record id 1 is our second record now just to drive the point home that we just did that without using ssl i'm going to run show variables like and we are going to do a wild card ssl another wild card and you can see these are our system variables for ssl and this is actually going to be server side settings that we're viewing when we're looking at these variables so you can see our server it's not compiled with openssl that's not going to change by the end of this video have openssl has to do with how this was compiled we're using the debian package for mariodb which is compiled with yasucel instead of openssl but you can see here have ssl is set to disabled and we don't have an ssl cipher set up here we don't have any certificates or keys configured so those are the server settings once again and then on the client side if i do status this is going to show us information about our connection that we're using right now and you can see ssl once again says not in use so we know that everything we just did was sent in plain text now i'm going to run quit and at this point i am going to do one more thing here normally when you're using mysql most people don't use it on the command line it's very helpful to be able to know how to use it on the command line how to insert records view records modify records very helpful to know how to do when troubleshooting and supporting a web application however most people are actually going to be using this with web applications maybe you're using an off-the-shelf app like wordpress or next cloud or composer or maybe you're writing your own so i'm just going to write a really simple php file that is going to connect to this database and basically just print out all the information in this table so that you can see what this looks like from a couple different perspectives in terms of usage so i'm going to run apt install php since we are going to be making a php file that will require the php program to run and i'm going to nano into a file this is a new file called read records.php and inside of here i am going to once again like i said i'm just going to write a really simple php file so i'm going to start off by putting ourselves in a try catch structure here and what we're going to catch is pdo exception so in this example file i'm going to be using something called pdo it stands for php data objects and it is one of the two main ways that you can connect to sql in a php application that you're writing now the other main way would be the mysqli plugin for php which is the mysql improved plugin i prefer to use pdo because theoretically if you ever need to change the type of database that you're connecting to pdo doesn't just support mysql it supports other database types as well so if you use pdo you'll have to make less changes to your application than if you use a mysql specific php module so all i'm really saying here is if we get a pdo exception if our php data object has an error we want to catch that error and we want to echo it out onto the terminal so we can see it i'm going to set up what our connection is going to be that's going to be a new php data object and that php data object is going to be mysql host equals example1 our db name database name is test database and that's the first argument for our pdo object here the second argument is going to be test user and then our third one is going to be password which is obviously the password we're using to connect next i'm going to go ahead and set an attribute on that connection object that attribute is going to be pdo attribute error mode we are going to set that to error mode exception and the reason i'm doing that is because by default pdo is going to just silently fail if it has errors we don't want it to silently fail we do want it to throw an exception because we already wrote the bit below here that's going to catch the exception and echo it out now after we establish the connection there i'm just going to echo out connected to sql and then a new line that way we know whether we connected successfully when we're testing this later and then i'm going to echo out ssl cipher info and the ssl cipher info that we are going to dump is going to be the exact same thing that we just looked at in the mario db command line we're going to run var dump connection query show status like and i'm not going to do everything with ssl i'm just going to do ssl cipher fetch all and so that's going to just be dumped out and echoed onto our command line then i'm going to echo database records new line we're going to write a statement here so on our connection we are going to query select all from test table once again just like we did on the command line a moment ago and then we are going to execute that statement we don't need to prepare it since there are no variables or anything it's just a simple query it's already written the way it needs to be executed so we will execute and then with the results i'll say while row equals statement fetch we're going to echo out the record id for each row and then a space and then we're going to echo out the record text for each row and then a new line in between every row and that's our file so very simple little php application that is just going to connect to a hard-coded sql server and run a hard-coded query or two now if i run this right now i'm going to run php read records.php it's actually going to give us an error and this is exactly why i put that try catch statement in there you saw me write error colon space and that's where this is coming from and then we've got the actual exception from pdo could not find driver i installed php on this server i did not install the php mysql plugin yet so i'm going to install phpmysql and once that's installed we can run this php file again and it looks like i made a typo in my query that needs to be show status not slow status try that once again and this is what i was expecting this time you can see we connected to sql successfully we've got our ssl cipher info and that cipher is empty there are cipher variables present but there is nothing inside of those variables because we're not connected over ssl and then down here we've got our database records so we are successfully reading from our database that much is working but once again this was sent unencrypted over the internet so we're in a good place because the functionality itself is working we just need to secure it and hope that we don't break the functionality in the process of securing it so to do that we're going to hop back over to our sql server and we're going to generate some ssl certificates and keys now to do this i'm going to make a directory in etsy mysql it's just going to be etsy mysql ssl you can do this wherever you want i would recommend if you're generating certificates and keys specifically for mysql you keep them with your mysql configuration files i'm going to cd in there real quick i do want to say though it is possible if you want to actually use a public certificate authority like let's encrypt for instance you can do that and you can use the let's encrypt certificate authority cert and you can use your public cert and private key that you get from let's encrypt with mariadb what i'm going to show you how to do here is we're going to actually be our own certificate authority the reason why let's encrypt has to exist and why it's so important is because when people connect to your website with a web browser um you know where my digital ocean go here my web browser here is just from my operating system i installed arch linux and i installed the brave web browser brave is based on chromium and there's a set of trusted certificate authorities that is built into brave here if i take a look at the certificate for digitalocean for example this is a digital ocean that was issued by cloudflare very big company on the internet and they are a root certificate authority that is trusted by brave when you're setting up a front-end website and you're setting up ssl tls for your front-end website you need to have your ssl certificate be signed by a public certificate authority if you try to self-sign a public-facing ssl certificate what's going to happen is people accessing your website are going to get warnings on their web browser that this website even though it is being accessed over an encrypted connection the certificate was not signed by a certificate authority which means that some sort of attack could be taking place so that's necessary because when you're setting up a website you don't have access to all the people's computers who are going to be going to your website you want it to just work for them automatically you don't want to have to install your certificate authority on their computers in this case though we control both ends of the connection we control the server and we also control the client so i am going to show you how to be your own certificate authority here just because that way you don't have to send any information at all to let's encrypt or whatever other certificate authority you might be using there's no reason to really do that and put that strain on their infrastructure like i said you could do that but in our example here we're going to use openssl on the command line to do this a large enterprise organization might actually have a separate third server that is just being the certificate authority for that organization and that server's job might just be to generate certificates all day every day that's all that server does is it handles the certificates for the organization it's not necessarily the best practice to have your certificate authority also be one of the servers that's using that certificate authority but in this case since we're just using this certificate authority for mysql it's more than sufficient for the security we're looking to set up so i'm going to run open ssl gen p key that's generate private key we're going to use the algorithm rsa private key opt so our options rsa kegen bits we are going to use a 4096-bit key and we are going to output that to ca dot pim so that's certificate authority key dot pem so we've got that now and next i'm going to run open ssl rec this is request a new certificate dash x 509 that's the certificate standard that ssl tls uses no des this is going to not encrypt our certificate if we encrypted their certificate the certificate would require a password to use we don't want to have to type a password in every time we try and use this certificate so we're specifying no des encryption that's also the default when you're setting up a public web server we're going to specify the number of days this is valid for and i'm going to specify 365 days so this certificate is not going to expire for an entire year this is in contrast to let's encrypt where your certificate is going to expire after 90 days let's encrypt does that because they want to encourage people to set up automated renewals for their certificates which is a good thing to do and even if you're being your own certificate authority like this it would be a good thing for you to set up automated renewals automated regenerations of these keys using scripts you know i've done a video about how to make systemd timers that might be useful for this sort of thing because right now if i just generate this for 365 days this is going to work great for a year and then it will break until i come back and manually fix it you don't want to have to manually fix it every year you want to set up a system d timer or a cron job or something else that is going to automatically regenerate these keys and then copy some of them over to the client if you really want to you can just specify an absurdly high number of days here so that this will last for 100 years or 500 years or however long you want it to the downside to doing that is if your key is compromised especially if you don't know about it then people will just be able to use that compromised key to read your communications that you thought were secured it's just a good practice to at least specify some human time frame in which this certificate will be automatically or manually recreated so we're going to specify the key we want to use to create this certificate is our certificate authority key we are going to output this to certificate authority cert.pim and then i'm going to put a subject line here now the subject line is optional if you don't run this it's going to actually prompt you it will walk you through in a sort of tutorial all the different pieces of information you need to provide the reason i'm showing you how to do this on the command line with a single line command using the subject option once again is because if you script this out if you put this in a cron job or a systemd timer your script can't interact with that prompt or it would be much more difficult to make it interact with the prompt so we're just going to use the subject line we'll say our country is the united states our state i'm going to put missouri and our locality i'm going to put st louis that's not actually where i am but i like st louis better than denver so i'm going to pretend that i'm in st louis um our organization is nerd on the street productions llc and then our common name is going to be knots ca so the common name here we have to make sure this is different for all three of our certificates and keys that we're generating so we're going to have one certificate and key for the certificate authority one certificating key for the server and one certificating key for the client for the server and client i'm actually going to use domain names here you don't have to use domain names you can make the common name whatever you want but you do have to make sure that those are different if the server and the client have common names that match they are going to get confused later and they'll throw error messages at you so at this point we now have our certificate that we just created using the key and those are both for our certificate authority so now like i said we need to create a key and certificate for our server so to do that we'll run openssl request new key rsa4096 no des key out so we're going to output our key to server key dot pim and then we are also going to output a request for our certificate that is going to be server request dot pim and we'll make our subject here once again country equals u.s state missouri locality st louis organization nerd on the street productions llc common name so this is where i'll put example1.nernonthestreet.com since the key and certificate requests that we're creating right now are for our server so i'll run that it's going to generate a private key for our server and we can see that that is here we also have a request for a certificate so this is not actually a certificate yet but if we take a look inside of there you can see it's a certificate request we need to send that request to the certificate authority and it will give us an actual certificate back to do that we'll run openssl x509 request input our server request days 365 certificate authority ca cert dot pim certificate authority key that we're going to sign this with ca key dot pim we're going to specify ca create serial so every certificate needs to have a serial number with it we don't actually need to use the serial number for anything so we could just specify a random serial number ourself but this is going to generate a random one for us and we'll output this certificate to server cert.pim and so that has been created and we now have server cert.pim you can see right there we're going to do the same thing for our client i'm just going to paste this command in but it's the exact same command that we just ran for the server the only thing that's different is the common name for our client is now i'm going to be example2.nerdinthestreet.com and we're naming these files client key and client request we'll run that it's going to generate another private key and then once again we are going to send that request to our certificate authority which is our self to get an actual public certificate that we can share so at this point if we take a look at all the files in this directory we are now a functioning certificate authority who has issued several certificates to ourself and just to make sure that that expiration date that we set took effect because that's kind of an important thing the default is 30 days if you don't specify anything so the difference between one month and 12 months in terms of how often you have to service this thing is quite a large difference we're going to run openssl x509 end date no out input ca cert.pim so our certificate authority certificate is going to be valid until january 10 2022. it says it's it's valid not after january 10 2022 which is a year from right now we'll do the same thing for our server cert and also for our client cert and you can see all three of those are good to go until january 10 2022 there is one more thing we have to do if we take a look inside of our server key.pim if this was a real server i would never be showing you this but this is the actual private key for this server um so if somebody has this private key this is what they need to decrypt the communication so anybody with this private key can see what we're saying even when it's encrypted now you can see at the top of the file here it says begin private key you might have also noticed earlier i used the openssl gen p key command which is the latest and greatest way to generate private keys using openssl however mariadb in particular does not work with these private keys as generated by openssl it needs an rsa key and our private key is in rsa format but the default format that it uses it puts that rsa key in there and then it's also got a little bit of metadata at the end and mariodb doesn't care about that metadata and it doesn't know how to handle it so mariadb would give us an error message or actually it wouldn't it would just not work if we tried to use it with this server key so we need to convert this serverkey.pim into a plain old rsa key like i said same key just without some of the metadata and in order to do that we're going to run openssl rsa we're going to input serverkey.pim and we are going to output serverkey rsa.pim so this is going to write an rsa key so if we nano into server key rsa you can see at the top it says begin rsa private key this is what you need to have at the top of the file that you're using with mariadb all right and if we do an ls-al to take a look at our permissions here the very last thing we need to do is just change one little permission mariodb is running as the mysql user now you can see that the directory that we're in etsy mysql ssl that directory does give read and execute permissions to all users on the system all of our certificates also have read access for everyone because certificates are generally public information you can share those and it doesn't breach any security to share your certificate the key that's private that's why we called it a private key so by default the only person who has permission to read the key is the owner of that file which is root so we're going to just change ownership to mysql we'll use the mysql user and then also the mysql group we're going to run that on server key rsa just because rsa.pim is the one that we're going to be putting into the mariodb configuration and it's a better idea to do that if you want to you could alternatively just give others read access to the server key rsa.pim file but then if you've got other users on your system if a different user on the system becomes compromised they could read your private key and once again decrypt your communications so it's a better idea since this particular file is only ever going to be used by mariodb we'll just give mysql ownership over that file it is something that you have to script though if you are making a systemd timer to remake these certs every year make sure that you include changing the permission of that file so that's everything we need to do for setting up our certificates that was a little bit more work than you might expect if you're doing something like let's encrypt on the other hand we didn't have to prove to anybody that we have control of this server we didn't have to have any external communications we generated all of that ourself which is kind of cool so now we are going to set up mariodb to serve over ssl tls because right now it's not configured to do so so if i nano into once again this is our etsy mysql directory i'm going to nano into mariodb.com.d 50-server.cnf and this is the configuration file that in debian by default is going to allow us to configure ssl there are other places we could put it but we've got the examples here so we'll use those examples because it's less typing so we've got ssl ca ssl cert and ssl key so our certificate authority that is going to be the public certificate for our ssl certificate authority so that's etsy mysql ssl ca search dot pim and then our ssl cert was at etsy mysql ssl server cert.pim and then we've got our ssl key that was etsy my sql ssl server key dash rsa.pim we are also going to come down here to the ssl equals on now if you take a look at these comments it says we can accept only connections using the latest and most secure tls protocol version that's a little misleading mysql is still going to accept non-encrypted connections as well it's talking about if you've got tls if you're connecting if your client supports tls you can restrict it to say i'm not going to use ssl 1.0 or 2.0 i'm only going to use tls version 1.2 or higher however that only works if you're running mariodb compiled with openssl which ours is not we're running on debian which is going to compile with yasasl we saw that earlier in our mariodb server output we're just going to turn ssl on here however on isn't the correct syntax if you actually try and start it with this the server will start but you won't actually have ssl enabled and if you take a look closely in your logs you might notice or you might not since it's such a small error message that ssl equals on is not a recognized option ssl is a boolean configuration option in mariodb and it only accepts true or false so we're going to change that to true if you set it to on it's going to default to false so these are things you learn while watching nerd of the street videos i have dug through all the logs so that you don't have to so we'll save that file and we will restart mariadb and it has restarted we are going to connect mariadb dash u root dash p i'll type in our mariadb servers root password and at this point i'm going to show variables like wild card ssl so let's take a look at uh what ssl configuration we have going on now as you can see have ssl this is set to yes once again you can ignore the have open ssl this was a compilation option that was not set there's nothing we can do about that unless we want to recompile mariadb from scratch but we do have ssl enabled now have ssl yes that's what you want to be looking for even if you see the rest of this configured if have ssl is set to no you've got a problem somewhere permissions issue configuration issue if this is set to no your mario db server is not going to be using ssl under any circumstance but we have have ssl set to yes that means that we are configured properly and the server is ready to accept connections over ssl you can see we've got our certificate authority our ssl cert our ssl key here once again we didn't specify any particular ciphers or anything more complex this is going to encrypt our communications at the very least though i'm going to quit out of that and now that our server is ready to accept ssl communication we're going to come back over to our client and set our client up to access the server over ssl tls now once again i'm going to make a directory at etsy mysql ssl and i am at this point going to open up a file browser on my local computer we need to copy a couple of files from our server to our client since we used ssh keys for authentication there's no password on my root user on this server or on this server so it would be a little tricky for me to use sftp or anything like that to copy the files directly from one computer to the other so i'm just going to use my own computer here as a intermediary if you're on windows or mac os you can use a program like filezilla but i'm going to run sftp i'm going to connect root at example1.nernonthestreet.com etsy mysql ssl and that is going to connect there i am also going to connect to the same directory on our client so example2.nernonthestreet.com and we just made that directory so it's empty so the only files we need to copy are cacert.pim once again we don't want to copy the the certificate authority key that's private and that should stay on the server that's generating the keys but our ca cert.pim that's what's going to allow our client to trust the certificates generated by this authority so we'll copy that one we also need client cert.pim and clientkey.pem since we generated those specifically for this client machine so i'll just copy those over with a control v there i'll close out of our file browsers if i do an ls in etsy mysql ssl you can see we have all three of those files i'm actually going to cd into etsy mysql ssl and i am going to run the same command that we did on the other machine to convert this key into key rsa once again it's a modern key with a little bit more metadata right now we need to strip that out and leave a plain rsa key so we are going to input our client key and output our client key rsa.pim okay and we'll nano into etsy mysql mariodb 50 client dot cnf this time instead of server since this is our client we want to come into here and we'll come down here we are going to uncomment our ssl cert and key we do want to also add on top an ssl ca option and the ca option is going to be etsy mysql ssl ca cert.pim we'll have etsy mysql ssl client cert and mysql ssl client key rsa.pam once again because even the client for mariodb doesn't know how to read a regular private key it needs to be an rsa format private key down here allow only tls encrypted connections we want to turn that on so ssl verify server cert equals on this is also going to check our server's certificate that it sends us against our certificate authority so we've got our client certificate here when we attempt to connect we're actually going to send this client cert to the server and the server is going to send the server cert to the client it's not going to send the server key but it will send the server cert to the client so the client is going to verify that server certificate by checking it against the certificate authorities certificate since as you might recall when we set this whole thing up the first thing we did was we made the server key in the server cert because it signed the rest of the keys inserts for us so we will save that and at this point we are going to run mariadb dash u test user dash p host equals example1.nernonthestreet.com it's going to ask for a password and we are connected and if we run status here you can see ssl cipher in use is and we've got our cipher that we're using right there if you see ssl cipher in use under your status you are using an encrypted connection do not be confused if you run that show variables like option again you'll see there's no cipher specified here because we're not forcing any particular cipher what you want to look at on your client is the status because this is talking about the actual connection we are using right now so it did automatically determine a cipher that is supported by both the client and the server so at this point if we do anything if we insert records if we show records from our database you know all of that is going to be encrypted now so it's secure none of the people in between those two servers on the network level can see what's happening other than some data being transferred now that's really great however if we quit out of this sql connection and we go back into our configuration file if we comment out all of that ssl configuration we'll save that we can connect again and we are connected and if i run status you can see once again there is no ssl ssl is optional right now so if anything happens if those certificates expire or if somebody deletes one of those certificates from our client or from the server if anything happens that makes ssl unable to be used this will fall back right now to a plain old clear text connection that can be useful in some cases in other cases you might want to avoid that if you want to be sure that you're always secure and you're not sending any sensitive data between these two servers unencrypted you want to force ssl all the time so we're going to do that by forcing ssl for the user that we're connecting with since we know we have this user anybody connecting to the server needs to be connecting with a specific user and this user is the only user that's allowed to connect from this ip address of this client the way that mariodb handles forcing ssl is on a per user basis so we're going to come back over to our server here and we're going to run mariadb dash u root p we'll enter in our mariadb root password again now i'm going to run show grants for test user at and i'm going to put in our ip address which i don't remember off the top of my head so i'll go ahead and copy that from our digital ocean page here so show grants for test user at this ip address and this is going to show us what permissions we have set up for this user if we just do show grants for test user it's going to default to a wildcard and it will say we don't have any grants defined for the user test user for any host so we do need to specify what host we are allowing it to connect from when we're on this ip address this particular user is able to do anything on test database we've got grant all privileges on test database to test user so that's the data manipulation portion of our permissions but above that you can see we've got grant usage on anything so we've got grant usage on our entire server to test user identified by password so we typed in a single command for both of these things we typed in grant all privileges on test database to test user identified by and we put our password in when we ran that command mariadb actually split it out into two different lines of configuration for us because the connection information is separated from the actual privileges to read and write data so this first one is what we want to modify and i'm going to run grant usage on once again everything to test user at 159.65.98.46 require ssl now since we're running grant usage we're not running grant privileges this is not actually going to change what this user is allowed to do once it's connected this is just changing how we're allowed to connect and if we do our show grants command again you can see that require ssl is now appended onto that grant usage line so we're going to quit out of mariodb now and we'll come back over to our client i'm going to attempt to connect and this time we're going to get access denied since we updated that rule and we said we need to connect using ssl ssl is still commented out in our configuration file on the client so even though we enter the correct password we got access denied so i'm going to nano into our config one more time we will uncomment our ssl configuration and if i connect now you can see we are connected and if i do a status the reason why we were allowed to connect is because we do have ssl enabled so that's great and then the last piece of the puzzle here is if we go back into our home directory our php file the way that you're probably going to be accessing mysql through some sort of application maybe it's not php maybe it's java maybe it's python maybe it's something else in my case it's php and if we run that php file we're going to get the same error access denied because that php file has not been configured to use ssl yet so we're going to nano into this php file and set it up to use ssl so how do you do this with pdo it is going to look different if you're using mysqli but for pdo since that's what i'm using in my example we're going to create an array here pdo options equals and this array is going to contain the same three things that we had to configure in our mariodb client configuration file so we'll say pdo mysql attribute ssl certificate authority that is going to be set to etsy mysql ssl ca cert.pim pdo mysql attribute ssl cert that is going to be set to etsy mysql ssl client cert dot pem and finally pdo mysql attribute ssl key now for this one i'm actually going to use something different than what i used in the configuration file pdo it's more modern than mariodb is in terms of what it supports with ssl and mariodb is actually being updated as we speak there are some things when i was researching for this video there are some options for the configuration file that are in newer versions of mariodb that have already been published they're just not endeviein yet because debian like most server grade distributions tends to lag behind a little bit in terms of its feature updates but the version of mariodb we have right now it needed us to convert that key into a plain old rsa format pdo doesn't need us to do that pdo can read the default open ssl format for that private key so if we add etsy mysql ssl client key dot that is going to work now for all three of these files they do need to be readable by php in my case since i'm running this on the command line it's going to be using my permissions and i'm logged in as the root user if you're making an application that's running with apache then you probably need to assign this client key file you need to make it so that the apache or www data user owns that file if it's not readable by everyone on the system but we've got an array with those three options and down here in our connection we've got the connection itself that we're creating we've got our username and password and then we're going to add one last thing in here in this php data object that is going to be our pdo options array that we're passing in so we'll save that and we'll run this one more time we've got an unexpected let's see i forgot to put a semicolon down here after this closing square bracket for our array my videos are very realistic they include the typos that you make while programming so we are going to run php man line four ah i need commas in between these array items as well and i can even put a comma on the last item there and it's not going to get mad at that now we can run our php file once again and you can see we now have an ssl cipher so we have verified well for one thing we knew that we weren't going to be able to access the server anymore without ssl but now we can also see in our output that our php pdo setup is using ssl it's got a cipher there and we are able to read our records so that's it that is how to set up your sql server to serve over ssl tls and that's how to implement your client accessing it over ssl tls both for command line usage and for real world application usage i hope this was helpful to you guys it was surprising how difficult it was to find information on this particular topic when i was trying to set this up for one of my projects and it is something that is relevant for a variety of different things that you might be doing so hopefully this was useful to somebody out there if it was feel free to join the nerd club at nerdclub.nauts.com it's just three dollars a month and you can help me make more cool tech videos like this one for now though that's everything i had to show you so i'm jacob kaufman i'm the nerd on the street and i'll see you guys in the next one bye [Music] you

Channel: Nerd on the Street

Views: 3,043

Rating: 4.9534883 out of 5

Keywords: nerdonthestreet, jacob, kauffmann, jacobgkau, MariaDB, MySQL, SSL, TLS, secure, encrypted, remote, connections, how to, setup, configure, tutorial, guide, walkthrough, SQL, PHP, PDO, MySQLi, database, access remotely, allow remote connections

Id: wDVYaaNy2k8

Length: 54min 32sec (3272 seconds)

Published: Sun Jan 10 2021