MySQL

Strong TLS Encryption at Alureon.net!

October 31, 2017 Apache, Linux, MySQL No comments

You may (or may not) have noticed that alureon.net is now served over strong TLS encryption!  This is a big win for me, as it’s always been something that has puzzled me.

You can check the certificate yourself in Chrome using Developer Tools

TLS in Chrome's developer tools

Aww yeah!

 

 

 

 

 

 

 

 

 

 

 

 

 

What Not To Do

Tempting as it might be, you apparently cannot self-sign a certificate and expect any browser not to freak out about it.

openssl req -x509 -new -nodes -key alureon.key -sha256 -days 1024 -out alureon.pem

(This opens up a whole new basket of problems with Subject Alternative Names as well.  Just don’t do it.)

Expecting users to click through the “INSECURE” prompts or install your root certificate seems a bit unreasonable in most cases.

What To Do

A buddy of mine pointed me over to https://letsencrypt.org/

Using certbot, I was able to get myself a legitimate, trusted cert (for free!) with little hassle.  I opted for:

certbot certonly --apache -w /srv/http -d www.alureon.net -d alureon.net

I answered a few questions, and it generated my SSL cert and key.  Super easy.  The only other steps are pretty much uncommenting the SSL module in httpd.conf.

LoadModule ssl_module modules/mod_ssl.so
Include conf/extra/httpd-ssl.conf

Also, you have to set up extra/httpd-ssl.conf to point to your new SSL cert and key.

SSLCertificateFile "/etc/letsencrypt/live/www.alureon.net/fullchain.pem"
SSLCertificateKeyFile "/etc/letsencrypt/live/www.alureon.net/privkey.pem"

I opted for some other pretty extreme settings as well, forcing only the latest version of TLS.

SSLCipherSuite EECDH+AESGCM:EDH+AESGCM:AES256+EECDH:ECDHE-RSA-AES128-SHA:DHE-RSA-AES128-GCM-SHA256:AES256+EDH:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GC
SSLProtocol -all +TLSv1.2

Fixing Insecure Resource Requests

After you go full TLS, browsers still claim you’re insecure if you make any requests to insecure resources (resources not served over http).  This includes fonts, javascript, and even images.  I wanted the green Secure text in Chrome badly enough to go the extra mile, so I continued to fight the good fight.

I’m sure there’s a better way to do this, but I did

UPDATE wp_posts SET `post_content` = REPLACE (`post_content`, 'http', 'https');

This worked wonderfully, but it also changed the literal text “http” in all of my posts to “https”.  This had some funny side effects, but I think I changed everything that actually needed to be “http” back.  There was one post where I had pasted text of myself working in a directory named “/srv/http”.  It changed the directory name to “/srv/https”, which I though was kind of funny.

Forcing TLS at the Domain Level

