This was originally posted to my employer Redpill Linpro's annual pre-Christmas Sysadvent calendar of tech blogging

Distro packages are a blessing that most of us take for granted (thank you and sorry package maintainers everywhere!). They make installation, maintenance and even removal of both simple and complex software a breeze.

Sometimes you disagree

But sometimes you disagree with a decision made in the distro package. Or maybe just have a use-case that is specific enough that the generalized setup provided by the distro package collides with your needs.

A sometime

A while ago I had one such “sometime”. Let me explain: a lot of our customers here at Redpill Linpro Managed Services use Mysql. Among them is one of my customers, utdanning.no - the official Norwegian national education and career portal. From time to time their developers need to export data from Mysql to CSV file (that is comma seperated values). The best way to do get CSV from Mysql is to use SELECT .. INTO OUTFILE. The resultset from such an SELECT is dropped as a CSV file in the import/export directory configured with the variable secure_file_priv.
Utdanning.no runs on Ubuntu, and in Ubuntu the mysql-server-5.5 package creates a directory intended for this usage - /var/lib/mysql-files - in it’s ‘‘postinst’’ script:
# Lets extract the scripts (and then some) from the mysql-server-5.5 package:
root@dbserver:/tmp/mysql-server-5.5_control# dpkg --control /var/cache/apt/archives/mysql-server-5.5_5.5.58-0ubuntu0.14.04.1_amd64.deb
# And pick put the parts relevant to the import/export directory from the post-install script
root@dbserver:/tmp/mysql-server-5.5_control# grep mysql_filesdir DEBIAN/postinst
    mysql_filesdir=/var/lib/mysql-files
    if [ ! -d "$mysql_filesdir"      -a ! -L "$mysql_filesdir"      ]; then mkdir "$mysql_filesdir"; fi
    chown -R mysql:mysql $mysql_filesdir
    chmod 700 $mysql_filesdir
root@dbserver:/tmp/mysql-server-5.5_control#
This code ensures a directory like this exist:
drwx------ 2 mysql mysql 4096 nov.  23 10:09 /var/lib/mysql-files/
Since my customer wants to use SELECT .. INTO OUTFILE to export data it is not surprising that they also want to access the produced files with another user than mysql. (And as a sysadmin I strongly prefer them to not use “my” system users, but rather “their” application users. And since they are smart they want me to prefer them to not use “my” system users.) Basically the customer wants their developers to ‘just be able to access the files’, and I want to facilitate that in a simple, effective and non-convoluted way.
Non-convoluted is important since we work in teams in Redpill Linpro Managed Services. The easier it is for my co-workers to see how something works on a server, the better services we can provide. So we prefer to avoid implementing things in complicated ways with possibly suprising behaviour.

Chmod as a solution

For our customer and us the preferred solution is to have the directory accesible by their application user. An easy way to do that is to add the application user to the mysql group and chmod it so that it is accessible for members of the mysql group.
chmod 0770 /var/lib/mysql-files
Easy-peasy.

Chmod reverted

But as you probably noticed above - the postinst script will revert this. After every run of the postinst script - thus efter every time the mysql-server-5.5 package is upgraded - the directory will revert to being only accessible to the mysql user.

Sudo as a non-solution

Giving the customer sudo access to copy the files acting as the mysql user is easy, but it forces the developers to remember that they can’t just copy the files as they are used to, but have to use sudo. It also opens us up to security problems with globbing arguments in our sudo rules, unless we force them to decide on filenames in advance. A helper script to which we provide sudo access would remove the security concerns, but still leaves us with a cumbersome way of getting where we want. And for all we know maybe they don’t really want to copy the file, but to do something to it? The developers just want to access their exported data, and since the developers are our friends I want to help them do just what they want - without hassle.

Different ways to get our will

We just need to make sure that it is we, not the postinst script, that get our will when it comes to these permissions. Preferably in an elegant way.
Luckily there are lots of ways to solve this on a Unix-based system. Some ways not as good as others. Some not so elegant.

An ensured failure

The most straighforward solution is to remember to run the chmod command every time the package is upgraded. That is of course not going to happen, it will be forgotten. And with all these computers around there isn’t really any reason why we should handle this manually.

Be a puppet master

At Redpill Linpro Managed Services we use Puppet for configuration management, so we could let Puppet handle this. Most likely noone will try to “SELECT .. INTO OUTFILE” in the time between the upgrade and the next Puppet run, right? Well, probably not, but I bet that the one time when it is important to get access those data really quickly will be between a package upgrade and a Puppet run.
Cron is is the daemon who execute scheduled commands on your Linux system.
So we can just make it right all the time (well, once a minute that is) with the help of cron
File: /etc/cron.d/fix_permissions_mysql-files

# Ensure correct permissions for /var/lib/mysql-files directory
* * * * * root /bin/chmod 0770 /var/lib/mysql-files
This of course is overkill, in the sense that a package upgrade will set the wrong permissions a handful times a year and this cron job will try to fix it a little over half a million times a year. The strain this puts on the server should be negliable, but the strain such a solution puts on the mental health and well being of many sysadmins probably isn’t. And that is not really elegant, is it?
rm  /etc/cron.d/fix_permissions_mysql-files

