We're currently running MySQL and Moodle v3.5.2+. We're migrating over to a newer MySQL server and the admin would like to enforce all connections to be over SSL. Is there any way to encrypt the connection between Moodle and its DB server without modifying core code?
The Moodle server will connect to the MySQL server through a MySQL process on the Moodle server. So it's MySQL to MySQL, and that's where the secure connection takes place. Other than noting the host address, the connection is pretty much the same command-wise.
I run MySQL on a different server and the connection security is based around MySQL client settings, and MySQL server settings. Whatever app (Moodle in this case) connects to the remote MySQL server will do so through the MySQL client.
In other words, I don't think modification will be necessary.
In that case, can you elaborate on your setup a bit? The MySQL connection is instantiated via the connection info in the config.php, so are there any other fields that need to be added to it in order to tell the MySQL server that this connection is intended to be SSL? If not, where do I enter all the information for my SSL certs and such?
Thanks!
What you are asking about is a MySQL connection issue, not a Moodle configuration, so you'll have to reference the MySQL documentation. In short, here are the requirements:
For MySQL server
- your MySQL build will need to be enabled to use openssl
- run show variables like "%ssl%"; in MySQL to see your current configurations
- my.cnf will need to have [mysqld] settings to require TLS standards, etc
- the certs are located where openssl keeps its certs - but these are by default self-signed and can be replaced
I have this bookmark that might help: https://www.digitalocean.com/community/tutorials/how-to-configure-ssl-tls-for-mysql-on-ubuntu-16-04
Oh, I have the server all ready to go and accepting SSL/TLS connections already. But I'm uncertain how to make Moodle connect to it securely. As in, there's nowhere in the config.php to indicate SSL or certs or any of the other items necessary to instantiate a secure connection. I could use something like stunnel to handle that and then have Moodle connect on localhost, but that feels more like a last resort/hack than anything.
Hi Paul, what Garrett is saying is that it's not in Moodle's config.php where you configure this. The cert settings are outside of Moodle, in the servers MySQL Client Configuration File.. See the "Create a MySQL Client Configuration File" section of the post Garrett linked.. I believe it would need to be setup for the user that Moodle runs as (www or apache?)
Hey Mathew. Ok, I may be completely ignorant here, so apologies if that's the case. But doesn't the client generally need to present a certificate to the server in order to do the TLS handshake? That's how it's worked for things like other Tomcat apps we've used. If so, I don't have a MySQL client installed on the Moodle server. The only thing MySQL-related on the Moodle server is the php7.0-mysql package - no libmysql, no /etc/my.cnf, no /var/lib/mysql, nothing. So while I understand it may not be specifically within Moodle where the configuration needs to take place, I don't understand where the correct place actually *is*. Can you point out where my thinking is wrong?
MySQL client will need to be installed on the Moodle server, if it is not already. The client is not the whole DB app, just the client.
My server side settings in my.cnf:
[mysqld]
port=3306
skip-name-resolve # I have this because the DB server doesn't have dns
tls_version=TLSv1.2 # this requires connections to be secure
You will also want to limit the rights on the database to the user@moodle.server.ip.address...which will limit accessing the database locally on the server, but you can access it through root locally instead.
I should also mention that the tutorial in that link suggests creating certs in a file and then referencing those certs in the my.cnf [client] section - however, I didn't need that because installing the mysql client provided certs in the data directory (/var/lib/mysql in my case) which were automatically used.
Ah! I see the disparity - you have the MySQL client installed on your Moodle server, whereas I do not. I just have the PHP module for it.
Once I install the MySQL client, is it safe to assume that PHP will know about the installation, and then use the requisite configuration files for it? Interestingly enough, Ubuntu didn't see fit to install that /var/lib/mysql. Odd, that.
It should. But, if it doesn't work, you'll have to start troubleshooting, and I would begin by simply trying:
mysql -u username -p -h re.mote.add.ress and attempting the connection manually.
If it CAN connect, but has security issues, it will tell you. If it just waits and waits, then it's not connecting...look server side as the error log will probably say why.
Ok, now we're getting somewhere. I installed the mysql-client package, and used the command-line client to successfully connect to the SSL MySQL server. I took the command-line flags and put them in /etc/mysql/conf.d/my.cnf and managed to connect again. So far, so good. But since the [client] and [mysql] sections of the /etc/mysql/conf.d/my.cnf specify SSL, I can no longer connect to the non-SSL MySQL server using the command-line tools, but this is good. This is to be expected. It's providing SSL criteria to a non-SSL MySQL and so the connection is rightfully refused.
My problem is this: the Moodle app still functions while set up with the non-SSL MySQL server. This tells me that PHP doesn't take into account the settings in /etc/mysql/conf.d/my.cnf when setting up connections. Is there a way to do that? I dug around in the php.ini and other PHP config files to see if there was a way to make it evaluate what's in the my.cnf file when establishing connections, but didn't see anything.
Thanks for the help so far.
That's why I'm thinking the SSL connection should be required on the server end, under [mysqld], and not on the client end.
From a client standpoint, you may not need any settings at all. I have none on my client side.
If you can't change the server side, try it without the cert settings, and see what happens. Maybe the admin already has the setting in place?
I do have certs and their locations are noted, but the requirement to use them is not in place.
When I was working through this the first time, I remember listening to the port and getting a dump of the back and forth in order to see what was happening.
This answer does not appear to be complete. Do a tcpdump on the mysql server to ensure there are no unencrypted packages being transmitted from the client.
tcpdump -i eth0 -s 0 -l -w - dst port 3306
If you can read anything at all, the connection is not encrypted.
By default, simply setting up the SSL on the server side will still allow connections on other than TLS unless you specify in the user creation "require ssl"
eg. GRANT ALL ON *.* TO 'username'@'192.169.1.100' IDENTIFIED BY 'password' REQUIRE SSL ;
From the command line on the remote client, include the --ssl option:
msyql --ssl -u username -h 192.168.1.100 -p
If you leave off the --ssl it will try and connect without SSL. If the user does not require ssl it will still connect.
Nothing above solves the original question - how to configure Moodle code to use SSL.
See https://tracker.moodle.org/browse/MDL-50780 for the solution within Moodle 2.7. It should be similar in 3.x
Basically you have to tell PHP to set ssl for the database connection.
You are correct in saying that the requirement can be on a user level.
However, if you read the tutorial from the link earlier in thread there is another method which involves setting require_secure_transport to ON. This makes it on a server level, vs user level.
https://dev.mysql.com/doc/refman/5.7/en/encrypted-connections.html for more info.
Thanks for clarifying. My apologies - I was testing on a MariaDb server. MySQL can secure data transmissions at the server level. This is not the case, however for MariaDb. MariaDb only allows SSL requirement at the user or resource (database or table) levels, not server wide, and does not have the same server variable as MySQL.
https://mariadb.com/kb/en/library/secure-connections-overview/
https://mariadb.com/kb/en/library/system-variable-differences-between-mariadb-103-and-mysql-57/
I feel the need to chime in to ask for some clarification from people and what I think are incorrect ideas in this thread:
1. Are MySQL connections really handled by MySQL client in Moodle? Not MySQL Devel compiled into PHP? Because I do not have MySQL client installed on any of my web servers and the websites work fine.
2. For things like PHPMyAdmin you absolutely need to configure client side certificates to connect correctly to MySQL, why wouldnt this be needed in moodle to define the database connection characteristics?
3. Yes, there are MySQL "ssl_" variables to set, but if your users on MySQL are configured to not require SSL or not require X509 then no encryption will be used, even if you have these set.
I think normally this type of information should be established in the initial new Database() object establishment (in standard PHP).
Maybe I am misunderstanding what is being said in this thread, but I do not think the information presented is correct. Minus what Kevin is saying, which looks more like what I expect.
Hi Wolf,
You are correct, MySQL client isn't necessary on the Web Server, but is useful for testing. The PHP libraries handle the connection.
A summary of my above post:
For Moodle, the only user you should be concerned about connecting to the database is the one configured in config.php to allow Moodle to read and write to the database. If the database is on the same server as the web server, not recommended, then an encrypted connection for the user is not necessary as no data leaves the server before the web server returns it to the web browsing client, which presumably is over https and not http. For a separate database server and web server, it is highly recommended to establish a secure and encrypted connection. The rule of thumb is to not allow the database server to communicate directly with the Internet, but only with the web server.
For confirming a good set up all you need to do is monitor the traffic being sent to/from the database server. If you can read anything being sent over port 3306 then the encryption isn't enabled for the connection.
For the server side, by default Moodle does not enable SSL/TLS for the connection to the database server. The encryption is created by configuring the database server to accept SSL connections, and established by setting a flag in the mysqli PHP connection - see http://php.net/manual/en/mysqli.ssl-set.php
There is a difference between MySQL and MariaDB as well. WIth a MySQL server SSL can be required for every connection. whereas with MariaDB you have to "require" the user that is configured in the Moodle Config to connect to the database server using SSL, or even better, to require X509 which checks for the certificates on connection.
Hope this helps someone.
Very much appreciated for the roundup of a complicated topic and summary of discussion.
I hope to see https://tracker.moodle.org/browse/MDL-54704 make its way into Production at some point.
So to be clear, is the only way to add SSL encryption to the Moodle config.php user connection by making the changes outlined in this patch?:
https://github.com/moodle/moodle/compare/master...aeijdenberg:securemysql (via this ticket: https://tracker.moodle.org/browse/MDL-50780)
Allow me to summarize what is correct and what has already been said, for those who follow later.
First, this thread is about MySQL connections between a server with Moodle and a remote MySQL server.
- MySQL has a full database version, and also a client version. Use the MySQL client on the Moodle server.
- MySQL, by default, does not require SSL connections, but DOES by default initiate them, if the client has SSL capability.
- To determine if a MySQL server has SSL capabilies already, this command can be run from the MySQL command line: show variables like "%ssl%";
- MySQL uses OpenSSL certs by default, but these certs can be replaced with other certs (like Let's Encrypt, etc)
- This tutorial (using Ubuntu 16.04) speaks of a MySQL setting called "require_secure_transport" which can be set to ON. This setting could possibly be set in a few different places. The tutorial speaks to this. When this setting is ON, it will reject any connection attempt that is not secure.
- The Moodle server will need to have certs, of course. Installing the MySQL client will often throw in the OpenSSL package as well, on many installation procedures.
- The level of encryption (recommended at this time no less than TLS1.2) can be set as well on the server side.
- In this sense, then, the Moodle app is not required to establish secure connections. Instead of such an approach, the only connections that Moodle is configured to use in this setup are already secured...due to prior work with MySQL settings.
Again, this thread is about MySQL connections between a server with Moodle and a remote MySQL server.
Garrett,
To be perfectly clear - there is NOTHING in the Moodle database connection classes (code) to initiate a secure connection to a secure MySQL server, or any other database server. If you run a scan on the network traffic from a Moodle server to a database server using unmodified core code regardless of how the MySQL server is configured, you will be able to read the traffic.
The database connection is established using PHP and is independent of having the MySQL client and the associated libraries and certificates installed on the Moodle Web Server. If the ssl flag and values are not passed using PHP, there is no encryption taking place.
As Wolf pointed out, there has been a patch developed to incorporate the SSL certificates and trigger the php ssl flag, however this patch was not implemented in the official releases of Moodle.
I'm a bit confused at two things.
1) I never said a single thing about Moodle settings as being a source of SSL settings. I can't understand why any talk of Moodle settings are being directed at me.
2) You say "If you run a scan on the network traffic from a Moodle server to a database server using unmodified core code regardless of how the MySQL server is configured, you will be able to read the traffic." Are you suggesting that my MySQL server settings which require encrypted connections have no effect on incoming connections....because the connections are coming from Moodle?
You introduced a completely different topic, in my opinion. One I'm not actually discussing.
I originally answered the question of "How does one create encrypted connections with a remote MySQL database?" Maybe the title of the thread is not specific enough.
These bullet points of yours:
- MySQL has a full database version, and also a client version. Use the MySQL client on the Moodle server.
- MySQL, by default, does not require SSL connections, but DOES by default initiate them, if the client has SSL capability
- In this sense, then, the Moodle app is not required to establish secure connections. Instead of such an approach, the only connections that Moodle is configured to use in this setup are already secured...due to prior work with MySQL settings.
Are not correct. The MySQL client being on the moodle server is not required, not a guarantee (I sure dont have it installed on my web servers)
Moodle, and PHP web level applications DO NOT utilize MySQL client, or MySQL client settings in the initiation of their database connection instance. It is incorrect to say that any default database connections will be SSL, even if the accounts are set to "require ssl" because the Moodle database initiation does not in any capacity specify the settings to initiate the database connection securely (and that is not something that happens "by default"). That can only be changed with the items outlined in this patch (https://github.com/moodle/moodle/compare/master...aeijdenberg:securemysql)
But this is what no one is talking about in the threads regarding mysql ssl: require_secure_transport=ON is a setting on the REMOTE mysql server that REQUIRES all incoming connections to be secure.
I never know anyone's totally specific setup, so help given is general. But it appears that everyone is trying to solve from the client side what appears to be solvable from the remote server side.
From MySQL docs (see bold):
MySQL can be compiled for encrypted-connection support using OpenSSL or yaSSL. For a comparison of the two packages, see Section 6.4.4, “OpenSSL Versus yaSSL” For information about the encryption protocols and ciphers each package supports, see Section 6.4.6, “Encrypted Connection Protocols and Ciphers”.
By default, MySQL programs attempt to connect using encryption if the server supports encrypted connections, falling back to an unencrypted connection if an encrypted connection cannot be established. For information about options that affect use of encrypted connections, see Section 6.4.1, “Configuring MySQL to Use Encrypted Connections” and Section 6.4.2, “Command Options for Encrypted Connections”.
MySQL performs encryption on a per-connection basis, and use of
encryption for a given user can be optional or mandatory. This
enables you to choose an encrypted or unencrypted connection
according to the requirements of individual applications. For
information on how to require users to use encrypted connections,
see the discussion of the REQUIRE clause of the
CREATE USER statement in
Section 13.7.1.2, “CREATE USER Syntax”. See also the description of the
require_secure_transport system
variable at Section 5.1.7, “Server System Variables”
I'm not a Moodle expert, and I use MySQL 8.0 now on all my servers. There are lots of ways to solve a problem, I'm just talking about making sure the connections are encrypted. Other problems need other solutions. It really does seem that we are discussing two different issues.
Excellent information Garrett, thank you for the clarifications.
I think that my confusion stems from the fact that require_secure_transport was introduced in MySQL 5.7 and I dont run any 5.7s
All of the MySQLs under my administration right now are 5.6 unfortunately. Which I think might put this solution out of my reach.
I have not used MySQL very long - in fact I'm not sure I ever used 5.6, but I've used it a lot. And, there were big changes from 5.7 to 8.0 as well. Improvements, mostly, but some unexpected inconveniences too.
I run Ubuntu 18.04 which serves up 5.7, and in order to get MySQL 8.0 I had to use the Oracle repository after purging all 5.7 packages, and the effect it had on my workflow when coming from 5.7 basically came down to two things:
1) The ways users authenticate. Older versions use username/password and the default in 8.0 is "caching_sha2_password", so when users are created they can be established with "mysql_native_password" for the plugin field. The databases then accepts connections with username/password credentials.
2) A couple of times an 8.0 update has caused a mismatch/number of fields error and I get locked out. I have to follow this guide to restore access.
For another example of how to solve the problem you describe, consider that a local MySQL client can maintain a connection with a MySQL remote server. The database and data are remote, but the connection is local. The local Moodle installation can run requests to the local MySQL client that then passes it off to the remote server.
No, this has nothing to do with Moodle. But the data leaving the server will be encrypted. I've never done an "unencrypted" connection to any of my servers.
I've just confirmed again on my servers that if the remote MySQL server has certs noted, and the require_secure_transport option enabled... And if the client sever with (any app) has certs noted in the MySQL client settings, then the app only needs to present the ca.pem in the connection settings and MySQL will figure the rest out.
Believe it or not.
I understand what you are saying regarding core settings in Moodle and php, etc. but it is not actually the case with MySQL that an app must completely define all connection settings.
That is, if the MySQL settings are already properly established through [mysqld] on the remote side and [mysql] on the client side, then connections that involve MySQL must use those settings.
I am willing to offer more specifics if a specific problem is shared.
A grain of salt is needed in the last two posts of mine. They we're not really responding to Wolf's very clear point regarding Moodle limitations.
Wolf is right.
Moodle is lagging on this very simple issue. My apologies. Apart from an SSH tunnel, running a remote MySql database securely does not appear to be an option.
Hi Garrett,
Moodle is lagging on this very simple issue. My apologies. Apart from an SSH tunnel, running a remote MySql database securely does not appear to be an option.
That's true: please vote MDL-54704. Spare time permitted, I'd contribute there to let the resolution of that issue land into the main stream.
HTH,
Matteo
I would be happy to help out...don't know how to vote, but you can pass along that the concern regarding whether the connection is encrypted or not, is completely resolved from the remote end of things. Tshark will easily print out the stream and will note that it is "encrypted" and to what level. 3.3 is the web server, 6.10 is the remote database in the following exchange:
55 10.073907679 10.10.3.3 → 10.10.6.10 TCP 74 59336 → 3306 [SYN] Seq=0 Win=29200 Len=0 MSS=1460 SACK_PERM=1 TSval=1209902918 TSecr=0 WS=128
56 10.073960143 10.10.6.10 → 10.10.3.3 TCP 74 3306 → 59336 [SYN, ACK] Seq=0 Ack=1 Win=28960 Len=0 MSS=1460 SACK_PERM=1 TSval=1798325109 TSecr=1209902918 WS=128
57 10.074179936 10.10.3.3 → 10.10.6.10 TCP 66 59336 → 3306 [ACK] Seq=1 Ack=1 Win=29312 Len=0 TSval=1209902918 TSecr=1798325109
58 10.075274658 10.10.6.10 → 10.10.3.3 MySQL 144 Server Greeting proto=10 version=8.0.13
59 10.075502361 10.10.3.3 → 10.10.6.10 TCP 66 59336 → 3306 [ACK] Seq=1 Ack=79 Win=29312 Len=0 TSval=1209902919 TSecr=1798325110
60 10.075532938 10.10.3.3 → 10.10.6.10 MySQL 102 Response: SSL Handshake user=
61 10.075542290 10.10.6.10 → 10.10.3.3 TCP 66 3306 → 59336 [ACK] Seq=79 Ack=37 Win=29056 Len=0 TSval=1798325111 TSecr=1209902919
62 10.075855264 10.10.3.3 → 10.10.6.10 TLSv1 583 Client Hello
63 10.075861290 10.10.6.10 → 10.10.3.3 TCP 66 3306 → 59336 [ACK] Seq=79 Ack=554 Win=30080 Len=0 TSval=1798325111 TSecr=1209902920
64 10.077840245 10.10.6.10 → 10.10.3.3 TLSv1.2 3072 Server Hello, Certificate, Server Key Exchange, Certificate Request, Server Hello Done
65 10.078138943 10.10.3.3 → 10.10.6.10 TCP 66 59336 → 3306 [ACK] Seq=554 Ack=3085 Win=35328 Len=0 TSval=1209902922 TSecr=1798325113
66 10.078937122 10.10.3.3 → 10.10.6.10 TLSv1.2 171 Certificate, Client Key Exchange, Change Cipher Spec, Encrypted Handshake Message
67 10.079472230 10.10.6.10 → 10.10.3.3 TLSv1.2 308 New Session Ticket, Change Cipher Spec, Encrypted Handshake Message
68 10.079793459 10.10.3.3 → 10.10.6.10 TLSv1.2 234 Application Data
69 10.080119806 10.10.6.10 → 10.10.3.3 TLSv1.2 106 Application Data
70 10.080352492 10.10.3.3 → 10.10.6.10 TLSv1.2 114 Application Data
71 10.080649670 10.10.6.10 → 10.10.3.3 TLSv1.2 106 Application Data
72 10.080911499 10.10.3.3 → 10.10.6.10 TLSv1.2 148 Application Data
73 10.081076874 10.10.6.10 → 10.10.3.3 TLSv1.2 111 Application Data
74 10.081287907 10.10.3.3 → 10.10.6.10 TLSv1.2 110 Application Data
75 10.081397063 10.10.6.10 → 10.10.3.3 TLSv1.2 106 Application Data
76 10.081607124 10.10.3.3 → 10.10.6.10 TLSv1.2 104 Application Data
77 10.081645473 10.10.3.3 → 10.10.6.10 TLSv1.2 240 Application Data
If the code simply used the mysqli_ssl_set and mysqli_connect functions instead, as Adam Eijdenberg does, and even if a user did not provide anything other than a path to the ca.pem file, I believe all other settings can be left empty really...as long as MySQL is configured properly.
When I read the thread in the tracker, it all seems like overkill to me. I think MySQL -> MySQL should be utilized, rather than PHP -> MySQL. Besides, as the OP stated, the DB admin is going to set the requirements anyway.
I have never used anything but MySQL from a database standpoint. Take Laravel, for example, since it uses PHP. It's database config setup file looks like this:
'mysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST'),
'port' => env('DB_PORT'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'unix_socket' => env('DB_SOCKET', ''),
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
// 'sslmode' => env('DB_SSLMODE'),
'strict' => true,
'engine' => null,
'options' => array (
PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
// PDO::MYSQL_ATTR_SSL_KEY => env('DB_SSL_KEY'),
// PDO::MYSQL_ATTR_SSL_CERT => env('DB_SSL_CERT'),
PDO::MYSQL_ATTR_SSL_CA => env('DB_SSL_CA'),
),
],
Notice how I have the bolded lines are commented out. I don't even need to say that SSL is required. As long as I configure the MySQL client and the remote MySQL server config properly, it will work. MySQL relies on OpenSSL file locations, and if an encrypted connection is required by the remote end of things, then the local MySQL client kicks in and provides what is needed. Super simple. I only need to provide the ca.pem path. Just like when I visit any HTTPS website.
And I have the verify setting to false because my server is not a public-accessed domain.
How do I vote?
PS - I should also mention that to set up MySQL like this, it requires quite a few steps, especially regarding things like apparmor and cert file locations and file access permissions, etc.
Hi Garrett,
How do I vote?
Please create an account at https://tracker.moodle.org, log into the Tracker, click on this link MDL-54704, finally click on Vote for this issue (in the right side of your browser).
TIA,
Matteo