This worked great for manually navigating using SSL (typing https:// in the browser), but I’m guessing most people aren’t going to do that.  How do we force them to use SSL at the domain level?

I found this hack on some random website, and it seems to work great (aside from appending an extra forward slash to my TLD).

RewriteEngine On
RewriteCond %{HTTPS} off
RewriteRule (.*) https://%{SERVERNAME}/$1 [R,L]

I just appended that to the bottom of my httpd.conf, and it worked!  If anyone knows a more efficient way, let me know.

Database Corruption

October 28, 2017 MySQL No comments

My old Raspberry Pi 2’s sdcard fell victim to sdcard corruption.  I wasn’t able to dump my MySQL tables cleanly because the database was unable to start by the time I realized what was going on.

I’ve restored what I could of my wonderful little blog, but there was data loss during the migration to a new sdcard.  That’s why things look a bit different.

Lessons Learned

What doesn’t work

If you’re moving MySQL tables, do not simply copy the directories from /var/lib/mysql

Worse yet, if you’re using InnoDB, you’ll be missing the /var/lib/mysql/ib* files that InnoDB requires.

MySQL is very picky and if it senses anything is amiss it will simply throw its hands in the air and give up.

What works

Ideally, you need to dump your databases while connected to the MySQL server with something like this:

mysql -u root -p wordpress > wordpress.sql

This dumps the entire database into an SQL script you can re-import on your new database.  You suck it back up with the same syntax:

mysql -u root -p wordpress < wordpress.sql

 

Creating A New MySQL User, Database, and Granting Permissions

March 27, 2017 MySQL No comments , , ,

In this example, I am creating a new database called moodle and it’s own user named moodle for accessing it.

Note: This is for a test/development machine on localhost. In the string IDENTIFIED BY 'moodle' you may wish to replace moodle with a real password.

mysql> CREATE DATABASE moodle;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT user,password,host FROM mysql.user;
+-----------+-------------------------------------------+-----------+
| user      | password                                  | host      |
+-----------+-------------------------------------------+-----------+
| root      | *<41 byte password hash>                  | localhost |
| root      | *<41 byte password hash>                  | 127.0.0.1 |
| sakaiuser | *<41 byte password hash>                  | localhost |
| sakaiuser | *<41 byte password hash>                  | 127.0.0.1 |
| olat      | *2D665452229FB636D7CACCE3273269E34C62CA5C | localhost |
| olat      | *2D665452229FB636D7CACCE3273269E34C62CA5C | 127.0.0.1 |
+-----------+-------------------------------------------+-----------+
10 rows in set (0.00 sec)

mysql> CREATE USER 'moodle'@'localhost' IDENTIFIED BY 'moodle';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'moodle'@'127.0.0.1' IDENTIFIED BY 'moodle';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL PRIVILEGES ON moodle.* TO 'moodle'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON moodle.* TO 'moodle'@'127.0.0.1' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'moodle'@'localhost';
+---------------------------------------------------------------------------------------------------------------+
| Grants for moodle@localhost                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'moodle'@'localhost' IDENTIFIED BY PASSWORD '*1ECDE898A7068C6422AF1220D301EBA14F576CF9' |
| GRANT ALL PRIVILEGES ON `moodle`.* TO 'moodle'@'localhost' WITH GRANT OPTION                                  |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SHOW GRANTS FOR 'moodle'@'127.0.0.1';
+---------------------------------------------------------------------------------------------------------------+
| Grants for moodle@127.0.0.1                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'moodle'@'127.0.0.1' IDENTIFIED BY PASSWORD '*1ECDE898A7068C6422AF1220D301EBA14F576CF9' |
| GRANT ALL PRIVILEGES ON `moodle`.* TO 'moodle'@'127.0.0.1' WITH GRANT OPTION                                  |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> exit;
Bye

Recovering WordPress After Changing ‘WordPress Address URL’

March 26, 2017 MySQL, WordPress No comments , ,

I learned that apparently if you change the WordPress Address (URL) field in Settings, you can actually cripple the site completely. Not only does your content fail to load, but you can’t even reach the admin interface.

I had removed the trailing /blog portion of the URL to cause this. So how do you fix it? Edit the database.

[alureon.pi] ~http % mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5532
Server version: 10.1.21-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE wordpress;
Database changed
MariaDB [wordpress]> SELECT * FROM `wp_options` WHERE option_name = "siteurl";
+-----------+-------------+--------------------+----------+
| option_id | option_name | option_value       | autoload |
+-----------+-------------+--------------------+----------+
|         1 | siteurl     | http://alureon.net | yes      |
+-----------+-------------+--------------------+----------+
1 row in set (0.00 sec)

MariaDB [wordpress]> UPDATE `wp_options` SET option_value = "http://alureon.net/blog" WHERE option_name = "siteurl";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [wordpress]> SELECT * FROM `wp_options` WHERE option_name = "siteurl";
+-----------+-------------+-------------------------+----------+
| option_id | option_name | option_value            | autoload |
+-----------+-------------+-------------------------+----------+
|         1 | siteurl     | http://alureon.net/blog | yes      |
+-----------+-------------+-------------------------+----------+
1 row in set (0.00 sec)

MariaDB [wordpress]> exit;
Bye

Not a terrible pain in the neck, but more manual intervention than most people probably want to deal with.