How to use Let’s Encrypt certificates with Nginx

Back in early 2011, I wrote a post on the most common reasonswhy SSL isn’t turned on by default for all websites, and one of these reasons at the time was cost.

Standard SSL certificates can be quite cheap these days, yet nothing beats free. According to their website, Let’s encrypt – which entered public beta on December 3 – is

a new Certificate Authority: It’s free, automated, and open.

So this essentially means you can get valid, trusted TLS/SSL certificates for free. Besides the cost, one thing I really like of Let’s Encrypt is that it is so easy and quick to get a new certificate! Normally you’d have to generate a Certificate Signing Request (CSR) and a private key on the server, then send the CSR to a provider/Certificate Authority in order to get the actual certificate. In many cases, the certificate you receive from the provider is a bundle of several certificates that you have to combine into a single certificate you can then install on the server. You need to repeat the process each time you need to renew the certificate.

The process overall isn’t complicated but is made much easier and quicker with Let’s Encrypt. If you use Apache, everything is pretty much automated with the Let’s Encrypt python tools, in that the certificate will be generated and installed in Apache automatically for you. The same level of support for Nginx is still in the works, but generating a certificate you can install with Nginx as well is quite straightforward.

First, you need to clone the git repo which contains the python tools you will use to generate new certificates:

git clone https://github.com/letsencrypt/letsencrypt
cd letsencrypt

Next, you need to stop Nginx before proceeding… I know this sounds like it may be a problem, but there is a reason for this will I will explain in a moment.

service nginx stop

Now you can run the python tool which will generate the certificate for you:

./letsencrypt-auto --agree-dev-preview --server https://acme-v01.api.letsencrypt.org/directory auth

This will require that you accept the terms and conditions and enter the domain or domains you need the certificate for. For example, you may want a certificate for a domain with and without the www subdomain.

Once the tool has done its stuff, you will find the new certificate in /etc/letsencrypt/live by default, with a directory for each domain which contains the following files:

cert.pem chain.pem fullchain.pem privkey.pem

The important files which you will use with Nginx are fullchain.pem and privkey.pem.

So open the relevant virtual host file (usually in /etc/nginx/sites-enabled) and add the following lines to the server block:

server {
listen 443 ssl;

server_name <domain name>;

ssl on;
ssl_certificate /etc/letsencrypt/live/<domain name>/fullchain.pem;
ssl_certificate_key /etc/letsencrypt/live/<domain name>/privkey.pem;

...
}

Of course replace domain name with the actual domain name (or names for the server_name directive if more than one, e.g. with and without www).

These are the minimum settings you need to add in order to enable https for your site, but I recommend you have a look at Mozilla’s SSL config generator for additional settings to improve the security of your setup. For example I’m currently using the following settings:

ssl_session_timeout 1d;
ssl_session_cache shared:SSL:50m;

ssl_protocols TLSv1 TLSv1.1 TLSv1.2;
ssl_ciphers "ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:DHE-DSS-AES128-GCM-SHA256:kEDH+AESGCM:ECDHE-RSA-AES128-SHA256:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA:ECDHE-ECDSA-AES256-SHA:DHE-RSA-AES128-SHA256:DHE-RSA-AES128-SHA:DHE-DSS-AES128-SHA256:DHE-RSA-AES256-SHA256:DHE-DSS-AES256-SHA:DHE-RSA-AES256-SHA:ECDHE-RSA-DES-CBC3-SHA:ECDHE-ECDSA-DES-CBC3-SHA:AES128-GCM-SHA256:AES256-GCM-SHA384:AES128-SHA256:AES256-SHA256:AES128-SHA:AES256-SHA:AES:CAMELLIA:DES-CBC3-SHA:!aNULL:!eNULL:!EXPORT:!DES:!RC4:!MD5:!PSK:!aECDH:!EDH-DSS-DES-CBC3-SHA:!EDH-RSA-DES-CBC3-SHA:!KRB5-DES-CBC3-SHA";
ssl_prefer_server_ciphers on;

add_header Strict-Transport-Security max-age=15768000;

ssl_stapling on;
ssl_stapling_verify on;

Once you have completed the configuration, reload or restart Nginx and test the configuration with this service.

If all is configured properly you should get a very good score, e.g.:

screen-shot-2015-12-18-at-22-35-31

Optionally, you may also want to redirect all the plain http traffic to the https ‘version’ of your site. To do this, just add another server block to the virtual hosts like the following:

