Stunneling (SSL Tunnels) MySQL on Ubuntu

We have really restrictive IT policies at my current employer which dictates that no client/server communication can happen in the clear. This has some interesting implications as most software servers do not support encryption or encrypted tunnels in any way.

What are the options?

• Write our own software that supports client/server encryption
• Only use existing applications that support client/server encryption
• Treat the encryption layer as an abstraction that the client/server is not aware of

The first two options have obvious drawbacks in terms of time and choice. The third seems to be the best way to transparently insert encryption into an existing architecture without requiring massive changes.

This approach can be done by:

• SSH tunnels
• OpenVPN tunnels
• Stunnel

There may be other options but these are the main ones I have had experience with. SSH tunnels are very convenient but tend to be unstable requiring restarts every now and again.

OpenVPN has also provided very little stability requiring frequent restarts.

Lets explore the setup of the third option then, stunnel.

Stunneling
The stunnel home page describes stunnel as:

Stunnel is a program that allows you to encrypt arbitrary TCP connections inside
SSL (Secure Sockets Layer) available on both Unix and Windows. Stunnel can allow
you to secure non-SSL aware daemons and protocols (like POP, IMAP, LDAP, etc) by
having Stunnel provide the encryption, requiring no changes to the daemon's code.

Sounds promising. They have a really comprehensive examples section on their site which I highly recommend you have a look at.

In my case I needed to get a MySQL master/slave pair to replicate via a secure channel. MySQL seems to support SSL but the package requires a rebuild with the required options which would mean I need a non-standard MySQL package which makes me itchy. I also needed a similar solution for several other applications that need secure communications channels so I've decided on this solution throughout my architecture.

See this Ubuntu thread for more info on rebuilding the MySQL package with SSL support if you're so inclined.

Our installation targets are an Ubuntu servers and will therefore be flavored accordingly but you should be able to easily adapt anything mentioned here for your platform of choice.

Preamble
If you are going to be using this for the same purposes I am ensure that you have a working MySQL master/slave setup. You don't want to troubleshoot multiple applications if something is not playing nice.

Installation
A simple aptitude install stunnel4 on the master and slave should do the trick.

Now, keep in mind that if one of the nodes you have stunnel installed on is acting as both a stunnel client and stunnel server you need to have at least one stunnel server running for the client entries in a config file and one for the server entries in a config file.

Not heading this warning will result in you seeing errors like this on the client:

2009.01.28 07:02:16 LOG7[11508:3082910608]: SSL state (connect): before/connect initialization
2009.01.28 07:02:16 LOG7[11508:3082910608]: SSL state (connect): SSLv3 write client hello A
2009.01.28 07:02:16 LOG7[11508:3082910608]: SSL alert (write): fatal: handshake failure
2009.01.28 07:02:16 LOG3[11508:3082910608]: SSL_connect: 1408F10B: error:1408F10B:SSL routines:SSL3_GET_RECORD:wrong version number
2009.01.28 07:02:16 LOG5[11508:3082910608]: Connection reset: 0 bytes sent to SSL, 0 bytes sent to socket

And this on the server:

2009.01.28 07:10:06 LOG7[13426:3082861456]: SSL state (connect): before/connect initialization
2009.01.28 07:10:06 LOG7[13426:3082861456]: SSL state (connect): SSLv3 write client hello A
2009.01.28 07:10:06 LOG7[13426:3082861456]: SSL alert (write): fatal: handshake failure
2009.01.28 07:10:06 LOG3[13426:3082861456]: SSL_connect: 1408F10B: error:1408F10B:SSL routines:SSL3_GET_RECORD:wrong version number
2009.01.28 07:10:06 LOG5[13426:3082861456]: Connection reset: 0 bytes sent to SSL, 0 bytes sent to socket

My convention is to use the default config file (/etc/stunnel/stunnel.conf) that ships with the stunnel4 package for the client configuration and a modified copy of that as a config (/etc/stunnel/mysql-master.conf, in this specific case) for the server.

If you will not be using the MySQL master server as a stunnel client then you can simply ensure that your /etc/stunnel/stunnel.conf is the same as my /etc/stunnel/mysql-master.conf.

Keep in mind that multiple config files in /etc/stunnel/ will cause multiple stunnel servers to be run with each of the files found there.

Server
First off, let's generate our own key (/etc/ssl/master.pem) that we'll be using for encryption. This key must be treated as a secure file that should have the relevant access controls placed on it.

