CHG-2018-05-22 migrate wiki to hetzner2

Status

2018-05-22

Marcin hasn't had time to finish the wiki validation (I've also stole much of his time for the higher-priority task of getting him an encrypted backup of his secret keys for ose server stuff at FeF), so we're delaying this migration until tomorrow, Wed May 23.

2018-05-18

Marcin set a tentative date of the migration to Tuesday 2018-05-22 pending the completion of validation of the staging site.

Purpose

This change does the following for our wiki

1. entirely migrate the wiki site from hetzner1 (shared hosting) to hetzner2 (dedicated hosting)
2. changes the domain from 'http://opensourceecology.org/wiki/' to '[1]'
3. update core mediawiki from v1.24.2 (released 2014-09-19) to v1.30.0 (released 2017-12-12)
4. installs many of the extensions using `git clone ...`, making it easier to update in the future
5. updates the vhost config to block all access to ".*wp-login.php"
6. updates the vhost config to block access to git files, and more generally: ".*\.(svn|git|hg|bzr|cvs|ht)/.*"
7. enable https via nginx
8. enable cache via varnish
9. disable cache & ddos protection on cloudflare
10. update extension 'Confirm User Accounts' to 4fe25f7
11. update extension 'Confrim Edit' from 1.3 to 1.5.0
12. update extension 'Interwiki' from 3.0 20140719 to 3.1 20160307
13. update extension 'Nuke' from 1.2.0 to 1.3.0
14. update extension 'Replace Text' from 1.0 to 1.2 (4426752)
15. update extension 'User Merge' from 1.9.0 to 1.10.1 (4546537)
16. update extension 'Widgets' from 0.8.10 to 1.3.0 (fce5acc)
17. update extension 'CategoryTree' to (850c018)
18. removed extension 'Flattr'
19. removed extension 'Google Co-op Extension'
20. removed extension 'IpbWiki Paypal'
21. removed extension 'JSWikiGantt'
22. removed extension 'RSS Reader'
23. removed extension 'TreeAndMenu'
24. removed extension 'ProxyConnect'
25. added new extension 'OATHAuth' version 0.2.2 (bed2e4b)
26. moves LocalSettings.php outside the docroot, replaces the existing LocalSettings.php with a simple file that does a php include of the LocalSettings.php file outside the docroot.
27. moves the ose logo into the uploads directory
28. does a sed text replacement within the db data for all http strings to use https instead for 'https://www.youtube.com/embed/', 'https://static.issuu.com/webembed/', 'https://scrumy.com/', 'https://embed.ted.com/', & 'https://player.vimeo.com/'
29. reduces the privileges of the wiki user on the db to only SELECT, INSERT, UPDATE, & DELETE
30. adds an additional "superuser" db user with all permissions on the db for maintenance scripts (creds stored in keepass, not on the server)
31. hardens the file permissions
32. prevents the web server from executing php files in the uploads directory
33. changes LocalSettings.php to ban IE6
34. changes LocalSettings.php to make the max upload size 1M. It warns > 500k.
35. changes LocalSettings.php to disable use of imagemagick as we don't let php exec()
36. changes LocalSettings.php to require all users to have >=10 character passwords and not be a common password or match their username
37. changes LocalSettings.php to require all sysop users to have >=20 character passwords
38. changes LocalSettings.php to use varnish
39. changes LocalSettings.php to not enable error/warning messages sent to user
40. changes LocalSettings.php to write debugging logs to 'wiki-error.log' outside the docroot
41. changes LocalSettings.php to use "MiserMode" to decrease db-heavy operations
42. changes the caching settings of mediawiki to use APCU (via CACHE_ACCEL) for the MainCache & MessageCache. Else it uses the db, and therefore every load includes a cpPosTime cookie, which causes varnish to hit-for-pass on every page.
43. changes the caching settings of mediawiki to use the DB for ParserCache
44. changes the caching settings of mediawiki enable the SidebarCache
45. changes the caching settings of mediawiki cache interface messages to files on disk outside the docroot ($IP/../cache/)

Points of Contact

Change being performed by: Michael Altfield

Service owners: by Catarina Mota & Marcin Jakubowski for Open Building Institute and Open Source Ecology

Apply to Production

