Practical study of a SQL-based server management solution

Updated: August 1st, 2011Published: December 11th, 2003
Time to read: 27 min

Article tags:

1. Introduction

1.1 Concepts

My objective is to put together a highly secure and usable server environment, that uses a SQL backend as much as possible for "everyday" routines, such as authentication, as well as storage for user account information. My aim was also to do all this with a very low budget, because I really didn't have a lot of money to spend. I'm well aware that you could do most of this by just using the ports collection, but sometimes compiling by hand is required, e.g. when you need to set a compile option not present in the ports version.

While there are some very good directory access protocols, such as LDAP, I've always preferred SQL databases, due to the fact that they are relatively widely supported and do an excellent job at storing and retrieving data.

The basic assumption here is that "everything is insecure by default".

If you're going to attempt a similar setup, I'll assume that you have knowledge of compiling software from source, installing FreeBSD, how jails work in FreeBSD and basic TCP/IP networking. This setup is also well suited for a VPS provider, such as DigitalOcean.

Throughout this document, you will see either hash symbols (#) or dollar signs ($) in front of the commands. These indicate whether the command should be run as a regular user or with superuser privileges (i.e. root). Do not include these in the commands! And by the way, copy-pasting commands is not such a great idea. I also provide my configuration files here for reference only. Please do not just copy them over to your server without fully understanding what they do. Additionally, please do not use the authors of this documents as a technical support, which we are most certainly not. Instead, you should consult the respective softwares' manual pages, documentations, forums and mailing lists.

1.4. Disclaimer

Use the information in this document at your own risk. I disavow any potential liability for the contents of this document. Use of the concepts, examples, and/or other content of this document is entirely at your own risk.

All copyrights are owned by their owners, unless specifically noted otherwise. Use of a term in this document should not be regarded as affecting the validity of any trademark or service mark.

Naming of particular products or brands should not be seen as endorsements.

You are strongly recommended to make a backup of your system before major installation and should make backups at regular intervals.

2. Tough choices

2.1. Choosing a server platform

I used to prefer Linux as a server platform, that is, until the day I came in contact with FreeBSD. While Linux is not a bad alternative, some areas of it are still very under-developed. I once tried setting up quotas on an ext3 filesystem and a stable 2.4.x series kernel, only to find out that you would have needed plenty of kernel patches at the time. Not an option in my books.

Windows was never an option for me, due to the fact that it can be very inflexible on some areas. The stability I won't even bother to go in on here.

While Linux still remains my number one workstation operating system, I've come to think of FreeBSD as the ultimate low-budget server operating system. It offers rock-solid stability most of the time, has really nice security features such as jailcells and kernel security levels, and has a bunch of useful performance-enhanching tools, such as the Vinum volume manager, which implements software RAID.

I was also considering OpenBSD, which is said to be "secure by default". OpenBSD does however not include the 'jail' functionality (other than 'chroot', that is). OpenBSD does, however, include a lot of seurity patches, claming that a 'jail'-like functionality is not needed in this case. I feel, that the 'jail' functionality is not equal to security, but more of a fail-safe against configuration error or coding mistakes, which could quite possibly happen. Unfortunately, OpenBSD lacks functional PAM and nsswitch implementations, thus making it hard if not impossible to authenticate against a SQL database.

In my books, NetBSD falls between OpenBSD and FreeBSD, not really offering anything new.

2.2. Choosing a database backend

This is most certainly not an easy choice to make, as a reliable database backend is the foundation for the server, and it's very difficult to change the backend when the system is running a production environment.

At the moment, MySQL seems to have the best support of the SQL databases. MySQL is also relatively easy to set up, but at the same time a bit more limited than, for example, PostgreSQL. In an ideal situation, the MySQL server should be replicated synchronously, but unfortunately - while such solutions do exist - that is a solution that comes at a quite heavy price tag.

PostgreSQL offeres open-source synchronous replication through their Postgres-R project.

However, there are several other aspects one must consider. One of these is software support. One must remember, that while for example the Exim MTA supports both MySQL and PostgreSQL, the Qpopper POP3 daemon can only be patched for MySQL support. Of course, at this point, it might be good to check out the competition, in order to see if someone else has implemented support for the database backend you're going to use.

3. Theory

3.1. Account theory

The basic services I wish to offer are the following:

  • FTP
  • HTTP
  • SSH
  • SQL
  • SMTP
  • POP3
  • IMAP4

FTP is mostly here for legacy reasons only. I've been meaning to replace it with SSH2's SFTP for a long time, but now that you can do TLS/SSL over FTP, the FTP service seems to die hard. In a high-security server, TLS/SSL is the ONLY way to properly do FTP, if it's non-anonymous.

These services will all need some sort of user accounts. Organising the accounts in a reasonable way is a challenging task of its own. As my focus is on SQL database backend, I wanted to store all user accounts in a SQL server. I did some research on some PAM (Pluggable Authentication Module) solutions, but discovered, that they were too inflexible for a solution such as this, due to the fact that they only provide an authentication method, whereas I wanted to get rid of the whole flat-file /etc/users and /etc/passwd system. I stumbled upon NSS modules while searching for a solution, and found libnss-mysql, which is perfect for the task.

The problem with flat-file /etc/users and /etc/passwd is that it's difficult to replicate the user base to another server. This issue is easily circumvented when using SQL replication with NSS-level authentication and account management. However, this leads to another problem. What happens if the SQL server is unreachable? The whole account system will become unusable, effectively crippling the offered services; hence my need for a replicated SQL server environment.

I have divided the accounts the above services require into four basic account levels:

Jailed system accounts:

  • Provide shell access (SSH) to website files (i.e. webspace).
  • Provide access to SQL server.
  • Provide quotas for website files.
  • Stored in MySQL using libnss-mysql.

MySQL accounts:

  • Provide access to MySQL databases.
  • Stored in MySQL's internal table.

Jailed FTP accounts:

  • Provide access to webspace.
  • Same as system accounts.

Mail accounts:

  • Provide access to mailboxes (both via POP3 and IMAP4).
  • Provide access to SMTP.
  • Provide quotas for maildirs.
  • Stored in a custom SQL table.
  • One mail login/mail account, no system accounts.

From the SQL point of view, the above setup will generate three tables; one for authenticating system (SSH/FTP) users, one for authenticating mail (SMTP/POP3/IMAP4) users, and finally one, which actually already exists in the default MySQL installation - the internal MySQL user database.

3.2. Partitioning theory

By placing /tmp and /var/log on their own partitions, we can easily avoid 'log-overflow' attacks. In these types of attacks, the idea is to make attacked daemon fill up the disk partition with logs, simply by submitting requests to it in a rapid pace - for example - each second, for an extended amount of time. At first I thought about creating separate partitions for all jailed /tmp and /var/log directories, in the following fashion:

Mount point

However, the clear disadvantage of this approach is the scattered log files and - obviously - the need for multiple, relatively small partitions. Also, managing this many partitions using bsdlabel is cumbersome. After some researching, I found that the -l argument of syslogd would do the trick, thus allowing me to use the following partition layout:

Mount point

The root filesystem of the host system should be placed on its own partition, due to the fact that any media errors that may occur during disk I/O to user files will corrupt the filesystem containing vital system files as well. One might even go as far as to create separate root partitions for all jails as well, but I think that's overkill.

3.3. Security theory

I have divided my server setup into several parts, which I will call subsystems. Each subsystem is actually a FreeBSD jailcell, running as few services as possible. Ideally, the jail/service ratio would be 1:1, but this is not possible in practice (read below why). Creating the jailcells is quite a bit of work, but pays off in terms of added security. The basic subsystems I'll need for the above services are as follows:

Webserver subsystem

This is by far the largest subsystem, due to the fact that many of the daemons need fileystem access to the same files. The daemons I need inside this jailcell are as follows:

  • Apache HTTPd. The HTTPd needs to have filesystem access to the files it's serving. Apache is in my opinion pretty much the de facto standard in today's Internet. Now that the HTTPd version 2 is quite stable, I decided to upgrade to that, and while I was at it, add support for a few more scripting languages. Communication with SQL is done via TCP.
  • Tomcat. As Java has begun to emerge from the deep deapths of Sun Microsystems to a usable product, I wanted support for JSP as well. For this task I chose Tomcat, from the Apache Jakarta team. The Apache HTTPd and Tomcat play quite nicely together.
  • PHP4. I do most of my web development in PHP, thus this is a must for me. PHP will run as an Apache module, and the module must reside somewhere where the HTTPd daemon finds it. The PHP language has native SQL support.
  • Perl. I use Perl quite extensively as the 'glue' that links all my services together. It also provides support for CGI scripts.
  • PureFTPd. As the FTP daemon also needs access to the actual files, this must also be placed inside this same jailcell.
  • Quota. This must be run inside this jailcell, as the FTP server must be subject to quota, in order to prevent the users from running amok with all the free space. While for example ProFTPd has a separate mod_sql module that provides quotas, it's irrelevant to my setup, as I can simply use FreeBSD's internal quotas, thanks to the NSS authentication system.
  • AwStats. I use AwStats to build the web site statistics. AwStats need access to the web server's log files, so in that goes as well. To build the statistics automatically, I'll also need cron. Communication with the SQL server is done over TCP using my AwStatsSQLBatch Perl script, which in turn uses Perl's DBI.

Mail subsystem

This is the second largest subsystem right after the webserver subsystem. The separation of the webserver files and maildirs allows me to specify separate quotas for both. The quota and account settings for mail are stored in a custom SQL table, which the following daemons will manipulate:

  • Exim. This is my choice of MTA, as it's reliable, handles Maildirs and both MySQL and PostgreSQL flawlessly. It needs filesystem access to the Maildirs. Exim provides the SMTP service.
  • Courier-IMAPd. Courier-IMAPd provides the IMAP4 service and has native MySQL and PostgreSQL support. The IMAP daemon needs filesystem access to the Maildirs.
  • Qpopper. Provides the POP3 service. The POP3 daemon requires filesystem access to the Maildirs. Qpopper can be patched for MySQL and Maildirs, Courier-IMAPd's POP3 daemon has native MySQL and PostgreSQL support.

The setup of this subsystem is documented in detail in my Exim, Amavisd-new, Courier-IMAP with TLS+MySQL Auth Mini How-To.

Amavisd-new subsystem

Amavisd-new is placed in its own chroot on the host system (no need for a full jail), as communication with Exim is done via SMTP and communication with SQL via TCP. Amavisd-new has native SQL support though Perl's DBI. The anti-virus scanner(s) must also reside inside this chroot. I will use F-Prot as my anti-virus scanner, because it's simple, gets the job done and mostly because I happen to have a license for it.

SQL subsystem

This subsystem will contain the heart and brains of the server, namely the SQL server. Communication with the 'outside world' is done via TCP.

The one big problem with MySQL is, that it hasn't got quota support. However, since MySQL lives inside the UFS file system, you can implement quotas by chown'ing databases to appropriate users and activating user quotas for the disk partition which the MySQL databases live on. You don't need to worry about this leaving your database in an inconsistent state when the quota is exceeded, as MySQL should then behave as in a "disk full" situation. The exact behaviour is documented in the MySQL manual.

3.4. Directory structuring theory

Organizing the directory structure is the most difficult of all tasks. My vision was to have per-user quotas, not per-website quotas. This approach has the advantage of a user being able to control several websites using only one login. The disadvantage is that each DocumentRoot needs to be looked up somewhere, as we cannot produce a valid path dynamically, for example, by using Apache's mod_rewrite's 'Mass Virtual Hosting'-technique.

At first I thought about solving the problem by using Apache's mod_rewrite to lookup paths using an External Rewriting Program, which actually was a Perl script, that pulls the data from my SQL table, which contains the DocumentRoots for all hosted virtual domains. That same SQL table is used by my AwStatsSQLBatch Perl script, which builds statistics for appropriate virtual domains.

Later, after some more thinking, I ditched that idea, and implemented mod_perl into my Apache httpd.conf file, thus allowing me to make the lookups straight from there.

Files created by content management systems (CMS) normally get the web server's UID/GID, and thus the files created by the CMS are not subject to quota. To solve this problem, PHP needs to be run using mod_suexec, which then runs the CMS with the user's UID/GID.

I decided to place all my jails (subsystems) under /var/jail/, and thus I ended up with a directory layout such as this.

User maildirs, access via POP3 and IMAP4 only, one login/mail address:


 `-- /var/jail/mail_subsystem/

User websites, access via SSH and FTP only:


 `-- /var/jail/www_subsystem
                                          /usr/bin -> /bin

The above directory structure is pretty much the only way to implement my vision of being able to administer several websites using one system (SSH/FTP) login. However, this layout leads to yet another problem. I wanted to chroot my FTP/SSH users to their own home directory inside the jailcell, for example /var/jail/www_subsystem/home/username. Thus, the users will only be able to see their own websites, and not other peoples. I do believe that chroot inside a jail is pure madness, but I couldn't think of any other way to implement this 'all-in-one' solution. Can you feel that paranoia running wild already? However, a problem arises when we want SSH functionality.

Providing a functional SSH shell is tricky when the users are chrooted into their own home directory, as they don't have access to /bin and /usr/bin. Symlinks outside the chroot will not work, but hardlinks do. I ended up solving this problem by hardlinking statically compiled basic programs under each user's home directory, under /bin and the creating a symlink from /usr/bin to /bin. The advantage of this approach is that you have very fine control of which programs each user has in their shell. The disadvantage is the increased disk space usage.

You'll need the MySQL client inside the chroot in order to allow access to the MySQL server, which is in its own subsystem.

4. Theory in practice

4.1. FreeBSD Installation

I'm doing this on a FreeBSD 5.2-RELEASE testing environment. I wanted to use the 5.x series mostly because they offer better jail support than the 4.x series, and due to the fact that NSS modules exist only in FreeBSD 5.0 and later. This is how I did this on my testing environment, and does not necessarily reflect the setup my production environment runs.

My testing environment has two 80GB IDE hard drives, ad0 is a Seagate (156296322 blocks) and ad2 a Samsung. I will create only one slice on each.

I'll now create a slightly modified setup of my 'A bootstrapped RAID-1 setup using Vinum' document. I will go through the installation pretty shortly here, see my Vinum document for more information about Vinum, in case you aren't familiar with it. Setting up a RAID-1 array is by no means necessary for a working setup, but it adds a whole lot of redundancy.

Below is the partitioning scheme I picked when installing FreeBSD:

Part		Mount		Size
----		-----		----
ad0s1b		swap		2000MB (4096000 blocks)
ad0s1a		/		4000MB (8192000 blocks)
ad0s1d		/usr		2000MB (4096000 blocks)
ad0s1e		/tmp		2000MB (4096000 blocks)
ad0s1f		/var/log	2000MB (4096000 blocks)
ad0s1g		/var		rest of drive (in my case 131720322 blocks, i.e. about 64347MB).

ad2s1b		swap		2000MB (4096000 blocks)
ad2s1d		/roootback	4000MB (8192000 blocks)
ad2s1e		/usr2		2000MB (4096000 blocks)
ad2s1f		/tmp2		2000MB (4096000 blocks)
ad2s1g		/var/log2	2000MB (4096000 blocks)
ad2s1h		/var2		131720322 blocks, i.e. about 64347MB

As ad2 was slightly bigger, I left 64260 blocks unallocated (about 31MB), as I couldn't think of any reasonable use for a partition that small.

After the installation and loader configuration, I edited the ad0s1 bsdlabel in a similar fashion:

  • Changed 'b' partition size from 4096000 to 4095719 and offset from 0 to 281.
  • Added 'h' partition, size 156296306, offset 16, fstype vinum.

After that, I created a similar configuration file for Vinum:

drive Redrum device /dev/ad0s1h
 volume root
   plex org concat
     sd len 8192000s driveoffset 4095984s drive Redrum
 volume swap
   plex org concat
     sd len 4095719s driveoffset 265s drive Redrum
 volume usr
   plex org concat
     sd len 4096000s driveoffset 12287984s drive Redrum
 volume tmp
   plex org concat
     sd len 4096000s driveoffset 16383984s drive Redrum
 volume varlog
   plex org concat
     sd len 4096000s driveoffset 20479984s drive Redrum
 volume var
   plex org concat
     sd len 131720322s driveoffset 24575984s drive Redrum

After I loaded the file into Vinum, I edited /etc/fstab to the following and rebooted to single-user mode:

# Device		Mountpoint	FStype	Options		Dump	Pass#
/dev/vinum/swap		none		swap	sw		0	0
/dev/vinum/root		/		ufs	rw		1	1
/dev/vinum/usr		/usr		ufs	rw		2	2
/dev/vinum/tmp		/tmp		ufs	rw		2	2
/dev/vinum/varlog	/var/log	ufs	rw		2	2
/dev/vinum/var		/var		ufs	rw,userquota,groupquota		2	2
/dev/acd0		/cdrom		cd9660	ro,noauto	0	0

Edited bsdlabel for ad2s1 to match ad0s1 and created a config file for Vinum:

drive BlackRain device /dev/ad2s1h
 volume root
   plex org concat
     sd len 8192000s driveoffset 4095984s drive BlackRain
 volume swap
   plex org concat
     sd len 4095719s driveoffset 265s drive BlackRain
 volume usr
   plex org concat
     sd len 4096000s driveoffset 12287984s drive BlackRain
 volume tmp
   plex org concat
     sd len 4096000s driveoffset 16383984s drive BlackRain
 volume varlog
   plex org concat
     sd len 4096000s driveoffset 20479984s drive BlackRain
 volume var
   plex org concat
     sd len 131720322s driveoffset 24575984s drive BlackRain

Loaded config into Vinum, revived plexes and exited to multi-user mode.

4.2. Kernel Compilation

The stock FreeBSD kernel does not include all of the features we need, and includes a few we don't. Thus, we need to compile a custom kernel. We'll definitely want to enable quota support:

options        QUOTA

The following option prevents something known as OS fingerprinting, which is a scan technique used to determine the type of operating system running on a host:

options        TCP_DROP_SYNFIN

However, as I'm running a web server I will NOT include this option in the kernel, as suggested in the FreeBSD handbook. Another interesting option is:

options        ICMP_BANDLIM

This option enables ICMP error response bandwidth limiting. You typically want this option as it will help protect the machine from denial of service packet attacks. However, this option is enabled by default in FreeBSD 5.x, so no need to add that. The following is almost verbatim from the FreeBSD handbook.

# cp /root/ZEUS /usr/src/sys/i386/conf/
# cd /usr/src/sys/i386/conf
# /usr/sbin/config ZEUS
# cd ../compile/ZEUS
# make depend
# make
# make install

I rebooted in order to load the new kernel.

4.3. Jail network setup

All of the subsystem IP addresses are aliases to my server's first NIC fxp0 (Intel EtherExpress Pro/100+). I'm using the A-class internal IP address range 10.x.x.x. I'll refer to the actual FreeBSD installation which contains the jailcells as the 'host system'. I will not go into the routing of these addresses here, as that's somewhat irrelevant to the jail setup. However, below is a simple example.

My host system is at, netmask, and has a direct connection to the Internet through fxp0. Before adding any jails, my network setup looks like this:

<*>Internet<*> ---- <Router> ---- <fxp0 ->[FreeBSD 5.1]

Jails are IP-centric, so we need to add one IP for each jail we want to use. Because each jail requires its own IP address, the services on your box must be configured to listen to specific addresses, not just every available address. For example, SSHd listens on all addresses by default. You need to set up ListenAddress in the host system's /etc/ssh/sshd_config to whatever your host system's address is. I set mine to Do a killall -HUP sshd to make sure SSHd notices the change in the config. If you fail to do this, conflicts may occur over the aliased IP address.

It is recommended that you experiment and caution that it is a lot easier to start with a 'fat' jail and remove things until it stops working, than it is to start with a 'thin' jail and add things until it works.

Here, I'm adding four IP addresses, which will be routed for Internet access in my router. In the host system's /etc/rc.conf I defined the following:

ifconfig_fxp0="inet netmask"
ifconfig_fxp0_alias0="inet alias netmask 0xffffffff"
ifconfig_fxp0_alias1="inet alias netmask 0xffffffff"
ifconfig_fxp0_alias2="inet alias netmask 0xffffffff"
ifconfig_fxp0_alias3="inet alias netmask 0xffffffff"

Note that we need to use a netmask that doesn't conflict with our current netmask, as "aliases on the same subnet as the primary IP should always have a netmask of". When setting the mask to, routes will be automatically set up for the aliases. Setting the netmask to something might work, but probably not. After adding the aliases, my network schematic would look like this:

<*>Internet<*> ---- <Router> ---- <fxp0 ->[FreeBSD 5.1]

One might go as far as to create the jail on a different subnet, and placing a firewall in between. However, I will not do so here.

4.4. Creating the jails

I have written a small shell script for jail setup. It's almost verbatim from man jail(8), with a few additions I've found useful. The script takes one argument, the name of the jail to be created.

# ./

Simple, isn't it? Now, in order to avoid having to go through the whole make world process for each jail, I simply copied the newly created, 'fresh' jail as many times as I needed jails.

4.5. Preliminary rc.conf configuration

We need to make some changes and additions to the host system's /etc/rc.conf.


RPCBind is evil. As I'm going to run Exim, no need for Sendmail to start (at all, see man rc.sendmail). Note that the sendmail_enable="NONE" method is DEPRECATED. ICMP redirects can be used to launch a DOS (Denial Of Service) attack. Let's prevent that:


If you wish to start all of your jails automatically at boot time, add rows such as this:

jail_list="kiwi cherry"
jail_kiwi_exec="/bin/sh /etc/rc"
jail_cherry_exec="/bin/sh /etc/rc"

4.6. Preliminary sysctl.conf configuration

Next, edit the host system's /etc/sysctl.conf, and place the following variables in it:


The first option will prevent the superuser in the jail to change the hostname of the jail. The second option must unfortunately be activated, as PostgreSQL requires it. If it's set to 0, PostgreSQL cannot be run inside a jail. Jail-NG would offer a solution for this, but is not yet quite production-ready. The third options prevents users from seeing information about processes that are being run under another UID. I see this as a good thing.

4.7. Updating ports

I will use the host system's ports tree along with mount_nullfs to install ports inside the jails. But first, we need to update the ports tree. I executed the following commands on the host system to accomplish this. This is almost verbatim from the FreeBSD handbook.

# pkg_add -r cvsup
# cp /usr/share/examples/cvsup/ports-supfile /root
# /usr/local/bin/cvsup -g -L 2 /root/ports-supfile

4.8. Keeping the time

I'm going to setup MSNTP (a Simple Network Time Protocol daemon/client) in order to keep the host system and some of the other computers on my LAN synchronized. IMHO full NTP is FAR too bloated nowadays. Do NOT use the NTP servers I use in my example. Find ones closer to where you are located. On the host system I executed the following commands:

# pkg_add -r msntp
# /usr/local/bin/msntp -r -x 480 &
# /usr/local/bin/msntp -S &

The idea here is to run the first msntp as the client to an external NTP server, and the second as a local server, which the other computers on my LAN will use. You might think "Hey, why not just run clients to external NTP sources on all of the other computers"? No. This is the proper way to do it.

Note that we needn't synchronize jails, as they use the host system's clock. It's sufficient the run tzsetup in a jail to set the timezone once, and then see to it that the host system's clock is synchronized.

To start the msntp daemon at boot, enter the following options in the host system's /etc/rc.conf:

ntpdate_flags="-S &"

Insert the following in the host system's crontab for root:

15 0,12 * * * /bin/nice --10 /usr/local/bin/msntp -r -x 480

I tried running the client msntp with the -a option, as recommended, but it failed. Don't really know why, maybe adjtime is broken? One could be paranoid and run the daemon in a jail of its own, but I considered this setup safe enough, as my firewall blocks traffic from outside my LAN to the msntp daemon.

4.9. syslog-ng Installation

I wanted a more fine-grained control over my log files, and the ability to store all log files form the jails outside the jails, in a centralized location. This approach prevents log file tampering, in case a malicious user would gain access to the log files inside a jail. Having all log files on a separate partition also helps dealing with log-overflow attacks.

The use of stunnel creates a SSL encrypted tunnel over which the log files pass between the log host and the client. Stunnel can encrypt any connection between two machines that are transferred using TCP. Because the original syslog can't send their logs to a remote log host over TCP, syslog-ng is used because it allows for TCP transfer.

If the remote log connection isn't encrypted, a packet sniffer, such as ethereal, can be used to read the log files as they are passed without encryption. Valuable information can be gathered by sniffing these log files that might pose a security risk.

Next, grab the syslog-ng package:

# pkg_add -r syslog-ng
# cp /usr/local/etc/syslog-ng/syslog-ng.conf.sample /usr/local/etc/syslog-ng/syslog-ng.conf

Now we need to replace FreeBSD's native syslogd with syslog-ng. This is done by placing the following in the host system's /etc/rc.conf:


Configuration of syslog-ng needs to be done carefully.

4.10. libnss-mysql Installation

Before installing libnss-mysql, you need to have MySQL running. See my 'Jailing MySQL on FreeBSD' document for a description on how I did it. libnss-mysql will replace the current /etc/passwd and /etc/group.

The real problem arises when we wish to use quotas. In order for quotas to work, we need to match the host system's UIDs/GIDs to the ones in our jails (they must be exactly the same!). Here libnss-mysql comes to the rescue, as using it's quite trivial to implement a unified user database for the host system and the jails. However, we will need to install libnss-mysql in all of the jails in which we wish to use quota.

On the host system, I executed the following commands:

$ fetch
$ tar xfvz libnss-mysql-1.0.tar.gz
$ cd libnss-mysql-1.0
$ ./configure --with-mysql-inc=/var/jail/ \ --with-mysql-lib=/var/jail/
$ make
# make install
# cp sample/freebsd/sample_database.sql /var/jail/

Edit /etc/libnss-mysql.cfg and optionally /etc/libnss-mysql-root.cfg. We need to tell libnss-mysql where to find our MySQL server. In /etc/libnss-mysql.cfg, define:

database    auth
username    nssuser
password    goodsecret
ssl         1
timeout     3
compress    1

This will allow us to transmit compressed SQL data over an SSL connection, which should be pretty hard to sniff. Save the file and exit the editor. Next, edit /etc/nsswitch.conf and add mysql to passwd/group:

passwd: files mysql
group:  files mysql

Also check that root:wheel is the owner of these files. Jail yourself to the SQL cell and load the sample database:

# jail /var/jail/ pineapple /bin/sh
# mysql -u root -p -e "source sample_database.sql"

Next, give SELECT privilege (enough for successful authentication) to the libnss user:

GRANT SELECT ON auth.* TO nssuser@ IDENTIFIED BY 'goodsecret' REQUIRE SSL;

Note the IP address! Even though we are connecting from (the host system), MySQL sees it as localhost, as it's an alias for fxp0. The rest of the command should be pretty self-explanatory.

We'll need to GRANT access from our webserver subsystem as well:

GRANT SELECT ON auth.* TO nssuser@ IDENTIFIED BY 'anothergoodsecret' REQUIRE SSL;

Tip: The id command is quite useful for testing the setup.

make will not work - you need gmake.

# pkg_add -r gmake
$ fetch
$ tar xfvz monit-4.1.1.tar.gz
$ cd monit-4.1.1
$ ./configure
$ gmake
# gmake install

About the author
I'm a millennial digital nomad and a seasoned IT professional with over 20 years of cross-industry experience, ready to help you with supercharging your business. Drop me a note or read more about what I can do for you!


This website is only intended to provide a quick overview of what I do. Please drop me a line if you'd like me to send you my full CV, references, certifications or any additional information.


Subscribe now to get notified of blog updates (no more than one email/month). No spam, promise!

Unsubscribe at any time. Signing up implies that you agree to the Terms.
This blog contains affiliate links to third parties. By using this site you agree to the Terms.