ubuntu 18.04 hw2018 server apache mysql

Many services I use have a web interface, so here is my basic apache setup. When I say basic, but I mean apache with https, http/2, mysql and phpmyadmin…

This post has been updated, since I changed my mind on http, changed my use of certbot and started using virtual hosts (not really by choice).

Services

On my home server, a web interface is available for:

Service	Description	Type	Related tag
backuppc	rsync based on disk backup	web-app/perl	-
tvheadend	TV streaming and recording server	webserver	tvheadend
zabbix	network monitoring	web-app/php	zabbix
phpmyadmin	web interface for mysql	web-app/php	mysql
grafana	data visualization and monitoring	webserver	grafana
weewx	weather station software	generated files	TBD
syncthing	distributed file syncthing	webserver	syncthing
jenkins	continuous integration	webserver	TBD
gogs	git services	webserver	TBD
portainer	docker management	webserver	TBD

If you have seen my previous post, you may have seen that the list continues growing.

Apparently the grafana, tvheadend, jenkins, gogs, syncthing and portainer guys thinks it’s a good idea to include a webserver… Please don’t, and here is why (IMHO):

• The http/https ports are already used by another program.
• There is already a lot of good opensource web servers, the time invested in your integrated web server could have been better spent.
• Most of the problems related to the security can be delegated to the existing existing web server.
• A person that is able to setup grafana, tvheadend, jenkins, gogs, syncthing or portainer is clearly able to install a webserver. Please have a look at phpmyadmin, it’s so easy to setup on apache.

Why apache?

There are plenty of opensource webservers, but I’ve chosen apache httpd mainly for two reasons:

• I know how to use it for years.
• I don’t even know how to pronounce nginx.

Getting rid of http (in favor of https)

I don’t want to run services on http since it’s not encrypted. Using both http and https with the same configuration and using the same services is possible, but I’m strongly against it. If http is enable, you may put accidentally your credentials in clear on the network.

It’s common practice to redirect http traffic to https, this may be comfortable to the end user, but you may still send your credentials in clear over the network.

With http disable, your unencrypted credentials won’t go beyond the browser or the command line.

http/2

For performance reasons I want to enable http/2, and this will affect which apache mpm to enable.

Domain names

https will link a domain name to a web server, and since I don’t want to deal with self-signed certificates, I use Let’s Encrypt certificates.

If you don’t know or don’t remember what a A record is or if you need a small refresh on DNS, feel free to read this interesting article.

Let say I own example.com and example.org (example.com and example.org are reserved for documentation like this, more on wikipedia and RFC 2606).

• The DNS for example.org and example.com is managed by my domain name registrar.
• example.org points to (the A record) a website hosted somewhere on the Internet.
• example.com points to (the A record) a website hosted somewhere else the Internet.
• foo.example.org point to my home router.
  • Since I don’t want to pay for a static IP, my IP is dynamic, I use the afraid.org service to make my dynamic address resolvable (see this post for how I update the address).
  • I have delegated the control of foo.example.org to afraid.org, by setting the NS record at my domain name registrar:

    # extract of the DNS record for example.org
    ...
    foo 10800 IN NS ns1.afraid.org.
    foo 10800 IN NS ns2.afraid.org.
    foo 10800 IN NS ns3.afraid.org.
    foo 10800 IN NS ns4.afraid.org.
    ...
    

    Added foo.example.org in the afraid.org web interface, then added a dynamic host.

• bar.example.com and baz.example.com points to foo.example.org, (CNAME record at my registrar):

  # extract of the DNS record for example.com
  ...
  bar 10800 IN CNAME foo.example.org.
  baz 10800 IN CNAME foo.example.org.
  ...
  

Install

My router redirects connections from http and https ports to my server.

apache

sudo apt-get install -y apache2 libapache2-mod-php php7.2-fpm

Enable https (and http2) and disable http

1. Enable/disable modules/sites:

    sudo a2enmod proxy_fcgi setenvif
    sudo a2enmod proxy
    sudo a2enmod proxy_wstunnel
    sudo a2enconf php7.2-fpm
    sudo a2dismod mpm_prefork
    sudo a2enmod mpm_event
    sudo a2enmod http2
    sudo a2enmod ssl
    sudo a2enmod headers
    sudo a2ensite default-ssl
    sudo a2dissite 000-default.conf
   

2. Config Edit the /etc/apache2/ports.conf, disable port 80:

    #Listen 80
   
    <IfModule ssl_module>
          Listen localhost:443
          Listen 10.13.9.40:443
    </IfModule>
   
    <IfModule mod_gnutls.c>
          Listen localhost:443
          Listen 10.13.9.40:443
    </IfModule>
   