####################
# run on hetzner1 #
####################

# STEP 0: CREATE BACKUPS
source /usr/home/osemain/backups/backup.settings
/usr/home/osemain/backups/backup.sh

# when finished, SSH into the dreamhost server to verify that the whole system backup was successful before proceeding
bash -c 'source /usr/home/osemain/backups/backup.settings; ssh $RSYNC_USER@$RSYNC_HOST du -sh backups/hetzner1/*'

# DECLARE VARIABLES
source /usr/home/osemain/backups/backup.settings
stamp=`date +%Y%m%d`
backupDir_hetzner1="/usr/home/osemain/noBackup/tmp/backups_for_migration_to_hetzner2/wiki_${stamp}"
backupFileName_db_hetzner1="mysqldump_wiki.${stamp}.sql.bz2"
backupFileName_files_hetzner1="wiki_files.${stamp}.tar.gz"
vhostDir_hetzner1='/usr/www/users/osemain/w'
dbName_hetzner1='osewiki'
 dbUser_hetzner1="${mysqlUser_wiki}"
 dbPass_hetzner1="${mysqlPass_wiki}"

# STEP 1: BACKUP DB
mkdir -p ${backupDir_hetzner1}/{current,old}
pushd ${backupDir_hetzner1}/current/
mv ${backupDir_hetzner1}/current/* ${backupDir_hetzner1}/old/
time nice mysqldump -u"${dbUser_hetzner1}" -p"${dbPass_hetzner1}" --all-databases --single-transaction | bzip2 -c > ${backupDir_hetzner1}/current/${backupFileName_db_hetzner1}

# STEP 2: BACKUP FILES
time nice tar -czvf ${backupDir_hetzner1}/current/${backupFileName_files_hetzner1} ${vhostDir_hetzner1}

####################
# run on hetzner2 #
####################

sudo su -

# STEP 0: CREATE BACKUPS
# for good measure, trigger a backup of the entire system's database & files:
time /bin/nice /root/backups/backup.sh &>> /var/log/backups/backup.log

# when finished, SSH into the dreamhost server to verify that the whole system backup was successful before proceeding
bash -c 'source /root/backups/backup.settings; ssh $RSYNC_USER@$RSYNC_HOST du -sh backups/hetzner2/*'

# DECLARE VARIABLES
source /root/backups/backup.settings
stamp=`date +%Y%m%d`
backupDir_hetzner1="/usr/home/osemain/noBackup/tmp/backups_for_migration_to_hetzner2/wiki_${stamp}"
backupDir_hetzner2="/var/tmp/backups_for_migration_from_hetzner1/wiki_${stamp}"
backupFileName_db_hetzner1="mysqldump_wiki.${stamp}.sql.bz2"
backupFileName_files_hetzner1="wiki_files.${stamp}.tar.gz"
dbName_hetzner1='osewiki'
dbName_hetzner2='osewiki_db'
 dbUser_hetzner2="osewiki_user"
 dbPass_hetzner2="CHANGEME"
 dbSuperUser_hetzner2="osewiki_superuser"
 dbSuperPass_hetzner2="CHANGEME"
vhostDir_hetzner2="/var/www/html/wiki.opensourceecology.org"
docrootDir_hetzner2="${vhostDir_hetzner2}/htdocs"
newMediawikiSourceUrl='https://releases.wikimedia.org/mediawiki/1.30/mediawiki-1.30.0.tar.gz'

# STEP 1: COPY FROM HETZNER1

mkdir -p ${backupDir_hetzner2}/{current,old}
mv ${backupDir_hetzner2}/current/* ${backupDir_hetzner2}/old/
scp -P 222 osemain@dedi978.your-server.de:${backupDir_hetzner1}/current/* ${backupDir_hetzner2}/current/

# STEP 2: ADD DB

# create backup before we start changing the sql file
pushd ${backupDir_hetzner2}/current
cp ${backupFileName_db_hetzner1} ${backupFileName_db_hetzner1}.orig

# extract .sql.bz2 -> .sql
bzip2 -dc ${backupFileName_db_hetzner1} > db.sql

# verify the first 2 (non-comment) occurances of $dbName meet the naming convention of "<siteName>_db
vim db.sql

# fix youtube embeds
fromString='https://www.youtube.com/embed/'
toString='https://www.youtube.com/embed/'
sed -i "s^$fromString^$toString^g" db.sql

# fix issuu embeds
fromString='https://static.issuu.com/webembed/'
toString='https://static.issuu.com/webembed/'
sed -i "s^$fromString^$toString^g" db.sql

# fix scrumy embeds
fromString='https://scrumy.com/'
toString='https://scrumy.com/'
sed -i "s^$fromString^$toString^g" db.sql

# fix ted embeds
fromString='https://embed.ted.com/'
toString='https://embed.ted.com/'
sed -i "s^$fromString^$toString^g" db.sql

# fix vimeo embeds
fromString='https://player.vimeo.com/'
toString='https://player.vimeo.com/'
sed -i "s^$fromString^$toString^g" db.sql

 time nice mysql -uroot -p${mysqlPass} -sNe "DROP DATABASE IF EXISTS ${dbName_hetzner2};" 
 time nice mysql -uroot -p${mysqlPass} -sNe "CREATE DATABASE ${dbName_hetzner2}; USE ${dbName_hetzner2};"
 time nice mysql -uroot -p${mysqlPass} < "db.sql"
 time nice mysql -uroot -p${mysqlPass} -sNe "GRANT ALL ON ${dbName_hetzner2}.* TO '${dbSuperUser_hetzner2}'@'localhost' IDENTIFIED BY '${dbSuperPass_hetzner2}'; FLUSH PRIVILEGES;"
 time nice mysql -uroot -p${mysqlPass} -sNe "GRANT SELECT, INSERT, UPDATE, DELETE ON ${dbName_hetzner2}.* TO '${dbUser_hetzner2}'@'localhost' IDENTIFIED BY '${dbPass_hetzner2}'; FLUSH PRIVILEGES;"

# STEP 3: Add vhost files
mv ${vhostDir_hetzner2}/* ${backupDir_hetzner2}/old/
time nice tar -xzvf ${backupFileName_files_hetzner1}

# set ['Database'] Name/User/Password (use dbUser/dbPass, not dbSuperUser/dbSuperPass)
# add logic to block IE6 so we can safely remove the XSS bugfix 28235 .htaccess that breaks css
# set `$wgTmpDirectory = "/var/lib/php/tmp_upload"`
# set `$wgLogo = "/images/ose-logo.png"`
vim "${vhostDir_hetzner2}/LocalSettings.php"

# download mediawiki core source code (note this must be done instead of using
# git since [a] git does not include the vendor dir contents and [b] we cannot
# use Composer since it would require breaking our hardened php.ini config

# first, do some string analysis to determine the file, version, and branch name
mwSourceFile=`basename "${newMediawikiSourceUrl}"`
mwReleaseName=`echo "${mwSourceFile}" | awk -F'mediawiki-' '{print $2}' | awk -F. '{print "REL" $1 "_" $2}'`
mwExtractedDir="`echo $mwSourceFile | awk -F'.tar.gz' '{print $1}'`"

wget "${newMediawikiSourceUrl}"
tar -xzvf "${mwSourceFile}"
mkdir "${docrootDir_hetzner2}"
rsync -av --progress "${mwExtractedDir}/" "${docrootDir_hetzner2}/"

# mediawiki ships with lots of calls to unsafe php functions that we've
# intentionally disabled in our hardened php.ini config. They're not necessary
# and just flood our log files with warnings; so let's just comment them out now

find "${docrootDir_hetzner2}/includes/" -type f -exec sed -i 's%^\(\s*\)ini_set\(.*\)%\1#ini_set\2%' '{}' \;
find "${docrootDir_hetzner2}/includes/" -type f -exec sed -i 's%^\(\s*\)putenv\(.*\)%\1#putenv\2%' '{}' \;

# copy-in our images from backups
rsync -av --progress "usr/www/users/osemain/w/images/" "${docrootDir_hetzner2}/images/"

# and move the lone image sticking in root into the images directory
rsync -av --progress "usr/www/users/osemain/w/ose-logo.png" "${docrootDir_hetzner2}/images/"

# create LocalSettings.php that just requires the file from outside the docroot
# write multi-line to file for documentation copy & paste
cat << EOF > "${docrootDir_hetzner2}/LocalSettings.php"
<?php
# including separate file that contains the database password so that it is not stored within the document root.
# For more info see:
#  * https://www.mediawiki.org/wiki/Manual:Security
#  * https://wiki.r00tedvw.com/index.php/Mediawiki/Hardening

\$docRoot = dirname( __FILE__ );
require_once "\$docRoot/../LocalSettings.php";
?>
EOF

# extensions
pushd "${docrootDir_hetzner2}/extensions"
git clone -b "${mwReleaseName}" https://gerrit.wikimedia.org/r/p/mediawiki/extensions/CategoryTree.git
git clone -b "${mwReleaseName}" https://gerrit.wikimedia.org/r/p/mediawiki/extensions/ConfirmAccount.git
git clone -b "${mwReleaseName}" https://gerrit.wikimedia.org/r/p/mediawiki/extensions/ConfirmEdit.git
git clone -b "${mwReleaseName}" https://gerrit.wikimedia.org/r/p/mediawiki/extensions/Cite.git
git clone -b "${mwReleaseName}" https://gerrit.wikimedia.org/r/p/mediawiki/extensions/ParserFunctions.git
git clone -b "${mwReleaseName}" https://gerrit.wikimedia.org/r/p/mediawiki/extensions/Gadgets.git
git clone -b "${mwReleaseName}" https://gerrit.wikimedia.org/r/p/mediawiki/extensions/ReplaceText.git
git clone -b "${mwReleaseName}" https://gerrit.wikimedia.org/r/p/mediawiki/extensions/Renameuser.git
git clone -b "${mwReleaseName}" https://gerrit.wikimedia.org/r/p/mediawiki/extensions/UserMerge.git
git clone -b "${mwReleaseName}" https://gerrit.wikimedia.org/r/p/mediawiki/extensions/Nuke.git
git clone -b "${mwReleaseName}" https://gerrit.wikimedia.org/r/p/mediawiki/extensions/OATHAuth.git

git clone -b "${mwReleaseName}" https://gerrit.wikimedia.org/r/p/mediawiki/extensions/Widgets.git
pushd Widgets
git submodule init
git submodule update
popd

# skins
pushd "${docrootDir_hetzner2}/skins"
git clone https://gerrit.wikimedia.org/r/p/mediawiki/skins/CologneBlue.git
git clone https://gerrit.wikimedia.org/r/p/mediawiki/skins/Modern.git
git clone https://gerrit.wikimedia.org/r/p/mediawiki/skins/MonoBook.git
git clone https://gerrit.wikimedia.org/r/p/mediawiki/skins/Vector.git
popd

# set permissions
chown -R not-apache:apache "${vhostDir_hetzner2}"
find "${vhostDir_hetzner2}" -type d -exec chmod 0050 {} \;
find "${vhostDir_hetzner2}" -type f -exec chmod 0040 {} \;

chown not-apache:apache-admins "${vhostDir_hetzner2}/LocalSettings.php"
chmod 0040 "${vhostDir_hetzner2}/LocalSettings.php"

[ -d "${docrootDir_hetzner2}/images" ] || mkdir "${docrootDir_hetzner2}/images"
chown -R apache:apache "${docrootDir_hetzner2}/images"
find "${docrootDir_hetzner2}/images" -type f -exec chmod 0660 {} \;
find "${docrootDir_hetzner2}/images" -type d -exec chmod 0770 {} \;

[ -d "${vhostDir_hetzner2}/cache" ] || mkdir "${vhostDir_hetzner2}/cache"
chown -R apache:apache "${vhostDir_hetzner2}/cache"
find "${vhostDir_hetzner2}/cache" -type f -exec chmod 0660 {} \;
find "${vhostDir_hetzner2}/cache" -type d -exec chmod 0770 {} \;

# remove the block that attempts to fix bug 28235, as it breaks css
vim "${docrootDir_hetzner2}/.htaccess"

# attempt to update
pushd ${docrootDir_hetzner2}/maintenance
php update.php --dbuser "${dbSuperUser_hetzner2}" --dbpass "${dbSuperPass_hetzner2}"

popd

After the steps above are finished, proceed with the validation following the test plan at Wiki Validation

See Also

1. Mediawiki
2. Wiki Validation