server {
listen 80;
server_name <domain name>;
rewrite ^/(.*) https://<domain name>/$1 permanent;
}

So, why do you need to stop Nginx before generating a certificate with Let’s Encrypt? When you request a certificate with a typical provider, they need to verify that you own the domain and this is done, for example, by sending an email to an email address of that domain with a confirmation link. If you own the domain, of course you have access to that email address and therefore you can proceed with the next steps required to get the certificate.

With Let’s Encrypt, everything is automated but they still need to verify the ownership of the domain first. So when you run letsencrypt-auto, it starts an HTTP server listening to the port 80 and requests a certificate from Let’s Encrypt CA. The CA, in order to verify that you own the domain, makes an HTTP request to your domain, which of course will be served by letsencrypt-auto’s server, confirming that you own the domain. Because this HTTP server runs on the port 80, you can’t run your Nginx server on the port 80 at the same time, so while you generate a certificate with letsencrypt-auto you will need to stop Nginx first. It doesn’t take long to get a certificate but this may be a problem depending on the application, especially considering that -as we’ll see later- Let’s Encrypt certificates must be renewed every 90 days. There is a module for Apache that does all of this automatically without downtime, but as said the same support for Nginx is still in the works so in the meantime you will have to stop Nginx while generating the certificate. Please note that what I described is the easiest way to obtain and install a certificate with Let’s Encrypt, so there may be other ways to do this without downtime even with Nginx. Update: I found this which might be of interest.

Limitations

Unfortunately, Let’s Encrypt certificates come with some limitations:

  • only Domain Validation (DV) certificates are issued, so the browsers will show the padlock as expected. However Organisation Validation and Extended Validation certificates are not available and apparently Let’s Encrypt has no plans to offer these certificates because they require some human intervention and thus they cost money, so the generation of these certificate cannot be fully automated nor offered for free, which are the key features of Let’s Encrypt.
  • wildcard certificates aren’t available either; you can get certificates for multiple subdomains though. This may be a problem with some applications.
  • certificates expire in 90 days, which seems a bit too short. See this for an explanation.
  • there is a limit of 5 certificates for a registered domain in 7 days; this limit should be lifted when Let’s Encrypt is out of beta. So for example if you request separate certificates for mydomain.com, http://www.mydomain.com and mail.mydomain.com these will be counted as 3 certificates for the same domain. But of course you can request a certificate with multiple subdomains at once.
  • all major browsers are supported, but some devices don’t recognise these certificates. See this list for more info.

Even with these limitations, Let’s Encrypt is an exciting initiative and it is likely that things will improve when LE is out of beta. It’s a great service because by offering free certificates that are also easier to obtain, it will surely speed up the adoption of TLS/SSL encryption, making for a more secure web.

I don’t have any particular reasons for enabling encryption on all pages on this blog since it doesn’t manage any user data and I am outsourcing comments to Disqus, but I am planning on switching anyway because another added benefit of https is that it helps increase search engine raking.

So if you haven’t yet, check Let’s Encrypt out!

Setting up a Ubuntu server for Ruby and PHP apps

There are several guides on the Internet on setting up a Ubuntu server, but I thought I’d add here some notes on how to set up a server capable of running both Ruby and PHP apps at the same time. Ubuntu’s latest Long Term Support (LTS) release is 14.04, so this guide will be based on that release.

I will assume you already have a a server with the basic Ubuntu Server Edition installed – be it a dedicated server or a VPS from your provider of choice – with just SSH access enabled and nothing else. We’ll be bootstrapping the basic system and install all the dependencies required for running Ruby and PHP apps; I usually use Nginx as web server, so we’ll be also using Phusion Passenger as application server for Ruby and fastcgi for PHP to make things easier.

First steps

Before anything else, it’s a good idea to update the system with the latest updates available. So SSH into the new server with the IP and credentials you’ve been given and -recommended- start a screen session with

screen -S <session-name>

Now change the root password with

passwd

then open /root/.ssh/authorized_keys with and editor and ensure no SSH keys have already been added other than yours; if you see any keys, I recommend you comment them out and uncomment them only if you ever need to ask your provider for support.

Done that, as usual run:

apt-get update
apt-get upgrade -y

to update the system.

Next, edit /etc/hostname with vi or any other editor and change the hostname with the hostname you will be using to connect to this server; also edit /etc/hosts and add the correct hostname in there as well. Reboot:

reboot now

SSH access

