Table of contents

1. Introduction

1.1. Concepts

1.2. Revision history

1.3. Copyright

1.4. Disclaimer

1.5. Acknowledgments and Thanks

2. Tough choices

2.1. Choosing a server platform

2.2. Choosing a database backend

3. Theory

3.1. Account theory

3.2. Partitioning theory

3.3. Security theory

3.4. Directory structuring theory

4. Theory in practice

4.1. FreeBSD Installation

4.2. Jail network setup

4.3. Creating the jails

4.4. Updating ports

4.5. Jailing MySQL

4.6. libnss-mysql Installation

APPENDIX A GNU Free Documentation License

1. Introduction

1.1 Concepts

4.5. Jailing MySQL

I'm placing MySQL in a jail for it's own, namely 10.0.1.0 with the hostname 'pineapple'.

Note that you will need libc_r (re-entrant version of libc) installed in the jail if you wish to use FreeBSD's native threads (recommended). In other words, compiling the jail with NOLIBC_R in make.conf is a bad thing.

Another thing to note is NO_OPENSSL in make.conf. If set, you cannot compile MySQL with SSL support. Compiling MySQL with SSL support is recommended. In other words, don't define NO_OPENSSL in make.conf when compiling the jail.

NOTE! The first time I tried this, I installed MySQL 4.0.16, which was at the time the recommended production environment version and with OpenSSL 0.9.7a on FreeBSD 5.1-RELEASE. However, MySQL generated signal 11s (segfaults) about once/day, and plenty of 'ERROR 2026: SSL connection error' errors for no apparent reason. This problem has apparently also been encountered by someone else. I upgraded OpenSSL to version 0.9.7b and compiled MySQL version 4.0.17, which did remove the segfaults problem, but not the SSL connection errors. I finally solved the problem by installing MySQL 4.1.10 from the ports collection on FreeBSD 5.3-RELEASE, in the following fashion:

devfs and procfs are required by MySQL's configure script, otherwise it will fail with the following error:

'checking "how to check if pid exists"... configure: error:
Could not find the right ps switches. Which OS is this ?.
See the Installation chapter in the Reference Manual.'

Loopback mount /usr/ports and jail yourself to the cell:

Edit your /etc/hosts to reflect the hostname you picked for this jail:

127.0.0.1	pineapple

This one line is sufficient. Otherwise, mysql_install_db will halt with the following error:

Neither host 'pineapple' and 'localhost' could not be looked up with
/usr/local/bin/resolveip
Please configure the 'hostname' command to return a correct hostname.
If you want to solve this at a later stage, restart this script with
the --force option

Install MySQL from the ports collection:

This will take quite a while... go have a cup of coffee. Note that BUILD_STATIC=yes cannot be used when builing with OpenSSL. I had a problem with the dependency to Perl, as there is something wrong with the auto-detection of dependencies, causing the installation to halt with the following error:

makefile line 615: Need an operator

The solution is to edit /usr/ports/lang/perl5/work/makefile, and deleting line 615, if it contains only a zero (0).

Now we need to create certificates for the SSL support. This is almost verbatim from the MySQL documentation. If something goes wrong, you can simply rm -r the openssl/ directory. Note that this is the way to set variables in bash, in tcsh/csh this won't work. These are so called "self-signed" certificates. Exit the jail first.

Sample output:

/root/openssl/openssl.cnf converted

Generation of Certificate Authority(CA). You must specify a common name!

Sample output:

Generating a 1024 bit RSA private key
.....................................++++++
...............................................................++++++
writing new private key to '/root/openssl/private/cakey.pem'
Enter PEM pass phrase: <pick a pass phrase>
Verifying - Enter PEM pass phrase: <pick a pass phrase>
-----
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) [AU]:FI
State or Province Name (full name) [Some-State]:.
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL
Organizational Unit Name (eg, section) []:.
Common Name (eg, YOUR name) []:MySQL admin
Email Address []:

Create server request and key. Be sure to enter a different pass phrase here than you just did! Again, you must specify a common name, but a different one you previously entered!

Sample output:

Generating a 1024 bit RSA private key
.++++++
..............................................++++++
writing new private key to '/root/openssl/server-key.pem'
Enter PEM pass phrase: <pick a pass phrase>
Verifying - Enter PEM pass phrase: <pick a pass phrase>
-----
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) [AU]:FI
State or Province Name (full name) [Some-State]:.
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL
Organizational Unit Name (eg, section) []:.
Common Name (eg, YOUR name) []:MySQL server
Email Address []:

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

