Revision as of 18:34, 23 October 2017

Sun Oct 22, 2017

tested demo of LibreOffice Online
added Google docs alternative article
issues with LibreOffice Online atm
1. no hyperlink support (filed bug for this = https://bugs.documentfoundation.org/show_bug.cgi?id=113361)
2. can't "draw" arrows & shapes — must type them or upload an image

Sat Oct 21, 2017

the error of ip address destination totally went away after a few days. I have the logs to prove it actually happened, but I can't explain how it's fixed now. Maybe something on hetzner's end *shrug*
further LibreOffice Online & CODE research
discovered that there's essentially no discount for the smallest subnet of internet ip addresses offered by heztner. 1 IP is €0.84. A '/29' for 6 addresses is €6.72. That's actually €1.68 _more_ than just getting 6 individual ip addresses. Perhaps that's because they're consecutive. Hmm.
discovered that the PROXY support was added to varnish version 4.1. The version installed by CentOS7 is 4.0.5. That explains why the strange character issue related to 'write-proxy-v2' from last week occured
1. in any case, the default hitch config file from centos7 repos was to have this enabled. If the centos7 repos install a version of varnish that doesn't support this, it's broken ootb.
decided to ditch hitch for nginx. I really didn't want to go down this rabbit hole unless necessary, but I'm afraid it is necessary because
1. hitch is just too simple. For example, it can't redirect 80->443. Instead, we would have varnish listen on port 80. But then varnish doesn't have wildcard redirects, so every new domain would require varnish changes. If we use nginx, we can have it listen on port 443 & 80 with dynamic redirects that are much more powerful without needing changes as often.
2. there's very little documentation & logging available for hitch
3. hitch requires a silly process of concatinating pem files into a hitch-specific pem file. that complicates the letsencrypt cert update process in a way that may break in the future.
4. many more varnish users terminate ssl before varnish using nginx than use hitch. at least, there's more blog posts describing this with nginx than with hitch.
5. Using nginx will also allow us to "return 444" on the first process getting requests, which is something that apache & hitch don't support. Varnish might; not sure.
6. Using nginx will also provide us with far better DOS protection
7. Using nginx will also allow us to do a simple redirection of a site to a "sitedown" page without requiring to modify the apache vhost backend. Then we can make our changes to the vhost backend, test, then switch the "sitedown" in nginx back to varnish. Though not a huge benefit, it's cool that we won't have to do any "sitedown" config changes in apache.
  1. Of course, since it's all on one host, we can't do any fancy "site down" redirection like a load balancer with failed health codes. Or if our 1 server goes down, all sites necessary go down. But that's a budget bottleneck.
8. in the interest of time, I'm going to leave apache as the backend. So we'll have nginx -> varnish -> apache. This is silly, but it works. I guess it would be OK to replace the apache backends with distinct/localhost-only/http-only nginx backend vhosts for varnish if someone wants to do this in the future.
  1. I'm sure many people will wonder why I didn't just use nginx's caching. it's not a bad thought, but Mediawiki integrates better with Varnish. And our wiki is our biggest site, so aligning with Wikipedia's stack makes sense. Indeed, Wikipedia uses Varnish (they used to use nginx & squid caching, but found Varinsh to be better).
    1. https://en.wikipedia.org/wiki/Wikipedia:FAQ/Technica[[1]]
    2. https://meta.wikimedia.org/wiki/Wikimedia_servers
    3. Wikipedia also uses HHVM to speed up php; we'll investigate this in the future, if needed
updated /etc/httpd/conf/httpd.conf so apache doesn't listen on port 80 on all interfaces anymore. Added 'Listen 138.201.84.223:80' so apache only listens on the obi-specific ip address. This will eventually be commented-out after we're done testing staging on .243 (nginx will listen on 80 instead & apache will become 127.0.0.1 only).
hardened nginx
added dos protection https://www.nginx.com/blog/mitigating-ddos-attacks-with-nginx-and-nginx-plus/
nginx started successfully && redirects 80->https successfully using the super-robust lines

   # redirect all port 80 requests to use https
   server {
	  ...
	  # force all traffic to use https
	  server_name ~^(?<subdomain>[^\.]+)\.(?<domain>.+)$;
	  return 301 https://$subdomain.$domain$request_uri;
   }


}

discovered that many things are still not working on the staging-specific site since wordpress is now not changing the links due to previously removing the below actions

#remove_action( 'template_redirect', 'redirect_canonical' );

commenting out yielded an infinte loop, causing nginx to block me per my anti-dos config above

Tue Oct 17, 2017

for some reason, hitch ignored my "host" declaration in the frontend block; it would stubbornly listen on all interfaces/ip addresses

frontend = {
	pem-file = "/etc/letsencrypt/live/openbuildinginstitute.org/hitch-bundle.pem"
	host = "138.201.84.243"
	port = "4444"
}

but it worked fine with this one-liner

frontend = "[138.201.84.243]:4444+/etc/letsencrypt/live/openbuildinginstitute.org/hitch-bundle.pem"

changed hitch config to run on unused ip address on port 443

frontend = "[138.201.84.243]:443+/etc/letsencrypt/live/openbuildinginstitute.org/hitch-bundle.pem"

updated the dns entry "staging.openbuildingininstitute.org" to '138.201.84.243'
discovered that a curl of the newly-updated dns entry (which was also hard-coded in my /etc/hosts) was hitting the server at the old ip address of .223
began to question the sanity of the networking scripts configuration, which are currently:

[root@hetzner2 iptables]# cat /etc/sysconfig/network-scripts/ifcfg-eth0
### Hetzner Online GmbH - installimage
#
# Note for customers who want to create bridged networking for virtualisation:
# Gateway is set in separate file
# Do not forget to change interface in file route-eth0 and rename this file
#
# device: eth0
DEVICE=eth0
BOOTPROTO=none
ONBOOT=yes
HWADDR=90:1B:0E:94:07:C4
IPADDR=138.201.84.223
IPADDR1=138.201.84.243
NETMASK=255.255.255.255
SCOPE="peer 138.201.84.193"
IPV6INIT=yes
IPV6ADDR=2a01:4f8:172:209e::2/64
IPV6_DEFAULTGW=fe80::1
IPV6_DEFAULTDEV=eth0
[root@hetzner2 iptables]# ip r
default via 138.201.84.193 dev eth0
138.201.0.0/16 dev eth0 proto kernel scope link src 138.201.84.243
138.201.84.193 dev eth0 proto kernel scope link src 138.201.84.223
169.254.0.0/16 dev eth0 scope link metric 1002
[root@hetzner2 iptables]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
	link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
	inet 127.0.0.1/8 scope host lo
	   valid_lft forever preferred_lft forever
	inet6 ::1/128 scope host
	   valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
	link/ether 90:1b:0e:94:07:c4 brd ff:ff:ff:ff:ff:ff
	inet 138.201.84.223 peer 138.201.84.193/32 brd 138.201.84.223 scope global eth0
	   valid_lft forever preferred_lft forever
	inet 138.201.84.223/32 scope global eth0
	   valid_lft forever preferred_lft forever
	inet 138.201.84.243/16 scope global eth0
	   valid_lft forever preferred_lft forever
	inet 138.201.84.243 peer 138.201.84.193/32 brd 138.201.255.255 scope global secondary eth0
	   valid_lft forever preferred_lft forever
	inet6 2a01:4f8:172:209e::2/64 scope global
	   valid_lft forever preferred_lft forever
	inet6 fe80::921b:eff:fe94:7c4/64 scope link
	   valid_lft forever preferred_lft forever
[root@hetzner2 iptables]#

found this hetzner doc describing the quirks of our server's network config & how we should configure it to work https://wiki.hetzner.de/index.php/Netzkonfiguration_CentOS/en#Additional_IP_addresses_.28Host.29
manually deleted the "/16" address, attempted to re-add using "/32"

[root@hetzner2 iptables]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
	link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
	inet 127.0.0.1/8 scope host lo
	   valid_lft forever preferred_lft forever
	inet6 ::1/128 scope host
	   valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
	link/ether 90:1b:0e:94:07:c4 brd ff:ff:ff:ff:ff:ff
	inet 138.201.84.223 peer 138.201.84.193/32 brd 138.201.84.223 scope global eth0
	   valid_lft forever preferred_lft forever
	inet 138.201.84.223/32 scope global eth0
	   valid_lft forever preferred_lft forever
	inet 138.201.84.243 peer 138.201.84.193/32 brd 138.201.255.255 scope global secondary eth0
	   valid_lft forever preferred_lft forever
	inet6 2a01:4f8:172:209e::2/64 scope global
	   valid_lft forever preferred_lft forever
	inet6 fe80::921b:eff:fe94:7c4/64 scope link
	   valid_lft forever preferred_lft forever
[root@hetzner2 iptables]# ip addres del  138.201.84.243 dev eth0
Warning: Executing wildcard deletion to stay compatible with old scripts.
		 Explicitly specify the prefix length (138.201.84.243/32) to avoid this warning.
		 This special behaviour is likely to disappear in further releases,
		 fix your scripts!
[root@hetzner2 iptables]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
	link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
	inet 127.0.0.1/8 scope host lo
	   valid_lft forever preferred_lft forever
	inet6 ::1/128 scope host
	   valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
	link/ether 90:1b:0e:94:07:c4 brd ff:ff:ff:ff:ff:ff
	inet 138.201.84.223 peer 138.201.84.193/32 brd 138.201.84.223 scope global eth0
	   valid_lft forever preferred_lft forever
	inet 138.201.84.223/32 scope global eth0
	   valid_lft forever preferred_lft forever
	inet6 2a01:4f8:172:209e::2/64 scope global
	   valid_lft forever preferred_lft forever
	inet6 fe80::921b:eff:fe94:7c4/64 scope link
	   valid_lft forever preferred_lft forever
[root@hetzner2 iptables]# ip addr add 138.201.84.243/32 dev eth0
[root@hetzner2 iptables]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
	link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
	inet 127.0.0.1/8 scope host lo
	   valid_lft forever preferred_lft forever
	inet6 ::1/128 scope host
	   valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
	link/ether 90:1b:0e:94:07:c4 brd ff:ff:ff:ff:ff:ff
	inet 138.201.84.223 peer 138.201.84.193/32 brd 138.201.84.223 scope global eth0
	   valid_lft forever preferred_lft forever
	inet 138.201.84.223/32 scope global eth0
	   valid_lft forever preferred_lft forever
	inet 138.201.84.243/32 scope global eth0
	   valid_lft forever preferred_lft forever
	inet6 2a01:4f8:172:209e::2/64 scope global
	   valid_lft forever preferred_lft forever
	inet6 fe80::921b:eff:fe94:7c4/64 scope link
	   valid_lft forever preferred_lft forever
[root@hetzner2 iptables]#

^ that looks better, and tcpdump successfully shows icmp pings coming into '.243' when pinging 'staging.openbuildinginstitute.org' from my workstation

[root@hetzner2 iptables]# tcpdump -n icmp
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 262144 bytes
19:20:32.072121 IP 12.227.214.121 > 138.201.84.243: ICMP echo request, id 44748, seq 1, length 64
19:20:32.072134 IP 138.201.84.243 > 12.227.214.121: ICMP echo reply, id 44748, seq 1, length 64
19:20:33.069612 IP 12.227.214.121 > 138.201.84.243: ICMP echo request, id 44748, seq 2, length 64
19:20:33.069620 IP 138.201.84.243 > 12.227.214.121: ICMP echo reply, id 44748, seq 2, length 64
19:20:34.075004 IP 12.227.214.121 > 138.201.84.243: ICMP echo request, id 44748, seq 3, length 64
19:20:34.075011 IP 138.201.84.243 > 12.227.214.121: ICMP echo reply, id 44748, seq 3, length 64
^C
6 packets captured
6 packets received by filter
0 packets dropped by kernel
[root@hetzner2 iptables]#

but then when I attempt to curl the same domain name on port 443, the server only sees my calls coming in to ip '.223'!

# on my workstation; same terminal that ran the ping

$ curl -i https://staging.openbuildinginstitute.org
HTTP/1.1 301 Moved Permanently
Date: Tue, 17 Oct 2017 19:29:46 GMT
Server: Apache
Strict-Transport-Security: max-age=15552001
Location: https://openbuildinginstitute.org/
X-XSS-Protection: 1; mode=block
Public-Key-Pins: pin-sha256="UbSbHFsFhuCrSv9GNsqnGv4CbaVh5UV5/zzgjLgHh9c="; pin-sha256="YLh1dUR9y6Kja30RrAn7JKnbQG/uEtLMkBgFF2Fuihg="; pin-sha256="C5+lpZ7tcVwmwQIMcRtPbsQtWLABXhQzejna0wHFr8M="; pin-sha256="Vjs8r4z+80wjNcr1YKepWQboSIRi63WsWXhIMN+eWys="; pin-sha256="lCppFqbkrlJ3EcVFAkeip0+44VaoJUymbnOaEUk7tEU="; pin-sha256="K87oWBWM9UZfyddvDfoxL+8lpNyoUB2ptGtn0fv6G2Q="; pin-sha256="Y9mvm0exBk1JoQ57f9Vm28jKo5lFm/woKcVxrYxu80o="; pin-sha256="EGn6R6CqT4z3ERscrqNl7q7RCzJmDe9uBhS/rnCHU="; pin-sha256="NIdnza073SiyuN1TUa7DDGjOxc1p0nbfOCfbxPWAZGQ="; pin-sha256="fNZ8JI9p2D/C+bsB3LH3rWejY9BGBDeW0JhMOiMfa7A="; pin-sha256="oyD01TTXvpfBro3QSZc1vIlcMjrdLTiL/M9mLCPX+Zo="; pin-sha256="0cRTd+vc1hjNFlHcLgLCHXUeWqn80bNDH/bs9qMTSPo="; pin-sha256="MDhNnV1cmaPdDDONbiVionUHH2QIf2aHJwq/lshMWfA="; pin-sha256="OIZP7FgTBf7hUpWHIA7OaPVO2WrsGzTl9vdOHLPZmJU="; max-age=3600; includeSubDomains; report-uri="http:opensourceecology.org/hpkp-report"
Content-Length: 0
Content-Type: text/html; charset=UTF-8

# on hetzner2

[root@hetzner2 iptables]# tcpdump dst port 443 -v -n -s0 -X
tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 262144 bytes
19:28:02.624459 IP (tos 0x0, ttl 56, id 31438, offset 0, flags [none], proto TCP (6), length 60)
	38.68.203.22.63096 > 138.201.84.223.https: Flags [S], cksum 0xfbc5 (correct), seq 4141488768, win 65535, options [mss 1460,sackOK,TS val 88041328 ecr 0,wscale 6,eol], length 0
		0x0000:  4500 003c 7ace 0000 3806 36eb 2644 cb16  E..<z...8.6.&D..
		0x0010:  8ac9 54df f678 01bb f6da 1a80 0000 0000  ..T..x..........
		0x0020:  a002 ffff fbc5 0000 0204 05b4 0402 080a  ................
		0x0030:  053f 6770 0000 0000 0303 0600            .?gp........
19:28:02.722739 IP (tos 0x0, ttl 56, id 43492, offset 0, flags [none], proto TCP (6), length 52)
	38.68.203.22.63096 > 138.201.84.223.https: Flags [.], cksum 0xbd98 (correct), ack 4014171832, win 4107, options [nop,nop,TS val 88041426 ecr 709746987], length 0
		0x0000:  4500 0034 a9e4 0000 3806 07dd 2644 cb16  E..4....8...&D..
		0x0010:  8ac9 54df f678 01bb f6da 1a81 ef43 66b8  ..T..x.......Cf.
		0x0020:  8010 100b bd98 0000 0101 080a 053f 67d2  .............?g.
		0x0030:  2a4d e12b                                *M.+