Immediate (i)notification and reaction

A more elegant, and thus attractive, solution would be if we could trigger a chmod when there is a change to the directory, not when there is a change to what time it is. And the Linux kernel provides us with a way to do just that through the inotify subsystem. Inotify is a facility that makes it possible to do things when something changes in a filesystem. Luckily, since throwing together small C programs using kernel system calls isn’t modus operandi for most of us, there are user space tools that makes it possible to utilise the inotify functionality quite easily in shell scripts (and if your weapon of choice is C, Haskell, ocaml, python, ruby or basically anything else, most likely there is a library to help you getting inotified).
One of the tools available is incron:
[..] the “inotify cron” system. It consist of a daemon and a table manipulator. You can use it a similar way as the regular cron. The difference is that the inotify cron handles filesystem events rather than time periods
So this gives ut a method to revert the postinst script’s revert of our chmod immediately. This script will do the actual chmod (and leave a trace of this in the syslog)
File: /usr/local/bin/RL_fix_permissions_mysql-files.sh

#!/bin/bash

logger -t incron -p daemon.notice "Chmodding /var/lib/mysql-files back to 0770"
/bin/chmod 0770 /var/lib/mysql-files
And this is the configuration for incron
File: /etc/incron.d/RL_fix_permissions_mysql-files

# Ensure correct permissions for /var/lib/mysql-files directory
# chmod if metadata changes, but disable while doing this, so we don't loop.

/var/lib/mysql-files IN_ATTRIB,IN_NO_LOOP /usr/local/bin/RL_fix_permissions_mysql-files.sh
The IN_NO_LOOP ensures that our chmodding of the file doesn’t trigger a new inotification and thus a new chmod and a new inotification and a new chmod and ….
But every time a file inside the directory is changed we also change the metadata for the directory, and then we trigger a chmod. A completely unecessary chmod. And that is not really elegant, is it?
rm /usr/local/bin/RL_fix_permissions_mysql-files.sh
rm /etc/incron.d/RL_fix_permissions_mysql-files

React instead of random act

It would be better if we could react only when our chmod is reverted by the postinst script. It would be neat if we could hook into the package handling. And we can!
We can configure apt to trigger a script after running dpkg:
File: /etc/apt/apt.conf.d/98RL_fix_permissions_mysql-files

# Ensure correct permissions after dpkg invocations
DPkg::Post-Invoke { "/bin/chmod -v 0770 /var/lib/mysql-files"; };
Now every time dpkg runs we will make sure the permissions are as we want them. Let me install an old favourite to demonstrate
root@host:~# aptitude install cowsay
The following NEW packages will be installed:
  cowsay
0 packages upgraded, 1 newly installed, 0 to remove and 0 not upgraded.
[..]
Setting up cowsay (3.03+dfsg1-6) ...
mode of ‘/var/lib/mysql-files’ changed from 0700 (rwx------) to 0770 (rwxrwx---)
You noticed that last line, right?
“But this too is a sub-optimal solution, isn’t it?”, you’re probably thinking, and you are absolutely right. It will run chmod every time dpkg has been invoked from apt, which is quite often. And if we for some reason or not download the package and install it directly with dpkg our chmod will not be ran. The latter could be handled if we configured this with a ‘‘post-invoke’’ hook in dpkg.conf instead of treating it as apt configuration, but we would still only solve this for ourselves, and only on the systems where we add this configuration. And that is not really elegant, is it?
It is tempting to just
rm /etc/apt/apt.conf.d/98RL_fix_permissions_mysql-files
but I’m not doing that until we have a better solution in place.

Isn’t this really a bug?

The elegant and community oriented solution is to remove the cause for this behaviour, and make sure we don’t need to go in and chmod the directory ever again.
Rembemer, the problem comes from this code in the postinst script:
if [ ! -d "$mysql_filesdir" -a ! -L "$mysql_filesdir" ]; then mkdir "$mysql_filesdir"; fi
[..]
chown -R mysql:mysql $mysql_filesdir
chmod 700 $mysql_filesdir
and could be resolved if we move the chmod (and that chown that could lead to the same kind of problems) inside the if statement:
if [ ! -d "$mysql_filesdir" -a ! -L "$mysql_filesdir" ]; then
   mkdir "$mysql_filesdir"
   chown -R mysql:mysql $mysql_filesdir
   chmod 700 $mysql_filesdir
fi
To that end I have proposed this as a bug to the Ubuntu package maintainers, and hopefully I can soon
rm /etc/apt/apt.conf.d/98RL_fix_permissions_mysql-files
You can track the progress of the bug and my patch at Ubuntu Launchpad

Closing remarks

I hope you have enjoyed this little walk through my sometime episode. If you learned something new that’s great. All of the approaches above can prove useful for solving problems completely different from this one. You mighe have suggestions to other ways of solving this, and that’s great - one of my favourite things with Unix-based systems is that there are so many ways you can solve a problem, and thus so many new things you still can learn even after having been in the game for a couple of decades.
From all of us (including my old friend) to all of you:
 _________________________
< A very merry Christmas! >
 -------------------------
        \   ^__^
         \  (oo)\_______
            (__)\       )\/\
                ||----w |
                ||     ||