root@master:~# cd /etc/ssl/
root@master:~# openssl req -new -x509 -nodes -days 730 -out master.pem -keyout master.pem
root@master:~# chmod 600 master.pem
root@master:~# dd if=/dev/urandom of=temp_file count=2
root@master:~# openssl dhparam -rand temp_file 512 >> master.pem
root@master:~# ln -sf master.pem `openssl x509 -noout -hash < coretools01.pem`.0

Now, change the following lines in /etc/stunnel/mysql-master.conf:

cert = /etc/ssl/master.pem
;client = yes
debug = 7
output = /var/log/stunnel4/stunnel.log

[mysql-master]
accept  = 443
connect = 127.0.0.1:3306
TIMEOUTclose = 0

This config will ensure we're running as a stunnel server, listening for incoming SSL connections on 0.0.0.0:443 and shunting the unencrypted connection to 127.0.0.1:3306 (ensure your MySQl master is listening for connections here).

Fire stunnel up on the master with /etc/init.d/stunnel4 start && tail -f /var/log/stunnel4/stunnel.log.

Client
Just modify the default /etc/stunnel/stunnel.conf that was installed via the package by changing the lines below:

;cert = /etc/stunnel/mail.pem
;key = /etc/stunnel/mail.pem
debug = 7
output = /var/log/stunnel4/stunnel.log
client = yes

[mysql-master]
accept  = 127.0.0.1:3307
connect = master.example.com:443
TIMEOUTclose = 0

This sets up the MySQL slave machine as a stunnel client, forwarding all unencrypted connections on 127.0.0.1:3307 via SSL to master.example.com:443. Just replace master.example.com with the correct FQDN for your master server. You also need to ensure that your MySQL slave's config points to 127.0.0.1:3307 for its replication requirements.

Fire stunnel up on the slave with /etc/init.d/stunnel4 start && tail -f /var/log/stunnel4/stunnel.log.

Testing
An initial test can be done from the slave with the mysql command line utility in a separate terminal session by connecting to a suitable user/db configured on the master with access from 'localhost' or '127.0.0.1':