Remove the passphrase from the server cert.

Sample output. Enter the same password you just did.

Enter pass phrase for /root/openssl/server-key.pem:
writing RSA key

Sign server cert. Use first pass phrase here. Valid for about 10 years (3650 days).

Sample output:

Using configuration from /root/openssl/openssl.cnf
Enter pass phrase for /root/openssl/private/cakey.pem:
Check that the request matches the signature
Signature ok
Certificate Details:
        Serial Number: 1 (0x1)
        Validity
            Not Before: Dec 16 11:43:35 2003 GMT
            Not After : Dec 13 11:43:35 2013 GMT
        Subject:
            countryName               = FI
            organizationName          = MySQL
            commonName                = MySQL server

Certificate is to be certified until Dec 13 11:43:35 2013 GMT (3650 days)
Sign the certificate? [y/n]: y


1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated

Create client request and key.

Again, you must specify a common name, but a different one from the previously entered!

Generating a 1024 bit RSA private key
.....++++++
..............................++++++
writing new private key to '/root/openssl/client-key.pem'
Enter PEM pass phrase: <pick a pass phrase>
Verifying - Enter PEM pass phrase: <pick a pass phrase>
-----
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) [AU]:FI
State or Province Name (full name) [Some-State]:.
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL
Organizational Unit Name (eg, section) []:
Common Name (eg, YOUR name) []:MySQL user
Email Address []:

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

Remove the passphrase from the client cert.

Sign client cert. Valid for about 10 years (3650 days).

Sample output:

Using configuration from /root/openssl/openssl.cnf
Enter pass phrase for /root/openssl/private/cakey.pem:
Check that the request matches the signature
Signature ok
Certificate Details:
        Serial Number: 2 (0x2)
        Validity
            Not Before: Dec 16 11:49:46 2003 GMT
            Not After : Dec 13 11:49:46 2013 GMT
        Subject:
            countryName               = FI
            localityName              = Helsinki
            organizationName          = MySQL
            commonName                = MySQL user
        X509v3 extensions:
...

Certificate is to be certified until Dec 13 11:49:46 2013 GMT (3650 days)
Sign the certificate? [y/n]:y


1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated

Copy the certs inside the jail. Don't delete them just yet!

Copy /var/jail/10.0.1.0/usr/local/share/mysql/my-large.cnf to /var/jail/10.0.1.0/etc/my.cnf and add the following under [client]:

ssl-ca          = /etc/ssl/certs/cacert.pem
ssl-cert        = /etc/ssl/certs/client-cert.pem
ssl-key         = /etc/ssl/certs/client-key.pem

Add the following under [mysqld]:

ssl-ca          = /etc/ssl/certs/cacert.pem
ssl-cert        = /etc/ssl/certs/server-cert.pem
ssl-key         = /etc/ssl/certs/server-key.pem

I also added "skip-symbolic-links" under [mysqld]. We'll want MySQL to start whenever we start the jail. This is easy, as MySQL provides a script for this, that we just need to copy to the right place. While inside the jail, execute:

Next, we need to assign proper permissions to the file, in order to prevent tampering:

Now that MySQL starts automatically when the jail is started, we still need to remove the unneccessary users in the jail. Issue the 'vipw' command inside the jailcell and delete all rows except mysql and root. Change the mysql line from:

mysql:*:1001:1001::0:0:User &:/home/mysql:/bin/sh

to something like:

mysql:*:1001:1001::0:0:User &:/nonexistent:/sbin/nologin

Save the file and exit the editor.

However, this still does not prevent log file tampering. Let's create a small shell script, so that we can send a copy of the MySQL logs outside the jail. I created the following /etc/scripts/mysqllog-error.sh script on the host system:

#!/bin/sh
tail -f /var/jail/10.0.1.0/var/db/mysql/pineapple.err >> /var/log/mysql.err &

Now, even if an attacker could delete the log files in the MySQL jail, little does he know that we still have a copy on the host system! In order to prevent the logs from running wild, we still need to setup newsyslog to rotate the mysql log files, both on the host system and inside the jail. For this, we also need cron. Note that there is no need to run a separate cron/newsyslog process inside the jail, as all maintenance can be done from the host system.

On the host system, I added an entires such as this to /etc/newsyslog.conf:

# logfilename                                         [owner:group]  mode count size when  flags
/var/log/mysql.err                                                   600  7     1000 *     J
/var/jail/10.0.1.0/var/db/mysql/pineapple.err                        600  7     1000 *     J