It’s a good idea to use a port other than the default one for SSH access, and a user other than root. In this guide, we’ll be:

  • using the example port 17239
  • disabling the root access and enabling access for the user deploy (only) instead
  • switching from password authentication to public key authentication for good measure.

Of course you can choose whichever port and username you wish.

For convenience, on your client computer (that is, the computer you will be connecting to the server from) edit ~/.ssh.config and add the following content:

Host my-server (or whichever name you prefer)
Hostname <the ip address of the server>
Port 22
User root

So you can more easily SSH into the new server with just

ssh my-server

As you can see for now we are still using the default port and user until the SSH configuration is updated.

Unless your public key has already been added to /root/.ssh/authorized_keys during the provisioning of the new server, still on the client machine run

ssh-copy-id <hostname or ip of the server>

to copy your public key over. You should now be able to SSH into your server without password.

Back on the server, it’s time to setup the user which you will be using to SSH into the server instead of root:

adduser deploy

Edit /etc/sudoers and add:

deploy ALL=(ALL:ALL) ALL

On the client, ensure you can SSH into the server as deploy using your key:

ssh-copy-id deploy@my-server

You should now be able to login as deploy without password.

Now edit /etc/ssh/sshd_config and change settings as follows:

Port 17239
PermitRootLogin no
PasswordAuthentication no
UseDNS no
AllowUsers deploy

This will:

  • change the port
  • disable root login
  • disable password authentication so we are forced to use public key authentication
  • disable DNS lookups so to speed up logins
  • only allow the user deploy to SSH into the system

Restart SSH server with:

service ssh restart

Keep the current session open just in case for now. On the client, open again ~/.ssh/config and update the configuration of the server with the new port and user:

Host my-server (or whichever name you prefer)
Hostname <the ip address of the server>
Port 17239
User deploy

Now if you run

ssh my-server

you should be in as deploy without password. You should no longer be able to login as root though; to test run:

ssh root@my-server date

you should see an error:

Permission denied (publickey).

Firewall

Now that SSH access is sorted, it’s time to configure the firewall to lock down the server so that only the services we want (such as ssh, http/https and mail) are allowed. Edit the file /etc/iptables.rules and paste the following:

# Generated by iptables-save v1.4.4 on Sat Oct 16 00:10:15 2010
*filter
:INPUT ACCEPT
:FORWARD ACCEPT
:OUTPUT ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -d 127.0.0.0/8 ! -i lo -j DROP
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p tcp -m tcp --dport 80 -j ACCEPT
-A INPUT -p tcp -m tcp --dport 443 -j ACCEPT
-A INPUT -p tcp -m tcp --dport 587 -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 17239 -j ACCEPT
-A INPUT -m limit --limit 5/min -j LOG --log-prefix "iptables [Positive[False?]: " --log-level 7
-A INPUT -p icmp -m icmp --icmp-type 8 -j ACCEPT
-A INPUT -j LOG
-A INPUT -j REJECT --reject-with icmp-port-unreachable
-A OUTPUT -j ACCEPT
COMMIT
# Completed on Sat Oct 16 00:10:15 2010
# Generated by iptables-save v1.4.4 on Sat Jun 12 23:55:23 2010
*mangle
:PREROUTING ACCEPT
:INPUT ACCEPT
:FORWARD ACCEPT
:OUTPUT ACCEPT
:POSTROUTING ACCEPT
COMMIT
# Completed on Sat Jun 12 23:55:23 2010
# Generated by iptables-save v1.4.4 on Sat Jun 12 23:55:23 2010
*nat
:PREROUTING ACCEPT
-A PREROUTING -p tcp --dport 25 -j REDIRECT --to-port 587
:POSTROUTING ACCEPT
:OUTPUT ACCEPT
COMMIT
# Completed on Sat Jun 12 23:55:23 2010

It’s a basic configuration I have been using for some years. It locks all incoming traffic apart from SSH access, web traffic (since we’ll be hosting Ruby and PHP apps) and mail. Of course, make sure you specify the SSH port you’ve chosen here if other than 17239 as in the example.

To apply the setting now, run:

iptables-restore < /etc/iptables.rules

and verify with

iptables -L

You should see the following output:

Chain INPUT (policy ACCEPT)
target prot opt source destination
ACCEPT all -- anywhere anywhere
DROP all -- anywhere loopback/8
ACCEPT all -- anywhere anywhere state RELATED,ESTABLISHED
ACCEPT tcp -- anywhere anywhere tcp dpt:http
ACCEPT tcp -- anywhere anywhere tcp dpt:https
ACCEPT tcp -- anywhere anywhere tcp dpt:submission
ACCEPT tcp -- anywhere anywhere state NEW tcp dpt:17239
LOG all -- anywhere anywhere limit: avg 5/min burst 5 LOG level debug prefix "iptables [Positive[False?]: "
ACCEPT icmp -- anywhere anywhere icmp echo-request
LOG all -- anywhere anywhere LOG level warning
REJECT all -- anywhere anywhere reject-with icmp-port-unreachable

Chain FORWARD (policy ACCEPT)
target prot opt source destination

Chain OUTPUT (policy ACCEPT)
target prot opt source destination
ACCEPT all -- anywhere anywhere

Now if you reboot the server, these settings will be lost, so you need to persist them in either of two ways:

1) open /etc/network/interfaces and add, in the eth0 section, the following line:

post-up iptables-restore < /etc/iptables.rules

So the file should now look similar to the following:

auto eth0
iface eth0 inet static
address ...
netmask ...
gateway ...
up ip addr add 10.16.0.5/16 dev eth0
dns-nameservers 8.8.8.8 8.8.4.4
post-up iptables-restore < /etc/iptables.rules

OR,

2) Run

apt-get install iptables-persistent

Either way, reboot now and verify again with iptables -L that the settings are persisted.

ZSH shell, editor (optional)

If you like me prefer ZSH over BASH and use VIM as editor, first install ZSH with:

apt-get install zsh git-core
curl -L https://raw.github.com/robbyrussell/oh-my-zsh/master/tools/install.sh | sh
ln -s ~/dot-files/excid3.zsh-theme ~/.oh-my-zsh/themes

Then you may want to use my VIM configuration so to have a nicer editor environment:

cd; git clone https://github.com/vitobotta/dot-files.git
cd dot-files; ./setup.sh

I’d repeat the above commands for both the deploy user and root (as usual you can use sudo -i for example to login as root). Under deploy, you’ll need to additionally run:

chsh

and specify /usr/bin/zsh as your shell.

Dependencies for Ruby apps

You’ll need to install the various dependencies required to compile Ruby and install various gems:

apt-get install build-essential curl wget openssl libssl-dev libreadline-dev libmysqlclient-dev ruby-dev mysql-client ruby-mysql xvfb firefox libsqlite3-dev sqlite3 libxslt1-dev libxml2-dev

You’ll also need to install nodejs for the assets compilation (Rails apps):

apt-get install software-properties-common
add-apt-repository ppa:chris-lea/node.js
apt-get update
apt-get install nodejs

Next, as deploy:

Ensure the following lines are present in the shell rc files (.zshrc and .zprofile) and reload the shell so the new Ruby can be “found”:

export PATH="$HOME/.rbenv/bin:$HOME/.rbenv/shims:$PATH"
eval "$(rbenv init -)"

ruby -v should now output the expected version number, 2.2.4 in the example.

Optionally, you may want to install the rbenv-vars plugin for environment variables support with rbenv:

git clone https://github.com/sstephenson/rbenv-vars.git ~/.rbenv/plugins/rbenv-vars
chmod +x ~/.rbenv/plugins/rbenv-vars/bin/rbenv-vars

Dependencies for PHP apps

Install the various packages required for PHP-FPM:

apt-get install php5-fpm php5-mysql php5-curl php5-gd php5-intl php-pear php5-imagick php5-mcrypt php5-memcache php5-memcached php5-ming php5-ps php5-pspell php5-recode php5-snmp php5-sqlite php5-tidy php5-xmlrpc php5-xsl php5-geoip php5-mcrypt php-apc php5-imap

MySQL

I am assuming here you will be using MySQL – I usually use the Percona distribution. If you plan on using some other database system, skip this section.

First, install the dependencies:

apt-get install curl build-essential flex bison automake autoconf bzr libtool cmake libaio-dev libncurses-dev zlib1g-dev libdbi-perl libnet-daemon-perl libplrpc-perl libaio1
gpg --keyserver hkp://keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
gpg -a --export CD2EFD2A | sudo apt-key add -

Next edit /etc/apt/sources.list and add the following lines:

deb http://repo.percona.com/apt trusty main
deb-src http://repo.percona.com/apt trusty main

Install Percona server:

apt-get update
apt-get install percona-xtradb-cluster-server-5.5 percona-xtradb-cluster-client-5.5 percona-xtradb-cluster-galera-2.x

Test that MySQL is running:

mysql -uroot -p

Getting web apps up and running