root@slave:~# mysql -u replication -h 127.0.0.1 -P 3307 -p test
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.0.51a-3ubuntu5.4-log (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Yay!

Now, apply your general method for determining if your master and slave is in sync. You can use the logs at /var/log/stunnel4/stunnel.log if you hit any snags. Once you're happy with everything ensure you disable the debugging in the stunnel config files.

drb.rb:852:in `initialize': getaddrinfo: nodename nor servname provided, or not known (SocketError) (aka DRb TCPServer.open(0) failure on OS X)

Update (2009/01/01): Problem TT moved to redmine.
Update (2009/05/13): TT resolved.

I am currently busy building an priority queue server in ruby and I have chosen to use DRb as my communications platform.

While experimenting the simple examples from the Net (see here and here) I was consistently getting the same error from inside drb.rb (/opt/local/lib/ruby/1.8/drb/drb.rb:852):

/opt/local/lib/ruby/1.8/drb/drb.rb:852:in `initialize': getaddrinfo: nodename nor servname provided, or not known (SocketError)
     from /opt/local/lib/ruby/1.8/drb/drb.rb:852:in `open'
     from /opt/local/lib/ruby/1.8/drb/drb.rb:852:in `open_server_inaddr_any'
     from /opt/local/lib/ruby/1.8/drb/drb.rb:864:in `open_server'
     from /opt/local/lib/ruby/1.8/drb/drb.rb:759:in `open_server'
     from /opt/local/lib/ruby/1.8/drb/drb.rb:757:in `each'
     from /opt/local/lib/ruby/1.8/drb/drb.rb:757:in `open_server'
     from /opt/local/lib/ruby/1.8/drb/drb.rb:1346:in `initialize'
     from /opt/local/lib/ruby/1.8/drb/drb.rb:1634:in `new'
     from /opt/local/lib/ruby/1.8/drb/drb.rb:1634:in `start_service'
     from ./queue-provider.rb:32

What's up?
After poking drb.rb::self.open_server_inaddr_any(host, port) with a stick a few times two issues came to light:

1. Multiple network address families are not catered for properly in the the code.


2. TCPServer.open(port) where port == 0 fails under OS X but not Linux

Multiple Address Families
The code in question looks like this:

def self.open_server_inaddr_any(host, port)
infos = Socket::getaddrinfo(host, nil,
                           Socket::AF_UNSPEC,
                           Socket::SOCK_STREAM,                                  0,
                           Socket::AI_PASSIVE)
family = infos.collect { |af, *_| af }.uniq
case family
when ['AF_INET']
 return TCPServer.open('0.0.0.0', port)
when ['AF_INET6']
 return TCPServer.open('::', port)
else
 return TCPServer.open(port)
end
end

From that we can see that we only seem to expect one network address family which is a little naive. Socket::getaddrinfo() on my MacBook Pro has the following to say (where host == 'localhost'):

$ irb
irb(main):006:0> require "socket"
=> true
irb(main):007:0> host = 'localhost'
=> "localhost"
irb(main):008:0> Socket::getaddrinfo(host, nil,
irb(main):009:1* Socket::AF_UNSPEC,
irb(main):010:1* Socket::SOCK_STREAM,                                  0,
irb(main):011:1* Socket::AI_PASSIVE)
=> [["AF_INET6", 0, "localhost", "::1", 30, 1, 6], ["AF_INET6", 0, "localhost", "fe80::1%lo0", 30, 1, 6], ["AF_INET", 0, "localhost", "127.0.0.1", 2, 1, 6]]

When you take this as your input you'll see that we don't end up matching either 'AF_INET' or 'AF_INET6' and we fall through to return TCPServer.open(port) because the case block expects a match against an array with one element.

TCPServer.open(0) OS X Weirdness
I have used DRb on both Linux and Windblowns in the past without a hitch so I was rather surprised to run into something like this which is a show stopper on OS X. I though I'd see if I was having the same problems on Linux to have something to compare with:

$ irb
irb(main):001:0> require "socket"
=> true
irb(main):002:0> port = 0
=> 0
irb(main):003:0> TCPServer.open(port)
=> #

Works a treat! Let's try that on OS X:

$ irb
irb(main):001:0> require "socket"
=> true
irb(main):002:0> port = 0
=> 0
irb(main):003:0> TCPServer.open(port)
SocketError: getaddrinfo: nodename nor servname provided, or not known
     from (irb):3:in `initialize'
     from (irb):3:in `open'
     from (irb):3
     from :0

CRASH! BOOM! BANG!

DRb Quilt
The first issue is rather trivial to fix:

def self.open_server_inaddr_any(host, port)
infos = Socket::getaddrinfo(host, nil,
                            Socket::AF_UNSPEC,
                            Socket::SOCK_STREAM,
                            0,
                            Socket::AI_PASSIVE)
families = Hash[*infos.collect { |af, *_| af }.uniq.zip([]).flatten]
return TCPServer.open('0.0.0.0', port) if families.has_key?('AF_INET')
return TCPServer.open('::', port) if families.has_key?('AF_INET6')
return TCPServer.open(port)
end

The code now rather assumes we're dealing with an array of one or more network address families and tries the IPv4 and IPv6 families first and then falls though to TCPServer.open(port).

I have opened a TT on RubyForge for this that contains a patch from me to fix the first issue.

What is required to fix the second issue? Dunno just yet, I'll keep looking and see if anything interesting pops up in the TT.

Massaging Rails Models (with a happy ending)

How do you alter data in a model so that the data which is stored to and gathered from the database is first filtered/transformed?

Two ways come to mind:

• Insert the required behavior into your model's before_save, before_create and after_initialize callbacks.
• Manually modify your attribute accessors for the attributes in question to do the magic for you.

We'll use the following contrived Model as our example:

class Gogga < ActiveRecord::Base
end

CREATE TABLE  `foo`.`goggas` (
`id` int(11) NOT NULL auto_increment,
`secret` varchar(255) default NULL,
PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

A Gogga has an id and a secret field. Let's pretend we need to keep Gogga.secret encrypted (using the super secret ROT13 algorithm) in our db and we would like the fact that it is encrypted to be transparent to our rails app. We need to therefore handle en/decryption of the secret data transparently from the rest of the app within the model.

Callbacks
The before_save, before_create and after_initialize callbacks are well documented in the Callbacks API documentation.

The strategy behind using the callbacks is to simply insert the behavior we want at the relevant stage of the object's life cycle. Here's one way to accomplish this using the mentioned callbacks:

class Gogga < ActiveRecord::Base
  def before_save
    self.secret = rot13(self.secret)
  end

  def before_create
    self.secret = rot13(self.secret)
  end

  def after_initialize
    self.secret = rot13(self.secret)
  end

protected
  def rot13(corpus)
    return corpus.tr!("A-Za-z", "N-ZA-Mn-za-m")
  end
end

If everything works as advertised our secret attribute should now be encoded when you call create or save on its model and decoded when you call a new on its model. The major drawback of this strategy is that if you are manipulating a large list of Goggas you will be post/pre-processing each of those instances.

The Lazy Way
An alternative would be to override the default behavior of the model to auto-generate attribute accessors via method_missing in the mystical black guts of ActiveRecord::Base. There's some info on this in the API docs as well in the Overwriting default accessors section.

This would look something like this:

class Gogga < ActiveRecord::Base
  def secret
    return rot13(read_attribute("secret"))
  end

  def secret=(value)
    write_attribute("secret", rot13(value))
  end

protected
  def rot13(corpus)
    return corpus.tr!("A-Za-z", "N-ZA-Mn-za-m")
  end
end

This technique has the advantage that you never really mess with the internals of the model (as the view you have of it from outside is tinted by the accessor transformations) and of course work only gets done when you need to read/write the specific attribute.

Now, when you have one or two attributes that need to be protected writing out two accessors for each is not the end of the world. However, when you have several things become messy, tedious and downright boring.

Maybe we can look at some meta-magic to DRY things up a bit in another article.

Oh ExtJS TreePanel Click, wherefore art thou?

ExtJS is a high quality JS UI library that really eases the cross-platform blues when it comes to designing online apps. Unfortunately I find the documentation can be a be a little obtuse from time to time.

Even the trusty Google librarian cannot answer some of the questions that pop up right off the bat and a fair amount of searching is required to get some info.

TreePanel Click
I have a TreePanel that I populate through lazy loading to save the amount of data I need to send off to the client in any one request. Unfortunately for the life of me I could not work out how to determine which node in the tree was clicked (so that I could fire off an Ajax request to populate another panel with data related to the clicked node).

Attaching Events
The first thing to do would obviously be to attach a click event listener somewhere so that we can pick up when were being prodded. If you're not familiar to JavaScript and ExtJS specifically you'd be inclined to attach an event to each of the nodes.

This is wasteful in the browser context as you should be taking advantage of event bubbling. So we simply attach a listener to the whole tree component:

  Ext.onReady(function(){
   // Create initial root node
   root = new Ext.tree.AsyncTreeNode({
     text: 'Invisible Root',
     id:'0'
   });
  
   // Create the tree
   tree = new Ext.tree.TreePanel({
     loader: new Ext.tree.TreeLoader({
       url:'/companies',
       requestMethod:'GET',
       baseParams:{format:'json'}
     }),
     renderTo:'company-tree',
     root: root,
     rootVisible:false
   });
  
   // Expand invisible root node to trigger load of the first level of actual data
   root.expand();
  
   // Listen for mouse clicks
   Ext.get('company-tree').on("click", function(){
     // Code to determine which node was clicked ...
   });
 });

Where did that click go?
Never having used the TreePanel control before I had no real inkling how I was going to reap the relevant id from the node that was clicked. I tried several approaches but nothing was bearing any fruit till I found this article on the ExtJS forums discussing something similar.

Armed with a new weapon I was able to refactor the listener:

  // Listen for mouse clicks
 Ext.get('company-tree').on("click", function(){
   node = tree.getSelectionModel().getSelectedNode();
   console.log("You clicked on", node.id);
 });

That'll do.

Installing PECL/PEAR PHP modules on a RHEL box

The default RHEL 5.2 installation does not come with xdebug as part of any of the php RPMs. A quick look around the Net also provided no real RPM candidates that I could use on this system so I had to fall back to using the package management tools (pecl and pear) provided by php.

PECL's Odyssey
RHEL is a general PITA for me already so I just sigh and get on with it:

$ sudo pecl install xdebug
downloading xdebug-2.0.3.tgz ...
Starting to download xdebug-2.0.3.tgz (286,325 bytes)
...........................................................done: 286,325 bytes
66 source files, building
running: phpize
Configuring for:
PHP Api Version:         20041225
Zend Module Api No:      20060613
Zend Extension Api No:   220060519
/usr/bin/phpize: /tmp/pear/download/xdebug-2.0.3/build/shtool: /bin/sh: bad interpreter: Permission denied
Cannot find autoconf. Please check your autoconf installation and the $PHP_AUTOCONF
environment variable is set correctly and then rerun this script.

ERROR: `phpize' failed

Yep, loving it already!

Why on earth am I not able to invoke /bin/sh (as can be seen by the '/bin/sh: bad interpreter: Permission denied' error above)? Let's see if root can actually run the shell interpreter:

$ sudo /bin/sh
sh-3.2# exit

OK, everything looks good. Why is it breaking when we're trying to run the interpreter from /tmp/pear/download/xdebug-2.0.3/build/shtool?

Back to basics
Perhaps this has something to do with where we're trying to run it from and the user we're doing the installation as (root) seems to be capable of running the interpreter but not from the shtool script for some reason.

$ ls -ld /tmp/
drwxrwxrwt 17 root root 4096 Jun 18 07:41 /tmp/

Obviously _not_ a permissions issue.

$ grep tmp /etc/fstab
/dev/sda2     /tmp    ext3    defaults,nosuid,nodev,noexec    1 2

Ah, there you are! /tmp is mounted with a 'noexec' flag so that's what's causing the execution to fail when we try to install xdebug via pecl. No problem, I'll just set pecl to use /var/tmp instead ... oh, wait, on RHEL systems /var/tmp is just a symlink to /tmp.

*sigh*

Hand me half a brick
Time to work around the issue. Let's go find those directories pear expects to be somehow related to /tmp or /var/tmp:

$ pear config-show | grep tmp
PEAR Installer download        download_dir     /tmp/pear/download
PEAR Installer temp directory  temp_dir         /var/tmp

I updated these to temporarily point elsewhere:

$ pecl config-show| grep tmp
PEAR Installer download        download_dir     /root/tmp/pear/download
PEAR Installer temp directory  temp_dir         /root/tmp
$ sudo mkdir -p /root/tmp/pear/download

Second verse, same as the first
Let's give that installation another whirl and see where we are:

$ sudo pecl install xdebug
downloading xdebug-2.0.3.tgz ...
Starting to download xdebug-2.0.3.tgz (286,325 bytes)
.....................................done: 286,325 bytes
66 source files, building
running: phpize
Configuring for:
PHP Api Version:         20041225
Zend Module Api No:      20060613
Zend Extension Api No:   220060519
building in /var/tmp/pear-build-root/xdebug-2.0.3
running: /root/tmp/pear/download/xdebug-2.0.3/configure
checking for egrep... grep -E
checking for a sed that does not truncate output... /bin/sed
checking for gcc... gcc
checking for C compiler default output file name... a.out
checking whether the C compiler works... configure: error: cannot run C compiled programs.
If you meant to cross compile, use `--host'.
See `config.log' for more details.
ERROR: `/root/tmp/pear/download/xdebug-2.0.3/configure' failed

A quick look at /root/tmp/pear/download/xdebug-2.0.3/configure shows no obvious reasons why we're failing so out comes the cluebat :

$ sudo strace pecl insstall xdebug 2>&1
access("/etc/ld.so.preload", R_OK)      = -1 ENOENT (No such file or directory)
execve("/usr/bin/pecl", ["pecl", "install", "xdebug"], [/* 16 vars */]) = 0
brk(0)                                  = 0x8c3d000

[... truncated ...]

flock(3, LOCK_UN)                       = 0
close(3)                                = 0
write(1, "ERROR: `/root/tmp/pear/download/"..., 63ERROR: `/root/tmp/pear/download/xdebug-2.0.3/configure' failed
) = 63
stat64("/var/tmp/pear-build-root/xdebug-2.0.3", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
lstat64("/var/tmp/pear-build-root/xdebug-2.0.3", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
open("/var/tmp/pear-build-root/xdebug-2.0.3", O_RDONLY|O_NONBLOCK|O_LARGEFILE|O_DIRECTORY) = 3
fstat64(3, {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
fcntl64(3, F_SETFD, FD_CLOEXEC)         = 0
time(NULL)                              = 1213740767
lstat64("/var", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
lstat64("/var/tmp", {st_mode=S_IFLNK|0777, st_size=4, ...}) = 0
readlink("/var/tmp", "/tmp", 4096)      = 4
lstat64("/tmp", {st_mode=S_IFDIR|S_ISVTX|0777, st_size=4096, ...}) = 0
lstat64("/tmp/pear-build-root", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
lstat64("/tmp/pear-build-root/xdebug-2.0.3", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
getdents(3, /* 4 entries */, 4096)      = 80
getdents(3, /* 0 entries */, 4096)      = 0
close(3)                                = 0
stat64("/tmp/pear-build-root/xdebug-2.0.3/config.log", {st_mode=S_IFREG|0644, st_size=4948, ...}) = 0
stat64("/tmp/pear-build-root/xdebug-2.0.3/config.nice", {st_mode=S_IFREG|0755, st_size=93, ...}) = 0
unlink("/tmp/pear-build-root/xdebug-2.0.3/config.log") = 0
unlink("/tmp/pear-build-root/xdebug-2.0.3/config.nice") = 0
rmdir("/tmp/pear-build-root/xdebug-2.0.3") = 0
stat64("/var/tmp/pear-build-root/install-xdebug-2.0.3", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
lstat64("/var/tmp/pear-build-root/install-xdebug-2.0.3", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
open("/var/tmp/pear-build-root/install-xdebug-2.0.3", O_RDONLY|O_NONBLOCK|O_LARGEFILE|O_DIRECTORY) = 3
fstat64(3, {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
fcntl64(3, F_SETFD, FD_CLOEXEC)         = 0
time(NULL)                              = 1213740767
lstat64("/var", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
lstat64("/var/tmp", {st_mode=S_IFLNK|0777, st_size=4, ...}) = 0
readlink("/var/tmp", "/tmp", 4096)      = 4
lstat64("/tmp", {st_mode=S_IFDIR|S_ISVTX|0777, st_size=4096, ...}) = 0
lstat64("/tmp/pear-build-root", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
lstat64("/tmp/pear-build-root/install-xdebug-2.0.3", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
getdents(3, /* 2 entries */, 4096)      = 32
getdents(3, /* 0 entries */, 4096)      = 0
close(3)                                = 0
rmdir("/tmp/pear-build-root/install-xdebug-2.0.3") = 0
stat64("/tmp/glibctestUP8dWe", 0xbfa97ca0) = -1 ENOENT (No such file or directory)
unlink("/tmp/glibctestUP8dWe")          = -1 ENOENT (No such file or directory)
umask(022)                              = 022
close(2)                                = 0
close(1)                                = 0
close(0)                                = 0
munmap(0xb7fd6000, 4096)                = 0
setitimer(ITIMER_PROF, {it_interval={0, 0}, it_value={0, 0}}, NULL) = 0
brk(0xa3ce000)                          = 0xa3ce000
setitimer(ITIMER_PROF, {it_interval={0, 0}, it_value={0, 0}}, NULL) = 0
brk(0xa18a000)                          = 0xa18a000
exit_group(0)

After all that pecl (aka pear) still tried to muck with /tmp by trying to run things in /var/tmp/pear-build-root/xdebug-2.0.3.

Fine. Be that way.

$ sudo cd /tmp && rm -fr pear pear-build-root
$ sudo ln -s /root/tmp/pear-build-root .

One more time please Sam:

$ sudo pecl install xdebug
downloading xdebug-2.0.3.tgz ...
Starting to download xdebug-2.0.3.tgz (286,325 bytes)
.....................................................done: 286,325 bytes
66 source files, building
running: phpize
Configuring for:
PHP Api Version:         20041225
Zend Module Api No:      20060613
Zend Extension Api No:   220060519
building in /var/tmp/pear-build-root/xdebug-2.0.3
running: /root/tmp/pear/download/xdebug-2.0.3/configure
checking for egrep... grep -E
checking for a sed that does not truncate output... /bin/sed
checking for gcc... gcc
checking for C compiler default output file name... a.out
checking whether the C compiler works... yes

[... truncated ...]

Build complete.
Don't forget to run 'make test'.

running: make INSTALL_ROOT="/var/tmp/pear-build-root/install-xdebug-2.0.3" install
Installing shared extensions:     /var/tmp/pear-build-root/install-xdebug-2.0.3/usr/lib/php/modules/
running: find "/var/tmp/pear-build-root/install-xdebug-2.0.3" -ls
36962402    4 drwxr-xr-x   3 root     root         4096 Jun 18 08:20 /var/tmp/pear-build-root/install-xdebug-2.0.3
36962465    4 drwxr-xr-x   3 root     root         4096 Jun 18 08:20 /var/tmp/pear-build-root/install-xdebug-2.0.3/usr
36962466    4 drwxr-xr-x   3 root     root         4096 Jun 18 08:20 /var/tmp/pear-build-root/install-xdebug-2.0.3/usr/lib
36962467    4 drwxr-xr-x   3 root     root         4096 Jun 18 08:20 /var/tmp/pear-build-root/install-xdebug-2.0.3/usr/lib/php
36962468    4 drwxr-xr-x   2 root     root         4096 Jun 18 08:20 /var/tmp/pear-build-root/install-xdebug-2.0.3/usr/lib/php/modules
36962464  608 -rwxr-xr-x   1 root     root       618210 Jun 18 08:20 /var/tmp/pear-build-root/install-xdebug-2.0.3/usr/lib/php/modules/xdebug.so

Build process completed successfully
Installing '/usr/lib/php/modules/xdebug.so'
install ok: channel://pecl.php.net/xdebug-2.0.3
configuration option "php_ini" is not set to php.ini location
You should add "extension=xdebug.so" to php.ini

Smell that? It's sweet success!

No more crawling, it's time to walk!
Let's finish the install off for brevity's sake:

$ sudo echo 'zend_extension="/usr/lib/php/modules/xdebug.so"' > /etc/php.d/xdebug.ini
$ php -i | grep xdebug
/etc/php.d/xdebug.ini,
xdebug
xdebug support => enabled
xdebug.auto_trace => Off => Off
xdebug.collect_includes => On => On
xdebug.collect_params => 0 => 0
xdebug.collect_return => Off => Off
xdebug.collect_vars => Off => Off
xdebug.default_enable => On => On
xdebug.dump.COOKIE => no value => no value
xdebug.dump.ENV => no value => no value
xdebug.dump.FILES => no value => no value
xdebug.dump.GET => no value => no value
xdebug.dump.POST => no value => no value
xdebug.dump.REQUEST => no value => no value
xdebug.dump.SERVER => no value => no value
xdebug.dump.SESSION => no value => no value
xdebug.dump_globals => On => On
xdebug.dump_once => On => On
xdebug.dump_undefined => Off => Off
xdebug.extended_info => On => On
xdebug.idekey => root => no value
xdebug.manual_url => http://www.php.net => http://www.php.net
xdebug.max_nesting_level => 100 => 100
xdebug.profiler_aggregate => Off => Off
xdebug.profiler_append => Off => Off
xdebug.profiler_enable => Off => Off
xdebug.profiler_enable_trigger => Off => Off
xdebug.profiler_output_dir => /tmp => /tmp
xdebug.profiler_output_name => cachegrind.out.%p => cachegrind.out.%p
xdebug.remote_autostart => Off => Off
xdebug.remote_enable => Off => Off
xdebug.remote_handler => dbgp => dbgp
xdebug.remote_host => localhost => localhost
xdebug.remote_log => no value => no value
xdebug.remote_mode => req => req
xdebug.remote_port => 9000 => 9000
xdebug.show_exception_trace => Off => Off
xdebug.show_local_vars => Off => Off
xdebug.show_mem_delta => Off => Off
xdebug.trace_format => 0 => 0
xdebug.trace_options => 0 => 0
xdebug.trace_output_dir => /tmp => /tmp
xdebug.trace_output_name => trace.%c => trace.%c
xdebug.var_display_max_children => 128 => 128
xdebug.var_display_max_data => 512 => 512
xdebug.var_display_max_depth => 3 => 3

I can now delete those temporary directories I created in /root/tmp and get back to twitching in the corner, or ...

Easy alternative - just add remount
Instead of the hoops I jumped through to get the pecl bits to stop addressing /tmp I could simply have remounted the /tmp filesystem to allow execution:

$ sudo mount -o remount,exec /tmp
$ sudo pecl install xdebug
$ sudo mount -o remount,defaults,nosuid,nodev,noexec /tmp

This however would have compromised the security that was put in place to stop the possible malicious execution of bits (especially root kits) in /tmp (or /var/tmp).

You have the power (and the knowledge now) so wield it to your benefit.

warning: SASL authentication failure: cannot connect to saslauthd server: No such file or directory

There's nothing quite like being in a complete coding frenzy, communicating with your customers to get feedback on critical bugs, and your mail server going to SMTP heaven on you.

The Setup
My MTA (postfix) is set up to do secure SMTP-AUTH and TLS via Cyrus SASL library (a.k.a. saslauthd via the sasl2-bin package) on an Ubuntu box.

Postfix SASL support (RFC 4954, formerly RFC 2554) is used to authenticate remote SMTP clients to the MTA and the Postfix SMTP client to a remote SMTP server.

The Error
I originally set things up via the Postfix-SMTP-AUTH-TLS-Howto and everything was working fine until earlier today when I started seeing the following log entries when trying to send mail vi the MTA:

May 30 03:03:36 pyxidis postfix/smtpd[2840]: connect from unknown[x.x.x.x]
May 30 03:03:37 pyxidis postfix/smtpd[2840]: setting up TLS connection from unknown[x.x.x.x]
May 30 03:03:40 pyxidis postfix/smtpd[2840]: Anonymous TLS connection established from unknown[x.x.x.x]: TLSv1 with cipher AES128-SHA (128/128 bits)
May 30 03:03:40 pyxidis postfix/smtpd[2840]: warning: SASL authentication failure: cannot connect to saslauthd server: No such file or directory
May 30 03:03:40 pyxidis postfix/smtpd[2840]: warning: SASL authentication failure: Password verification failed
May 30 03:03:40 pyxidis postfix/smtpd[2840]: warning: unknown[x.x.x.x]: SASL PLAIN authentication failed: generic failure
May 30 03:03:46 pyxidis postfix/smtpd[2840]: lost connection after AUTH from unknown[x.x.x.x]
May 30 03:03:46 pyxidis postfix/smtpd[2840]: disconnect from unknown[x.x.x.x]

The Solution
I checked and the saslauthd process was happily running. Next up I had a peek in /var/spool/postfix/var/run/saslauthd/ (which I had previously created as per the HOWTO above) but there were no *mux* files to be seen as there should have been.

I then dawned on me that postfix runs in a chrooted jail and that saslauthd for some reason had stopped writing the required info to the chrooted jail where postfix was running. A quick look at the saslauthd rc script and its default file showed that it no longer had the required config to do this properly.

Why? Dunno. I'll have to go do some snooping a little later.

For now thought the fix was as simple as modifying the OPTIONS variable in the /etc/defaults/saslauthd config file to be something like this:

OPTIONS="-c -m /var/spool/postfix/var/run/saslauthd"

Restart saslauthd and things start appearing where they should and mail is back in business.

Cacti Segfaults

I recently did a cacti installation for a customer and ended up skipping step #3 from the Install and Configure Cacti guide.

Step in question:

shell> mysql cacti 

Why would I have skipped this very crucial step you might say?

Well, the installation was done in two sessions with enough time having elapsed between the initial and final sessions that I had gotten hazy on what was and wasn't done. I had created the database but just never took the next step.

After completing the rest of the configuration I fired Cacti but via my browser and php promptly did a segfault and lay there on the ground haemorrhaging. From my recent experience php has a propensity to do this in two specific cases:

• Something went awry with a database connection or using a database resource
• You're pushing the php boundaries with recursive regexps in a pre_match*() function

Once I sat down and went through each of the steps required to set the beastie up I realized I simply needed to import the db schema and initial data to get things going.

Presto!

Tales from the (PHP and Perl) Crypt - AES Encryption in MySQL

I was looking for a way to share encrypted information between two systems where a table in MySQL was the integration point.

The one system is based on php while the other component is a perl daemon.

Let's get cryptic

My first stab at this was a perl based solution using the Crypt::CBC and Crypt::Blowfish libraries plus a shared secret/key. This meant I had to develop a perl script which I called from php to do the encryption which is a rather inelegant solution.

At first I could not find the right libs in php to get this done but later stumbled upon the Mcrypt suit off php and MCrypt perl functions that allow you to do encryption between the two different subsystems.

Unfortunately this means you have double the amount of hassle when it comes to updates and ensuring things Just Work™.

Move it back to the source
Some more checking brought me to the MySQL AES encryption functions that are built into MySQL. They provide the best cryptographic algorithms MySQL currently has to offer and are pretty respectable from a academic encryption perspective.

This means en/decryption is dealt with at one integration point across all languages involved which is much more elegant.

Tales from the Crypt
The MySQL AES encryption functions allow you to en/decrypt data quite easily. To encrypt a string you simply issue the following, assuming your shared secret is lesser-spotted-mountain-squid:

mysql> INSERT INTO test_table (test_column) VALUES(AES_ENCRYPT('this is a super-secret message', 'lesser-spotted-mountain-squid'));
Query OK, 1 row affected (0.09 sec)

mysql> SELECT * FROM test_table;
+----------------------------------+
| test_column                      |
+----------------------------------+
| Aÿ„1
ý#ôärO™é=:Žï   ¼Ñ†kWA |
+----------------------------------+
1 row in set (0.00 sec)

Et voila!

One thing you need to keep in mind is that the field you want to store your encrypted data in must be a MySQL BLOB data type.

Sucking our super secret string back out into a usable form is as simple as:

mysql> SELECT AES_DECRYPT(test_column, 'lesser-spotted-mountain-squid') AS top_secret FROM test_table;
+--------------------------------+
| top_secret                     |
+--------------------------------+
| this is a super-secret message |
+--------------------------------+
1 row in set (0.00 sec)

The security lesson
This is rather obvious but your security is only as strong as the weakest link in the chain. In this specific case I did not want to have clear text data in the db and achieved that amicably.

Because my secret is in clear text in two different systems I am rather exposed if those systems are not as secure as they could be. Lucky for my they are pretty much locked away from daylight so I'm not too concerned.