To run newsyslog each hour, I added a cron job such as this on the host system:

0 * * * * newsyslog

Next, get rid of unneccessary files and directories. I did exactly as the FreeBSD man page for jail(8) recommends; I started out with a 'fat' jail and worked my way down. With only a few neccessary binaries inside the jailcell, a possible attacker will not have any tools which could be used to break from the jailcell, and even more importantly, can't install any. Make sure you are inside the jail before executing these! Also, I'd like to note that this worked for me, you'll probably want to customise these, depending on what you actually want in the jail.

This is the bare minimum, if you wish to have a "bootable" jail.

Finally, tighten the permissions of the remaining files.

MySQL will now start automatically each time the jail is started with the following command (or, even at boot time, if you've set jail_list in the host system's /etc/rc.conf):

You should see some similar messsages when the jail is starting:

Loading configuration files.
Entropy harvesting:sysctl: kern.random.sys.harvest.interrupt: Operation not permitted
 interruptssysctl: kern.random.sys.harvest.ethernet: Operation not permitted
 ethernetsysctl: kern.random.sys.harvest.point_to_point: Operation not permitted
 point_to_point.
ps: bad namelist
ps: bad namelist
Fast boot: skipping disk checks.
mount: /: unknown special file or file system
/etc/rc: INFO: run_rc_command: cannot run (/sbin/dhclient).
Additional routing options:.
hw.bus.devctl_disable: 1 -> 1
Mounting NFS file systems:.
Starting local daemons:.
Initial i386 initialization:.
Additional ABI support:.
Local package initialization:.
Additional TCP options:.
Starting mysqld daemon with databases from /usr/local/mysql/var
ps: bad namelist
Starting background file system checks in 60 seconds.

Fri Dec 19 15:01:52 EET 2003

You will then be returned to the host system's prompt. Don't worry about these errors, you can safely ignore them.

If you wish to start the daemon manually, execute the following command inside the jailcell:

Or, simply execute the /usr/local/etc/rc.d/mysql.server.sh script with the 'start' argument. Note that you'll need to chmod the jailcell's /tmp to 0777, or chown it to the MySQL user for MySQL to be able to start. I prefer to chown it, as there are no other programs inside the jail that need to be able to write to /tmp.

We'll want to rename the MySQL 'root' user to something else, and set a password to that user. A possible hack attempt on a MySQL server might target the one user that exists on most systems, "root", both because it has superuser powers and because it is a known user. By changing the name of the "root" user, you make it more difficult for would-be hackers to try a brute-force attack. First, jail yourself to the jailcell and start the MySQL shell:

No password is needed to access MySQL at this stage, as we haven't set one yet. First, let's rename the root user. Do NOT use the same as my example here ('root' read backwards is 'toor'), come up with something a bit more hard to guess.

mysql> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> UPDATE user SET User='toor' WHERE User='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

After that, set a password for our new 'toor' user:

mysql> UPDATE user SET Password=PASSWORD('new-password') WHERE user='toor';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Excellent. Finally, change the host from which we will be connecting. Note that we won't be connecting from localhost (10.0.1.0), but only from our webserver subsystem (10.0.1.1).

mysql> DELETE FROM user WHERE host='localhost';
Query OK, 2 rows affected (0.01 sec)

mysql> UPDATE user SET Host='10.0.1.1' WHERE user='toor';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

It might also be a good idea to require a SSL connection for the root (now 'toor') user:

mysql> UPDATE user SET ssl_type='ANY' WHERE user='toor';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Drop the 'test' database, which is just for testing purposes:

mysql> DROP DATABASE test;
Query OK, 0 rows affected (0.01 sec)

mysql> DELETE FROM db WHERE Db='test';
Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM db WHERE Db='test\_%';
Query OK, 1 row affected (0.01 sec)

mysql> DELETE FROM mysql.user WHERE User = '';
Query OK, 2 rows affected (0.01 sec)

Finally, make mysql notice the changes:

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

You may now exit MySQL's shell and the jail. Congratulations, the MySQL jail is now ready!

Currently, there is either a port or a package for PostgreSQL 7.4, thus we'll have to compile by hand:

Note that you'll need to chmod the jailcell's /tmp to 0777, or chown it to the 'postgres' user for PostgreSQL to be able to start. I prefer to chown it, as there are no other programs inside the jail that need to be able to write to /tmp.

Security Check. Please enter this code.