First install Nginx with Passenger for Ruby support (also see this:

apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 561F9B9CAC40B2F7
apt-get install apt-transport-https ca-certificates

Edit /etc/apt/sources.list.d/passenger.list and add the following:

deb https://oss-binaries.phusionpassenger.com/apt/passenger trusty main

Update sources:

chown root: /etc/apt/sources.list.d/passenger.list
chmod 600 /etc/apt/sources.list.d/passenger.list
apt-get update

Then install Phusion Passenger for Nginx:

apt-get install nginx-extras passenger

Edit /etc/nginx/nginx.conf and uncomment the passenger_root and passenger_ruby lines, making sure the latter points to the version of Ruby installed with rbenv, otherwise it will point to the default Ruby version in the system. Make the following changes:

user deploy;
worker_processes auto;
pid /run/nginx.pid;

events {
use epoll;
worker_connections 2048;
multi_accept on;
}

http {
sendfile on;
tcp_nopush on;
tcp_nodelay on;
keepalive_timeout 65;
types_hash_max_size 2048;
server_tokens off;
…
passenger_root /usr/lib/ruby/vendor_ruby/phusion_passenger/locations.ini;
passenger_ruby /home/deploy/.rbenv/shims/ruby;
passenger_show_version_in_header off;
}

Restart nginx with

service nginx restart

Test that nginx works by opening http://the_ip_or_hostname in your browser.

For PHP apps, we will be using fastcgi with unix sockets. Create for each app a profile in /etc/php5/fpm/pool.d/, e.g. /etc/php5/fpm/pool.d/myapp. Use the following template:

[<app name>]
listen = /tmp/<app name>.php.socket
listen.backlog = -1
listen.owner = deploy
listen.group = deploy

; Unix user/group of processes
user = deploy
group = deploy

; Choose how the process manager will control the number of child processes.
pm = dynamic
pm.max_children = 75
pm.start_servers = 10
pm.min_spare_servers = 5
pm.max_spare_servers = 20
pm.max_requests = 500

; Pass environment variables
env[HOSTNAME] = $HOSTNAME
env[PATH] = /usr/local/bin:/usr/bin:/bin
env[TMP] = /tmp
env[TMPDIR] = /tmp
env[TEMP] = /tmp

; host-specific php ini settings here
; php_admin_value[open_basedir] = /var/www/DOMAINNAME/htdocs:/tmp

To allow communication between Nginx and PHP-FPM via fastcgi, ensure each PHP app’s virtual host includes some configuration like the following:

location / {
try_files $uri /index.php?$query_string;
}

location ~ \.php$ {
fastcgi_split_path_info ^(.+\.php)(/.+)$;
fastcgi_pass unix:/tmp/<app name>.php.socket;
fastcgi_index index.php;
include fastcgi_params;
fastcgi_param SCRIPT_FILENAME $document_root/$fastcgi_script_name;
}

Edit /etc/php5/fpm/php.ini and set cgi.fix_pathinfo to 0. Restart both FPM and Nginx:

service php5-fpm restart
service nginx restart

Congrats, you should now be able to run both Ruby and PHP apps.

Backups

There are so many ways to backup a server…. what I usually use on my personal servers is a combination of xtrabackup for MySQL databases and duplicity for file backups.

As root, clone my admin scripts:

cd ~
git clone https://github.com/vitobotta/admin-scripts.git
apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

Edit /etc/apt/sources.list and add:

deb http://repo.percona.com/apt trusty main
deb-src http://repo.percona.com/apt trusty main

Proceed with the installation of the packages:

apt-get update
apt-get install duplicity xtrabackup

Next refer to this previous post for the configuration.

Schedule the backups with crontab -e by adding the following lines:

MAILTO = <your email address>

00 02 * * sun /root/admin-scripts/backup/duplicity.sh full
00 02 * * mon-sat /root/admin-scripts/backup/duplicity.sh incr
00 13 * * * /root/admin-scripts/backup/xtrabackup.sh incr

Mailing

  • install postfix and dovecot with
apt-get install postfix dovecot-common mailutils
  • run dpkg-reconfigure postfix and set the following:
  • General type of mail configuration -> Internet Site
  • System mail name -> same as the server’s hostname
  • Root and postmaster email recipient -> your email address
  • Force synchronous updates on mail queue -> no
  • Local networks -> leave default
  • Mailbox size limit (bytes) -> set 10485760 (10MB) or so, to prevent the default mailbox from growing with no limits
  • Internet protocols to use -> all

  • SMTP authentication: run

postconf -e 'home_mailbox = Maildir/'
postconf -e 'smtpd_sasl_type = dovecot'
postconf -e 'smtpd_sasl_path = private/auth'
postconf -e 'smtpd_sasl_local_domain ='
postconf -e 'smtpd_sasl_security_options = noanonymous'
postconf -e 'broken_sasl_auth_clients = yes'
postconf -e 'smtpd_sasl_auth_enable = yes'
postconf -e 'smtpd_recipient_restrictions = permit_sasl_authenticated,permit_mynetworks,reject_unauth_destination'
  • TLS encryption: run
mkdir /etc/postfix/certificate && cd /etc/postfix/certificate
openssl genrsa -des3 -out server.key 2048
openssl rsa -in server.key -out server.key.insecure
mv server.key server.key.secure
mv server.key.insecure server.key
openssl req -new -key server.key -out server.csr
openssl x509 -req -days 365 -in server.csr -signkey server.key -out server.crt

postconf -e 'smtp_tls_security_level = may'
postconf -e 'smtpd_tls_security_level = may'
postconf -e 'smtp_tls_note_starttls_offer = yes'
postconf -e 'smtpd_tls_key_file = /etc/postfix/certificate/server.key'
postconf -e 'smtpd_tls_cert_file = /etc/postfix/certificate/server.crt'
postconf -e 'smtpd_tls_loglevel = 1'
postconf -e 'smtpd_tls_received_header = yes'
postconf -e 'myhostname = <hostname>'
  • SASL
  • edit /etc/dovecot/conf.d/10-master.conf, and uncomment the following lines so that they look as follows (first line is a comment so leave it…commented out):

Postfix smtp-auth

unix_listener /var/spool/postfix/private/auth {
mode = 0666
}
* edit /etc/dovecot/conf.d/10-auth.conf and change the setting auth_mechanisms to “plain login”
* edit /etc/postfix/master.cf and a) comment out smtp, b) uncomment submission
* restart postfix: service postfix restart
* restart dovecot: service dovecot restart
* verify that all looks good