3. Restart apache2

   sudo apache2ctl configtest && sudo systemctl restart apache2
   

4. Verify that http is disable and https has bad certificate:

    wget -O /dev/null http://foo.example.org
    --2019-01-25 11:24:02--  http://foo.example.org/
    Resolving foo.example.org (foo.example.org)... 127.0.0.1
    Connecting to foo.example.org (foo.example.org)|127.0.0.1|:80... failed: Connection refused.
   
    wget -O /dev/null https://foo.example.org
    --2019-01-25 11:24:08--  https://foo.example.org/
    Resolving foo.example.org (foo.example.org)... 127.0.0.1
    Connecting to foo.example.org (foo.example.org)|127.0.0.1|:443... connected.
    ERROR: cannot verify foo.example.org's certificate, issued by ‘CN=ubuntu’:
      Self-signed certificate encountered.
    ERROR: no certificate subject alternative name matches
    	requested host name foo.example.org.
    To connect to foo.example.org insecurely, use `--no-check-certificate'.
   

Install the certificates

1. Install certbot:

    sudo add-apt-repository ppa:certbot/certbot
    sudo apt-get update
    sudo apt-get install -y certbot
   

2. Configure certbot, this will request and install certificates, cron will manage certificate renewal (automagically configured). During the validation certbot will run it’s own webserver on port 80, and let's encrypt server must be able to access it.

     sudo certbot --standalone certonly -d foo.example.org -d bar.example.com -d baz.example.com
     Saving debug log to /var/log/letsencrypt/letsencrypt.log
     Plugins selected: Authenticator standalone, Installer None
     Enter email address (used for urgent renewal and security notices) (Enter 'c' to
     cancel): me@example.org
   
     -------------------------------------------------------------------------------
     Please read the Terms of Service at
     https://letsencrypt.org/documents/LE-SA-v1.2-November-15-2017.pdf. You must
     agree in order to register with the ACME server at
     https://acme-v01.api.letsencrypt.org/directory
     -------------------------------------------------------------------------------
     (A)gree/(C)ancel: A
   
     -------------------------------------------------------------------------------
     Would you be willing to share your email address with the Electronic Frontier
     Foundation, a founding partner of the Let's Encrypt project and the non-profit
     organization that develops Certbot? We'd like to send you email about EFF and
     our work to encrypt the web, protect its users and defend digital rights.
     -------------------------------------------------------------------------------
     (Y)es/(N)o: N
     Obtaining a new certificate
     Performing the following challenges:
     http-01 challenge for foo.example.org
     http-01 challenge for bar.example.com
     http-01 challenge for baz.example.com
     Waiting for verification...
     Cleaning up challenges
   
     IMPORTANT NOTES:
     - Congratulations! Your certificate and chain have been saved at:
       /etc/letsencrypt/live/foo.example.org/fullchain.pem
       Your key file has been saved at:
       /etc/letsencrypt/live/foo.example.org/privkey.pem
       Your cert will expire on YYYY-MM-DD. To obtain a new or tweaked
       version of this certificate in the future, simply run certbot
       again. To non-interactively renew *all* of your certificates, run
       "certbot renew"
     - If you like Certbot, please consider supporting our work by:
   
     Donating to ISRG / Let's Encrypt:   https://letsencrypt.org/donate
     Donating to EFF:                    https://eff.org/donate-le
   

   Enable the new certificates and h2

3. Edit /etc/apache/site-available/default-ssl.conf

    <VirtualHost _default_:443>
    Protocols h2 http/1.1
    ServerAdmin webmaster@localhost
    ServerName foo.example.org
    ServerAlias localhost
   
    DocumentRoot /var/www/html
   
    SSLCertificateFile /etc/letsencrypt/live/foo.example.org/fullchain.pem
    SSLCertificateKeyFile /etc/letsencrypt/live/foo.example.org/privkey.pem
    Include /etc/letsencrypt/options-ssl-apache.conf
    Header set Strict-Transport-Security "max-age=31536000" env=HTTPS
    ...
   

4. restart apache

    sudo apache2ctl configtest && sudo systemctl restart apache2
   

5. Verify that http is diable and https has a valid certificate:

    wget -O /dev/null http://foo.example.org
    --2019-01-25 11:24:02--  http://foo.example.org/
    Resolving foo.example.org (foo.example.org)... 127.0.0.1
    Connecting to foo.example.org (foo.example.org)|127.0.0.1|:80... failed: Connection refused.
   
    wget -O /dev/null https://foo.example.org
    --2019-01-25 11:32:50--  https://foo.example.org/
    Resolving foo.example.org (foo.example.org)... 127.0.0.1
    Connecting to foo.example.org (foo.example.org)|127.0.0.1|:443... connected.
    HTTP request sent, awaiting response... 200 OK
    Length: 552 [text/html]
    Saving to: ‘/dev/null’
   

mysql + automysqlbackup

sudo apt-get install -y mysql-server automysqlbackup

mysql use system password for root, but root has no system password, so let’s use mysql password for root:

sudo mysql -u root mysql
UPDATE user SET plugin='mysql_native_password' WHERE User='root';
SET PASSWORD FOR root@'localhost' = PASSWORD('MyCoolPassword');
FLUSH PRIVILEGES;
exit;

phpmyadmin

sudo apt-get install -y phpmyadmin

• Http server : apache2
• Configuring phpmyadmin : Yes
• Password for phpmyadmin : MyCoolPassword

Testing

Open this URL in your favorite browser : https://YOUR_SERVER_IP_HERE/phpmyadmin, then login a root/MyCoolPassword.

mysql backups

You may have noticed that automysqlbackup is installed. It will periodically backup all mysql databases into /var/lib/automysqlbackup.

It is configurable by modifying /etc/default/automysqlbackup.

Here is some recommendations extracted from the automysqlbackup script:

# Daily Backups are rotated weekly..
# Weekly Backups are run by default on Saturday Morning when
# cron.daily scripts are run...Can be changed with DOWEEKLY setting..
# Weekly Backups are rotated on a 5 week cycle..
# Monthly Backups are run on the 1st of the month..
# Monthly Backups are NOT rotated automatically...
# It may be a good idea to copy Monthly backups offline or to another

Configuring virtual hosts (for bar.example.com and baz.example.com)

1. Create the file /etc/apache2/sites-available/bar-ssl.conf:

    <IfModule mod_ssl.c>
    	<VirtualHost _default_:443>
    		ServerName bar.example.com
    		ServerAlias baz.example.com
    		Protocols h2 http/1.1
    		ServerAdmin webmaster@localhost
   
    		SSLCertificateFile /etc/letsencrypt/live/foo.example.org/fullchain.pem
    		SSLCertificateKeyFile /etc/letsencrypt/live/foo.example.org/privkey.pem
    		Include /etc/letsencrypt/options-ssl-apache.conf
    		Header set Strict-Transport-Security "max-age=31536000" env=HTTPS
   
    		DocumentRoot /SOMEWHERE/
    		<Directory "/SOMEWHERE">
    			Require all granted
    			Options Indexes MultiViews
    		</directory>
    	</VirtualHost>
    </IfModule>
   

Proxy example - tvheadend

No that we’ve got a working https server, let’s map the tvheadend service behind it. I have chosen tvheadend as an example because it uses websockets.

1. Edit /etc/apache/site-available/bar-ssl.conf:

    <IfModule mod_ssl.c>
    	<VirtualHost _default_:443>
    		ServerName bar.example.com
            ServerAlias baz.example.com
    		Protocols h2 http/1.1
    		ServerAdmin webmaster@localhost
   
    		SSLCertificateFile /etc/letsencrypt/live/foo.example.org/fullchain.pem
    		SSLCertificateKeyFile /etc/letsencrypt/live/foo.example.org/privkey.pem
    		Include /etc/letsencrypt/options-ssl-apache.conf
    		Header set Strict-Transport-Security "max-age=31536000" env=HTTPS
   
    		DocumentRoot /SOMEWHERE/
    		<Directory "/SOMEWHERE">
    			Require all granted
    			Options Indexes MultiViews
    		</directory>
   
    		ProxyRequests   on
   
    		# Map /hts/comet/ws before /hts, because the first proxy directive
    		# matched will be used.
    		# Since hts will manage it's own users, anybody is permitted access
    		# (Require all granted)
   
    		# Web sockets
    		ProxyPass /hts/comet/ws ws://localhost:9981/hts/comet/ws retry=0
    		<Location /hts/comet/ws>
    			ProxyPassReverse	ws://localhost:9981/hts/comet/ws
    			Require all granted
    		</Location>
   
    		# Web interface
    		ProxyPass	/hts http://localhost:9981/hts retry=0
    		<Location /hts>
    			ProxyPassReverse http://localhost:9981/hts
    			Require all granted
    		</Location>
   
    		# Request not matching any proxy (for intance /) will still go to
    		# DocumentRoot
   
    	</VirtualHost>
    </IfModule>
   

2. Reload apache This time a simple reload is sufficient:

    sudo apache2ctl configtest && sudo systemctl restart reload
   

3. Enjoy the tvheadend web interface now available at https://bar.example.com/example!

To be continued

Now that the infrastructure is in place, let’s do something with it : monitor system health with zabbix and grafana, then making grafana faster using compression.

~~~

Question, remark, bug? Don't hesitate to contact me or report a bug.