19:28:02.723191 IP (tos 0x0, ttl 56, id 43498, offset 0, flags [none], proto TCP (6), length 341)
	38.68.203.22.63096 > 138.201.84.223.https: Flags [P.], cksum 0x8270 (correct), seq 0:289, ack 1, win 4107, options [nop,nop,TS val 88041426 ecr 709746987], length 289
		0x0000:  4500 0155 a9ea 0000 3806 06b6 2644 cb16  E..U....8...&D..
		0x0010:  8ac9 54df f678 01bb f6da 1a81 ef43 66b8  ..T..x.......Cf.
		0x0020:  8018 100b 8270 0000 0101 080a 053f 67d2  .....p.......?g.
		0x0030:  2a4d e12b 1603 0101 1c01 0001 1803 0359  *M.+...........Y
		0x0040:  e659 b567 973a 3611 c70b e101 aead 596f  .Y.g.:6.......Yo
		0x0050:  393f 895b 6bc9 953d 2163 66d6 cbb9 cc00  9?.[k..=!cf.....
		0x0060:  006c c02b c02c c086 c087 c009 c023 c00a  .l.+.,.......#..
		0x0070:  c024 c072 c073 c0ac c0ad c008 c02f c030  .$.r.s......./.0
		0x0080:  c08a c08b c013 c027 c014 c028 c076 c077  .......'...(.v.w
		0x0090:  c012 009c 009d c07a c07b 002f 003c 0035  .......z.{./.<.5
		0x00a0:  003d 0041 00ba 0084 00c0 c09c c09d 000a  .=.A............
		0x00b0:  009e 009f c07c c07d 0033 0067 0039 006b  .....|.}.3.g.9.k
		0x00c0:  0045 00be 0088 00c4 c09e c09f 0016 0100  .E..............
		0x00d0:  0083 0017 0000 0016 0000 0005 0005 0100  ................
		0x00e0:  0000 0000 0000 2600 2400 0021 7374 6167  ......&.$..!stag
		0x00f0:  696e 672e 6f70 656e 6275 696c 6469 6e67  ing.openbuilding
		0x0100:  696e 7374 6974 7574 652e 6f72 67ff 0100  institute.org...
		0x0110:  0100 0023 0000 000a 000c 000a 0017 0018  ...#............
		0x0120:  0019 0015 0013 000b 0002 0100 000d 0016  ................
		0x0130:  0014 0401 0403 0501 0503 0601 0603 0301  ................
		0x0140:  0303 0201 0203 0010 000b 0009 0868 7474  .............htt
		0x0150:  702f 312e 31                             p/1.1

Mon Oct 16, 2017

logging hours
email hetzner follow-up on outage
owncloud vs airos research

Fri Oct 13, 2017

Marcin reported timouts attempting to load the wiki
confirmed that heztzner's status page reported no current issues
confirmed issues with curl & browser to both servers, which suggests a large hetzner-side issue
was unable to ssh into the servers at first, but then got on; it's flapping
opened a support ticket for hetzner to investigate
1. I can't really do much from our non-root shared hosting server to investigate the issue; all I see is ~50% cpu wait..no idea if that's normal or not

Wed Oct 11, 2017

added documentation on Wordpress page on how to create a staging clone of a wordpress site from the production site
created a new staging clone of obi for testing obi behind hitch/varnish
hit an issue where wordpress wants to 301 redirect hits from varnish to https, since varnish hits it on http. I went to change the apache backend back to https, but then found that the free version of varnish actually doesn't support https backends. Only Varnish Plus does
1. requested a trial of Varnish Plus
2. requested a quote from Varnish asking if they offer Varnish Cashe Plus free to nonprofits
3. got an email back from Daniel Jacobs at Varnish. replied && cc'd Marcin
4. Daniel responded stating that non profits do not get Varnish Cache Plus for free
5. stopped wordpress redirects by adding this to the end of wp-config.php

# prevent redirecting varnish to https (which the free version can't do)
remove_action( 'template_redirect', 'redirect_canonical' );

wordpress finally loaded (rather than just retrun an infinate loop of 301s until mod_evasive 403'd us), but much of the content was broken as the site referenced http, not https links to itself, which now don't work through hitch -> varnish -> apache. ie: http://staging.openbuildinginstitute.org:4444/wp-content/plugins/be-page-builder/js/opt_plugins/jquery.justifiedGallery.min.js?ver=4.8.1

Tue Oct 10, 2017

spent a few hours debugging hitch. I could query the http-only & localhost-only apache backend on port 8000 via curl. I could query the http-only & localhost-only varnish backend on port 6081 using curl. But when I queried the https public backend on port 4444, it failed.
1. In firefox, the error was "Secure Connection Failed / The connection to the server was reset while the page was loading. / The page you are trying to view cannot be shown because the authenticity of the received data could not be verified. / Please contact the website owners to inform them of this problem. with only the "Try Again" button & a link to this article: https://support.mozilla.org/en-US/kb/what-does-your-connection-is-not-secure-mean
2. In curl, I kept getting "Empty reply from server" as the response

$ curl -vi "https://staging.openbuildinginstitute.org:4444/"
* Trying 138.201.84.223...
* Connected to staging.openbuildinginstitute.org (138.201.84.223) port 4444 (#0)
* found 148 certificates in /etc/ssl/certs/ca-certificates.crt
* found 597 certificates in /etc/ssl/certs
* ALPN, offering http/1.1
* SSL connection using TLS1.2 / ECDHE_RSA_AES_128_GCM_SHA256
* server certificate verification OK
* server certificate status verification SKIPPED
* common name: openbuildinginstitute.org (matched)
* server certificate expiration date OK
* server certificate activation date OK
* certificate public key: RSA
* certificate version: #3
* subject: CN=openbuildinginstitute.org
* start date: Sun, 08 Oct 2017 13:29:42 GMT
* expire date: Sat, 06 Jan 2018 13:29:42 GMT
* issuer: C=US,O=Let's Encrypt,CN=Let's Encrypt Authority X3
* compression: NULL
* ALPN, server did not agree to a protocol
> GET / HTTP/1.1
> Host: staging.openbuildinginstitute.org:4444
> User-Agent: curl/7.47.0
> Accept: */*
> 
* Empty reply from server
* Connection #0 to host staging.openbuildinginstitute.org left intact
curl: (52) Empty reply from server

1. I confirmed with ss & tcpdump that the connections were going through as expected
2. eventually, after determining the issue was between hitch -> varnish, I killed varnish & replaced it's daemon with netcat to see exactly what hitch was sending to varnish different from me querying varnish directly. I found it passes some strange unprintable characters, followed by "QUIT" before the actual GET request

# tty1
[root@hetzner2 ~]# service varnish stop
Redirecting to /bin/systemctl stop varnish.service
[root@hetzner2 ~]# nc -l 6081

# tty2
[root@hetzner2 ~]# curl -vi "https://staging.openbuildinginstitute.org:4444/index.html"
* About to connect() to staging.openbuildinginstitute.org port 4444 (#0)
* Trying 127.0.0.1...
* Connected to staging.openbuildinginstitute.org (127.0.0.1) port 4444 (#0)
* Initializing NSS with certpath: sql:/etc/pki/nssdb
* CAfile: /etc/pki/tls/certs/ca-bundle.crt
  CApath: none
* SSL connection using TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
* Server certificate:
* subject: CN=openbuildinginstitute.org
* start date: Oct 08 13:29:42 2017 GMT
* expire date: Jan 06 13:29:42 2018 GMT
* common name: openbuildinginstitute.org
* issuer: CN=Let's Encrypt Authority X3,O=Let's Encrypt,C=US
> GET /index.html HTTP/1.1
> User-Agent: curl/7.29.0
> Host: staging.openbuildinginstitute.org:4444
> Accept: */*
>
* Empty reply from server
* Connection #0 to host staging.openbuildinginstitute.org left intact
curl: (52) Empty reply from server
[root@hetzner2 ~]#

# back in tty1, we see
[root@hetzner2 ~]# nc -l 6081



QUIT
!\GET /index.html HTTP/1.1
User-Agent: curl/7.29.0
Host: staging.openbuildinginstitute.org:4444
Accept: */*

# compared to when we hit the would-be varnish directly with curl (not going through hitch) back on tty2
[root@hetzner2 ~]# curl -vi "http://staging.openbuildinginstitute.org:6081/index.html"
* About to connect() to staging.openbuildinginstitute.org port 6081 (#0)
* Trying 127.0.0.1...
* Connected to staging.openbuildinginstitute.org (127.0.0.1) port 6081 (#0)
> GET /index.html HTTP/1.1
> User-Agent: curl/7.29.0
> Host: staging.openbuildinginstitute.org:6081
> Accept: */*
>

# and back on tty1 we see
[root@hetzner2 ~]# nc -l 6081
GET /index.html HTTP/1.1
User-Agent: curl/7.29.0
Host: staging.openbuildinginstitute.org:6081
Accept: */*

1. passing the netcat output through od, we can see the byte encoding of the unprintable characters when we go through hitch

[root@hetzner2 ~]# nc -l 6081 | od -c
0000000  \r  \n  \r  \n  \0  \r  \n   Q   U   I   T  \n   ! 021  \0  \f
0000020 177  \0  \0 001 177  \0  \0 001 212 030 021   \   G   E   T
0000040   /   i   n   d   e   x   .   h   t   m   l       H   T   T   P
0000060   /   1   .   1  \r  \n   U   s   e   r   -   A   g   e   n   t
0000100   :       c   u   r   l   /   7   .   2   9   .   0  \r  \n   H
0000120   o   s   t   :       s   t   a   g   i   n   g   .   o   p   e
0000140   n   b   u   i   l   d   i   n   g   i   n   s   t   i   t   u
0000160   t   e   .   o   r   g   :   4   4   4   4  \r  \n   A   c   c

1. googling this string sequence lead me to this hitch.c source code file https://github.com/varnish/hitch/blob/master/src/hitch.c#L1736
2. this "memcpy(&p->sig,"\r\n\r\n\0\r\nQUIT\n", 12);" line was in the context "static void write_proxy_v2(...){"
3. sure enough, the defaults pre-loaded in /etc/hitch/hitch.conf had "write-proxy-v2 = on" set. When I set this to "off", hitch passthrough worked as expected!!

Mon Oct 09, 2017

rebooted hetzner2
1. server came back in a reasonable time with mysql & apache running ,but port 443 was blocked by iptables. I updated the rules to open incoming ports on 443, 4443, & 4444 & saved the rules with `service iptables save`
2. verified that 'https://openbuildinginstitute.org/' was accessible back
3. our new ip address (138.201.84.243) still wasn't in use, though
4. updated /etc/sysconfig/network-scripts/ifcfg-eth0 by adding a line defining "IPADDR1=138.201.84.243"
5. restarted networking with `systemctl restart network`
6. that worked; after several seconds, both IP addresses were responding to pings
7. rebooted again for good measure. the server's reboot time (between no pings & ping again) was 53 seconds

$ ping -D 138.201.84.223
PING 138.201.84.223 (138.201.84.223) 56(84) bytes of data.
[1507558391.925006] 64 bytes from 138.201.84.223: icmp_seq=1 ttl=45 time=134 ms
[1507558392.921355] 64 bytes from 138.201.84.223: icmp_seq=2 ttl=45 time=131 ms
[1507558393.921015] 64 bytes from 138.201.84.223: icmp_seq=3 ttl=45 time=131 ms
[1507558394.920918] 64 bytes from 138.201.84.223: icmp_seq=4 ttl=45 time=132 ms
[1507558395.919804] 64 bytes from 138.201.84.223: icmp_seq=5 ttl=45 time=131 ms
[1507558396.920338] 64 bytes from 138.201.84.223: icmp_seq=6 ttl=45 time=132 ms
[1507558449.934733] 64 bytes from 138.201.84.223: icmp_seq=59 ttl=45 time=131 ms
[1507558450.934509] 64 bytes from 138.201.84.223: icmp_seq=60 ttl=45 time=132 ms
[1507558451.933150] 64 bytes from 138.201.84.223: icmp_seq=61 ttl=45 time=131 ms
[1507558452.933126] 64 bytes from 138.201.84.223: icmp_seq=62 ttl=45 time=131 ms
[1507558453.932302] 64 bytes from 138.201.84.223: icmp_seq=63 ttl=45 time=131 ms
[1507558454.932066] 64 bytes from 138.201.84.223: icmp_seq=64 ttl=45 time=131 ms
[1507558455.933211] 64 bytes from 138.201.84.223: icmp_seq=65 ttl=45 time=132 ms
^C
--- 138.201.84.223 ping statistics ---
65 packets transmitted, 13 received, 80% packet loss, time 64010ms
rtt min/avg/max/mdev = 131.270/132.035/134.839/0.963 ms
guttersnipe@guttersnipe:~$

1. confirmed obi was accessible this time with no manual intervention
fixed awstats cron (it was creating the files in the vhost dir, not the docroot dir!

[root@hetzner2 awstats]# cat /etc/cron.d/awstats_generate_static_files                                                 06 * * * * root /bin/nice /usr/share/awstats/tools/awstats_updateall.pl -configdir=/etc/awstats/ now && /bin/nice /usr/share/awstats/tools/awstats_buildstaticpages.pl -config=openbuildinginstitute.org -dir=/var/www/html/awstats.openbuildinginstitute.org/htdocs/

found a guide to get varnish to log (by default it doesn't at all) + with vhost-specific log files https://www.garron.me/en/linux/install-awstats-for-varnish-varnishncsa-logs-ubuntu-linux.html https://serverfault.com/questions/790267/varnishncsa-for-varnish-4-split-log-by-vhost

varnishncsa -q "ReqHeader ~ '^Host: staging.openbuildinginstitute.org'" -a -w /var/log/varnish/staging.openbuildinginstitute.org -D

queries to hitch kept returning "Empy reply from server".

$ curl -I "https://staging.openbuildinginstitute.org:4444"
curl: (52) Empty reply from server

1. confirmed that the local varnish config works (well, gives a 404, but that's all I want from hitch to return at this point

[root@hetzner2 ~]# curl "http://staging.openbuildinginstitute.org:6081"
<!DOCTYPE HTML PUBLIC "-IETFDTD HTML 2.0//EN">
<html><head>
<title>404 Not Found</title>
</head><body>
<h1>Not Found</h1>
<p>The requested URL / was not found on this server.</p>
</body></html>
[root@hetzner2 ~]#

1. Hitch documentation is poor, so I couldn't find a hitch-specific log file. But I found this bullet-point = 'Shared memory log as in Varnish. (hitchlog, hitchstat)" which lead me to using `varnishlog` while attempting the query from my workstation again, and this popped-up https://staging.openbuildinginstitute.org:4444/

[root@hetzner2 ~]# varnishlog
* << Session  >> 32808
-   Begin          sess 0 HTTP/1
-   SessOpen       127.0.0.1 38916 127.0.0.1:6081 127.0.0.1 6081 1507576757.414453 12
-   SessClose      RX_TIMEOUT 2.001
-   End

Sun Oct 08, 2017

renewed let's encrypt certificate with only 2 SANs = www + staging
updated vhost config to point staging to varnish testing webroot
configured varnish & hitch for letsencrypt per this guide https://docs.varnish-software.com/tutorials/hitch-letsencrypt/
1. created file /etc/varnish/letsencrypt.vcl && included it in /etc/varnish/default.vcl

backend certbot {
	.host = "127.0.0.1";
	.port = "888";
}

sub vcl_recv {
	if (req.url ~ "^/.well-known/acme-challenge/.*") {
		set req.backend_hint = certbot;
		return(pass);
	}
}
[root@hetzner2 varnish]# cat /etc/varnish/default.vcl
#
# This is an example VCL file for Varnish.
#
# It does not do anything by default, delegating control to the
# builtin VCL. The builtin VCL is called when there is no explicit
# return statement.
#
# See the VCL chapters in the Users Guide at https://www.varnish-cache.org/docs/
# and http://varnish-cache.org/trac/wiki/VCLExamples for more examples.

# Marker to tell the VCL compiler that this VCL has been adapted to the
# new 4.0 format.
vcl 4.0;

include "/etc/varnish/letsencrypt.vcl";

# Default backend definition. Set this to point to your content server.
backend default {
#    .host = "127.0.0.1";
	.host = "varnishtest";
	.port = "80";
}

sub vcl_recv {
	# Happens before we check if we have this in cache already.
	#
	# Typically you clean up the request here, removing cookies you don't need,
	# rewriting the request, etc.
}

sub vcl_backend_response {
	# Happens after we have read the response headers from the backend.
	#
	# Here you clean the response headers, removing silly Set-Cookie headers
	# and other mistakes your backend does.
}

sub vcl_deliver {
	# Happens when we have all the pieces we need, and are about to send the
	# response to the client.
	#
	# You can do accounting or modifying the final object here.
}

1. updated /etc/varnish/varnish.params to bind only on 127.0.0.1 (we want hitch to be the only public-facing service here, if possible)
2. increased VARNISH_STORAGE to 40G (from the default of 256M) since we're only actually using 1G out of our 62 gigs of ram!

[root@hetzner2 varnish]# cat varnish.params
# Varnish environment configuration description. This was derived from
# the old style sysconfig/defaults settings

# Set this to 1 to make systemd reload try to switch VCL without restart.
RELOAD_VCL=1

# Main configuration file. You probably want to change it.
VARNISH_VCL_CONF=/etc/varnish/default.vcl

# Default address and port to bind to. Blank address means all IPv4
# and IPv6 interfaces, otherwise specify a host name, an IPv4 dotted
# quad, or an IPv6 address in brackets.
VARNISH_LISTEN_ADDRESS=127.0.0.1
VARNISH_LISTEN_PORT=6081

# Admin interface listen address and port
VARNISH_ADMIN_LISTEN_ADDRESS=127.0.0.1
VARNISH_ADMIN_LISTEN_PORT=6082

# Shared secret file for admin interface
VARNISH_SECRET_FILE=/etc/varnish/secret

# Backend storage specification, see Storage Types in the varnishd(5)
# man page for details.
VARNISH_STORAGE="malloc,40G"

# User and group for the varnishd worker processes
VARNISH_USER=varnish
VARNISH_GROUP=varnish

# Other options, see the man page varnishd(1)
#DAEMON_OPTS="-p thread_pool_min=5 -p thread_pool_max=500 -p thread_pool_timeout=300"

installed 'hitch' on hetzner2 via yum for ssl termination for varnish
1. configured hitch to listen on port 444 (for initial testing)

unfortunately, there's very, very little info on how to harden hitch. here's some config options I should further research:
1. https://stackoverflow.com/questions/46101087/hitch-solution-of-varnish-doesnt-work

frontend = "[*]:443"
backend = "[127.0.0.1]:80"
pem-file = "/etc/hitch/xxxxxxxxxxxxxx.pem"
ciphers = "EECDH+AESGCM:EDH+AESGCM:AES256+EECDH:AES256+EDH"
prefer-server-ciphers = off
ssl-engine = ""
workers = 1
backlog = 100
keepalive = 3600
chroot = ""
user = "hitch"
group = "hitch"
quiet = off
syslog = on
syslog-facility = "daemon"
daemon = on
write-ip = off
write-proxy-v1 = on
write-proxy-v2 = off
proxy-proxy = off
sni-nomatch-abort = off

created hitch-specific pem file

[root@hetzner2 hitch]# cd /etc/letsencrypt/live/openbuildinginstitute.org/
[root@hetzner2 openbuildinginstitute.org]# ls
cert.pem  chain.pem  fullchain.pem  privkey.pem  README
[root@hetzner2 openbuildinginstitute.org]# cat privkey.pem fullchain.pem /etc/pki/dhparam/dhparam.pem > hitch-bundle.pem
[root@hetzner2 openbuildinginstitute.org]# ls -lah hitch-bundle.pem
-rw-r--r-- 1 root root 5.9K Oct  8 15:54 hitch-bundle.pem
[root@hetzner2 openbuildinginstitute.org]# chmod 0400 hitch-bundle.pem
[root@hetzner2 openbuildinginstitute.org]# ls -lah hitch-bundle.pem
-r-------- 1 root root 5.9K Oct  8 15:54 hitch-bundle.pem

opened port 4444 on firewall

Sat Oct 07, 2017

deleted all piwik web files & vhost files
dropped databses: obi2_db, obi3_db, osemain, osewiki, piwik_obi_db, wordpress, test
fixed some obi http/https "mixed content warning" issues
1. changed oshin theme's logo settings, which were linking to brandexponents' domain's images, causing http/https "mixed content warning"s in he browser. I simply downloaded the files we were using from their site & uploaded it to our site, using the wp wui @ Appearence -> Customize -> Logo -> {Light Logo, Logo on Sidebar}
2. attempted to replace all instances of 'http://openbuildinginstitute.org' with 'https://openbuildinginstitute.org', but this broke the theme
  1. and the error_log filled with mesages like:

==> /var/log/httpd/openbuildinginstitute.org/error_log <==
[Sat Oct 07 16:20:07.202628 2017] [:error] [pid 21883] [client 184.170.79.118:54974] PHP Warning:  array_key_exists() expects parameter 2 to be array, null given in /var/www/html/openbuildinginstitute.org/htdocs/wp-content/themes/oshin/functions/be-styles-functions.php on line 93
[Sat Oct 07 16:20:07.202677 2017] [:error] [pid 21883] [client 184.170.79.118:54974] PHP Warning:  array_key_exists() expects parameter 2 to be array, null given in /var/www/html/openbuildinginstitute.org/htdocs/wp-content/themes/oshin/functions/be-styles-functions.php on line 95

1. 1. attempted to replace only images ending in .png or .jpg, but this still broke the theme. Only doing jpg was fine, but apparently the theme depends on 'http' linked PNGs.

dbName=obi_db
dbUser=obi_user
 dbPass=CHANGEME
 rootDbPass=CHANGEME

fromString=http://openbuildinginstitute.org
toString=https://openbuildinginstitute.org

stamp=`date +%Y%m%d_%T`
tmpDir=/var/tmp/dbChange.$stamp
mkdir $tmpDir
chown root:root $tmpDir
chmod 0700 $tmpDir
pushd $tmpDir
service httpd stop

# create backup of everything for good measure
 time nice mysqldump -uroot -p$rootDbPass --all-databases | gzip -c > preBackup.all_databases.$stamp.sql.gz

# dump obi wordpress db contents
 time nice mysqldump -u$dbUser -p$dbPass --database $dbName > $dbName.$stamp.sql

# make backup
cp $dbName.$stamp.sql $dbName.$stamp.sql.orig

# sed
#sed -i "s%$fromString\([^\"]*\.png\)%$toString\1%g" $dbName.$stamp.sql
sed -i "s%$fromString\([^\"]*\.jpg\)%$toString\1%g" $dbName.$stamp.sql

# verify
grep "$fromString" $dbName.$stamp.sql | less
grep "$toString" obi_db.$stamp.sql | less

# delete db tables
 mysql -u$dbUser -p$dbPass $dbName -sNe 'show tables' | while read table; do mysql -u$dbUser -p$dbPass -sNe "DROP TABLE $dbName.$table;"; done

# verify
 mysql -u$dbUser -p$dbPass $dbName -sNe 'show tables' 

# import
 mysql -u$dbUser -p$dbPass < obi_db.$stamp.sql

# verify
 mysql -u$dbUser -p$dbPass $dbName -sNe 'show tables' 
service httpd start

1. did another pass replacing all occurences of 'http://openbuildinginstitute.org' with 'https://openbuildinginstitute.org', but only on the wp_posts table. this worked, but there appears to still be 'http://openbuildinginstitute.org' entries in tables = wp_options & wp_postmeta
created subdomain 'staging.openbuildinginstitue.org' & deleted the piwik, obi2, obi3, & obi4 subdomains

Tue Oct 03, 2017

added documenation to [[Wordpress] about our new plugin stack
added documentation to 2FA on our two factor solution & how to recover if your phone is lost per converstation with Marcin
ordered an additional ip address for https on separate domain, same port = opensourceecology.org. this costs ~10 EUR per year, and will be paid for by OBI per our conversation on 2017-07-11
1. hetzner confirmed we have a new ip address = 138.201.84.243 (in addition to the original = 138.201.84.223)
2. TODO: reboot the server _later_ (ie: *not* before I'm about to hop on a bus for 24 hours..)
researched wordpress plugins for varnish, found two on the varnish-cache.org website https://varnish-cache.org/extras/index.html
1. Varnish HTTP Purge by Mika Epstein https://wordpress.org/plugins/varnish-http-purge/
  1. + actively developed
  2. + 50,000+ active installs
  3. + 4.5/5 star rating, but only 16 reviewers
  4. + author works for DreamHost and the plugin page states that this plugin is installed for *all* DreamPress installs
  5. + integrates into wp-cli
  6. This definitely seems like the winner
2. Varnish Caching by Razvan Stanga https://wordpress.org/plugins/vcaching/
  1. + actively developed
installed varnish

yum install varnish

Mon Oct 02, 2017

updated awstats static content manually (this time in docroot)

/usr/share/awstats/tools/awstats_buildstaticpages.pl -config=openbuildinginstitute.org -dir=/var/www/html/awstats.openbuildinginstitute.org/

confirmed icons are working
enabled indexes for the obi awstats docroot dir in the vhost config
moved the centos 'welcome.conf' dir to be disabled to allow indexes
updated obi vhost config to use domain-specific log files

<VirtualHost openbuildinginstitute.org:443>
...
		CustomLog "/var/log/httpd/openbuildinginstitute.org/access_log" combined
		ErrorLog "/var/log/httpd/openbuildinginstitute.org/error_log"
...
</VirtualHost>

updated the awstats config file with the new domain-specific log files above to /etc/awstats.openbuildinginstitute.org.conf

...
LogFile="/var/log/httpd/openbuildinginstitute.org/access_log"
...

found awstats data dir to be: /var/lib/awstats
added new log file path to existing httpd lograte && added the awstats data collection here as "prerotate" step

[root@hetzner2 htdocs]# cat /etc/logrotate.d/httpd
/var/log/httpd/*log /var/log/httpd/openbuildinginstitute.org/*log {
	missingok
	notifempty
	sharedscripts
	delaycompress
	compress
	prerotate
		/bin/nice /usr/share/awstats/tools/awstats_updateall.pl now
	postrotate
		/bin/systemctl reload httpd.service > /dev/null 2>/dev/null || true
	endscript
}

added cron to collected data && generate static files every hour from the data

[root@hetzner2 ~]# cat /etc/cron.d/awstats_generate_static_files
01 * * * * root /bin/nice /usr/share/awstats/tools/awstats_updateall.pl now && /bin/nice /usr/share/awstats/tools/awstats_buildstaticpages.pl -config=openbuildinginstitute.org -dir=/var/www/html/awstats.openbuildinginstitute.org/
[root@hetzner2 ~]#

Fri Sep 29, 2017

updated links section of OSE Server
installed 'wonderm00ns-simple-facebook-open-graph-tags' plugin for obi's wordpress

sudo -u wp -i wp --path=/var/www/html/openbuildinginstitute.org/htdocs plugin install --activate 'wonderm00ns-simple-facebook-open-graph-tags'

configured plugin with groups url, app id (was used before but I think this was a misconfig), type=blog, & defined image
disabled plugin "OA Open Graph for FB"

sudo -u wp -i wp --path=/var/www/html/openbuildinginstitute.org/htdocs plugin deactivate 'oa-open-graph-for-fb'

confirmed that the ALTER TABLE entries are no longer spamming into the /var/log/httpd/error_log file
1. the above change should significantly reduce the size of the error_log files
2. the httpd logs are rotated daily & gzipped after rotation. each of the following sizes are therefore *after* compression
3. access logs for the past month are ~300-600 KB
4. error logs for the past month are 500 KB - 4 MB. They grew to MB ranges only after Sep 22nd, which is when I made CHG-2017-09-25
  1. an outlier: there is one log file that's 30M (!) = /var/log/httpd/error_log-20170926.gz. There *is* a jump on the access long on this day too, perhaps it's just because there were an insane number of errors thrown for each query.
5. modsec_audit logs range from 2-200 KB for the month
6. ssl_error_logs range from 100 bytes - 1 kb
added subdomain 'awstats' to openbuildinginstitute.org
updated letsencrypt certificate with the awstats altname
added script to renew letsencrypt certificate (it expires necessarily every 90-days)

[root@hetzner2 ~]# ls -lah /root/bin/letsencrypt/renew.sh
-rwx------ 1 root root 124 Sep 30 21:39 /root/bin/letsencrypt/renew.sh
[root@hetzner2 letsencrypt]# cat /root/bin/letsencrypt/renew.sh
#!/bin/bash

/bin/certbot renew
/bin/chmod 0400 /etc/letsencrypt/archive/*/pri*
service httpd reload

# exit cleanly
exit 0

added /etc/cron.d/letsencrypt to call above script

[root@hetzner2 cron.d]# cat /etc/cron.d/letsencrypt
# once a month, update our letsencrypt cert
20 4 13 * * root /root/bin/letsencrypt/renew.sh &>> /var/log/letsEncryptRenew.log

added symlink from /var/www/html/awstats.openbuildinginstitute.org/htdocs/awstatsicons to /usr/share/awstats/wwwroot/icon

Thr Sep 28, 2017

changed apache log level back to 'warn' from 'debug'
discovered the ALTER TABLE error log spammig is coming from the wp plugin = oa-open-graph-for-fb
1. plugin is 2 years old & not very popular
2. found alternative with what seems like the same functionality, but much more popular & actively maintained = wonderm00ns-simple-facebook-open-graph-tags
3. send email to Catarina asking if I could swap them)
OSSEC was still sending bruteforce attempt alerts, because attackers were using http, which would return a 301 rather than a 403. Fixed with

<VirtualHost 138.201.84.223:80>
	ServerName openbuildinginstitute.org
	ServerAlias www.openbuildinginstitute.org

	# block access to 'wp-login.php' from brute-forcers; see wp plugin 'rename-wp-login'
	<LocationMatch .*wp-login.php>
		Deny From All
	</LocationMatch>
	<Location />
		Redirect permanent / https://openbuildinginstitute.org/
	</Location>
</VirtualHost>

Mon Sep 25, 2017

in lieu of a proper ticket tracking system, created CHG-2017-09-25 for the first major obi production change
1. successfully completed change. looks good, except for a few "mixed content warnings" from the browser. some are a result of the theme, and are not trivial to fix, but don't prevent the image from loading. others the browser refuse to load, such as the 3Dmodels. Asked Catarina to s/http/https for the latter.
I'm considering abandoning Piwik for many reasons. First, I'm going to try an awstats POC

yum install awstats
cd /etc/awstats
cp awstats.localhost.localdomain.conf awstats.openbuildinginstitute.org.conf
vim awstats.openbuildinginstitute.org

1. set 'SiteDomain="openbuildinginstitute.org"'
2. set 'HostAliases="www.openbuildinginstitute.org 138.201.84.223"'
3. set 'DNSLookups=0'
4. set 'AllowAccessFromWebToAuthenticatedUsersOnly=1'
5. set 'AllowAccessFromWebToFollowingAuthenticatedUsers="admin"'
6. discovered icons in '/usr/share/awstats/wwwroot/icon/'
7. created configs for awstats.openbuildinginstitute.org

mkdir -p /var/www/html/awstats.openbuildinginstitute.org
/usr/share/awstats/tools/awstats_buildstaticpages.pl -config=openbuildinginstitute.org -dir=/var/www/html/awstats.openbuildinginstitute.org/

1. successfully loaded files in browser, but it's not navigable https://piwik.openbuildinginstitute.org:4443/awstats.openbuildinginstitute.org.html

Sat Sep 22, 2017

discovered that the "ALTER TABLE error, which gets spammed to /var/log/httpd/error_log, appears to have been fixed in wordpress core v4.6 https://core.trac.wordpress.org/ticket/20263#comment:15
1. this will be fixed when we upgrade obi next week from v4.5.9 to v4.8.1

did a `yum update`
added yum-cron & configured it to update the server automatically for security-releated package updates

yum install yum-cron

# add the following 2 lines to /etc/yum/yum-cron.conf
#update_cmd = minimal-security
#apply_updates = yes
vim /etc/yum/yum-cron.conf
sudo systemctl status yum-cron
sudo systemctl enable yum-cron
service yum-cron start

spent time reading the piwik howto guide https://piwik.org/faq/how-to/
spent time digging through the piwik settings
added better geoip tracking (by default it only supports best-guess based on the language)

mkdir -p /var/tmp/geoip
cd /var/tmp/geoip
wget http://geolite.maxmind.com/download/geoip/database/GeoLiteCity.dat.gz
gzip -d GeoLiteCity.dat.gz
cp GeoLiteCity.dat "${piwikDocroot}/misc/"
chown apache:apache "${piwikDocroot}/misc/GeoLiteCity.dat"
chmod 0640 "${piwikDocroot}/misc/GeoLiteCity.dat"

discovered that geoip is slower than pecl, which is preferred if you can install packages, so installed this preferred method

yum install php56w-pecl-geoip

interestingly, 'https://piwik.openbuildinginstitute.org:4443' entirely started timing-out after this install
1. confirmed that openbuildinginstitute.org is not impacted
2. restarted apache, syntax was fine, no errors, & still an issue
3. tried clearing tmp files per the troubleshooting guide https://piwik.org/faq/troubleshooting/
4. tried removing the package, and got some interesting messages

[root@hetzner2 htdocs]# yum remove php56w-pecl-geoip
...
  Erasing    : php56w-pecl-geoip-1.1.1-1.w7.x86_64                                                                                            1/1
PHP Warning:  ini_set() has been disabled for security reasons in /usr/share/pear/peclcmd.php on line 24
PHP Warning:  require_once(): open_basedir restriction in effect. File(/usr/share/pear/pearcmd.php) is not within the allowed path(s): (/home/wp/.wp-cli:/var/lib/php/tmp_upload:/var/lib/php/session:/var/www/html/obi/:/var/www/html/obi2/:/var/www/html/obi3:/var/www/html/osemain:/var/www/html/piwik.openbuildinginstitute.org/) in /usr/share/pear/peclcmd.php on line 31
PHP Warning:  require_once(/usr/share/pear/pearcmd.php): failed to open stream: Operation not permitted in /usr/share/pear/peclcmd.php on line 31
PHP Fatal error:  require_once(): Failed opening required 'pearcmd.php' (include_path='/usr/share/pear') in /usr/share/pear/peclcmd.php on line 31
  Verifying  : php56w-pecl-geoip-1.1.1-1.w7.x86_64                                                                                            1/1
...

1. confirmed with tcpdump that the server was getting the traffic still

[root@hetzner2 ~]# tcpdump -f host piwik.openbuildinginstitute.org and port 4443 -s0 -X
...

1. tried adding /usr/share/pear to the basedir, but there were still errors
2. tried adding DEBUG file [log] section to config/config.ini.php, but nothing showed up in tmp/ anyway

[log]
; possible values for log: screen, database, file
log_writers[] = file

; log level, everything logged w/ this level or one of greater severity
; will be logged. everything else will be ignored. possible values are:
; ERROR, WARN, INFO, DEBUG
log_level = DEBUG

; if configured to log in a file, log entries will be made to this file
logger_file_path = tmp/logs/piwik.log

1. deleted misc/GeoLiteCity.dat
2. increased /etc/http/conf/httpd.conf log level to debug
3. changed the vhost to http & I now got basic auth again
  1. tmp/logs is now populated, though with nothing useful.

==> tmp/logs/piwik.log <==
DEBUG Piwik\Profiler[2017-09-22 18:03:36 UTC] [53603] <hr /><strong>SQL Profiler</strong><hr /><strong>Summary</strong><br/>Executed 1 queries in 0 seconds(Average query length: 0 seconds)<br />Queries per second: 2074.3<br />Longest query length: 0 seconds (<code>connect</code>)
DEBUG Piwik\Profiler[2017-09-22 18:03:36 UTC] [53603] <hr /><strong>Breakdown by query</strong><br/>Executed <b>1</b> time in <b>0.5ms</b>  connect
DEBUG Piwik\Profiler[2017-09-22 18:03:36 UTC] [53603] Total queries = 1 (total sql time = 0.00s)

1. saw that it was trying to redirect me to 'https://<ip_address>', so I tried removing the 'force_ssl = 1' config. That gave me the login page successfully over http, so I switched back to https in the vhost with the dumb (vhost ignorant) redirect still absent.
2. it's still timing-out, and it's unclear why; I'll have to address this issue next week..

Mon Sep 18, 2017

timeout issues appear to be related to my VPN usage. I blame cloudfront & hope it will go away after we deprecate CF. VPNs (skateboards, whistleblowing, etc) are not crimes!
1. confirmed that the 2x cryptostorm useast VPN IPs are *not* in the CF fireswall logs. Maybe it's a mediawiki thing on our end? Or hetzner's. If it's an issue on hetzner2 after migration, I'll debug more there (where we actually have root so I can tcpdump, check system logs, etc).
realized that my ossec emails were sent to spam by Google
1. added SPF record that allows messages from google, hetzner1, hetzner2, and any other addresses that are resolved by our dns's A or MX records with SOFTFAIL for all else

v=spf1 a mx include:_spf.google.com ip4:78.46.3.178 ip4:138.201.84.223 ~all

deleted existing piwik data from mariadb
1. confirmed that existing 'piwik' db is empty
2. confirmed that `DROP USER 'X'@'Y';` also deletes all assocated privliges from the 'mysql.db' table too

DROP USER 'piwik'@'localhost';
DROP DATABASE piwik;

cleanup of an old permission I added by accident

REVOKE ALL PRIVILEGES ON databasename.* FROM 'obi_user'@'localhost';
FLUSH PRIVILEGES;

created new db & user that's obi-specific (we'll have distinct piwik sites for the different orgs)

CREATE DATABASE piwik_obi_db;
GRANT ALL PRIVILEGES ON piwik_obi_db.* TO "piwik_obi_user"@"localhost" IDENTIFIED BY "thisShouldBeARandomlyGeneratedPasswordOfExactly70Characters";
FLUSH PRIVILEGES;

set 'always_populate_raw_post_data' to '-1' in /etc/php.ini because piwik requires it, and it's safe to do. -1 sets it to the post-php-v7.0.0 behaviour = $HTTP_RAW_POST_DATA is not set at all.
followed WUI with these entries

For "Database Server" enter 'localhost' For "Login" enter 'piwik_obi_user' For "Password" enter the randomly generated 70-character db user password generated above & added to keepass For "Database Name" enter 'piwik_obi_db' For "Table Prefix" enter 'piwik_obi_' Leave "Adapter" at the default

Click "Next"

For "Super user login" enter 'insecure' to make it clear that we don't trust this application-level authorization Genereate a long, random, unique password, and enter it into the "Password" fields

Click "Next"

Got a JS script to put in sites we want to track; it says to put it just before </head>

<!-- Piwik -->
<script type="text/javascript">
  var _paq = _paq || [];
  /* tracker methods like "setCustomDimension" should be called before "trackPageView" */
  _paq.push(['trackPageView']);
  _paq.push(['enableLinkTracking']);
  (function() {
	var u="//138.201.84.223/";
	_paq.push(['setTrackerUrl', u+'piwik.php']);
	_paq.push(['setSiteId', '1']);
	var d=document, g=d.createElement('script'), s=d.getElementsByTagName('script')[0];
	g.type='text/javascript'; g.async=true; g.defer=true; g.src=u+'piwik.js'; s.parentNode.insertBefore(g,s);
  })();
</script>
<!-- End Piwik Code -->

discovered that Piwik respects browser's Do Not Track & will automatically anonymize the last byte of IP Addresses before storing it to our logs; that's cool ☺
discovered that Piwik has an "image tracking" option for users who don't have JS. brilliant!
had issues with ossec active response banning me when attempting to click around in piwik due to "High amount of POST requests in a small period of time (likely bot)"
1. added local rule to exclude this active reponse from log entries, just for piwik's index.php page--which is already locked behind basic auth

  <rule id="100050" level="0">                                                
	<if_sid>31533</if_sid>                                                    
	<match>https://piwik.openbuildinginstitute.org:4443/index.php</match>     
	<description>it is normal piwik behaviour to spam POST requests; this prevents an active response ban of admins interacting with the piwik wui</description>                                                                          
  </rule>

discovered first-hand that the "Do not track" policy *is* respected; I spent a lot of time scratching my head trying to figure out why piwik's js & image trackers were loading successfully, but not being added to the dashboard--before realizing that my browser, of course, is configured with "do not track" enabled
1. also, "private browsing" & "incognito mode" generally enable this; which is annoying since I use them to clear cache for testing. beware.
2. found the best way to test this: ephemeral firefox profiles running in a firejail sandbox!

firejail firefox -profile `mktemp -d` -no-remote -new-instance

discovered that sometimes piwik's behaviour is also denied (on a pre request basis, rather than banning with iptables/hosts.deny) by DOS preventer mod_evasive
1. mod_evasive does *not* allow per-vhost or per-dir/file exceptions, probably because it must drop the request before any processing, as is expected from a tool to thwart DOS attacks https://wiki.atomicorp.com/wiki/index.php/Mod_evasive

Sat Sep 16, 2017

stripped-out ini_set requirements

perl -pi -e ' BEGIN{undef $/;} s%(.*)if(.*)function_exists(.*)ini_set[^}]*}%${1}\n/*****************************************************************************\n * disabling ini_set detction\n * it was intentionally disabled for security! -maltfield\n******************************************************************************\nif${2}function_exists${3}ini_set${4}\n*/%smg' /var/www/html/piwik.openbuildinginstitute.org/htdocs/core/testMinimumPhpVersion.php

fixed permissions

vhostDir="/var/www/html/piwik.openbuildinginstitute.org/"
piwikDocroot="${vhostDir}/htdocs"

chown -R apache:apache "${vhostDir}"
find "${vhostDir}" -type d -exec chmod 0750 {} \;
find "${vhostDir}" -type f -exec chmod 0640 {} \;
chown apache:apache-admins "${piwikDocroot}/config/config.ini.php"
chmod 0440 "${piwikDocroot}/config/config.ini.php"

began documenting the Piwik install process http://opensourceecology.org/wiki/Piwik
hit issue when writing Piwik wiki page where the wiki would just timeout; this was not reproducable with my personal page
discovered that, despite the warnings on the documentation page, our debug logs are in the docroot x_x
wiki debug log file was stuck at ~2G & not growing. I moved it aside & touched the file, and new data came screaming in.

Fri Sep 15, 2017

When Chris & I were digging, we found lots of evidence of sec incompetence from Piwik (requiring ini_set & marking requests to fix as "won't fix", getting sec audits & *not* publishing the results). This is usually a no-go, but since there's no better alternative && it's not necessary to make it public, we'll lock it behind a non-standard port && behind htpasswd (in addition to untrustwrothy application-level
1. https://forum.piwik.org/t/ini-set-in-piwik/801
switched piwik.openbuildinginstitue.org back to port 4443 (from 443, which was just done for testing ssl hardning with qualy's ssllabs)
created htpasswd file for obi's piwik

cd /var/www/html/piwik.openbuildinginstitute.org
htpasswd -c -B .htpasswd admin
chown apache:apache .htpasswd
chmod 0400 .htpasswd

removed the line "Require all granted" && changed AllowOverride to None && added basic auth config to /etc/httpd/conf.d/piwik.openbuildinginstitute.org

	# we don't trust piwik due to obvious sec incompetence in the project, so we
	# restrict the whole site with basic auth *in addition* to the untrusted
	# applicaiton-level auth                                                                                                                
   AuthType Basic                                                                                                                          
   AuthName "Authentication Required"                                                                                                      
   AuthUserFile /var/www/html/piwik.openbuildinginstitute.org/.htpasswd                                                                    
   Require valid-user

unzipped latest pwik files to /var/www/html/piwik.openbuildinginstitute.org/htdocs/
added '/var/www/html/piwik.openbuildinginstitute.org/' to php.ini basedir

Wed Sep 13, 2017

Catarina had issues authing with 2FA on obi3
1. disabled obi3 & sent her updated instructions to validate the accuracy of her phone's cock & to enable "relaxed mode" which will accept codes +/- 4 min from the current time

Tue Sep 12, 2017

Catarina finished validation of content on obi3
discovered that EDH isn't listed in our cipher suite, and therefore the quest to validate the dhparams is moot. We use ECDH.
1. "We have three recommendations for correctly deploying Diffie-Hellman for TLS: ... 2. Deploy (Ephemeral) Elliptic-Curve Diffie-Hellman (ECDHE). Elliptic-Curve Diffie-Hellman (ECDH) key exchange avoids all known feasible cryptanalytic attacks, and modern web browsers now prefer ECDHE over the original, finite field, Diffie-Hellman. The discrete log algorithms we used to attack standard Diffie-Hellman groups do not gain as strong of an advantage from precomputation, and individual servers do not need to generate unique elliptic curves." -source: https://weakdh.org/sysadmin.html
2. if we have client issues that force us to enable EDH, then I will further investigate this issue

Mon Sep 11, 2017

emailed with marcin for d3d
emailed with Catarina for obi3 vaidation update
created DH key & added to apache config

mkdir -p /etc/pki/dhparam
chown apache:apache /etc/pki/dhparam
chmod 0500 /etc/pki/dhparam
cd /etc/pki/dhparam
openssl dhparam -out dhparam.pem 4096
chown apache:apache /etc/pki/dhparam/dhparam.pem
chmod 0400 /etc/pki/dhparam/dhparam.pem

attempted to add the dh key to apache config in /etc/httpd/conf.d/ssl.conf & /etc/httpd/conf.d/ssl.openbuildinginstitute.org
1. discovered that the "SSLOpenSSLConfCmd" directive wasn't added until apache v2.4.7, but cent7 is pinned to 2.4.6. The solution is to append the dhparams file to the certificate, which should be done with the letsencrypt cron renewal
  1. https://weakdh.org/sysadmin.html
  2. https://serverfault.com/questions/698093/invalid-command-sslopensslconfcmd-perhaps-misspelled-or-defined-by-a-module-n

cat /etc/pki/dhparam/dhparam.pem >> /etc/letsencrypt/live/openbuildinginstitute.org/cert.pem
cat /etc/pki/dhparam/dhparam.pem >> /etc/letsencrypt/live/openbuildinginstitute.org/fullchain.pem

1. could not validate the config; created StackOverflow question https://stackoverflow.com/questions/46164547/how-to-validate-dhparams-in-apache-2-4-6

Mon Sep 04, 2017

generated obi lets encrypt certificate; these certbot args *might* need to change after we redirect all http -> https

yum install python-certbot-apache
certbot certonly -v --webroot -w /var/www/html/obi/htdocs/ -d openbuildinginstitute.org -d www.openbuildinginstitute.org -d piwik.openbuildinginstitute.org
chmod 0400 /etc/letsencrypt/archive/*/pri*

1. a cron job should be added to run `certbot renew` before the current cert expires in 4 months on 2017-12-03
apache https hardening
1. first tested default configs with the qualys ssllabs.com test. got "C" due to bad ciphers, lack of PFS support, POODLE & RC4 attack vuln, & incomplete cert chain.
2. used mozilla's ssl-config-generator with "Apache v2.4.6" + "Modern" + "Openssl v1.0.1e" https://mozilla.github.io/server-side-tls/ssl-config-generator/

<VirtualHost *:443>
	...
	SSLEngine on
	SSLCertificateFile      /path/to/signed_certificate
	SSLCertificateChainFile /path/to/intermediate_certificate
	SSLCertificateKeyFile   /path/to/private/key

	# Uncomment the following directive when using client certificate authentication
	#SSLCACertificateFile    /path/to/ca_certs_for_client_authentication


	# HSTS (mod_headers is required) (15768000 seconds = 6 months)
	Header always set Strict-Transport-Security "max-age=15768000"
	...
</VirtualHost>

# modern configuration, tweak to your needs
SSLProtocol             all -SSLv3 -TLSv1 -TLSv1.1
SSLCipherSuite          ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA256
SSLHonorCipherOrder     on
SSLCompression          off


# OCSP Stapling, only in httpd 2.3.3 and later
SSLUseStapling          on
SSLStaplingResponderTimeout 5
SSLStaplingReturnResponderErrors off
SSLStaplingCache        shmcb:/var/run/ocsp(128000)

1. determined SPKI public key hashes to use in HPKP per best practices guide https://community.letsencrypt.org/t/hpkp-best-practices-if-you-choose-to-implement/4625
2. determined SPKI public key hashes for alternatie CA StartCom (which also issues free certs)
3. decided *not* to pin SPKI public key hashes for alternate free CA CACert, which uses an md5 signature on their root ca, and has failed to fix it since this bug was issued in 2014 http://bugs.cacert.org/view.php?id=1305
4. decided *not* to pin key hashes for StartCom, or wosign since it was detrusted by google, apple, & mozilla https://security.googleblog.com/2016/10/distrusting-wosign-and-startcom.html
5. decided to pin key hashes for root certs provided by trusted CAs with free 90-day certs or free certs = cloudflare (free) and comodo & ssl.com (both 90 days free)
  1. cloudflare itself requires pinning digicert, addtrust, globalsign, and gtecybertrust (now digicert)
  2. comodo's root cert documentation is a clusterfuck that requires javascript & a gui, and there's a ton of them. decided to abandon this; we already picked-up a few CAs just from cloudflare

[root@hetzner2 ~]# cd /etc/letsencrypt/live/openbuildinginstitute.org/
[root@hetzner2 openbuildinginstitute.org]# openssl x509 -in cert.pem -pubkey | openssl pkey -pubin -outform der | openssl dgst -sha256 -binary | base64
UbSbHFsFhuCrSv9GNsqnGv4CbaVh5UV5/zzgjLgHh9c=
[root@hetzner2 openbuildinginstitute.org]# openssl x509 -in chain.pem -pubkey | openssl pkey -pubin -outform der | openssl dgst -sha256 -binary | base64
YLh1dUR9y6Kja30RrAn7JKnbQG/uEtLMkBgFF2Fuihg=

[root@hetzner2 ~]# mkdir /var/tmp/letsencrypt
[root@hetzner2 ~]# cd /var/tmp/letsencrypt/
[root@hetzner2 letsencrypt]# wget --quiet https://letsencrypt.org/certs/isrgrootx1.pem
[root@hetzner2 letsencrypt]# cat isrgrootx1.pem 
-----BEGIN CERTIFICATE-----
MIIFazCCA1OgAwIBAgIRAIIQz7DSQONZRGPgu2OCiwAwDQYJKoZIhvcNAQELBQAw
TzELMAkGA1UEBhMCVVMxKTAnBgNVBAoTIEludGVybmV0IFNlY3VyaXR5IFJlc2Vh
cmNoIEdyb3VwMRUwEwYDVQQDEwxJU1JHIFJvb3QgWDEwHhcNMTUwNjA0MTEwNDM4
WhcNMzUwNjA0MTEwNDM4WjBPMQswCQYDVQQGEwJVUzEpMCcGA1UEChMgSW50ZXJu
ZXQgU2VjdXJpdHkgUmVzZWFyY2ggR3JvdXAxFTATBgNVBAMTDElTUkcgUm9vdCBY
MTCCAiIwDQYJKoZIhvcNAQEBBQADggIPADCCAgoCggIBAK3oJHP0FDfzm54rVygc
h77ct984kIxuPOZXoHj3dcKi/vVqbvYATyjb3miGbESTtrFj/RQSa78f0uoxmyF+
0TM8ukj13Xnfs7j/EvEhmkvBioZxaUpmZmyPfjxwv60pIgbz5MDmgK7iS4+3mX6U
A5/TR5d8mUgjU+g4rk8Kb4Mu0UlXjIB0ttov0DiNewNwIRt18jA8+o+u3dpjq+sW
T8KOEUt+zwvo/7V3LvSye0rgTBIlDHCNAymg4VMk7BPZ7hm/ELNKjD+Jo2FR3qyH
B5T0Y3HsLuJvW5iB4YlcNHlsdu87kGJ55tukmi8mxdAQ4Q7e2RCOFvu396j3x+UC
B5iPNgiV5+I3lg02dZ77DnKxHZu8A/lJBdiB3QW0KtZB6awBdpUKD9jf1b0SHzUv
KBds0pjBqAlkd25HN7rOrFleaJ1/ctaJxQZBKT5ZPt0m9STJEadao0xAH0ahmbWn
OlFuhjuefXKnEgV4We0+UXgVCwOPjdAvBbI+e0ocS3MFEvzG6uBQE3xDk3SzynTn
jh8BCNAw1FtxNrQHusEwMFxIt4I7mKZ9YIqioymCzLq9gwQbooMDQaHWBfEbwrbw
qHyGO0aoSCqI3Haadr8faqU9GY/rOPNk3sgrDQoo//fb4hVC1CLQJ13hef4Y53CI
rU7m2Ys6xt0nUW7/vGT1M0NPAgMBAAGjQjBAMA4GA1UdDwEB/wQEAwIBBjAPBgNV
HRMBAf8EBTADAQH/MB0GA1UdDgQWBBR5tFnme7bl5AFzgAiIyBpY9umbbjANBgkq
hkiG9w0BAQsFAAOCAgEAVR9YqbyyqFDQDLHYGmkgJykIrGF1XIpu+ILlaS/V9lZL
ubhzEFnTIZd+50xx+7LSYK05qAvqFyFWhfFQDlnrzuBZ6brJFe+GnY+EgPbk6ZGQ
3BebYhtF8GaV0nxvwuo77x/Py9auJ/GpsMiu/X1+mvoiBOv/2X/qkSsisRcOj/KK
NFtY2PwByVS5uCbMiogziUwthDyC3+6WVwW6LLv3xLfHTjuCvjHIInNzktHCgKQ5
ORAzI4JMPJ+GslWYHb4phowim57iaztXOoJwTdwJx4nLCgdNbOhdjsnvzqvHu7Ur
TkXWStAmzOVyyghqpZXjFaH3pO3JLF+l+/+sKAIuvtd7u+Nxe5AW0wdeRlN8NwdC
jNPElpzVmbUq4JUagEiuTDkHzsxHpFKVK7q4+63SM1N95R1NbdWhscdCb+ZAJzVc
oyi3B43njTOQ5yOf+1CceWxG1bQVs5ZufpsMljq4Ui0/1lvh+wjChP4kqKOJ2qxq
4RgqsahDYVvTH9w7jXbyLeiNdd8XM2w9U/t7y0Ff/9yi0GE44Za4rF2LN9d11TPA
mRGunUHBcnWEvgJBQl9nJEiU0Zsnvgc/ubhPgXRR4Xq37Z0j4r7g1SgEEzwxA57d
emyPxgcYxn/eR44/KJ4EBs+lVDR3veyJm+kXQ99b21/+jh5Xos1AnX5iItreGCc=
-----END CERTIFICATE-----
[root@hetzner2 letsencrypt]# openssl x509 -in isrgrootx1.pem -pubkey | openssl pkey -pubin -outform der | openssl dgst -sha256 -binary | base64
C5+lpZ7tcVwmwQIMcRtPbsQtWLABXhQzejna0wHFr8M=

# copied & pasted IdenTrust's DST Root CA X3 Certificate from here https://www.identrust.com/certificates/trustid/root-download-x3.html
# added '-----BEGIN CERTIFICATE-----' to top of file and
# added '-----END CERTIFICATE-----' to end of file
# per https://www.c-rieger.de/http-public-key-pinning-nginx-and-nextcloud/
[root@hetzner2 tmp]# vim /var/tmp/identrust.dst.root.x3.pem
[root@hetzner2 tmp]# cat /var/tmp/identrust.dst.root.x3.pem
-----BEGIN CERTIFICATE-----
MIIDSjCCAjKgAwIBAgIQRK+wgNajJ7qJMDmGLvhAazANBgkqhkiG9w0BAQUFADA/
MSQwIgYDVQQKExtEaWdpdGFsIFNpZ25hdHVyZSBUcnVzdCBDby4xFzAVBgNVBAMT
DkRTVCBSb290IENBIFgzMB4XDTAwMDkzMDIxMTIxOVoXDTIxMDkzMDE0MDExNVow
PzEkMCIGA1UEChMbRGlnaXRhbCBTaWduYXR1cmUgVHJ1c3QgQ28uMRcwFQYDVQQD
Ew5EU1QgUm9vdCBDQSBYMzCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEB
AN+v6ZdQCINXtMxiZfaQguzH0yxrMMpb7NnDfcdAwRgUi+DoM3ZJKuM/IUmTrE4O
rz5Iy2Xu/NMhD2XSKtkyj4zl93ewEnu1lcCJo6m67XMuegwGMoOifooUMM0RoOEq
OLl5CjH9UL2AZd+3UWODyOKIYepLYYHsUmu5ouJLGiifSKOeDNoJjj4XLh7dIN9b
xiqKqy69cK3FCxolkHRyxXtqqzTWMIn/5WgTe1QLyNau7Fqckh49ZLOMxt+/yUFw
7BZy1SbsOFU5Q9D8/RhcQPGX69Wam40dutolucbY38EVAjqr2m7xPi71XAicPNaD
aeQQmxkqtilX4+U9m5/wAl0CAwEAAaNCMEAwDwYDVR0TAQH/BAUwAwEB/zAOBgNV
HQ8BAf8EBAMCAQYwHQYDVR0OBBYEFMSnsaR7LHH62+FLkHX/xBVghYkQMA0GCSqG
SIb3DQEBBQUAA4IBAQCjGiybFwBcqR7uKGY3Or+Dxz9LwwmglSBd49lZRNI+DT69
ikugdB/OEIKcdBodfpga3csTS7MgROSR6cz8faXbauX+5v3gTt23ADq1cEmv8uXr
AvHRAosZy5Q6XkjEGB5YGV8eAlrwDPGxrancWYaLbumR9YbK+rlmM6pZW87ipxZz
R8srzJmwN0jP41ZL9c8PDHIyh8bwRLtTcm1D9SZImlJnt1ir/md2cXjbDaJWFBM5
JDGFoqgCWjBH4d1QB7wCCZAA62RjYJsWvIjJEubSfZGL+T0yjWW06XyxV3bqxbYo
Ob8VZRzI9neWagqNdwvYkQsEjgfbKbYK7p2CNTUQ
-----END CERTIFICATE-----
[root@hetzner2 tmp]# openssl x509 -in /var/tmp/identrust.dst.root.x3.pem -pubkey | openssl pkey -pubin -outform der | openssl dgst -sha256 -binary | base64
Vjs8r4z+80wjNcr1YKepWQboSIRi63WsWXhIMN+eWys=
[root@hetzner2 tmp]# 

##############################################
# pin cloudflare root CA certs if Let's Encrypt disappears one day #
##############################################
# https://support.cloudflare.com/hc/en-us/articles/115001186052-What-intermediates-and-roots-are-Cloudflare-issued-certs-signed-against-

[root@hetzner2 openbuildinginstitute.org]# mkdir /var/tmp/cloudflare
[root@hetzner2 openbuildinginstitute.org]# cd /var/tmp/cloudflare

# CloudFlare Universal SSL Certs are signed by Comodo or GlobalSign
# https://www.crt.sh/?d=1
# https://www.crt.sh/?d=88

[root@hetzner2 cloudflare]# wget --quiet -O addtrust.pem https://www.crt.sh/?d=1
[root@hetzner2 cloudflare]# cat addtrust.pem 
-----BEGIN CERTIFICATE-----
MIIENjCCAx6gAwIBAgIBATANBgkqhkiG9w0BAQUFADBvMQswCQYDVQQGEwJTRTEU
MBIGA1UEChMLQWRkVHJ1c3QgQUIxJjAkBgNVBAsTHUFkZFRydXN0IEV4dGVybmFs
IFRUUCBOZXR3b3JrMSIwIAYDVQQDExlBZGRUcnVzdCBFeHRlcm5hbCBDQSBSb290
MB4XDTAwMDUzMDEwNDgzOFoXDTIwMDUzMDEwNDgzOFowbzELMAkGA1UEBhMCU0Ux
FDASBgNVBAoTC0FkZFRydXN0IEFCMSYwJAYDVQQLEx1BZGRUcnVzdCBFeHRlcm5h
bCBUVFAgTmV0d29yazEiMCAGA1UEAxMZQWRkVHJ1c3QgRXh0ZXJuYWwgQ0EgUm9v
dDCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBALf3GjPm8gAELTngTlvt
H7xsD821+iO2zt6bETOXpClMfZOfvUq8k+0DGuOPz+VtUFrWlymUWoCwSXrbLpX9
uMq/NzgtHj6RQa1wVsfwTz/oMp50ysiQVOnGXw94nZpAPA6sYapeFI+eh6FqUNzX
mk6vBbOmcZSccbNQYArHE504B4YCqOmoaSYYkKtMsE8jqzpPhNjfzp/haW+710LX
a0Tkx63ubUFfclpxCDezeWWkWaCUN/cALw3CknLa0Dhy2xSoRcRdKn23tNbE7qzN
E0S3ySvdQwAl+mG5aWpYIxG3pzOPVnVZ9c0p10a3CitlttNCbxWyuHv77+ldU9U0
WicCAwEAAaOB3DCB2TAdBgNVHQ4EFgQUrb2YejS0Jvf6xCZU7wO94CTLVBowCwYD
VR0PBAQDAgEGMA8GA1UdEwEB/wQFMAMBAf8wgZkGA1UdIwSBkTCBjoAUrb2YejS0
Jvf6xCZU7wO94CTLVBqhc6RxMG8xCzAJBgNVBAYTAlNFMRQwEgYDVQQKEwtBZGRU
cnVzdCBBQjEmMCQGA1UECxMdQWRkVHJ1c3QgRXh0ZXJuYWwgVFRQIE5ldHdvcmsx
IjAgBgNVBAMTGUFkZFRydXN0IEV4dGVybmFsIENBIFJvb3SCAQEwDQYJKoZIhvcN
AQEFBQADggEBALCb4IUlwtYj4g+WBpKdQZic2YR5gdkeWxQHIzZlj7DYd7usQWxH
YINRsPkyPef89iYTx4AWpb9a/IfPeHmJIZriTAcKhjW88t5RxNKWt9x+Tu5w/Rw5
6wwCURQtjr0W4MHfRnXnJK3s9EK0hZNwEGe6nQY1ShjTK3rMUUKhemPR5ruhxSvC
Nr4TDea9Y355e6cJDUCrat2PisP29owaQgVR1EX1n6diIWgVIEM8med8vSTYqZEX
c4g/VhsxOBi0cQ+azcgOno4uG+GMmIPLHzHxREzGBHNJdmAPx/i9F4BrLunMTA5a
mnkPIAou1Z5jJh5VkpTYghdae9C8x49OhgQ=
-----END CERTIFICATE-----
[root@hetzner2 cloudflare]# openssl x509 -in /var/tmp/cloudflare/addtrust.pem -pubkey | openssl pkey -pubin -outform der | openssl dgst -sha256 -binary | base64
lCppFqbkrlJ3EcVFAkeip0+44VaoJUymbnOaEUk7tEU=

[root@hetzner2 cloudflare]# wget --quiet -O globalsign.pem https://www.crt.sh/?d=88
[root@hetzner2 cloudflare]# cat globalsign.pem 
-----BEGIN CERTIFICATE-----
MIIDdTCCAl2gAwIBAgILBAAAAAABFUtaw5QwDQYJKoZIhvcNAQEFBQAwVzELMAkG
A1UEBhMCQkUxGTAXBgNVBAoTEEdsb2JhbFNpZ24gbnYtc2ExEDAOBgNVBAsTB1Jv
b3QgQ0ExGzAZBgNVBAMTEkdsb2JhbFNpZ24gUm9vdCBDQTAeFw05ODA5MDExMjAw
MDBaFw0yODAxMjgxMjAwMDBaMFcxCzAJBgNVBAYTAkJFMRkwFwYDVQQKExBHbG9i
YWxTaWduIG52LXNhMRAwDgYDVQQLEwdSb290IENBMRswGQYDVQQDExJHbG9iYWxT
aWduIFJvb3QgQ0EwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQDaDuaZ
jc6j40+Kfvvxi4Mla+pIH/EqsLmVEQS98GPR4mdmzxzdzxtIK+6NiY6arymAZavp
xy0Sy6scTHAHoT0KMM0VjU/43dSMUBUc71DuxC73/OlS8pF94G3VNTCOXkNz8kHp
1Wrjsok6Vjk4bwY8iGlbKk3Fp1S4bInMm/k8yuX9ifUSPJJ4ltbcdG6TRGHRjcdG
snUOhugZitVtbNV4FpWi6cgKOOvyJBNPc1STE4U6G7weNLWLBYy5d4ux2x8gkasJ
U26Qzns3dLlwR5EiUWMWea6xrkEmCMgZK9FGqkjWZCrXgzT/LCrBbBlDSgeF59N8
9iFo7+ryUp9/k5DPAgMBAAGjQjBAMA4GA1UdDwEB/wQEAwIBBjAPBgNVHRMBAf8E
BTADAQH/MB0GA1UdDgQWBBRge2YaRQ2XyolQL30EzTSo//z9SzANBgkqhkiG9w0B
AQUFAAOCAQEA1nPnfE920I2/7LqivjTFKDK1fPxsnCwrvQmeU79rXqoRSLblCKOz
yj1hTdNGCbM+w6DjY1Ub8rrvrTnhQ7k4o+YviiY776BQVvnGCv04zcQLcFGUl5gE
38NflNUVyRRBnMRddWQVDf9VMOyGj/8N7yy5Y0b2qvzfvGn9LhJIZJrglfCm7ymP
AbEVtQwdpf5pLGkkeB6zpxxxYu7KyJesF12KwvhHhm4qxFYxldBniYUr+WymXUad
DKqC5JlR3XC321Y9YeRq4VzW9v493kHMB65jUr9TU/Qr6cf9tveCX4XSQRjbgbME
HMUfpIBvFSDJ3gyICh3WZlXi/EjJKSZp4A==
-----END CERTIFICATE-----
[root@hetzner2 cloudflare]# openssl x509 -in /var/tmp/cloudflare/globalsign.pem -pubkey | openssl pkey -pubin -outform der | openssl dgst -sha256 -binary | base64
K87oWBWM9UZfyddvDfoxL+8lpNyoUB2ptGtn0fv6G2Q=

# CloudFlare Dedicated Certificates are signed by DigiCert
# https://www.crt.sh/?d=76

[root@hetzner2 cloudflare]# wget --quiet -O digicert.pem https://www.crt.sh/?d=76
[root@hetzner2 cloudflare]# cat digicert.pem 
-----BEGIN CERTIFICATE-----
MIIDdzCCAl+gAwIBAgIEAgAAuTANBgkqhkiG9w0BAQUFADBaMQswCQYDVQQGEwJJ
RTESMBAGA1UEChMJQmFsdGltb3JlMRMwEQYDVQQLEwpDeWJlclRydXN0MSIwIAYD
VQQDExlCYWx0aW1vcmUgQ3liZXJUcnVzdCBSb290MB4XDTAwMDUxMjE4NDYwMFoX
DTI1MDUxMjIzNTkwMFowWjELMAkGA1UEBhMCSUUxEjAQBgNVBAoTCUJhbHRpbW9y
ZTETMBEGA1UECxMKQ3liZXJUcnVzdDEiMCAGA1UEAxMZQmFsdGltb3JlIEN5YmVy
VHJ1c3QgUm9vdDCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAKMEuyKr
mD1X6CZymrV51Cni4eiVgLGw41uOKymaZN+hXe2wCQVt2yguzmKiYv60iNoS6zjr
IZ3AQSsBUnuId9Mcj8e6uYi1agnnc+gRQKfRzMpijS3ljwumUNKoUMMo6vWrJYeK
mpYcqWe4PwzV9/lSEy/CG9VwcPCPwBLKBsua4dnKM3p31vjsufFoREJIE9LAwqSu
XmD+tqYF/LTdB1kC1FkYmGP1pWPgkAx9XbIGevOF6uvUA65ehD5f/xXtabz5OTZy
dc93Uk3zyZAsuT3lySNTPx8kmCFcB5kpvcY67Oduhjprl3RjM71oGDHweI12v/ye
jl0qhqdNkNwnGjkCAwEAAaNFMEMwHQYDVR0OBBYEFOWdWTCCR1jMrPoIVDaGezq1
BE3wMBIGA1UdEwEB/wQIMAYBAf8CAQMwDgYDVR0PAQH/BAQDAgEGMA0GCSqGSIb3
DQEBBQUAA4IBAQCFDF2O5G9RaEIFoN27TyclhAO992T9Ldcw46QQF+vaKSm2eT92
9hkTI7gQCvlYpNRhcL0EYWoSihfVCr3FvDB81ukMJY2GQE/szKN+OMY3EU/t3Wgx
jkzSswF07r51XgdIGn9w/xZchMB5hbgF/X++ZRGjD8ACtPhSNzkE1akxehi/oCr0
Epn3o0WC4zxe9Z2etciefC7IpJ5OCBRLbf1wbWsaY71k5h+3zvDyny67G7fyUIhz
ksLi4xaNmjICq44Y3ekQEe5+NauQrz4wlHrQMz2nZQ/1/I6eYs9HRCwBXbsdtTLS
R9I4LtD+gdwyah617jzV/OeBHRnDJELqYzmp
-----END CERTIFICATE-----
[root@hetzner2 cloudflare]# openssl x509 -in /var/tmp/cloudflare/digicert.pem -pubkey | openssl pkey -pubin -outform der | openssl dgst -sha256 -binary | base64 
Y9mvm0exBk1JoQ57f9Vm28jKo5lFm/woKcVxrYxu80o=

# GTE CyberTrust was acquired by DigiCert
[root@hetzner2 cloudflare]# wget --quiet -O gtecybertrust.pem https://www.crt.sh/?d=10
[root@hetzner2 cloudflare]# cat gtecybertrust.pem 
-----BEGIN CERTIFICATE-----
MIICWjCCAcMCAgGlMA0GCSqGSIb3DQEBBAUAMHUxCzAJBgNVBAYTAlVTMRgwFgYD
VQQKEw9HVEUgQ29ycG9yYXRpb24xJzAlBgNVBAsTHkdURSBDeWJlclRydXN0IFNv
bHV0aW9ucywgSW5jLjEjMCEGA1UEAxMaR1RFIEN5YmVyVHJ1c3QgR2xvYmFsIFJv
b3QwHhcNOTgwODEzMDAyOTAwWhcNMTgwODEzMjM1OTAwWjB1MQswCQYDVQQGEwJV
UzEYMBYGA1UEChMPR1RFIENvcnBvcmF0aW9uMScwJQYDVQQLEx5HVEUgQ3liZXJU
cnVzdCBTb2x1dGlvbnMsIEluYy4xIzAhBgNVBAMTGkdURSBDeWJlclRydXN0IEds
b2JhbCBSb290MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQCVD6C28FCc6HrH
iM3dFw4usJTQGz0O9pTAipTHBsiQl8i4ZBp6fmw8U+E3KHNgf7KXUwefU/ltWJTS
r41tiGeA5u2ylc9yMcqlHHK6XALnZELn+aks1joNrI1CqiQBOeacPwGFVw1Yh0X4
04Wqk2kmhXBIgD8SFcd5tB8FLztimQIDAQABMA0GCSqGSIb3DQEBBAUAA4GBAG3r
GwnpXtlR22ciYaQqPEh346B8pt5zohQDhT37qw4wxYMWM4ETCJ57NE7fQMh017l9
3PR2VX2bY1QY6fDq81yx2YtCHrnAlU66+tXifPVoYb+O7AWXX1uw16OFNMQkpw0P
lZPvy5TYnh+dXIVtx6quTx8itc2VrbqnzPmrC3p/
-----END CERTIFICATE-----
[root@hetzner2 cloudflare]# openssl x509 -in /var/tmp/cloudflare/gtecybertrust.pem -pubkey | openssl pkey -pubin -outform der | openssl dgst -sha256 -binary | base64
EGn6R6CqT4z3ERscrqNl7q7RC//zJmDe9uBhS/rnCHU=

#############################################
# pin ssl.com root CA certs if Let's Encrypt disappears one day #
#############################################
# https://www.ssl.com/repository/

[root@hetzner2 cloudflare]# mkdir /var/tmp/ssl.com
[root@hetzner2 cloudflare]# cd /var/tmp/ssl.com

[root@hetzner2 ssl.com]# wget --quiet https://www.ssl.com/repository/SSLcom-RootCA.zip
[root@hetzner2 ssl.com]# unzip SSLcom-RootCA.zip Archive:  SSLcom-RootCA.zip
  inflating: SSLcom-RootCA-EV-RSA-4096-R2.pem  
  inflating: SSLcomEVRootCertificationAuthorityECC.pem  
  inflating: SSLcomRootCertificationAuthorityECC.pem  
  inflating: SSLcomRootCertificationAuthorityRSA.pem  
[root@hetzner2 ssl.com]# cat SSLcom-RootCA-EV-RSA-4096-R2.pem
Subject: CN=SSL.com EV Root Certification Authority RSA R2,O=SSL Corporation,L=Houston,ST=Texas,C=US
Issuer: CN=SSL.com EV Root Certification Authority RSA R2,O=SSL Corporation,L=Houston,ST=Texas,C=US
-----BEGIN CERTIFICATE-----
MIIF6zCCA9OgAwIBAgIIVrYpzTS8ePYwDQYJKoZIhvcNAQELBQAwgYIxCzAJBgNV
BAYTAlVTMQ4wDAYDVQQIDAVUZXhhczEQMA4GA1UEBwwHSG91c3RvbjEYMBYGA1UE
CgwPU1NMIENvcnBvcmF0aW9uMTcwNQYDVQQDDC5TU0wuY29tIEVWIFJvb3QgQ2Vy
dGlmaWNhdGlvbiBBdXRob3JpdHkgUlNBIFIyMB4XDTE3MDUzMTE4MTQzN1oXDTQy
MDUzMDE4MTQzN1owgYIxCzAJBgNVBAYTAlVTMQ4wDAYDVQQIDAVUZXhhczEQMA4G
A1UEBwwHSG91c3RvbjEYMBYGA1UECgwPU1NMIENvcnBvcmF0aW9uMTcwNQYDVQQD
DC5TU0wuY29tIEVWIFJvb3QgQ2VydGlmaWNhdGlvbiBBdXRob3JpdHkgUlNBIFIy
MIICIjANBgkqhkiG9w0BAQEFAAOCAg8AMIICCgKCAgEAjzZlQOHWTcDXtOlG2mvq
M0fNTPl9fb69LT3w23jhhqXZuglXaO1XPqDQCEGD5yhBJB/jchXQARr7XnAjssuf
OePPxU7Gkm0mxnu7s9onnQqG6YE3Bf7wcXHswxzpY6IXFJ3vG2fThVUCAtZJycxa
4bH3bzKfydQ7iEGonL3Lq9ttewkfokxykNorCPzPPFTOZw+oz12WGQvE43LrrdF9
HSfvkusQv1vrO6/PgN3B0pYEW3p+pKk8OHakYo6gOV7qd89dAFmPZiw+B6KjBSYR
aZfqhbcPlgtLyEDhULouisv3D5oi53+aNxPN8k0TayHRwMwi8qFG9kRpnMphNQcA
b9ZhCBHqurj26bNg5U257J8UZslXWNvNh2n4ioYSA0e/ZhN2rHd9NCSFg83XqpyQ
Gp8hLH94t2S42Oim9HizVcuE0jLEeK6jj2HdzghTreyI/BXkmg3mnxp3zkyPuBQV
PWKchjgGAGYS5Fl2WlPAApiiECtoRHuOec4zSnaqW4EWG7WK2NAAe15itAnWhmMO
pgWVSbooi4iTsjQc2KRVbrcc0N6ZVTsj9CLg+SlmJuwgUHfbSguPvuUCYHBBXtSu
UDkiFCbLsjtzdFVHB3mBOagwE0TlBIqulhMlQg+5U8Sb/M3kHN48+qvWBkofZ6aY
MBzdLNvcGJVXZsb/XItW9XcCAwEAAaNjMGEwDwYDVR0TAQH/BAUwAwEB/zAfBgNV
HSMEGDAWgBT5YLvU49U09rj1BoAlp3PbRmmonjAdBgNVHQ4EFgQU+WC71OPVNPa4
9QaAJadz20ZpqJ4wDgYDVR0PAQH/BAQDAgGGMA0GCSqGSIb3DQEBCwUAA4ICAQBW
s47LCp1Jjr+kxJG7ZhcFUZh1++VQLHqe8RT6q9OKPv+RKY9ji9i0qVQBDb6Thi/5
Sm3HXvVX+cpVHBK+Rw82xd9qt9t1wkclf7nxY/hoLVUE0fKNsKTPvDxeH3jnpaAg
cLAExbf3cqfeIg29MyVGjGSSJuM+LmOW2puMPfgYCdcDzH2GguDKBAdRUNf/ktUM
79qGn5nX67evaOI5JpS6aLe/g9Pqemc9YmeuJeVy6OLk7K4S9ksrPJ/psEDzOFSz
/bdoyNrGj1E8svuR3Bznm53htw1yj+KkxKl4+esUrMZDBcJlOSgYAsOCsp0FvmXt
ll9ldDz7CTUue5wT/RsPXcdtgTpWD8w74a8CLyKsRspGPKAcTNZEtF4uXBVmCeEm
Kf7GUmG6sXP/wwyc5WxqlD8UykAWlYTzWamsX0xhk23RO8yilQwipmdnRC652dKK
QbNmC1r7fSOl8hqw/96bg5Qu0T/fkreRrwU7ZcegbLHNYhLDkBvjJc40vG93drEQ
w/cFGsDWr3RiSBd3kmmQYRzelYB0VI8YHMPzA9C/pEN1hlMYegouCRw2n5H9gooi
S9EOUCXdywMMF8mDAAhONU2Ki+3wApRmLER/y5UnlhetCTCstnEXbosX9hwJ1C07
mKVx01QT2WDz9UtmT/rx7iASjbSsV7FFY6GsdqnC+w==
-----END CERTIFICATE-----
[root@hetzner2 ssl.com]# cat SSLcomEVRootCertificationAuthorityECC.pem
-----BEGIN CERTIFICATE-----
MIIClDCCAhqgAwIBAgIILCmcWxbtBZUwCgYIKoZIzj0EAwIwfzELMAkGA1UEBhMC
VVMxDjAMBgNVBAgMBVRleGFzMRAwDgYDVQQHDAdIb3VzdG9uMRgwFgYDVQQKDA9T
U0wgQ29ycG9yYXRpb24xNDAyBgNVBAMMK1NTTC5jb20gRVYgUm9vdCBDZXJ0aWZp
Y2F0aW9uIEF1dGhvcml0eSBFQ0MwHhcNMTYwMjEyMTgxNTIzWhcNNDEwMjEyMTgx
NTIzWjB/MQswCQYDVQQGEwJVUzEOMAwGA1UECAwFVGV4YXMxEDAOBgNVBAcMB0hv
dXN0b24xGDAWBgNVBAoMD1NTTCBDb3Jwb3JhdGlvbjE0MDIGA1UEAwwrU1NMLmNv
bSBFViBSb290IENlcnRpZmljYXRpb24gQXV0aG9yaXR5IEVDQzB2MBAGByqGSM49
AgEGBSuBBAAiA2IABKoSR5CYG/vvw0AHgyBO8TCCogbR8pKGYfL2IWjKAMTH6kMA
VIbc/R/fALhBYlzccBYy3h+Z1MzFB8gIH2EWB1E9fVwHU+M1OIzfzZ/ZLg1Kthku
WnBaBu2+8KGwytAJKaNjMGEwHQYDVR0OBBYEFFvKXuXe0oGqzagtZFG22XKbl+ZP
MA8GA1UdEwEB/wQFMAMBAf8wHwYDVR0jBBgwFoAUW8pe5d7SgarNqC1kUbbZcpuX
5k8wDgYDVR0PAQH/BAQDAgGGMAoGCCqGSM49BAMCA2gAMGUCMQCK5kCJN+vp1RPZ
ytRrJPOwPYdGWBrssd9v+1a6cGvHOMzosYxPD/fxZ3YOg9AeUY8CMD32IygmTMZg
h5Mmm7I1HrrW9zzRHM76JTymGoEVW/MSD2zuZYrJh6j5B+BimoxcSg==
-----END CERTIFICATE-----
[root@hetzner2 ssl.com]# cat SSLcomRootCertificationAuthorityECC.pem 
-----BEGIN CERTIFICATE-----
MIICjTCCAhSgAwIBAgIIdebfy8FoW6gwCgYIKoZIzj0EAwIwfDELMAkGA1UEBhMC
VVMxDjAMBgNVBAgMBVRleGFzMRAwDgYDVQQHDAdIb3VzdG9uMRgwFgYDVQQKDA9T
U0wgQ29ycG9yYXRpb24xMTAvBgNVBAMMKFNTTC5jb20gUm9vdCBDZXJ0aWZpY2F0
aW9uIEF1dGhvcml0eSBFQ0MwHhcNMTYwMjEyMTgxNDAzWhcNNDEwMjEyMTgxNDAz
WjB8MQswCQYDVQQGEwJVUzEOMAwGA1UECAwFVGV4YXMxEDAOBgNVBAcMB0hvdXN0
b24xGDAWBgNVBAoMD1NTTCBDb3Jwb3JhdGlvbjExMC8GA1UEAwwoU1NMLmNvbSBS
b290IENlcnRpZmljYXRpb24gQXV0aG9yaXR5IEVDQzB2MBAGByqGSM49AgEGBSuB
BAAiA2IABEVuqVDEpiM2nl8ojRfLliJkP9x6jh3MCLOicSS6jkm5BBtHllirLZXI
7Z4INcgn64mMU1jrYor+8FsPazFSY0E7ic3s7LaNGdM0B9y7xgZ/wkWV7Mt/qCPg
CemB+vNH06NjMGEwHQYDVR0OBBYEFILRhXMw5zUE044CkvvlpNHEIejNMA8GA1Ud
EwEB/wQFMAMBAf8wHwYDVR0jBBgwFoAUgtGFczDnNQTTjgKS++Wk0cQh6M0wDgYD
VR0PAQH/BAQDAgGGMAoGCCqGSM49BAMCA2cAMGQCMG/n61kRpGDPYbCWe+0F+S8T
kdzt5fxQaxFGRrMcIQBiu77D5+jNB5n5DQtdcj7EqgIwH7y6C+IwJPt8bYBVCpk+
gA0z5Wajs6O7pdWLjwkspl1+4vAHCGht0nxpbl/f5Wpl
-----END CERTIFICATE-----
[root@hetzner2 ssl.com]# cat SSLcomRootCertificationAuthorityRSA.pem 
-----BEGIN CERTIFICATE-----
MIIF3TCCA8WgAwIBAgIIeyyb0xaAMpkwDQYJKoZIhvcNAQELBQAwfDELMAkGA1UE
BhMCVVMxDjAMBgNVBAgMBVRleGFzMRAwDgYDVQQHDAdIb3VzdG9uMRgwFgYDVQQK
DA9TU0wgQ29ycG9yYXRpb24xMTAvBgNVBAMMKFNTTC5jb20gUm9vdCBDZXJ0aWZp
Y2F0aW9uIEF1dGhvcml0eSBSU0EwHhcNMTYwMjEyMTczOTM5WhcNNDEwMjEyMTcz
OTM5WjB8MQswCQYDVQQGEwJVUzEOMAwGA1UECAwFVGV4YXMxEDAOBgNVBAcMB0hv
dXN0b24xGDAWBgNVBAoMD1NTTCBDb3Jwb3JhdGlvbjExMC8GA1UEAwwoU1NMLmNv
bSBSb290IENlcnRpZmljYXRpb24gQXV0aG9yaXR5IFJTQTCCAiIwDQYJKoZIhvcN
AQEBBQADggIPADCCAgoCggIBAPkP3aMrfcvQKv7sZ4Wm5y4bunfh4/WvpOz6Sl2R
xFdHaxh3a3by/ZPkPQ/CFp4LZsNWlJ4Xg4XOVu/yFv0AYvUiCVToZRdOQbngT0aX
qhvIuG5iXmmxX9sqAn78bMrzQdjt0Oj8P2FI7bADFB0QDksZ4LtO7IZl/zbzXmcC
C52GVWH9ejjt/uIZALdvoVBidXQ8oPrIJZK0bnoix/geoeOy3ZExqysdBP+lSgQ3
6YWkMyv94tZVNHwZpEpox7Ko07fKoZOI68GXvIz5HdkihCR0xwQ9aqkpk8zruFvh
/l8lqjRYyMEjVJ0bmBHDOJx+PYZspQ9AhnwC9FwCTyjLrnGfDzrIM/4RJTXq/LrF
YD3ZfBjVsqnTdXgDciLKOsMf7yzlLqn6niy2UUb9rwPW6mBo6oUWNmuF6R7As93E
JNyAKoFBbZQ+yODJgUEAnl6/f8UImKIYLEJAs/lvOCdLToD0PYFH4Ih86hzOtXVc
US4cK38acijnALXRdMbX5J+tB5O2UzU1/Dfkw/ZdFr4hc96SCvigY2q8lpJqPvi8
ZVWb3vUNiSYE/CUapiVpy8JtynziWV+XrOvvLsi81xtZPCvM8hnIk2snYxnP/Okm
+Mpxm3+T/jRnhE6Z6/yzeAkzcLpmpnbtG3PrGqUNxCITIJRWCk4sbE6x/c+cCbqi
M+2HAgMBAAGjYzBhMB0GA1UdDgQWBBTdBAkHovV6fVJTEpKV7jiAJQ2mWTAPBgNV
HRMBAf8EBTADAQH/MB8GA1UdIwQYMBaAFN0ECQei9Xp9UlMSkpXuOIAlDaZZMA4G
A1UdDwEB/wQEAwIBhjANBgkqhkiG9w0BAQsFAAOCAgEAIBgRlCn7Jp0cHh5wYfGV
cpNxJK1ok1iOMq8bs3AD/CUrdIWQPXhq9LmLpZc7tRiRux6n+UBbkflVma8eEdBc
Hadm47GUBwwyOabqG7B52B2ccETjit3E+ZUfijhDPwGFpUenPUayvOUiaPd7nNgs
PgohyC0zrL/FgZkxdMF1ccW+sfAjRfSda/wZY52jvATGGAslu1OJD7OAUN5F7kR/
q5R4ZJjT9ijdh9hwZXT7DrkT66cPYakylszeu+1jTBi7qUD3oFRuIIhxdRjqerQ0
cuAjJ3dctpDqhiVAq+8zD8ufgr6iIPv2tS0a5sKFsXQP+8hlAqRSAUfdSSLBv9jr
a6x+3uxjMxW3IwiPxg+NQVrdjsW5j+VFP3jbutIbQLH+cU0/4IGiul607BXgk90I
H37hVZkLId6Tngr75qNJvTYw/ud3sqB1l7UtgYgXZSD32pAAn8lSzDLKNXz1PQ/Y
K9f1JmzJBjSWFupwWRoyeXkLtoh/D1JIPb9s2KJELtFOt3JY04kTlf5Eq/jXixtu
nLwsoFvVagCvXzfh1foQC5ichucmj87w7G6KVwuA406ywKBjYZC6VWg3dGq2ktuf
oYYitmUnDuy2n0Jg5GfCtdpBC8TTi2EbvPofkSvXRAdeuims2cXp71NIWuuA8ShY
Ic2wBlX7Jz9TkHCpBB5XJ7k=
-----END CERTIFICATE-----
[root@hetzner2 ssl.com]# for file in $(ls *.pem); do echo /var/tmp/ssl.com/$file; openssl x509 -in /var/tmp/ssl.com/$file -pubkey | openssl pkey -pubin -outform der | openssl dgst -sha256 -binary | base64; done
/var/tmp/ssl.com/SSLcomEVRootCertificationAuthorityECC.pem
NIdnza073SiyuN1TUa7DDGjOxc1p0nbfOCfbxPWAZGQ=
/var/tmp/ssl.com/SSLcom-RootCA-EV-RSA-4096-R2.pem
fNZ8JI9p2D/C+bsB3LH3rWejY9BGBDeW0JhMOiMfa7A=
/var/tmp/ssl.com/SSLcomRootCertificationAuthorityECC.pem
oyD01TTXvpfBro3QSZc1vIlcMjrdLTiL/M9mLCPX+Zo=
/var/tmp/ssl.com/SSLcomRootCertificationAuthorityRSA.pem
0cRTd+vc1hjNFlHcLgLCHXUeWqn80bNDH/bs9qMTSPo=

1. created backup CSRs per HPKP best practices

[root@hetzner2 ~]# mkdir /etc/pki/tls/hpkpBackupKeys
[root@hetzner2 ~]# chown root:root /etc/pki/tls/hpkpBackupKeys
[root@hetzner2 ~]# chmod 0700 /etc/pki/tls/hpkpBackupKeys/
[root@hetzner2 ~]# cd /etc/pki/tls/hpkpBackupKeys/
[root@hetzner2 hpkpBackupKeys]# openssl genrsa -out first.key 4096
Generating RSA private key, 4096 bit long modulus
.................................................................++
..............................................................................................................................................................................................................................++
e is 65537 (0x10001)
[root@hetzner2 hpkpBackupKeys]# openssl req -new -key first.key -sha256 -out first.csr
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:US
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

[root@hetzner2 hpkpBackupKeys]# openssl genrsa -out second.key 4096
Generating RSA private key, 4096 bit long modulus
...............................................................................................................++
...............................................................................................................................................++
e is 65537 (0x10001)
[root@hetzner2 hpkpBackupKeys]# openssl req -new -key second.key -sha256 -out second.csr
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:US
State or Province Name (full name) []:Missouri
Locality Name (eg, city) [Default City]:Maysville
Organization Name (eg, company) [Default Company Ltd]:Open Source Ecology
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:opensourceecology.org
Email Address []:marcin@opensourceecology.org

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

[root@hetzner2 hpkpBackupKeys]# chown -R root:root /etc/pki/tls/hpkpBackupKeys
[root@hetzner2 hpkpBackupKeys]# chmod 0400 /etc/pki/tls/hpkpBackupKeys/*

[root@hetzner2 hpkpBackupKeys]# openssl req -pubkey < first.csr | openssl pkey -pubin -outform der | openssl dgst -sha256 -binary | base64
MDhNnV1cmaPdDDONbiVionUHH2QIf2aHJwq/lshMWfA=
[root@hetzner2 hpkpBackupKeys]# openssl req -pubkey < second.csr | openssl pkey -pubin -outform der | openssl dgst -sha256 -binary | base64
OIZP7FgTBf7hUpWHIA7OaPVO2WrsGzTl9vdOHLPZmJU=

1. now, taking all the SPKI public key hashes from above, we have the following line to add to our apache config:

Header set Public-Key-Pins "pin-sha256=\"changeme\"; pin-sha256=\"changeme=\"; pin-sha256=\"UbSbHFsFhuCrSv9GNsqnGv4CbaVh5UV5/zzgjLgHh9c=\"; pin-sha256=\"YLh1dUR9y6Kja30RrAn7JKnbQG/uEtLMkBgFF2Fuihg=\"; pin-sha256=\"C5+lpZ7tcVwmwQIMcRtPbsQtWLABXhQzejna0wHFr8M=\"; pin-sha256=\"Vjs8r4z+80wjNcr1YKepWQboSIRi63WsWXhIMN+eWys=\"; pin-sha256=\"lCppFqbkrlJ3EcVFAkeip0+44VaoJUymbnOaEUk7tEU=\"; pin-sha256=\"K87oWBWM9UZfyddvDfoxL+8lpNyoUB2ptGtn0fv6G2Q=\"; pin-sha256=\"Y9mvm0exBk1JoQ57f9Vm28jKo5lFm/woKcVxrYxu80o=\"; pin-sha256=\"EGn6R6CqT4z3ERscrqNl7q7RCzJmDe9uBhS/rnCHU=\"; pin-sha256=\"NIdnza073SiyuN1TUa7DDGjOxc1p0nbfOCfbxPWAZGQ=\"; pin-sha256=\"fNZ8JI9p2D/C+bsB3LH3rWejY9BGBDeW0JhMOiMfa7A=\"; pin-sha256=\"oyD01TTXvpfBro3QSZc1vIlcMjrdLTiL/M9mLCPX+Zo=\"; pin-sha256=\"0cRTd+vc1hjNFlHcLgLCHXUeWqn80bNDH/bs9qMTSPo=\"; pin-sha256=\"MDhNnV1cmaPdDDONbiVionUHH2QIf2aHJwq/lshMWfA=\"; pin-sha256=\"OIZP7FgTBf7hUpWHIA7OaPVO2WrsGzTl9vdOHLPZmJU=\"; max-age=43200; includeSubDomains; report-uri=\"http:opensourceecology.org/hpkp-report\""

1. created /etc/httpd/conf.d/ssl.openbuildinginstitute.org to be included in all vhosts using the *.openbuildinginstitute.org certificate

<VirtualHost piwik.openbuildinginstitute.org:443>                                           
   ServerName piwik.openbuildinginstitute.org                                               
   ServerAlias piwik.openbuildinginstitute.org                                              
   DocumentRoot "/var/www/html/piwik.openbuildinginstitute.org/htdocs"                      
                                                                                            
   Include /etc/httpd/conf.d/ssl.openbuildinginstitute.org                                  
</VirtualHost>

1. updated <VirtualHost> block to include file above in /etc/httpd/conf.d/piwik.openbuildinginstitute.org

[root@hetzner2 conf.d]# cat ssl.openbuildinginstitute.org
################################################################################
# Purpose: To be included inside the <VirtualHost> block for all
#          *.openbuildinginstitute.org sites
#
# This has been hardened by Michael Altfield in 2017-09.
#
# For updating, I recommend:
#   * https://mozilla.github.io/server-side-tls/ssl-config-generator/
#
# Followed by testing with ssllabs:
#   * https://www.ssllabs.com/ssltest
################################################################################

		SSLEngine on
		SSLCertificateFile /etc/letsencrypt/live/openbuildinginstitute.org/fullchain.pem
		SSLCertificateChainFile /etc/letsencrypt/live/openbuildinginstitute.org/fullchain.pem
		SSLCertificateKeyFile /etc/letsencrypt/live/openbuildinginstitute.org/privkey.pem
		SSLProtocol -ALL -SSLv3 -TLSv1 -TLSv1.1 +TLSv1.2
		SSLCipherSuite ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA256

		SSLHonorCipherOrder on

		# HSTS so a browser will refuse a downgrade attack after their first visit
		# TODO: increase this back to 6-months
		#Header always set Strict-Transport-Security "max-age=15768000"
		Header always set Strict-Transport-Security "max-age=15552001"

		# TODO: increase this back to 1 month
		#Header set Public-Key-Pins "pin-sha256=\"UbSbHFsFhuCrSv9GNsqnGv4CbaVh5UV5/zzgjLgHh9c=\"; pin-sha256=\"YLh1dUR9y6Kja30RrAn7JKnbQG/uEtLMkBgFF2Fuihg=\"; pin-sha256=\"C5+lpZ7tcVwmwQIMcRtPbsQtWLABXhQzejna0wHFr8M=\"; pin-sha256=\"Vjs8r4z+80wjNcr1YKepWQboSIRi63WsWXhIMN+eWys=\"; pin-sha256=\"lCppFqbkrlJ3EcVFAkeip0+44VaoJUymbnOaEUk7tEU=\"; pin-sha256=\"K87oWBWM9UZfyddvDfoxL+8lpNyoUB2ptGtn0fv6G2Q=\"; pin-sha256=\"Y9mvm0exBk1JoQ57f9Vm28jKo5lFm/woKcVxrYxu80o=\"; pin-sha256=\"EGn6R6CqT4z3ERscrqNl7q7RCzJmDe9uBhS/rnCHU=\"; pin-sha256=\"NIdnza073SiyuN1TUa7DDGjOxc1p0nbfOCfbxPWAZGQ=\"; pin-sha256=\"fNZ8JI9p2D/C+bsB3LH3rWejY9BGBDeW0JhMOiMfa7A=\"; pin-sha256=\"oyD01TTXvpfBro3QSZc1vIlcMjrdLTiL/M9mLCPX+Zo=\"; pin-sha256=\"0cRTd+vc1hjNFlHcLgLCHXUeWqn80bNDH/bs9qMTSPo=\"; pin-sha256=\"MDhNnV1cmaPdDDONbiVionUHH2QIf2aHJwq/lshMWfA=\"; pin-sha256=\"OIZP7FgTBf7hUpWHIA7OaPVO2WrsGzTl9vdOHLPZmJU=\"; max-age=43200; includeSubDomains; report-uri=\"http:opensourceecology.org/hpkp-report\""
		Header set Public-Key-Pins "pin-sha256=\"UbSbHFsFhuCrSv9GNsqnGv4CbaVh5UV5/zzgjLgHh9c=\"; pin-sha256=\"YLh1dUR9y6Kja30RrAn7JKnbQG/uEtLMkBgFF2Fuihg=\"; pin-sha256=\"C5+lpZ7tcVwmwQIMcRtPbsQtWLABXhQzejna0wHFr8M=\"; pin-sha256=\"Vjs8r4z+80wjNcr1YKepWQboSIRi63WsWXhIMN+eWys=\"; pin-sha256=\"lCppFqbkrlJ3EcVFAkeip0+44VaoJUymbnOaEUk7tEU=\"; pin-sha256=\"K87oWBWM9UZfyddvDfoxL+8lpNyoUB2ptGtn0fv6G2Q=\"; pin-sha256=\"Y9mvm0exBk1JoQ57f9Vm28jKo5lFm/woKcVxrYxu80o=\"; pin-sha256=\"EGn6R6CqT4z3ERscrqNl7q7RCzJmDe9uBhS/rnCHU=\"; pin-sha256=\"NIdnza073SiyuN1TUa7DDGjOxc1p0nbfOCfbxPWAZGQ=\"; pin-sha256=\"fNZ8JI9p2D/C+bsB3LH3rWejY9BGBDeW0JhMOiMfa7A=\"; pin-sha256=\"oyD01TTXvpfBro3QSZc1vIlcMjrdLTiL/M9mLCPX+Zo=\"; pin-sha256=\"0cRTd+vc1hjNFlHcLgLCHXUeWqn80bNDH/bs9qMTSPo=\"; pin-sha256=\"MDhNnV1cmaPdDDONbiVionUHH2QIf2aHJwq/lshMWfA=\"; pin-sha256=\"OIZP7FgTBf7hUpWHIA7OaPVO2WrsGzTl9vdOHLPZmJU=\"; max-age=3600; includeSubDomains; report-uri=\"http:opensourceecology.org/hpkp-report\""

		# enabling compression can be a security risk - see "CRIME" attack
		SSLCompression off

1. updated /etc/httpd/conf.d/ssl.conf with general settings, though I'm not sure this ever actually gets usedu

[root@hetzner2 conf.d]# diff ssl.conf.orig ssl.conf
0a1,10
> ################################################################################
> # This has been hardened by Michael Altfield in 2017-09.
> #
> # For updating, I recommend:
> #   * https://mozilla.github.io/server-side-tls/ssl-config-generator/
> #
> # Followed by testing with ssllabs:
> #   * https://www.ssllabs.com/ssltest
> ################################################################################
> 
75c85,88
< SSLProtocol all -SSLv2
---
> #SSLProtocol all -SSLv2
> # moved outside VirtualHost block (see below)
> #SSLProtocol -ALL -SSLv3 -TLSv1 -TLSv1.1 +TLSv1.2
> SSLProtocol -ALL -SSLv3 -TLSv1 -TLSv1.1 +TLSv1.2
80c93,95
< SSLCipherSuite HIGH:MEDIUM:!aNULL:!MD5:!SEED:!IDEA
---
> #SSLCipherSuite HIGH:MEDIUM:!aNULL:!MD5:!SEED:!IDEA
> # moved outside VirtualHost block (see below)
> #SSLCipherSuite ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA256                                                                             
93d107
< #SSLHonorCipherOrder on 
100c114,117
< SSLCertificateFile /etc/pki/tls/certs/localhost.crt
---
> #SSLCertificateFile /etc/pki/tls/certs/localhost.crt
> 
> #SSLCertificateFile /etc/letsencrypt/live/opensourceecology.org/fullchain.pem
> SSLCertificateFile /etc/letsencrypt/live/openbuildinginstitute.org/fullchain.pem
107c124,127
< SSLCertificateKeyFile /etc/pki/tls/private/localhost.key
---
> #SSLCertificateKeyFile /etc/pki/tls/private/localhost.key
> 
> #SSLCertificateKeyFile /etc/letsencrypt/live/opensourceecology.org/privkey.pem
> SSLCertificateKeyFile /etc/letsencrypt/live/openbuildinginstitute.org/privkey.pem
216,217c236,263
< </VirtualHost>                                  
< 
---
> ###################################
> # other custom options -maltfield #
> ###################################
> 
> SSLProtocol -ALL -SSLv3 -TLSv1 -TLSv1.1 +TLSv1.2
> SSLCipherSuite ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA256                                                                              
> 
> SSLHonorCipherOrder on 
> 
> # HSTS so a browser will refuse a downgrade attack after their first visit
> # TODO: increase this back to 6-months
> #Header always set Strict-Transport-Security "max-age=15768000"
> Header always set Strict-Transport-Security "max-age=15552001"
> 
> # TODO: increase this back to 1 month
> #Header set Public-Key-Pins "pin-sha256=\"UbSbHFsFhuCrSv9GNsqnGv4CbaVh5UV5/zzgjLgHh9c=\"; pin-sha256=\"YLh1dUR9y6Kja30RrAn7JKnbQG/uEtLMkBgFF2Fuihg=\"; pin-sha256=\"C5+lpZ7tcVwmwQIMcRtPbsQtWLABXhQzejna0wHFr8M=\"; pin-sha256=\"Vjs8r4z+80wjNcr1YKepWQboSIRi63WsWXhIMN+eWys=\"; pin-sha256=\"lCppFqbkrlJ3EcVFAkeip0+44VaoJUymbnOaEUk7tEU=\"; pin-sha256=\"K87oWBWM9UZfyddvDfoxL+8lpNyoUB2ptGtn0fv6G2Q=\"; pin-sha256=\"Y9mvm0exBk1JoQ57f9Vm28jKo5lFm/woKcVxrYxu80o=\"; pin-sha256=\"EGn6R6CqT4z3ERscrqNl7q7RCzJmDe9uBhS/rnCHU=\"; pin-sha256=\"NIdnza073SiyuN1TUa7DDGjOxc1p0nbfOCfbxPWAZGQ=\"; pin-sha256=\"fNZ8JI9p2D/C+bsB3LH3rWejY9BGBDeW0JhMOiMfa7A=\"; pin-sha256=\"oyD01TTXvpfBro3QSZc1vIlcMjrdLTiL/M9mLCPX+Zo=\"; pin-sha256=\"0cRTd+vc1hjNFlHcLgLCHXUeWqn80bNDH/bs9qMTSPo=\"; pin-sha256=\"MDhNnV1cmaPdDDONbiVionUHH2QIf2aHJwq/lshMWfA=\"; pin-sha256=\"OIZP7FgTBf7hUpWHIA7OaPVO2WrsGzTl9vdOHLPZmJU=\"; max-age=43200; includeSubDomains; report-uri=\"http:opensourceecology.org/hpkp-report\""                                     
> Header set Public-Key-Pins "pin-sha256=\"UbSbHFsFhuCrSv9GNsqnGv4CbaVh5UV5/zzgjLgHh9c=\"; pin-sha256=\"YLh1dUR9y6Kja30RrAn7JKnbQG/uEtLMkBgFF2Fuihg=\"; pin-sha256=\"C5+lpZ7tcVwmwQIMcRtPbsQtWLABXhQzejna0wHFr8M=\"; pin-sha256=\"Vjs8r4z+80wjNcr1YKepWQboSIRi63WsWXhIMN+eWys=\"; pin-sha256=\"lCppFqbkrlJ3EcVFAkeip0+44VaoJUymbnOaEUk7tEU=\"; pin-sha256=\"K87oWBWM9UZfyddvDfoxL+8lpNyoUB2ptGtn0fv6G2Q=\"; pin-sha256=\"Y9mvm0exBk1JoQ57f9Vm28jKo5lFm/woKcVxrYxu80o=\"; pin-sha256=\"EGn6R6CqT4z3ERscrqNl7q7RCzJmDe9uBhS/rnCHU=\"; pin-sha256=\"NIdnza073SiyuN1TUa7DDGjOxc1p0nbfOCfbxPWAZGQ=\"; pin-sha256=\"fNZ8JI9p2D/C+bsB3LH3rWejY9BGBDeW0JhMOiMfa7A=\"; pin-sha256=\"oyD01TTXvpfBro3QSZc1vIlcMjrdLTiL/M9mLCPX+Zo=\"; pin-sha256=\"0cRTd+vc1hjNFlHcLgLCHXUeWqn80bNDH/bs9qMTSPo=\"; pin-sha256=\"MDhNnV1cmaPdDDONbiVionUHH2QIf2aHJwq/lshMWfA=\"; pin-sha256=\"OIZP7FgTBf7hUpWHIA7OaPVO2WrsGzTl9vdOHLPZmJU=\"; max-age=3600; includeSubDomains; report-uri=\"http:opensourceecology.org/hpkp-report\""                                       
> 
> </VirtualHost>
> 
> # enabling compression can be a security risk - see "CRIME" attack
> SSLCompression off
> 
> # OCSP Stapling
> SSLUseStapling on
> SSLStaplingResponderTimeout 5
> SSLStaplingReturnResponderErrors off
> SSLStaplingCache shmcb:/var/run/ocsp(128000)

Successfully got "A+" rating from ssllabs.com !!!
added "https" documentation to OSE_Server
further documented today's hpkp solution on my blog https://tech.michaelaltfield.net/2017/09/05/hpkp-best-practices-lets-encrypt/

Sat Sep 02, 2017

created letsencrypt@opensourceecoloy.org & added to keepass
follow-up with Chris & assigned jitsi videobridge task of documenting install commands on cent7 local vm with due date of 2017-10-01

Fri Sep 01, 2017

created obi3 for updating obi without the theme updates..for now
1. reviewed all changelogs for oshine theme & sent email to Catarina on issues of not updating the theme
2. updated wordpress wiki article with wp-cli updates
installed google-authenticator & google-authenticator-encourage-user-activation

sudo -u wp -i wp --path=$newVhostDir/htdocs plugin install google-authenticator --activate
sudo -u wp -i wp --path=$newVhostDir/htdocs plugin install google-authenticator-encourage-user-activation --activate

activated google-authenticator plugin in wp wui
1. changed default name to be obi-specific

defaultOtpAccountDescription="`basename $newVhostDir` wp"
cd $newVhostDir/htdocs/wp-content/plugins/google-authenticator
#sed -i "s/WordPressBlog/$defaultOtpAccountDescription/g" lang/google-authenticator.pot
sed -i "s^\$GA_description\s=\s(\s[\"'].*[\"']^\$GA_description = ( '$defaultOtpAccountDescription'^" google-authenticator.php

required 2FA for all users
1. went to "Settings" -> "General" -> "Google Authenticator - Encourage User Activation"
2. selected "Force" option
3. Clicked "Save Changes"
confirmed that if a user looses their 2FA config on their phone, an Admin can "reset" their account to let them in again
1. The admin needs to login, go to "Users" -> "All Users"
2. click "Edit" under the locked-out user
3. uncheck the "Activate" checkbox under "Google Authenticator Settings"
4. ...then the user will be able to login again, but will still be required to setup 2FA before they can do anything (locked to Subscriber) per the plugin = google-authenticator-encourage-user-activation
installed & activated 'rename-wp-login' plugin

sudo -u wp -i wp --path=$newVhostDir/htdocs plugin install rename-wp-login --activate

1. changed login slug by:
  1. "Settings" -> "Permalinks"
  2. changing the field under "Rename wp-login.php" -> "Login url" to 'ose-hidden-login'
2. added section to "deny from all" for anyone attempting to hit '.../wp-login.php' to vhost config

<LocationMatch .*wp-login.php>                                                  
   Deny From All                                                                
</LocationMatch>

confirmed that the default wp core providers a password strength meter, but does not enforce it
1. installed 'force-strong-passwords' & validated that it blocked my new user from setting their password to 'password'
2. also validated that sufficiently long all-lowercase-character passwords are considered "stong" — as they should be. Symbols are a negligible benefit to long passwords.

sudo -u wp -i wp --path=$newVhostDir/htdocs plugin install force-strong-passwords --activate

researched FOSS TOTP apps for android & found andOTP to be the best
created 'obi4' && 'piwik' subdomains for obi for testing let's encrypt & piwik
decided to have a distinct piwki site for obi & ose
created vhost for "http://piwik.openbuildinginstitute.org:4443"
1. opened port for 4443 in iptables

Sun Aug 28, 2017

began investigating updating to v6 of oshine theme
1. dug through the theme's code, but was unable to see an obvious update url & the necessary variables
2. temporarily removed iptables rules blocking apache from making new connections outbound
3. `tcpdump`ed all calls to brandexponents.com, and extracted the following GET request:
  1. brandexponents.com/oshin-plugins/oshine-purchase-verifier.php?installed_version=5.0&purchase_key=XYZ (which is also only http, so our purchase key is trivial to steal *facepalm*)
  2. this returned a json saying that the latest version is v5.0.5, which is what I downloaded yesterday though wp-cli. I assume that wp-cli uses this same method as the wp wui
  3. the theme itself is publically accessible for some reason here http://brandexponents.com/oshin-plugins/oshine.zip
4. emailed Catarina stating that brandexponents hasn't made their new version available for update yet, even if it is published on themeforest
began attempting to manually update & install plugins that oshin malicously-like expects to install itself with their own code & calls to the internet

cd /var/www/html/obi2/htdocs/wp-content
manualPlugins='oshine-modules tatsu revslider masterslider'
for plugin in $manualPlugins; do cp "../themes/oshin/lib/plugins/${plugin}.zip" . && unzip -o "${plugin}"; done

1. went into the wp dashboard to enable the new Tatsu plugin that oshin was complaining it needed
emailed Catarina for post-theme-and-plugin-updates validation

Sat Aug 26, 2017

discovered wiki db was locked again, repeated mv of 'read-only-message' in docroot for wiki
1. investigated konsoleh's config to see if there's any hetzner-level mysql backups triggering the lock, but found none
2. probably found the culprit in /usr/home/osemain/cron, which contained many cron jobs that weren't listed in the crontab or in hetzner's knosleh wui. Moved this entire dir to /usr/home/osemain/cron/noBackup/deleteMeIn2018/usr/home/osemain/ & created an empty '/usr/home/osemain/cron' dir. These backups were supurfluious; we're doing a mysqldump of everything daily for months now.
added documentation for the commands to produce the obi ephemeral vhost clone in the Wordpress article http://opensourceecology.org/wiki/Wordpress
investigated missing '3Dmodels' render in obi2
1. the file referenced is actually the original obi site, but it's in an iframe. there's a 200 OK, I determined that this is the browser is refusing to show it by respecting the "X-Frame-Options: SAMEORIGIN" header or similar. When I removed this option from the apache config & reloaded, it worked.
2. to be consistent with the wp install upgrade documentation, '3Dmodels' should *really* be in the wp-content dir
3. discovered unnecessary & empty 'upgrade' dir in wp-content. deleting.
investigated missing images on '/how-it-works/'
1. solved by repeating ip address find-and-replace done on 2017-08-15, but for 'openbuildginstitute.org' to 'openbuildinginstitute.org'
2. this also solved all the other issues
deleted obi2 & re-cloned
began investigation of updating/cleanup of obi themes & plugins
1. discovered 17 plugins
  1. 3 were begging to be updated in the admin panel
  2. deleted 3 unused plugins

rm -rf htdocs/wp-content/plugins/hello.php
rm -rf htdocs/wp-content/plugins/simple-301-redirects
rm -rf htdocs/wp-content/plugins/password-protected

1. 1. we intentionally blocked apache from making outgoing connections with iptables, which makes cripples wordpress, so I use this to update plugins. the output can be opened en-mass in firefox using the "Copy All URLs" addon

cd wp-content/plugins
for line in $(ls -1); do echo "https://wordpress.org/plugins/$line"; done

1. looked for a better way to update plugins
  1. found wp-cli https://wp-cli.org/
    1. wp-cli is part of the official wordpress.org project https://developer.wordpress.org/cli/commands/
    2. wp-cli appears to be funded by automatic, bluehost, dreamhost, siteground, wp engine, etc (basically a ton of hosting companies that offer cheap wordpress hosting, which makes sense for them to want & fund)
    3. the wp-cli github repo was created in 2011-09. it was last updated 3 days ago https://github.com/wp-cli/wp-cli
    4. the most recent stable release is 1.3.0, which was released earlier this month. the relesae before that was a couple months prior. there's been 49 releases, usually a few months apart.
    5. this looks like a great solution. especially because large-scale wordpress stakeholders like bluehost are using it, it's been around a while, it's still being actively maintained, and that doesn't look like it would end anytime soon.
    6. breifly checked through the sourcecode. I don't see any ugly `curl -k | bash` bullshit. Actually, they have the certs included in the source, pinned. And it instaled from github over https. TOFU, but well above the sec of other options.
    7. there's no centos package wp-cli
    8. considered making wp-cli accessible to all users in the 'apache' group, but decided that wp-related updates should only be preformed by someone who can do a backup first, which is necessarily root. And we wouldn't want someone attempting an update unless they had the experience necessary to do a restore. All of these should just require a root user. Else they shouldn't be using the tool.
    9. installed wp-cli into /root/wp-cli

sudo su -
mkdir -p /root/.wp-cli
cd /root/.wp-cli
curl -O https://raw.githubusercontent.com/wp-cli/builds/gh-pages/phar/wp-cli.phar
mkdir -p /root/bin
ln -s /root/.wp-cli/wp-cli.phar /root/bin/wp
chown [[root:root]] -R /root/.wp-cli
find /root/.wp-cli -type d -exec chmod 0700 {} \;
find /root/.wp-cli -type f -exec chmod 0600 {} \;
chmod 0700 /root/.wp-cli/wp-cli.phar

1. 1. 1. added '/root/.wp-cli' to php.ini & restarted apache
    2. successfully updated akismet with wp-cli

[root@hetzner2 .wp-cli]# wp plugin --path=/var/www/html/obi/htdocs list
...
+----------------------------+----------+-----------+---------+
| name                       | status   | update    | version |
+----------------------------+----------+-----------+---------+
| akismet                    | inactive | available | 3.3     |
| amr-shortcode-any-widget   | active   | available | 3.3     |
| be-themes-one-click-import | active   | none      | 1.6     |
| be-page-builder            | active   | none      | 4.6.1   |
| be-portfolio-post          | active   | none      | 1.1     |
| duplicate-page             | active   | available | 2.2     |
| hello                      | inactive | none      | 1.6     |
| masterslider               | active   | none      | 2.29.0  |
| meta-box-conditional-logic | active   | none      | 1.0.8   |
| meta-box-show-hide         | active   | none      | 0.2.1   |
| meta-box-tabs              | active   | none      | 0.1.5   |
| oa-open-graph-for-fb       | active   | none      | 1.0.2   |
| open-in-new-window-plugin  | active   | none      | 2.4     |
| password-protected         | inactive | none      | 2.0.3   |
| simple-301-redirects       | inactive | none      | 1.07    |
| revslider                  | active   | none      | 5.2.5   |
| wordpress-importer         | active   | none      | 0.6.3   |
+----------------------------+----------+-----------+---------+
[root@hetzner2 .wp-cli]# wp plugin --path=/var/www/html/obi/htdocs update akismet
...
Downloading update from https://downloads.wordpress.org/plugin/akismet.3.3.4.zip...
Unpacking the update...
Installing the latest version...
Removing the old version of the plugin...
Plugin updated successfully.
Success: Updated 1 of 1 plugins.
+---------+-------------+-------------+---------+
| name    | old_version | new_version | status  |
+---------+-------------+-------------+---------+
| akismet | 3.3         | 3.3.4       | Updated |
+---------+-------------+-------------+---------+
[root@hetzner2 .wp-cli]# wp plugin --path=/var/www/html/obi/htdocs list
...
+----------------------------+----------+-----------+---------+
| name                       | status   | update    | version |
+----------------------------+----------+-----------+---------+
| akismet                    | inactive | none      | 3.3.4   |
| amr-shortcode-any-widget   | active   | available | 3.3     |
| be-themes-one-click-import | active   | none      | 1.6     |
| be-page-builder            | active   | none      | 4.6.1   |
| be-portfolio-post          | active   | none      | 1.1     |
| duplicate-page             | active   | available | 2.2     |
| hello                      | inactive | none      | 1.6     |
| masterslider               | active   | none      | 2.29.0  |
| meta-box-conditional-logic | active   | none      | 1.0.8   |
| meta-box-show-hide         | active   | none      | 0.2.1   |
| meta-box-tabs              | active   | none      | 0.1.5   |
| oa-open-graph-for-fb       | active   | none      | 1.0.2   |
| open-in-new-window-plugin  | active   | none      | 2.4     |
| password-protected         | inactive | none      | 2.0.3   |
| simple-301-redirects       | inactive | none      | 1.07    |
| revslider                  | active   | none      | 5.2.5   |
| wordpress-importer         | active   | none      | 0.6.3   |
+----------------------------+----------+-----------+---------+

1. 1. 1. I skipped a ton of WARNINGs that wp-cli yells about. But it works regardless.
    2. updated the remaining plugins:

[root@hetzner2 .wp-cli]# wp plugin --path=/var/www/html/obi2/htdocs update --all
...
Success: Updated 3 of 3 plugins.
+--------------------------+-------------+-------------+---------+
| name                     | old_version | new_version | status  |
+--------------------------+-------------+-------------+---------+
| akismet                  | 3.3.3       | 3.3.4       | Updated |
| amr-shortcode-any-widget | 3.3         | 3.6         | Updated |
| duplicate-page.bak       | 2.2         | 2.3         | Updated |
+--------------------------+-------------+-------------+---------+

1. 1. 1. updated themes with wp-cli

[root@hetzner2 .wp-cli]# wp --path=/var/www/html/obi2/htdocs theme update --all
+-------+-------------+-------------+---------+
| name  | old_version | new_version | status  |
+-------+-------------+-------------+---------+
| oshin | 4.3.1       | 5.0.5       | Updated |
+-------+-------------+-------------+---------+

then I read the documentation, and found that they tell you not to run it as root. https://make.wordpress.org/cli/handbook/common-issues/#error-yikes-it-looks-like-youre-running-this-as-root
this is a good read. since our php is hardened (for now), bellwood's stance matches mine. in any case, it's a bad idea to run the wp-cli as root. So I'll set it up (and document it) using a non-root user https://github.com/wp-cli/wp-cli/pull/973
added root to sudoers `gpasswd -a root wheel`
created a new group 'apache-admins', which is only for users who need to be able to update files with passwords like 'wp-config.php'. Other users can be added to the group 'apache' and they will only be able to view files that don't contain passwords. Users who are added to 'apache-admins' should also be added to the group 'apache'

groupadd apache-admins
chown [[apache:apache-admins]] /var/www/html/obi2/wp-config.php
chmod 0440 /var/www/html/obi2/wp-config.php
gpasswd -a cmota apache-admins
gpasswd -a maltfield apache-admins
gpasswd -a marcin apache-admins
gpasswd -a crupp apache-admins
gpasswd -a tgriffing apache-admins

also giving write access to users in the apache group to the wp-content dir

find /$HOME/.wp-cli -type d -exec chmod 0770 {} \;
find /$HOME/.wp-cli -type f -exec chmod 0660 {} \;

also added documentation on how to set the correct permssions in a wordpress docroot to the Wordpress article http://opensourceecology.org/wiki/Wordpress#Proper_File.2FDirectory_Ownership_.26_Permissions

installed wp-cli for a user = 'wp'

useradd wp
gpasswd -a wp apache
gpasswd -a wp apache-admins
su - wp
mkdir -p /$HOME/.wp-cli
cd /$HOME/.wp-cli
curl -O https://raw.githubusercontent.com/wp-cli/builds/gh-pages/phar/wp-cli.phar
mkdir -p /$HOME/bin
ln -s /$HOME/.wp-cli/wp-cli.phar /$HOME/bin/wp
chown wp:wp -R /$HOME/.wp-cli
find /$HOME/.wp-cli -type d -exec chmod 0700 {} \;
find /$HOME/.wp-cli -type f -exec chmod 0600 {} \;
chmod 0700 /$HOME/.wp-cli/wp-cli.phar

updated again using user wp = the right way

[root@hetzner2 ~]# sudo -u wp -i wp plugin --path=/var/www/html/obi2/htdocs list
...
+----------------------------+----------+-----------+---------+
| name                       | status   | update    | version |
+----------------------------+----------+-----------+---------+
| akismet                    | inactive | none      | 3.3.4  |
|----------------------------|----------|-----------|--------|
| amr-shortcode-any-widget   | active   | available | 3.3    |
| be-themes-one-click-import | active   | none      | 1.6    |
| be-page-builder            | active   | none      | 4.6.1  |
| be-portfolio-post          | active   | none      | 1.1    |
| duplicate-page             | active   | available | 2.2    |
| hello                      | inactive | none      | 1.6    |
| masterslider               | active   | none      | 2.29.0 |
| meta-box-conditional-logic | active   | none      | 1.0.8  |
| meta-box-show-hide         | active   | none      | 0.2.1  |
| meta-box-tabs              | active   | none      | 0.1.5  |
| oa-open-graph-for-fb       | active   | none      | 1.0.2  |
| open-in-new-window-plugin  | active   | none      | 2.4    |
| password-protected         | inactive | none      | 2.0.3  |
| simple-301-redirects       | inactive | none      | 1.07   |
| revslider                  | active   | none      | 5.2.5  |
| wordpress-importer         | active   | none      | 0.6.3  |
+----------------------------+----------+-----------+---------+

successfully updated plugins with wp-cli running under the user 'wp'

sudo -u wp -i wp plugin --path=/var/www/html/obi2/htdocs update --all
...
Success: Updated 2 of 2 plugins.
+--------------------------+-------------+-------------+---------+
| name                     | old_version | new_version | status  |
+--------------------------+-------------+-------------+---------+
| amr-shortcode-any-widget | 3.3         | 3.6         | Updated |
| duplicate-page           | 2.2         | 2.3         | Updated |
+--------------------------+-------------+-------------+---------+

attempted to do an update of the wordpress core, but it appears that wp-cli necessarily needs access to '/tmp/'. This directly is necessarily insecure with 777 permissions, so it should never be included in the basedir of php.ini. Therefore, we'll just stick to using svn to update the core wp software, and just use wp-cli for updating plugins & themes

Fri Aug 25, 2017

troubleshooting site-down alerts for oswh & obi
1. both oswh & obi (which are located on distinct servers) went down. I confirmed the sites were inaccessible in the browser
2. in fact, the dns wouldn't resolve to an ip address. I confirmed this on opendns & google nameservers from both my lan in NYC and from my personal server in the Neatherlands
3. within a few hours, the ip address was being returned on dns lookup, and the sites came back online (not at the same time)

Tue Aug 15, 2017

dialog with Chris on php, mod_security, & htaccess settings
discovered that the WP-Piwik plugin had a major XSS vulnerability that allowed anyone to persistently insert arbitrary JS code into all WP pages without any authorization. Asked Chris to investigate if the issue was resolved, and--if not--what's the best way to integrate piwik into wordpress https://www.pluginvulnerabilities.com/tag/wp-piwik/
discovered that piwik talks a lot about security, but I found many red flags that smell like bullshit
1. their site requires ini_set to be enabled in php. Many people have complained about the security implications of this, and the piwik response was "won't fix" https://forum.piwik.org/t/workaround-possible-for-ini-set/6569/6
2. piwik has mentioned that they've received many independent security audits from professionals in the past, but they don't include the reports from the audits! they claim things were fixed, but how can we know without transparency? I specifically want to see what the auditors said about ini_set, and other things. I've asked Chris to post in the forum for a link to the reports from the audit. If they can't or won't produce it to the public, that's a huge red flag. https://piwik.org/blog/2011/01/professional-security-audit-in-piwik/
Chris mentioned that obi is throwing forbidden messages
1. all the failed requests seem to occur to content referenced over ip address rather than openbuildgininstitute.org
2. mod_security doesn't like it when people reference content over ip address
3. regardless of mod_security, it's not very robust to have an ip address hard-coded into your content's links
4. I decided the solution is to replace all occurrences of '138.201.84.223' with the domain 'openbuildinginstitute.org'
5. I confirmed that the settings in the wp admin page defined wordpress address & site address by domain name, not ip address
6. I dug deeper & searched all of the wp_options table, and confirmed only 1 reference to the ip address, which was in regard to the (now deprecated) ftp credentials, which is unrelated
7. I decided that this must be done with a mysqldump backup -> cp -> sed -> db delete -> db import -> verify
  1. While this is annoying, it's our best option. And it is good practice, as it will probably be necessary to sed s/http/https/ in the near future
8. emailed catarina for dns info & credentials

dbName=obi_db
dbUser=obi_user
 dbPass=CHANGEME

fromString=138.201.84.223
toString=openbuildinginstitute.org

stamp=`date +%Y%m%d_%T`
tmpDir=/var/tmp/dbChange.$stamp
mkdir $tmpDir
chown root:root $tmpDir
chmod 0700 $tmpDir
pushd $tmpDir
service httpd stop

# create backup of everything for good measure
time nice mysqldump -uroot -p --all-databases | gzip -c > preBackup.all_databases.$stamp.sql.gz

# dump obi wordpress db contents
 time nice mysqldump -u$dbUser -p$dbPass --database $dbName > $dbName.$stamp.sql

# make backup
cp $dbName.$stamp.sql $dbName.$stamp.sql.orig

# sed
sed -i "s/$fromString/$toString/g" $dbName.$stamp.sql

# verify
grep "$fromString" $dbName.$stamp.sql | less
grep "$toString" obi_db.$stamp.sql | less

# delete db tables
 mysql -u$dbUser -p$dbPass $dbName -sNe 'show tables' | while read table; do mysql -u$dbUser -p$dbPass -sNe "DROP TABLE $dbName.$table;"; done

# verify
 mysql -u$dbUser -p$dbPass $dbName -sNe 'show tables' 

# import
 mysql -u$dbUser -p$dbPass < obi_db.$stamp.sql

# verify
 mysql -u$dbUser -p$dbPass $dbName -sNe 'show tables' 
service httpd start

successfully finished the above change. verified site & absence of forbidden messages with firefox debugger.
gained access to robot for managing hetzner2
1. added creds to keepass
2. confirmed that I have access to order an additional ip address, which will be needed for 2x seperate domains over https on 1 server over port 443
3. added my email address to the "Main addresses" field
4. saw that hetzner sells a package of 2x the servers we currently have as a "private cloud" option using openstack. This may be the best way to scale (while remaining powered by renewable energy) when needed, maybe. https://www.hetzner.com/cloud/private-cloud
installed subversion
began creating a clone of obi2 as a test for upgrading all its software
1. new dir installs wordpress from subversion to make updates easier
2. created obi2.conf vhost from obi.conf
3. added "<LocationMatch .*\.(svn|git|hg|bzr|cvs|ht)/.*> Deny From All </LocationMatch>" to obi2 vhost

oldVhostDir=/var/www/html/obi
newVhostDir=/var/www/html/obi2
oldDbName=obi_db
newDbName=obi2_db
newDbUser=obi2_user
 newDbPass=CHANGEME

mkdir -p $newVhostDir/htdocs
pushd $newVhostDir/htdocs

yum install subversion
svn co https://core.svn.wordpress.org/tags/4.8.1 .

find $newVhostDir -type d -exec chmod 750 {} \;
find $newVhostDIr -type f -exec chmod 640 {} \;
chown -R apache:apache $newVhostDir

stamp=`date +%Y%m%d_%T`
tmpDir=/var/tmp/dbChange.$stamp
mkdir $tmpDir
chown root:root $tmpDir
chmod 0700 $tmpDir
pushd $tmpDir
 time nice mysqldump -uroot -p --all-databases | gzip -c > preBackup.all_databases.$stamp.sql.gz
 time nice mysqldump -uroot -p --databases $oldDbName > $oldDbName.$stamp.sql
cp $oldDbName.$stamp.sql $newDbName.$stamp.sql

# replace the first 2 (non-comment) occurances of $OldDbName with $newDbName
vim $newDbName.$stamp.sql
 
time nice mysql -uroot -p -sNe "CREATE DATABASE $newDbName; USE $newDbName; SOURCE $oldDbName.$stamp.sql;"
 time nice mysql -uroot -p -sNe "GRANT ALL ON $newDbName.* TO '$newDbUser'@'localhost' IDENTIFIED BY '$newDbPass'; FLUSH PRIVILEGES;"
popd

rsync -av --progress $oldVhostDir/wp-config.php $newVhostDir/
chown apache:apache $newVhostDir/wp-config.php
chmod 400 $newVhostDir/wp-config.php

# change DB_NAME, DB_USER, DB_PASSWORD, WP_HOME, & WP_SITEURL
vim $newVhostDir/wp-config.php

# we want to copy files that don't exist yet in our new install dir. if a file exists in both, don't overwrite the new from the old
rsync -av --progress --ignore-existing $oldVhostDir/htdocs/wp-content/ $newVhostDir/htdocs/wp-content/
rsync -av --progress $oldVhostDir/htdocs/.htaccess $newVhostDir/htdocs/

attempted to log into new obi clone with updated wordpress
1. after login, I was prompted to click a button to update the db, which I did. It completed in seconds.
everything apeared to be working on the obi clone
1. except just loading the domain directly 301 redirects--though index.php loads fine
sent an email to Catarina for initial validation before I attempt to update plugins & themes

Mon Aug 14, 2017

began investigating why the wiki became locked from edits
1. "Warning: The database has been locked for maintenance, so you will not be able to save your edits right now. You may wish to copy and paste your text into a text file and save it for later...The administrator who locked it offered this explanation: Wiki backup in progress. Access should be restored in about 5 minutes"
2. successfully connected to the DB locally
3. got a permission error when I attempted to load Special:UnlockDB "You are not allowed to execute the action you have requested. "
4. couldn't immediately find any backup-related extensions to mediawiki that may have triggered the lock
5. found a file 'read-only-message' in the docroot
6. after finding no safer way to restore the wiki to write-enabled mode, I renamed 'read-only-message' to 'read-only-message.20170814.bak'
7. I confirmed an edit
8. I saw the last edit was just under 3 days ago
9. I emailed the OSE Devs
10. I emailed Marcin for access to Special:UnlockDB
11. I emailed Jozef for access to the OSE Dev contact list
12. I emailed Tom to ask if he was aware of any mediaiwki-related backup processes or otherwise that would have triggered the db lock

Fri, Aug 11, 2017

commented-out monitoring of kern.log (I got nearly 1,000 ossec alerts since enabling them almost a week ago
added /var/log/kern.log, which was 42M since its first entry less than a month ago
updated common log format for apache to track sessionid & time to generate
confirmed that ossec is now reporting diffs in alerts
installed mod_evasive
confirmed that mod_evasive is running & actively blocking DOS attacks (or at least attempting to)
disabled webdav by commenting out all lines in /etc/httpd/conf.modules.d/00-dav.conf
commented-out mod_info in /etc/httpd/conf.modules.d/00-base.conf
moved mod_security overrides to the vhost file (/etc/httpd/conf.d/obi.conf) by Location match (where needed)
removed file /etc/httpd/modsecurity.d/modsecurity_crs_00_config.conf
finished apache hardening (minus changes needed for https & related hardening)
added documentation on mod_security to the OSE_Server page
mysql (mariadb) hardening
1. added 'skip-networking', 'skip-show-database', and 'local-infile=0' to /etc/my.cnf
2. changed DB_HOST of oib/wp-config.php to 'localhost:/var/lib/mysql/mysql.sock'
3. reset root password to a good, long, random passphrase (and updated keepass)
4. updated /root/backups/backup.settings with the new mysql root password
5. dropped unnecessary users: osemain, osewiki, & osewiki_w & unnecssary hosts '127.0.0.1' & '::1' & 'centos-72-64-minimal'. Only 2 entries remain: 'root'@'localhost' & 'wordpressuser'@'localhost'
began hardening obi
1. researched ~10 2FA/OTP wordpress plugins
  1. best option is probably 'google-authenticator' https://wordpress.org/plugins/google-authenticator
    1. this doesn't support forcing users to use 2FA, but this can be achieved with this additional plugin https://wordpress.org/plugins/google-authenticator-encourage-user-activation/
2. defined all salts in wp-config.php. Passwords in the db will only be updated when a user logs-in again. We'll force this to happen by resetting everyone's password once https is implemented.
3. set DISALLOW_FILE_EDIT in wp-config.php
4. renamed wordpress db & created new user

sudo su -

oldDbName=wordpress
newDbName=obi_db
oldUsername=wordpressuser
newUser=obi_user
newPass=CHANGEME
 rootDbPass=CHANGEME

stamp=`date +%Y%m%d_%T`
tmpDir=/var/tmp/dbChange.$stamp
mkdir $tmpDir
chown root:root $tmpDir
chmod 0700 $tmpDir
pushd $tmpDir
service httpd stop

# create backup
time nice mysqldump -uroot -p --all-databases | gzip -c > preBackup.all_databases.sql.gz

# create new db
echo "CREATE DATABASE $newDbName; GRANT ALL PRIVILEGES ON $newDbName.* TO '$newUser'@'localhost' IDENTIFIED BY '$newPass'; FLUSH PRIVILEGES;" | mysql -uroot -p$rootDbPass

# move tables from old db to new db
 mysql -uroot -p$rootDbPass $oldDbName -sNe 'show tables' | while read table; do mysql -uroot -p$rootDbPass -sNe "RENAME TABLE $oldDbName.$table TO $newDbName.$table;"; done

# update the wp-config.php file & start httpd

1. verified that the site was working, then deleted the old user

DROP USER 'wordpressuser'@'localhost';

Tue, Aug 08, 2017

keepass training meeting with Marcin & Christian

Sat, Aug 05, 2017

added marcin to recieve monthly statuscake reports
isolated the cookie obi issue to the "Secure" flag, so I enabled it to be just "HttpOnly" to provide some XSS protection, until we can enable the original line after implementing https
installed git on hetzner2
installed mod_security_crs
added '/etc/httpd/modesecurity.d/modsecurity_crs_00_config.conf' to disable several rules, which were triggering false positives.
added ossec local rule to calm down alerts of mod_security
added /var/log/cron to ossec
added /var/log/kern.log to ossec
added ossec local rule to ignore alerts on kern.log for IN=eth0. We should get an alert if something tries to get out, but attempts to get in are unavoidably continuious
1. also ignored OUT=lo; not sure why this is happening, but I don't think it's necessary
added report_changes="yes" and realtime="yes" to the directories listed undersyscheck in /var/ossec/etc/ossec.conf
updated log_format of mariadb logfile to 'mysql_log'

Fri, Aug 04, 2017

confirmed that hetzner1's daily backups dropped from 39G to 14G starting on 2017-08-03 due to the cleanup from 08-02
hardened keepas file
1. now has a randomly generated 100-character password
2. also requires the 4 KiB key file
3. increased transformation rounds to 87654321, which takes about 5 seconds on my 2-year-old laptop
4. moved to /etc/keepass, which is owned by root:keepass 770. The files inside are root:keepass 660.
emailed Marcin & Christian for keepass hand-off/training session

Thr, Aug 03, 2017

generated a 4 KiB key file for passwords keepass file
1. discovered that dd will take an undeterministic size from /dev/random, regardless of bs & count. used head instead
2. `head -c 4096 < /dev/random > ose.passwords.key`

Wed, Aug 02, 2017

confirmed the hetzner1 2017-08-01 backup of 39G is on backup server
created /usr/home/osemain/noBackup on hetzner1
added "--exclude /usr/home/osemain/noBackup" to backup.sh on hetzner1 to reduce nightly backup size from 39G to 12G
fixed wp-login.php cookie issue preventing logins on obi
1. ERROR: Cookies are blocked or not supported by your browser. You must enable cookies to use WordPress
2. fix was commenting-out "Header edit Set-Cookie ^(.*)$ $1;HttpOnly;Secure" in /etc/httpd/conf/httpd.conf

Tue, Aug 01, 2017

meeting with Lex
sent Marcin my signed Liability Waiver for Aug 25 workshop

Mon Jul 31, 2017

moved obi's wp-config.php up 1 dir to be outside the docroot & chmod to 400
begun debugging oib wp-login issues (cookie error)
1. confirmed that I cannot login
2. did not see any error logs
3. began reverting all session-related hardening changes to php.ini

Sat Jul 29, 2017

multi-user keepass research

Fri Jul 28, 2017

added christian to statuscake admin contact group
emailed christian & marcin about gpg key creation for recieving ossec alerts

Thr Jul 27, 2017

email to Devs about new github accounts for forking filament winder
created account for christian on hetzner2

Thr Jul 20, 2017

further docker research
dialog with Lex about docker
meeting with Christian about sysadmin assistance

Wed Jul 19, 2017

keepass research
preparing mediawiki file dump for Lex
container research

Tue Jul 18, 2017

emailed with Marcin about containers
emailed Lex asking for his ssh public key
1. created user on hetzner 2
2. added user to sshaccess
3. added Lex's new public key
4. uploaded last night's dump of the wiki to /var/tmp/ with a symlink in his $HOME per Marcin's request
confirmed that "fake" backup "from" 2017-07-01 of hetzner1 was not deleted, so it's safe to make changes to hetzner1 now
moved 27G of assumed-unnecessary files from hetzner1 into '/usr/home/osemain/deleteMeIn2018/'
1. if nothing is reported as broken by 2017-08-02, I'll validate that the files were included in the first-of-the-month August backup, then add an "exclude" argument to the tarball creation in 'backup.sh' for 'deleteMeIn2018'. And I will not be migrating these files to hetzner2.
finishing php hardening
1. changed upload_tmp_dir to '/var/lib/php/tmp_upload' per best practices
2. added upload_tmp_dir to basedir
3. set session.use_strict_mode = 1
4. set session.cookie_httponly = 1
5. changed PHPSESSID to custom value (session.name)
6. changed session.save_path to "/var/lib/php/session"
7. set the session.hash_function to 'sha512'
8. disable_functions = ini_set,php_uname,getmyuid,getmypid,passthru,leak,listen,diskfreespace,tmpfile,link,ignore_user_abord,shell_exec,dl,set_time_limit,exec,system,highlight_file,source,show_source,fpaththru,virtual,posix_ctermid,posix_getcwd,posix_getegid,posix_geteuid,posix_getgid,posix_getgrgid,posix_getgrnam,posix_getgroups,posix_getlogin,posix_getpgid,posix_getpgrp,posix_getpid,posix,_getppid,posix_getpwnam,posix_getpwuid,posix_getrlimit,posix_getsid,posix_getuid,posix_isatty,posix_kill,posix_mkfifo,posix_setegid,posix_seteuid,posix_setgid,posix_setpgid,posix_setsid,posix_setuid,posix_times,posix_ttyname,posix_uname,proc_open,proc_close,proc_get_status,proc_nice,proc_terminate,phpinfo,popen,curl_exec,curl_multi_exec,parse_ini_file,allow_url_fopen,allow_url_include,pcntl_exec,chgrp,chmod,chown,lchgrp,lchown,putenv
9. soap.wsdl_cache_dir = /var/lib/php/soap_cache
began hardening apache
1. reset permissions of files:
  1. chown -R apache:apache /var/www/html
  2. find /var/www/html/ -type d -exec chmod 750 {} \;
  3. find /var/www/html/ -type f -exec chmod 640 {} \;
2. added ServerTokens Prod
3. added ServerSignature Off
4. removed DocumentRoot from the main /etc/httpd/conf/httpd.conf since we're using vhosts
  1. removed cooresponding Directory block
5. added 'Options -Indexes -Includes' to all Directory blocks
6. added "Order allow,deny/nAllow from all" to all Directory blocks
7. set main "Directory /" block to only contain "Options -Indexes -Includes\nAllowOverride none"
8. added 'FileETag None' option
9. added "TraceEnable off" option
10. recursively changed ownership of all files in /var/www/html/obi/htdocs to 'apache:apache'
  1. changed wp-config.php back to 'root:root'
11. recursively changed ownership of '/etc/httpd/conf' & '/etc/httpd/conf.d' to apache:apache & set permissions to 750 (from root:root & 755)
  1. recursively changed permissions to 640 within the above dirs
12. added LimitExcept block to deny all requests other than the 3 basic: GET, POST, & HEAD for all vhost Directory blocks
13. added "Header edit Set-Cookie ^(.*)$ $1;HttpOnly;Secure" for XSS protection
14. added "Header always append X-Frame-Options SAMEORIGIN" for clickjacking protection
15. added mod_rewrite rules to disable HTTP 1.0 to avoid session hijacking risks
16. decreased apache timeout to 60 (default is 300) to decrease DoS risk
17. installed mod_security

Mon Jul 17, 2017

validated with Marcin that obi's content saves are now functioning, following my php.ini changes from yesterday

Sun Jul 16, 2017

Recieved an email from marcin describing issues adding content to OBI wordpress
grepped through /var/log/error_log & found issues with php temp dir & max_input_vars due to recent php hardening that may be breaking wordpress changes
1. relevant errors:
  1. [Sun Jul 16 04:34:18.532015 2017] [:error] [pid 21569] [client 184.157.59.85:45406] PHP Warning: Unknown: Input variables exceeded 100. To increase the limit change max_input_vars in php.ini. in Unknown on line 0, referer: http://openbuildinginstitute.org/wp-admin/post.php?post=4509&action=edit
  2. [Sun Jul 16 07:11:02.922720 2017] [:error] [pid 24088] [client 184.157.59.85:49642] PHP Warning: File upload error - unable to create a temporary file in Unknown on line 0, referer: http://openbuildinginstitute.org/wp-admin/post.php?post=4531&action=edit
  3. [Sun Jul 16 07:11:35.723209 2017] [:error] [pid 23510] [client 184.157.59.85:49646] PHP Warning: Unknown: open_basedir restriction in effect. File(/tmp) is not within the allowed path(s): (/var/www/html/obi/:/var/www/html/osemain) in Unknown on line 0, referer: http://openbuildinginstitute.org/wp-admin/post.php?post=4531&action=edit
2. changed upload_tmp_dir to '/var/lib/php/tmp_upload' per best practices (currently it's '/tmp')
3. set the new tmp_upload dir to root:apache 770, unlike '/tmp' which is necessarily 777
4. increased max_input_vars back to 1000, the default
emailed with marcin about configuring his mail client with gpg for ossec reports
statuscake started marking oswh as "recovered" at 04:08 ET, after a 9-hour false-negative streak. I confirmed that they have not responded to my support question about the issue.

Sat Jul 15, 2017

email exchange with marcin about security limitations
investigating statuscake's false-negative "outage" of opensourcewarehouse.org
1. confirmed that 200 OK was returned with `curl -I "http://www.opensourcewarehouse.org/"`
2. sent statuscake a support message about the false-negative

Fri Jul 14, 2017

Got Christian's ssh pubkey & asked for availability for a meeting
Got credentials for themeforest, but was unable to login due to a shitty 2FA over email
Asked Catarina to login to themeforest & send me the osemain theme's "Item Purchase Code" so that I can register an account with the theme creator's forum https://support.livemeshthemes.com/wp-login.php?action=register
gained access to themeforest & downloaded all necessary files
fixed osemain wp theme issue
1. I downloaded the theme archive from themeforest
2. I scp'd the archive to /usr/home/osemain/enigmaticTheme/20170714/themeforest-3919108-enigmatic-responsive-multipurpose-wp-theme.zip
3. I extracted the archive & the archive within named enigmatic.zip to /usr/home/osemain/enigmaticTheme/20170714/themeforest-3919108-enigmatic-responsive-multipurpose-wp-theme/enigmatic
4. I changed directory to /usr/home/osemain/public_html/wp-content/themes
5. I changed the current directory & the 'enigmatic' directory's permissions to '755'
6. `mv enigmatic enigmatic.20170714.bak` && `rsync -av --progress ~/enigmaticTheme/20170714/themeforest-3919108-enigmatic-responsive-multipurpose-wp-theme/enigmatic .`
7. I emailed Marcin for validation of the changes
created a user on osemain for Tom

Thr Jul 13, 2017

fixed migration oversite that broke all the obi pages but the main page
1. new docroot was missing /var/log/http/obi/htdocs/.htaccess, which included mod_rewrite rules
emailed Christian asking for portfolio & CV for risk analysis before user creation
manually added an admin user for myself to the osemain wp db (ose_website) as uid = 55 using SQL commands directly
login attempt produced a fatal php error:
1. Fatal error: Call to undefined function ot_register_meta_box() in /usr/www/users/osemain/wp-content/themes/enigmatic/framework/presentation/metabox-manager.php on line 34
created new user 'osemain' on mysql db of hetzner2 & database 'osemain' per the wordpress install guide
copied last night's backup of the osemain db's mysqldump sql file to hetzner2 from backup server
copied public_html from backup server to hetzner2 & extracted to /var/www/html/osemain.old/htdocs/
created vhost at /etc/httpd/conf.d/osemain.conf
manually reset theem through mysql to 'twentyeleven'
successfully logged-in to osemain on the new server under new theme
1. confirmed wp is currently 4.7.5 on osemain

Tue Jul 11, 2017

verified that the cleanup of files on hetzner2 reduced the daily backup size from 20G to 1.3G
1. The dir 'ose_wiki' (16G) & file w.tar.gz (9.4G) had been moved to /var/tmp/deleteMeIn2018/ a few days ago
confirmed that ossec has been actively blocking abusive ip addresses
1. <active-response> is configured to block these ip addresses via host-deny & iptables for 10 minutes
2. logs are found in /var/ossec/logs/active-responses.log
3. I was able to ban myself by failing ssh attempt 10 times in a row
organized httpd vhost as prereq to harden php
1. created /etc/httpd/conf.d/obi.conf
2. moved all files from /var/www/html to /var/www/html/obi/htdocs/
  1. soon obi-related files that should be stored outside the docroot for security reasons can be stored in the 'obi' dir, but the docroot will be 'htdocs' ie: wp-config.php!
hardened php
1. disabled allow_url_fopen
2. reduced max_input_time & max_execution_time to 30 seconds
3. disabled expose_php
4. decreased max_input_vars to 100
5. decreased post_max_size & upload_max_filesize to 10M
6. added open_basedir to whitelist the php dir "/var/www/html/obi/"
researched our https options with multiple domain names running as vhosts on a single server
1. sent an emal to Marcin & Catarina asking if they'd be interested in consolidating to a single domain with many subdomains to simplify the costs & config
2. discovered that Hetzner charges € 0.84 / month for each additional ip address, which is our best non-consolidated option, as there's client support issues with SNI & running on separate ports is unpractical
3. research shows that ~10% of internet users do *not* support SNI as of last year (2016)
4. Catarina said she wants separate domains, and she said she'd cover the 10 eur/yr fee

Mon Jul 10, 2017

installed make, gcc, gcc-c++, kernel-devs
installed & started ossec service
1. added config to monitor mariadb logs
installed & started postfix service
added TMOUT=14400 to '/root/.bash_profile' to force all root logins to timeout after 4 hours
configured ossec to send pgp-encrypted emails
1. installed procmail & mailx
2. configured mailbox_command in /etc/postfix/main.cf
3. imported my gpg public key to keyring @ /var/ossec/.gnupg/
4. created /var/ossec/.procmailrc
5. created script /var/ossec/sent_encrypted_alarm.sh
emailed Catarina & gained admin access to OBI WP
receieved many OSSEC alerts regarding brute-force attempts on wp-login.php from a distributed set of IPs
1. researched wp-login.php hardning options, including 2FA, captcha, rate limiting plugins, and ossec active response iptables ip address blocking. I'm thinking we'll do all except the captcha, especially if 2FA can be enforced for all users.

Sun Jul 09, 2017

discovered that hezner2 has 2x 250G disks: sda & sdb
1. each disk has 3 partitions of exactly the same size, and is RAID1'd between their cooresponding partition numbers on each disk
  1. 1 = 34.4G
  2. 2 = 537M
  3. 215G
2. RAID config is as follows:
  1. /dev/md0 = 34.3G = swap
  2. /dev/md1 = 536M = ext3. mounted at '/boot'. filesystem features includes has_journal.
  3. /dev/md2 = 215G = ext4. mounted at '/'. `df` total size is listed as 197G. flesystem features includes has_journal.
3. currently, 83G are in-use out of 197G
reset the root password on hetzner2. added new credential to keepass.
updated all packages (210) with `yum update`
hardened sshd_config
1. set 'PermitRootLogin no'
2. set 'PasswordAuthentication no'
3. set 'PermitEmptyPasswords no'
4. set 'IgnoreRhosts yes'
5. set 'AllowGroups sshaccess'
  1. created group 'sshaccess' & added all 4 users
added documentation to OSE_Server on how to add new users with ssh access to the server
created new, hardened ssh host keys
1. `ssh-keygen -f /etc/ssh/ssh_host_rsa_key -t rsa -b 4096 -o -a 100 -N `
2. `ssh-keygen -f /etc/ssh/ssh_host_ecdsa_key -t ecdsa -b 521 -o -a 100 -N `
3. `ssh-keygen -f /etc/ssh/ssh_host_ed25519_key -t ed25519 -a 100 -N `
moved some large, seemingly unnecessary files from /var/www/html/ to /var/tmp/deleteMeIn2018/ to reduce backup sizes
updated logrotate config to decrease backup sizes
1. added 'compress' & 'delaycompress' to /etc/logrotate.conf
2. changed default rotation from 'weekly' to 'daily' & rotate from '4' to '32' to keep the same amount of data locally, but now compressed daily (per above)
3. added /etc/logrotate.d/backups for our new backup log
updated the hostname of hetzner2 to 'hetzner2.opensourceecology.org' in /etc/hosts, /etc/hostname, and using `hostnamectl`
set timezone to UTC with `timedatectl set-timezone UTC`
restarted rsyslog & confirmed hostname & date updates got pushed to /var/log/secure
replaced firewalld with iptables
configured iptables
1. apache & mysql users cannot send traffic out unless it's already established or dns
2. no traffic can come in over eth0 unless it's over ssh or http or established

Sat Jul 08, 2017

researching GPL & BSD license info
got an email from statuscake that the site was down, verified that hitting opensourceecology.org in my browser lead tme to the cloudflare error page. so confirmed that statuscake works for email alerts & outages even when using cloud flare's "always on" cache
added status.opensourceecology.org CNAME to statuscake.com
added open building institue to statuscake
added documentation to OSE Server about statuscake
emailed OSE Devs, providing them with the statuscake URL & asking if anything else should be monitored that I missed
created fake backup '20170701' from June 5th's backup as a long-term backup that won't be deleted by the cleanup script (the 1st of every month is normally kept, but Hetzner brought our site down, so backups were missing from the 1st through 3rd) by setting the mtime of the files with 'find . -type f | xargs touch -d '20170701'
Added 'keepass solution' to TODO on OSE Server

Fri Jul 07, 2017

researching OSE wiki's contents for Licensing info

Thr Jul 06, 2017

created email statuscake at opensourceecology.org & added credentials to keepass
created statuscake & added crednetials to keepass
meeting with marcin, who now has a password-protected ssh keypair that is verified to work with both ssh & filezilla (sftp)
configured (untested) statuscake basic GET checks with a public reporting page https://uptime.statuscake.com/?TestID=itmHX7Pfj2

Tue Jul 04, 2017

SSH is now working to Hetzner 1, and I validated that backups are running again
1. The last backup before the outage that still existed on dreamhost was from 2017-06-30. I made a copy of this outside the 'backups/hetzner1/' dir in $HOME so it's preserved (not deleted by the cron) just in case
2. The first backup we have after the outage is from today, 2017-07-04. This started automatically, indicating that the site was offline at 07:20 for the daily backup for 3 days in a row. That's pretty awful service from Hetzner!
Verified that my email address was added to the admin/techincal emails list on the hetzner1 web console
Submitted a support request asking for [1] an RCA of the outage they caused [2] a way to communicate with Hetzner that has confidentiality [3] access to "the Robot"
Added Tom's ssh key to hetzner1's authorized_keys file
Emailed Marcin how I can create a new Gapps Email account as prereq to signing up for statuscake

Mon Jul 03, 2017

Site came back online with no word from hetzner, but we still cannot ssh in.
Hetzner support is still unresponsive to my numerous emails
Spent some time researching hetzner alternatives that use 100% renewable energy & offer dedicated servers for comparable prices

Sun Jul 02, 2017

The site was still down this morning, and hetzner 1 was still inaccessible. No word from Marcin
1. I sent an email to support@heztner.de asking if our server was offline, indicating that we lost it about the time of their maintenance window
2. I logged into cloudflare, and saw that opensourceecology.org points to '78.46.3.178'. Note that this is distinct from `dig` results, as the cloudflare service is essentially a MITM
  1. could not ping or ssh into 78.46.3.178
  2. determined that our old 'dedi978.your-server.de` dns host resolves to the above-listed IP address. That whold endpoint is insaccessible. I hope that we can find a static IP address that's associated with our vServer, and reconfigure to use that directly.

Sat Jul 01, 2017

07/01 backups from hetzner #2 were successful
07/01 backups from hetzner #1 failed
1. I checked if they were still rsync-ing, and was unable to ssh into the server
2. I saw mail from Marcin indicating the server was out
3. I could not ping our server on dedi978.your-server.de'
4. I could not preform any admin actions from the hetzner web portal
5. Google analtyics still showed traffic to our site, perhaps a false negative due to the cloudflare "always on" feature
6. I found that hetzner recently did maintenance to their VHosts, indicating that our vServer's vHost may change, and that we should check "robot" to determine our new vHost https://www.hetzner-status.de/en.html
7. I found the URL for the "robot" service, but was unable to login with the credentials given to me by Marcin https://robot.your-server.de/
8. I messaged Marcin, asking if he had these credentials

Thr Jun 29, 2017

confirmed that 2017-06-29 backups came in, though hetzner1's dropped to 33G--not sure why

Wed Jun 28, 2017

watched Monday's meeting
Added my profile to the OSE Developers wiki article, including avatar & badge upload
Added email & website support to the Department template Template:Department
Met with Marcin
Documented the backup procedure on OSE Server

Tue Jun 27, 2017

confirmed that 06/26 backups were successfully rsync'd to dreamhost from hetzner1 & hetzner2.
the cleanup cron was still in NOP mode, as unlink was commented-out. manually ran cleanup as /home was 99% full. It's now 93% (257G avail) after manually running the cleanup script. I'll need to validate that this cron is fully working later this week.
cleanup script on dreamhost leaves behind empty dirs. updated script to clean out empty dirs too.
fixed LOCK TABLE permissions issue on wiki mysqldump. Solution was to add the '--single-transaction' argument to the mysqldump command. The file is now 169M after bz2 compression. That's bigger than all the other DBs combined, but still not unreasonably large.
meeting with Catarina to generate an ssh keypair
1. added ssh key to her authorized_keys file & set permissions. validated access successfully.
2. set her password on the server & validated sudo permissions were already in-place.
3. fixed permissions on 3DModels directory, set to apache:apache from root recursively
4. added 'define('ALLOW_UNFILTERED_UPLOADS', true);' to obi's wp-config.php. Because of this, we should not allow untrusted people accounts on this wp site. Currently it's just marcin, me, & cmota. I also asked Catarina to limit files stored on wp to <10mb

Sat Jun 24, 2017

Discovered that at least 9.6G out of the 51G on hetzner1 are log files. This should be manually cleaned for now. Hetzner2 should be configured with logrotate, where we delete the files after a few days (now that we'll have backups we don't need to retain logs since 2001.
1. 7.5G /usr/home/osemain/www_logs
2. 2.1G /usr/www/users/osemain/logs
3. Discovered what appears to be a 9.4G (deprecated) backup at /usr/www/users/osemain/w.tar.gz
4. Discovered another 9.6G (deprecated) backup at /usr/home/osemain/tmpd/upgrade/w.tar.gz
Therefore, if I move these files off to /var/tmp/, it will cut the backup size of hetzner1 in less than half.
backup cron failed to run on hetzener 2. /var/log/cron says '(time) ERROR (getpwnam() failed)'. I was mising the user between the times & the command (which is unnecessary in hetzner 1, as she uses crontab). Added 'root' as the 7th option before the command, and confirmed it works on non-07:20 test. Logging works as well.
backup cron on hetzner 1 ran successfully, though it failed to log. Interesting to note, the filenames are timestamped to 05:20, even though the job is set to run at 07:20. This is because the system time is CEST (UTC+2), but the backup script explicitly generates the timestamp with the -u argument for UTC. This is ideal, as the time of the backup files are unambigious.
finished configuring ssh-ident & secpanel for ssh key compartimentalization
discovered that dreamhost does have a policy on "unlimited storage", which explicitly says you can't run a file-hosting site, and it seems that using it as a backup archive may violate their "unlimited" policy. We should not depend on dreamhost to not delete out data.. Ideally, we'd need a service that'd give us ~3TB.
added cleanLocal.pl to marcin_ose@hancock.dreamhost.com:'/home/marcin_ose/bin/'
created crontabs for daily deletion of hetzner1 & hetzner2 backup files from dreamhost that are 3 days old. Note that cleanLocal.pl will intentionally *not* delete any backups created on the 1st day of every month. These will have to be manually deleted every year or so if space becomes an issue. Hopefully I'll have all the backups in perfect shape by July 1st (1 week from today)

Fri Jun 23, 2017

Enabling google analytics in cloudflare was wildly successful. We now have the entire day of 06-22 showing:
1. 1,230 sessions. 1,086 users. 3,024 page views. 3,321 unique page views.
2. 36% from US, 7% from India, 4% from Canada, then (in order, including all with >=1%): Germany, Australia, UK, Brazil, Neterlands, France, Phillippines, Spain, Italy, Malaysia, Poland, Chile, South Korea, South Africa.
3. 76% desktop. 20% mobile. 4% tablet.
4. 7% of page views are '/', 3% "/wiki/List_of_CAD_Programs", 3% '/gvcs', 3% '/gvcs/gvcs-machine-index/', 2% '/wiki/Cost_of_Living', 2% '/wiki/Main_Page', 2% '/wiki/Global_Village_Construction_Set'
5. the biggest site 45% of traffic
  1. it's especially interesting that Cost_of_Living is more viewed than the wiki home. This page is the 14th result on DDG for 'average cost of living' (which gave us 2 hits yesterday from this search term), and it doesn't even show up on the first 10 pages of google.
6. 61% of sessions come from a search engine. The top 2 search terms (making up 96%) were not provided.
7. Referreal traffic is 13% of overall traffic, domains in-order are: youtube.com, pintrest.com, duckduckgo.com, com.google.android.googlequicksearchbox, facebook.com, mg.mail.yahoo.com, ecosia.org, reddit.com, waldenlabs.com
8. yesterday, there were <50 users at: 12am, 4am, 5am, 6am, 7am, 2pm, 6pm, 7pm, 8pm, 9pm, 10pm, & 11pm. The lowest time was 33 users at 9pm. To avoid ambiguity, I changed the GA UI to UTC, but the hours didn't change--so who knows what this data means! I could also find no information on what "49 users at 1am" means. Is that 01:00-01:59? 00:01-01:00? 00:30-01:29? This wasn't hugely helpful, but it's reasonable to assume that the 00:00-04:00 US PT time is a low-traffic window (as most of our users geolocate to North America). Therefore, I'll start the backups with cron at 07:20 UTC.
Hetzner 2 is set to use CEST, which is UTC+2. I'm not sure, but it may switch to CET = UTC+1 sometimes. FeF is UTC-6, but sometimes UTC-5. I'm currently in NYC, which is UTC-4, but sometimes UTC-5. But the OSE devs could be in any timezone. To avoid ambiguity, and ensure consistancy across logs, I'll be changing everything to UTC in the future.
Added '/etc/cron.d/backup_to_dreamhost' to hetzner2 to kick-off a backup job at 07:20 UTC (at least it _will be_ UTC in the future; I'm not going to make that change [or any changes] until I've validated automated backups are working with no intervention for at least a few days in a row). Logs go to '/var/log/backups/backup.log'
Added a line to hetzner 1's osemain crontab to initiate a backup at 07:20 (this will probably always be CEST). Logs go to /usr/home/osemain/backups/log/backup.log'

Thr Jun 22, 2017

First successful execution of the backup.sh script on both servers without manual intervention with all nice & bandwith-caps in-place on both hetzner 1 &2
1. hetzner 1's mysqldump of the wiki is still failing, requiring an unlock of a table & further research into the potential impact of the change
2. hetzner1's full backup execution time (including cleaning old local backups, mysqldump, tarball creation, and rsync to dreamhost) is 11 hours. If this server weren't to be deprecated shortly, I'd switch to gz to reduce this, but as space is a concern & the server is temporary, this should suffice.
3. hetzner2's full backup execution time is under 3 hours

Wed Jun 21, 2017

confirmed access to dreamhost web ui
confirmed that we have ssh key control from the dreamhost dashboard
determined that 'opensourceecology.org' is purchased from dreamhost for $14/yr
1. found subdomains blog, community, eerik, & forum
2. found 10 databases on dreamhost: dp7civicrm (drupal 7 civicrm db), dp7crm (drupal 7 civicrm), oftblog (Blog), oftcivi (CiviCRM), oftdrupal, oftforum, oftjoomla, oftsurvey (For LimeSurvey), oftwiki (Wiki), openfarmtech_org (openfarmtech.org/osefriends). Are any in use & in need of backup? Will confirm with Marcin.
3. confirmed that dreamhost does *not* offer us free https certs (other than letsencrypt.org); they're $15/yr through Comodo. We just go straight with letsencrypt.org
4. found 5x users on dreamhost: marcin_ose (17G), ose_site (32G), ose_community (0.2G), osecolby (<0.1G), osebackup (<0.1G). The creds I've been given were for marcin_ose, and I'm putting the backups in marcin_ose@hancock.dreamhost.com:/home/marcin_ose/backups/{hetzner1,hetzner2}/$timestamp/
The backup of the data on hetzner 1 finished after 7 hours with the following sizes (note it was all bz2 compressed):
1. 22G public_html (uncompressed size is 31G)
2. 17G $HOME (uncompressed size is 20G)
3. 43M mysqldump-forum
4. 2.4M mysqldump-osemain
5. 1.2M mysqldump-openswh
6. 125K mysqldump-fef
7. 527 bytes mysqldump-wiki
There was an issue encountered with the wiki db. I've found a solution command, but I need to research its side-effects in relation to mediawiki to ensure I don't cause any issues
23% space savings probably isn't worth the 7 hour slamming of the CPU to compress at bz2 levels, but I'll keep it this way on hetzner 1, as we've already exceeded our disk quota many times over. I'll switch to gz compression on hetzner 2.
1. mysqldump: Got error: 1044: Access denied for user 'osewiki_w'@'%' to database 'osewiki' when using LOCK TABLES
tx of the 38G from hetzner 1 to dreamhost took just under 1 hour with speeds between 2.82 MB/s - 15.31 MB/s
1. I'll configure the automated rysnc to cap at 3 MB/s so it takes a reasonable 3-5 hours while reducing the risk of saturating the network bandwidth. TODO: determine the best 5-hour window when the box is most idle on a daily cycle.
Added backup scripts to hetzner 2. It does a single root mysqldump + file backups of /etc/, /home/, /var/log/, /root/, and /var/www/. Unlike hetzner 1, we have ample disk space (113G available after the first backup), so I'm using gzip instead of bz2. This whole backup process took 21 min on hetzner2, producing 31G. Transferring this to dreamhost took another 31 min
confirmed access to cloudflare acount
1. their site's "analytics" app has an error & returns an empty data set in both firefox & chrome
2. it appears that we do have caching enabled, and some other last-mile optimization for mobile
  1. hetzner 2 has 61G of unused RAM. We have plenty of RAM to run a fat cache. Reverse proxy or application-level proxy? Squid or nginx? First, we'll have to see what the bottlenecks are & what the page requests and static vs dynamic content looks like over a few weeks.
3. WAF events show that 2-200-ish IPs are actively being blocked every day. The worst are attacks on 'wp-login'. This is something we can have ossec or a wp plugin for rate limiting handle with iptables for free
4. Email Address Obfuscation is currently enabled. There is no good replacement for this afaik, besides author's awareness
5. "Always on" is enabled. There is no reasonably cheap alternative to this, though it's not strictly a requirement.
I added google analytics tracking through cloudflare temporarily so I can get an understanding of the hourly & weekly usage trends of the sites. Eventually I hope to deprecate google analytics for awstats

Tue Jun 20, 2017

Determined only backups done on hetzner 1 is mediawiki using MediaWiki's built-in 'maintenance/dumpBackup.php' script to 'w/export/'
Confirmed access to mysql databases for mediawiki, main ose wp site, open warehouse wp, fef wp, & oseforum vanilla on old server
begun a keepass db as central location for safely storing OSE credentials
sent email to Marcin, Tom, and Catarina to get their ssh public keys to populate their user's authorized_keys file prior to disabling password-based authentication
added maltfield user to wheel for sudo access
discovered that the only 2 dns entries on ghandi are: 'oswarehouse.org' & 'opensourcewarehouse.org'. oswarehouse.org is an unconfigured landing page, and opensourcewarehouse.org points to dreamhost a NS.
confirmed ssh access to dreamhost. we're using 18G in our $HOME, and '/home' (which is likley shared with other customers) has 357G free. I'll be using this unlimited storage plan to store compressed tarballs of daily backups of the server's DBs, webroots, and important config file dirs (/etc/, /home/, /root/, etc)
was unable to connect to dreamhost web console, messaged Marcin for proper credentials
determined size of $HOME on hetzner 1 to be 20G, and size of its 'public_html/' dir to be 31G
added backup scripts for mysqldumps of 5x DBs + all files in $HOME (except the backups themselves, of course) to '$HOME/backups/'
I noticed that the maximum disk usage for hezner 1's opensourceecology.org domain (not sure how these arbitrary divisions are made between "domains" since everything is actually just thrown into the same '$HOME/public_html' dir--and where would '$HOME' fit anyway?) is 10G, but we're currently using 56G. Trying to create a backup has suspiciously caused my session to be terminated, and when I reconnected I found my screen session no longer existed, suggesting that my whole session was `kill`ed. Using `nice` produced less lethal repercussions.

Mon Jun 19, 2017

Document as you go along

Meeting with Marcin for knowledge transfer on credentials to servers & prioritizing steps to migrate off old server.
Established secure channel for credentials exchange
Still missing root db credentials & root access to old server Hetzner 1
Created a 'maltfield' account on the server, added my ssh public key, and Enabled PubKeyAuthentication on sshd
Gained access to opensourcecology.org Google Apps email account with Google Analytics access. I should use this for OSE-related user accounts going forward.

Sun Jun 11, 2017

researching free/discounted cloud and/or hardware services for non-profits
researching FOSS slack alternatives for real time chatting. decided wire is best.
researched bug tracking vs issue tracking software. Found FOSS Request Tracker, OTRS, Liberum Help Desk, GLPI, and Faveo. Marcin mentioned investigations into Mantis

Fri Jun 9, 2017

Backup Hetzner Old - Wiki, Wordpress, Opensourcewarehouse, Forum, Factor e Farm Blog
Fix OSE Wordpress (Main Site)
Move all to Hetzner New. New Hetzner has Openbuildinginstitute. OSE Server
Install HTTPS
Install Jitsi Videobridge
Do dev work
Discuss password management for IT Team
Discuss OSE Website Wordpress Theme

Sun Jun 04, 2017

Added my video to the FreeCAD_101#Self-Verifying_FreeCAD_Exam_Videos article
Added my comment to disqus

Thr June 01, 2017

Updated OSE Wiki with better instructions to install the Assembly 2 Workbench FreeCAD Assembly Workbench
successfully finished sketching polylines w/ constraints to pocket my 2d initials into the 3d xyz cube
recorded myself building another xyz cube, initials, pocketed. Sped-up video to 30-seconds, added soundtrack, & uploaded my finished freecad test to youtube]
Created a new repo on my github or my OSE work, and committed/pushed my xyz cube work to here
1. See video.fcstd for the freecad file that was created during the video's recording.

Tue May 30, 2017

Finished MarthamEngineering's 3-part freecad youtube introduction series
Began [Marcin's 2-part freecad tutorial]
Updated OSE Wiki with better instructions to install Fastener's Workbench Fasteners Workbench in FreeCAD
Successfully finished building my first xyz cube in FreeCAD

Mon May 29, 2017

Began reading freecad documentation, watching videos on, & playing with freecad
Began MarthamEngineering's FreeCAD Tutorials youtube video series, starting with [part 1]

Sat Apr 22, 2017

Online_conferencing

@@ Line 6: / Line 6: @@
 =Sun Oct 22, 2017=
 # tested demo of LibreOffice Online
-# added Google docs alternative article
+# added [[Google docs alternative]] article
 # issues with LibreOffice Online atm
 ## no hyperlink support (filed bug for this = https://bugs.documentfoundation.org/show_bug.cgi?id=113361)

Maltfield Log: Difference between revisions