root@nl:/etc/postfix/certificate# telnet localhost 587
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
220 <hostname> ESMTP Postfix (Ubuntu)
ehlo <hostname>
250-<hostname>
250-PIPELINING
250-SIZE 10240000
250-VRFY
250-ETRN
250-STARTTLS
250-AUTH PLAIN LOGIN
250-AUTH=PLAIN LOGIN
250-ENHANCEDSTATUSCODES
250-8BITMIME
250 DSN

Test email sending:

echo "" | mail -s "test" <your email address>

There’s a lot more that could be done, but this should get you started. Let me know in the comments if you run into any issues.

CentOS Parallels VM and missing network configuration

I was using CentOS with Parallels today, and had problems with networking after cloning a template VM into several VMs. Basically, after cloning the template, the clones appear to report only the loopback interface and one eth interface which seems to be inactive, so of course Internet doesn’t work:

[root@centos ~]# ifconfig -a
eth1 Link encap:Ethernet HWaddr 00:1C:42:22:36:26
BROADCAST MULTICAST MTU:1500 Metric:1
RX packets:2464262 errors:0 dropped:0 overruns:0 frame:0
TX packets:1221954 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:3716624972 (3.4 GiB) TX bytes:106808282 (101.8 MiB)

lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNING MTU:65536 Metric:1
RX packets:3502 errors:0 dropped:0 overruns:0 frame:0
TX packets:3502 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:359663 (351.2 KiB) TX bytes:359663 (351.2 KiB)
[root@centos ~]# ping 8.8.8.8
connect: Network is unreachable

I am not too familiar with CentOS so I googled and found out that networking is disabled in the default installation or something like that.

Anyway, in case someone runs into the same issue, if you run ifup it complains that the configuration for the eth interface could not be found:

[root@centos ~]# ifup eth1
/sbin/ifup: configuration for eth1 not found.
Usage: ifup <device name>

I’ve had this particular issue – missing network configuration – only with CentOS VMs, but networking doesn’t work with Ubuntu VMs either after cloning. On Ubuntu however I usually run

rm /etc/udev/rules.d/70-persistent-net.rules

and then reboot the VM, and that usually fixes it. I tried the same on the CentOS clones but it didn’t work.

It turns out on the CentOS clones there is a profile for the loopback interface and a profile for eth0 but not for eth1 – which is the interface I see in the VMs after cloning – and that’s the the reason why the configuration could not be found:

[root@centos ~]# ls /etc/sysconfig/network-scripts/ifcfg*
/etc/sysconfig/network-scripts/ifcfg-eth0 /etc/sysconfig/network-scripts/ifcfg-lo

So the way I fixed the missing configuration was by making a copy of the eth0 profile for eth1, and updating the content of the new profile with the correct device name and MAC address. First, make a copy of the profile:

[root@centos ~]# cd /etc/sysconfig/network-scripts/
[root@centos network-scripts]# cp ifcfg-eth0 ifcfg-eth1

Then, open the new profile with any editor and make sure the DEVICE name is eth1 (or whatever ethX it is for you if you have removed/added virtual NICs) and that HWADDR is set to the MAC address of the VM:

DEVICE=eth1
HWADDR=00:1C:42:22:36:26
TYPE=Ethernet
UUID=6326455c-37eb-48f7-b2a4-0dbf113e3c93
ONBOOT=no
NM_CONTROLLED=yes
BOOTPROTO=dhcp

You can find the MAC address in the Network > Advanced Settings of the virtual machine:

screen-shot-2015-12-11-at-18-37-35-2

Then, run

[root@centos network-scripts]# ifup eth1

Determining IP information for eth1... done.

And Internet should now work:

[root@centos network-scripts]# ping 8.8.8.8
PING 8.8.8.8 (8.8.8.8) 56(84) bytes of data.
64 bytes from 8.8.8.8: icmp_seq=1 ttl=53 time=25.9 ms
...

That’s it. Not sure why this happens but anyway it’s easy to fix.

MySQL: Generate column names dynamically from row values

Let’s say you have a table or the results of a query with some values by date in different rows. You want to generate column names dynamically from these dates, and show the relevant results as values for these new columns instead of values in different rows. So you basically want to transpose rows into columns and have dynamically generated column names at the same time. Unfortunately MySQL doesn’t allow the use of functions to generate column names, and as far as I know it doesn’t have a means out of the box to generate column names dynamically in general (please let me know if I am mistaken; I’m keen to learn something new), but it is definitely possible at least with a trick using prepared statements. Let me show you with an example.

I have a table, named hcu_collection, which I use to collect some data for each of a number of software licenses. The relevant columns in this example are the license_id, the collect_date and an integer column named total_hv_cores (it’s just an example from a real app so ignore the meaning of this column as it’s not important); for example I want to know the MAX(total_hv_cores) by date for each license over the past 3 days. So I can use a simple query like the following:

SELECT license_id, collect_date, MAX(total_hv_cores) cores
FROM hcu_collection
WHERE collect_date >= SUBDATE(CURDATE(), 2)
GROUP BY license_id, collect_date
ORDER BY collect_date ASC, collect_hour ASC;

which produces these results:

+------------+--------------+-------+
| license_id | collect_date | cores |
+------------+--------------+-------+
| 18 | 2015-12-04 | 1108 |
| 67 | 2015-12-04 | 436 |
| 102 | 2015-12-04 | 140 |
...
...
| 12174 | 2015-12-10 | 78 |
| 12380 | 2015-12-10 | 624 |
...
...

What I want instead is a table that looks like the following, for example for the past 3 days:

+------------+-----------+-----------+-----------+
| license_id | Tue 08/12 | Wed 09/12 | Thu 10/12 |
+------------+-----------+-----------+-----------+
| 2 | 238 | 238 | 246 |
| 3 | 60 | 68 | 68 |
| 4 | 12 | 16 | 12 |
| 7 | 212 | 212 | 220 |
...
...

As said I am not aware if MySQL already has some means to achieve this, so the way I have done it is by generating a query dynamically which, when executed, will then generate the column names from the dates as I want.

The first step is to create a temporary table with the results from the original query, for convenience, since we are going to need these results more than once in the query that will be generated dynamically.

DROP TABLE IF EXISTS tmp_results;

CREATE TEMPORARY TABLE tmp_results AS
SELECT license_id, collect_date, MAX(total_hv_cores) cores
FROM hcu_collection
WHERE collect_date >= SUBDATE(CURDATE(), 2)
GROUP BY license_id, collect_date
ORDER BY collect_date ASC, collect_hour ASC;

Next, we need to generate a new query dynamically. Here’s an example:

SELECT CONCAT('
SELECT license_id, ',cores_by_dates,'
FROM tmp_results
GROUP BY license_id
ORDER BY license_id'
)
INTO @query
FROM
(
SELECT GROUP_CONCAT(CONCAT('IFNULL(MAX(CASE WHEN collect_date=''',actual_date,''' THEN cores END), ''-'') AS "',col_name,'"')) cores_by_dates
FROM (
SELECT actual_date, DATE_FORMAT(actual_date,'%a %d/%m') AS col_name
FROM (SELECT DISTINCT collect_date AS actual_date FROM tmp_results) AS dates
) dates_with_col_names
) result;

The important bit is

SELECT GROUP_CONCAT(CONCAT('IFNULL(MAX(CASE WHEN collect_date=''',actual_date,''' THEN cores END), ''-'') AS "',col_name,'"')) cores_by_dates
FROM (
SELECT actual_date, DATE_FORMAT(actual_date,'%a %d/%m') AS col_name
FROM (SELECT DISTINCT collect_date AS actual_date FROM tmp_results) AS dates
) dates_with_col_names

which generates something like:

IFNULL(MAX(CASE WHEN collect_date='2015-12-08' THEN cores END), '-') AS "Tue 08/12",IFNULL(MAX(CASE WHEN collect_date='2015-12-09' THEN cores END), '-') AS "Wed 09/12",IFNULL(MAX(CASE WHEN collect_date='2015-12-10' THEN cores END), '-') AS "Thu 10/12"

We save this new query in @query so that we can use it to prepare a statement:

PREPARE statement FROM @query;

Last, we just need to execute it:

EXECUTE statement;

This shows the results I want, with the dates as column names. Don’t forget to deallocate the prepared statement after fetching the results:

DEALLOCATE PREPARE statement;

Note: depending on how many dates you use to generate the columns, you may exceed the limit allowed for GROUP_CONCAT‘s length (default is 1024 bytes). So you may need to add something like

SET SESSION group_concat_max_len = 1000000;

before the dynamic generation of the query.

Hope it can be useful to someone.

I’m back!

Well, it’s been a while since I’ve last updated this blog and I just wanted to make a short little post here to update y’all on a few things. It seems it’s not just me – the blog thing is kinda dying lately from what I can see from blogs I used to visit often and that are now rarely updated.

Anyways, I’m back after a long hiatus. I somehow missed it and am going to post again every now and then mainly with tips, tricks, and solutions to problems I encounter in my work life, especially on web development, as usual.

Not sure where I should start. Perhaps with a super brief update about myself?

Last time I blogged was in April 2014, so yeah, it’s been a while. I can’t say/remember much of what happened during the rest of that year, so I’ll say something quick about this past year.

In a nutshell, 2015 has been a overall good year, albeit I can’t say there have been any particularly important events. I still live in Finland – well, it doesn’t look I’m going anywhere else anymore – and I still work for OnApp, managing a small team of developers based in London. So as usual I work remotely, and visit the London office every now and then.

I’m still happy with my job, although sometimes I feel like it would be nice to do something different. But it’s nice to be able to kinda manage my own time since I work from home, although I usually try to be available during UK office hours. I wish I had more free time to work on some side project though.

Work aside, unfortunately I had to give up boxing mainly for health related reasons. I really miss it, as it’s the only kind of sport I would never get bored with. Anything else I tried just didn’t work for me. So the result is that I am not fit at all.. and that’s not a good thing, especially because I spend most of the time sitting in front of a computer. Anyways…

Here in Finland days are quickly getting shorter and darker, and the weather in general is depressing. Would be nice to spend some time in a sunnier place at the moment.

A little site news for the devs and bloggers who might be reading this: I have just switched from static pages hosted on Github Pages to Ghost. So I started with a self hosted WordPress, switched to Jekyll, then back to WordPress, then I decided to stop blogging and just to keep the existing posts available to Googlers I generated a static version of the blog which I published on Github pages. So it looks like I spent more time switching blogging software than actually writing posts. LOL.

Eventually, I switched to Ghost, but this time I am using a hosted service and I am kinda committed to it (I already paid for the first year). I didn’t want to bother with having to keep the blog software up to date on my servers, so it’s easier this way.

I really like Ghost! It’s pretty fast and its very simple, clean design makes blogging fun again. It’s not cluttered and bloated like WordPress, and it’s so nice to just write posts in Markdown (I know you can do this in WordPress too) with a live preview on the side. It makes blogging much easier and quicker. I can just focus on what I want to write more easily.

I guess that’s all for now. Stay tuned for updates and thank you for stopping by 🙂

Cheers
Vito