AthCon 2012 Review

Alternate title: “Being a lamb around a pack of wolves” … A venue full of hackers that are eager to attack your systems…

On 3-4/05/2012 the third AthCon conference was held in Athens. AthCon is an international security conference whose motto is “The First HIGHLY TECHNICAL Security Conference in Greece”.

Even though I am not a security professional, my daily job title is “Systems and Services Engineer” which of course includes various aspects of security but I am certainly not a security researcher, I had decided months ago that I would be attending this year’s AthCon. Since I like messing a lot with IPv6 for the past 2-3 years, I decided that I could try and submit an introductory talk about IPv6 security issues. My talk was accepted, so I was not only attending AthCon this year but I was going to give a presentation as well.

My presentation – Are you ready for IPv6 insecurities ? was during the first day of the conference. I am always worried when I give presentations on IPv6 that the people attending have probably no clue about this ‘not-so-new’ protocol. Most people think that IPv6 is like IPv4 with bigger addresses and ‘:’ instead of ‘.’ to separate the address groups, which is of course a HUGE mistake/misunderstanding. I was hopeful that this wouldn’t be the case in AthCon, so when I started my presentation and I asked the crowd ‘how many of you know what SLAAC is ?’ and I only saw 3-4 hands raised I kinda froze, I was expecting at least a double digit…I was going to give a presentation on IPv6 security concepts to people that have absolutely no idea what I’m talking about. Being prepared for the fact that some people would need some ‘refreshing’ on their IPv6 knowledge, I had prepared around 20 introductory slides explaining some IPv6 concepts before I entered the security details, but I doubt these were enough for most people there. I am hopeful though that some of the attendees might be motivated to read more about the protocol since I think my security slides contained enough details, references and links to get people started. If someone needs more details feel free to contact me.

Enough with my presentation, what about other presentations ?
My personal view is that this year’s AthCon had some great talks, some that were ok and some that I didn’t like. I won’t mention which ones I didn’t like, but I noticed that a LOT of people were gossiping about these in the hallways. I will only mention here the ones that I really liked.

Day 1:
“Packing Heat!” by Dimitrios Glynos
A presentation that every pentester should download/watch somehow. Techniques about packing your executables to avoid detection by anti-virus programs, need I say more ? Great content and very well presented. Congrats Dimitris!

“PostScript: Danger Ahead” by Andrei Costin
How to use PostScript programming language to take advantage of Printers, OS, etc. Very interesting concepts were presented and also the examples/demos shown were pretty cool and easy to understand.

Day 2:
“Apple vs. Google Client Platforms” by Felix ‘FX’ Lindner
I guess mostly everyone reading this blog knows FX and what a great speaker he is. If you don’t then start watching his previous presentations and start reading about his work. His presentation at AthCon, apart from being the best one in terms of “presenting it”, was also extremely interesting. He connected the security concepts behind Apple’s iOS and Google’s Chromebook with their business tactics and policies. Just wait for AthCon to publish the videos and watch it. Probably the best talk at AthCon 2012.

“Advances in BeEF: RESTful API, WebSockets, XssRays enhancements” by Michele Orru
Jaw-dropping. That’s all I have to say about BeEF. Scary. Watch it to see what browsers and IDS have to face and defend against…not in the future but right now.

“Exploitation and state machines” by Halvar Flake
This presentation was about exploitation techniques and why automated exploitation engines don’t work that well. Even though reversing and exploitation is far from my interest topics I enjoyed the talk a lot. Very well structured and very clear points. Too bad this talk did not appear on the schedule and was there as “tbc”, I am sure many more people would come just to listen to this talk and speak to Halvar.

If I were to suggest a couple of things for next year…
a) Please put the CTF in separate slots within the day, not at the same time with the presentations. In a conference of 150-200 people (just guessing here) having 30+ people leaving the presentation room and just attending the CTF all day long leaves the main room a bit empty. I am pretty sure there were people that wanted to attend both the presentations and the CTF, unfortunately they had to make a choice.
b) Send some details/info to the speakers about the conference a few days earlier. Maybe non-greek presenters were given but we weren’t, at least I wasn’t.
c) The venue is really nice, but maybe it would help if the next AthCon was organized somewhere downtown. Yeah I can understand that the cost would be higher but number of people attending would also raise (I think).
d) Give us even more highly technical presentations/speakers! People starve for these kind of talks!

My congratulations fly to AthCon people for organizing the conference. See you next year!

You can find some of the pics I took from the speakers at: AthCon 2012 speaker pics (if any of the speakers wants his pic removed please contact me ASAP)

AthCon 2012 – Are you ready for IPv6 insecurities ?

My presentation for AthCon 2012 is now available online: Are you ready for IPv6 insecurities ?

Greek AdblockPlus Filter on github

For the past months Greek AdblockPlus Filter has steadily been growing in subscribers. I recently did a change in the metadata so that clients fetch/check the list every 2 days instead of the default 7, and the daily subscriber count has surpassed 10.000 unique IPs.

The following graph shows this increase over time:

In order to help people contribute to the project I’ve created a repo on github: greek-adblockplus-filter. So now, if you want to help filter out ads from the greek web just get fork the project via git, make your changes and send me a pull request on github :)

open magnet URLs with xdg-open

Browsers on Linux should use xdg-open to open URLs. From xdg-open’s description:

xdg-open opens a file or URL in the user’s preferred application. If a URL is
provided the URL will be opened in the user’s preferred web browser. If a file
is provided the file will be opened in the preferred application for files of
that type. xdg-open supports file, ftp, http and https URLs.

Magnet is a new type of URLs that’s currently not supported by xdg-open. It’s very easy to add a quick hack to support it though. Just copy xdg-open to a local binary path (e.g /usr/local/bin or preferably $HOME/bin) and apply the following patch. Change deluge with your preferred torrent application.

--- /usr/bin/xdg-open  2010-09-15 14:08:29.000000000 +0300
+++ bin/xdg-open  2012-01-24 22:05:03.935338593 +0200
@@ -437,6 +437,11 @@
                 exit_success
             fi
         fi
+    elif (echo "$1" | grep -q '^magnet:'); then
+        deluge "$1" 
+        if [ $? -eq 0 ]; then
+            exit_success
+        fi
     fi
 
     sensible-browser "$1"

For those who don’t know how to patch a file, here it is in full: patched xdg-open to open magnet URLs

Works fine on my Debian using Chrome which properly uses xdg-open to handle URLs. Firefox after version 3.5 became a piece of bloatware and it’s not possible to open magnet links using about:config settings or xdg-open. For Opera just go to Settings->Preferences->Programs and add new protocol with name magnet. At Open with other application, use the path that you saved the patched xdg-open.

Now go download some creative commons licensed files.

*UPDATE*
Patch for latest xdg-utils (since some people complained that previous patch does not apply):



--- xdg-open  2012-02-18 14:22:48.058497027 +0200
+++ xdg-open  2012-02-18 14:24:04.326875223 +0200
@@ -440,6 +440,11 @@
                 exit_success
             fi
         fi
+    elif (echo "$1" | grep -q '^magnet:'); then
+        deluge "$1" 
+        if [ $? -eq 0 ]; then
+            exit_success
+        fi
     fi
 
     IFS=":"

Music Player Daemon on OS X

I use a Mac Mini with OS X 10.5.8 as a media center connected to my TV and I wanted to install Music Player Daemon on it so I could control the music remotely from my laptop or phone. I mostly followed the OS X guide from MPD’s wiki to do it but I ran into some problems while trying to daemonize mpd.

I got the following error while running mpd without –no-daemon:

The process has forked and you cannot use this CoreFoundation functionality safely. You MUST exec().
Break on __THE_PROCESS_HAS_FORKED_AND_YOU_CANNOT_USE_THIS_COREFOUNDATION_FUNCTIONALITY___YOU_MUST_EXEC__() to debug.

When I ran mpd –no-daemon everything was fine though. So in order to “solve” this problem I’ve changed the plist file to include a screen invocation.

My mpd.plist looks like that now:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
    <dict>
    <key>Label</key>
    <string>mpd</string>
    <key>ProgramArguments</key>
    <array>
        <string>/opt/local/bin/daemondo</string>
        <string>--label=mpd</string>
        <string>--start-cmd</string>
        <string>screen</string> <!-- path to screen -->
        <string>-dmS</string>
        <string>mpd</string>
        <string>/opt/local/bin/mpd</string> <!-- path to MPD -->
        <string>-v</string>
        <string>--no-daemon</string>
        <string>/Users/kargig/.mpd/mpd.conf</string> <!-- path to MPD config -->
        <string>;</string>
        <string>--stop-cmd</string>
        <string>/opt/local/bin/mpd</string> <!-- path to MPD, again -->
        <string>--kill</string>
        <string>/Users/kargig/.mpd/mpd.conf</string> <!-- path to MPD config, again -->
        <string>;</string>
    </array>
    <key>RunAtLoad</key>
    <true/>
    <key>Disabled</key>
    <false/>
    </dict>
</plist>

So launchctl calls daemondo which calls screen which runs mpd –no-daemon, so mpd doesn’t crash.

I use mpdscribble for scrobbling my music to last.fm. Clients-wise, I use Theremin on OS X, Gnome Music Player Client/gmpc on Debian Linux and MPDroid on Android. And all those connections over IPv6 of course, over my LAN’s Unique Local Addresses to be exact, mpd and all clients listed above work fine with IPv6.

 # lsof -n -i | grep ESTABLISHED | grep 6600
mpd       43025         kargig   12u  IPv6 0x49c719c      0t0    TCP [fdbf:aaaa:aab0:447d:216:XXff:feaa:11XX]:6600->[fdbf:aaaa:aab0:447d:222:XXff:fe1e:d8XX]:48703 (ESTABLISHED)
mpd       43025         kargig   15u  IPv6 0x3127cd4      0t0    TCP [fdbf:aaaa:aab0:447d:216:XXff:feaa:11XX]:6600->[fdbf:aaaa:aab0:447d:fadb:XXff:fe4f:aXX]:51113 (ESTABLISHED)

Apart from MPD’s wiki there’s another nice blog post you can read to help you install mpd on OS X, Integrating MPD with OS X.
For general reference on setting up mpd, Archilinux has a fine wiki entry.

Linux SSD partition alignment tips

Yes, this is another post on the internet about properly aligning your SSD partitions on Linux. It’s mostly my notes that I have gathered from other posts around the net. Please read the whole post before starting to create partitions on your SSD.

Intro
I bought myself a brand new SSD for Xmas, OCZ Agilty 3 120Gb. But I also bought a CDROM caddy so that I could replace my useless macbook CDROM drive, last time I used it was probably 2009 or 2010. So my plan was to put the old, original macbook SATA hard disk inside the caddy and use the SSD as the primary one. Sounds easy right ? Well you just need patience, lots of patience in order to remove all necessary screws in order to get the CDROM drive out and replace it with the caddy. Instructions for this procedure can be found at iFixIt.com.

Create Partitions on the SSD disk
Before one begins some definitions!

Heads = Tracks per cylinder
Sectors = Sectors per track

The goal here is to have the partitions aligned to the SSD’s Erase Block Size.
Googling around the net I found out that OCZ always uses 512Kb as Erase Block Size. If one uses fdisk with 32 Heads and 32 Sectors that makes a cylinder size of 1024b = 1Kb. Multiplying with 512 (sector size), which is fdisk’s default unit size, that makes it 512kb (=32*32*512)! Exactly the Erase Block Size that’s needed. So one needs to start fdisk issuing the following command:
# fdisk -H32 -S32 /dev/sdb
where /dev/sdb is the SSD.

It is very important to remember to start the first partition from the 2nd unit (or 512th cylinder if you prefer). Due to MS-DOS compatibility if the first partition were to start at the first cylinder, it would skip one track. So it would actually start at 32(sectors)*512(sector size)=16Kb, messing up the alignment.

Then create necessary partitions as needed.

LVM alignment
So, the partitions on the SSD are aligned, but what if one wants to use LVM ? Then LVM’s overhead has to be taken into account as well.
To create an aligned PV based on the partitions that have already been created one needs to use the “–dataalignment” option found in newer versions of LVM utilities.
# pvcreate --dataalignment 512k /dev/sdb3
To check the alignment use the following command:

# pvs /dev/sdb3 -o+pe_start
  PV         VG   Fmt  Attr PSize   PFree  1st PE 
  /dev/sdb3  ssd  lvm2 a-   111.46g 81.46g 512.00k

Check that “1st PE” is what is actually needed for the alignment.

Proceed creating VGs and LVs as needed.

Formatting Partitions with ext4
There’s no reason to use ext3 on SSD, one needs to take advantage of ext4 SSD features. I prefer 4K as block size.
For a further explanation of the following formulas read Linux RAID Wiki – RAID setup
stride = chunk (Sector size) / block size = 512Kb / 4K = 128
stripe-width is usually calculated by a formula that uses multiple disks. Since there’s only one disk in this scenario, stripe-width is equal to stride.
stripe-width = 128

# mkfs.ext4 -O extent -b 4096 -E stride=128,stripe-width=128 /dev/mapper/ssd-debian

Mounting the partition
To enable SSD TRIM support, which protects the disk from wearing off, one needs to enable the discard option while mounting the partition. Edit /etc/fstab and add the discard mount option (and noatime if you want to).
/dev/mapper/ssd-deb / ext4 discard,noatime,errors=remount-ro 0 1

Note 1: As of 2.6.37 Linux Kernel supports TRIM on device mapper. Previous kernel versions will report errors upon trying to mount an LVM partition with discard mount option. If you have an older kernel either don’t use LVM on your SSD yet or upgrade your kernel!
Note 2: Read the links posted below for a complete blog post over TRIM command. Apparently it’s not always the best choice

That’s basically it…

Extra – copying the old root partition to the new disk

# mkdir /mnt/ssd/
# mount /dev/mapper/ssd-debian /mnt/ssd/
# rsync -aPEHv --exclude=/dev --exclude=/proc --exclude=/sys --exclude=/mnt --exclude=/var/cache/apt/archives/ / /mnt/ssd/
# mkdir /mnt/ssd/dev
# mkdir /mnt/ssd/proc
# mkdir /mnt/ssd/sys
# mkdir /mnt/ssd/mnt
# cp -avp /dev/console /mnt/ssd/dev/
# cp -avp /dev/urandom /mnt/ssd/dev/
# cp -avp /dev/zero /mnt/ssd/dev/
# cp -avp /dev/random /mnt/ssd/dev/
# Edit /mnt/ssd/etc/fstab to change the device names
# Update grub

Using the above commands one avoids copying unneeded directories like /dev, /sys, etc that will be recreated later. Don’t forget to at least copy the above 4 devices in the new /mnt/ssd/dev dir, else the partition won’t be bootable.

References
1. Aligning Filesystems to an SSD’s Erase Block Size (link goes to archive.org since original article has unfortunately disappeared from the web)
2. [linux-lvm] Re: Aligning PVs on SSDs?
3. Aligning an SSD on Linux (excellent article!)
4. ArchWiki – Solid State Drives
5. SSD performance tips for RHEL6 and Fedora
6. Re: [dm-devel] trim support (discard)
7. How to check TRIM is working on your SSD running Linux
8. Impact of ext4′s discard option on my SSD (very useful insight on TRIM command, read the comments as well)

Thanks
Thanks fly to @apoikos for helping me with the CDROM replacement and @faidonl for his original SSD alignment tips :)

Rate limit outgoing emails from PHP web applications using postfix and policyd

One of the worst things a webmaster or a anyone else that runs some web application can do, is to constantly send “informative newsletters” to people. Most CMS applications make it really easy to send such emails. These are 99% spam, and as such there are many good reasons that you should limit the amount of such outgoing “newsletters” coming out of your email server. Else there’s a good chance you might get added to a blacklist, and you don’t want your legitimate clients to have their emails blocked because of some irresponsible people. I recently had to deploy such a solution to a hosting server that serves multiple (>300) domains. The server already ran postfix, so I had to implement something useful around it.

The problem with postfix is that you can’t really rate-limit the outgoing queue per sender domain/address. There are only generic settings that control the general mail server’s capabilities of sending emails. What I wanted though is to have the ability to restrict specific domains to some specific email message count per day. This is something that a postfix addon named postfix-policyd can do by deferring/greylisting, but still just on the incoming queue. One would think that the problems would be solved by just applying this, but truth is that they don’t. Applying a defer/greylisting policy on the incoming queue is fine while the client on the remote side is another SMTP server that can happily store the deferred email on its queue and retry some minutes/hours later. What happens though if the SMTP client is a PHP application that connects through the mail() function ? There you have no queue and if you defer a message at the SMTP server it will get forever lost, PHP can’t resend it. So the solution would be to apply an intermediate SMTP queue between PHP and the primary SMTP server, that is another local postfix installation that would only serve as a queue that relays emails to the primary.

Using a “simple” diagram sending an email from PHP should follow this path upon a successful installation:

PHP mail() –(sendmail binary)–> intermediate_POSTFIX –(SMTP relay)–> POSTFIX –(smtpd_sender_restrictions)–> POLICYD –(pickup)–> POSTFIX –(SMTP)–> REMOTE SERVER

Here are the steps I took on a Debian Squeeze server to install this little monster.

1. Create a new postfix configuration directory for the new intermediate postfix instance
I named my intermediate postfix config dir as postfix2525, name comes from the port that it will listen on but you can definitely be more creative.

# mkdir /etc/postfix2525
# cp -av /etc/postfix /etc/postfix2525

Remove everything from /etc/postfix2525/main.cf and just add the following lines:

data_directory = /var/lib/postfix2525
queue_directory = /var/spool/postfix2525
relayhost = 127.0.0.1:12525

This defines a new data and queue directory and instructs this postfix to relay all emails through another one that listens on the localhost, the primary one, on port 12525. More about this port later when you will create some special config on the primary postfix.

Remove previous contents of /etc/postfix2525/master.cf and just add these lines:

127.0.0.1:2525      inet  n       -       -       -       2       smtpd
        -o syslog_name=postfix2525
pickup    fifo  n       -       -       60      1       pickup
cleanup   unix  n       -       -       -       0       cleanup
qmgr      fifo  n       -       n       300     1       qmgr
#qmgr     fifo  n       -       -       300     1       oqmgr
tlsmgr    unix  -       -       -       1000?   1       tlsmgr
rewrite   unix  -       -       -       -       -       trivial-rewrite
bounce    unix  -       -       -       -       0       bounce
defer     unix  -       -       -       -       0       bounce
trace     unix  -       -       -       -       0       bounce
verify    unix  -       -       -       -       1       verify
flush     unix  n       -       -       1000?   0       flush
proxymap  unix  -       -       n       -       -       proxymap
proxywrite unix -       -       n       -       1       proxymap
smtp      unix  -       -       -       -       -       smtp
# When relaying mail as backup MX, disable fallback_relay to avoid MX loops
relay     unix  -       -       -       -       -       smtp
        -o smtp_fallback_relay=
#       -o smtp_helo_timeout=5 -o smtp_connect_timeout=5
showq     unix  n       -       -       -       -       showq
error     unix  -       -       -       -       -       error
retry     unix  -       -       -       -       -       error
discard   unix  -       -       -       -       -       discard
local     unix  -       n       n       -       -       local
virtual   unix  -       n       n       -       -       virtual
lmtp      unix  -       -       -       -       -       lmtp
anvil     unix  -       -       -       -       1       anvil
scache    unix  -       -       -       -       1       scache

Obviously the most important part here is the first line. It defines that this postfix instance will listen for SMTP connections on localhost, port 2525 and it’s syslog output name will be postfix2525 so that it’s easier to tell apart which SMTP instance spits which errors.

After this is done you need to run the following command that will create all necessary directories with their proper permissions.

# postfix -c /etc/postfix2525/ check

You will also need a new init script. Since the script by itself is quite big and there are only a few lines that actually differ, I will post my diff here:

--- /etc/init.d/postfix  2011-05-04 21:17:47.000000000 +0200
+++ /etc/init.d/postfix2525  2011-12-19 19:22:09.000000000 +0100
@@ -17,8 +17,10 @@
 # Description:       postfix is a Mail Transport agent
 ### END INIT INFO
 
+CONFDIR=/etc/postfix2525
 PATH=/bin:/usr/bin:/sbin:/usr/sbin
 DAEMON=/usr/sbin/postfix
+DAEMON_OPTIONS="-c /etc/postfix2525"
 NAME=Postfix
 TZ=
 unset TZ
@@ -28,13 +30,13 @@
 
 test -f /etc/default/postfix && . /etc/default/postfix
 
-test -x $DAEMON && test -f /etc/postfix/main.cf || exit 0
+test -x $DAEMON && test -f /etc/postfix2525/main.cf || exit 0
 
 . /lib/lsb/init-functions
 #DISTRO=$(lsb_release -is 2>/dev/null || echo Debian)
 
 running() {
-    queue=$(postconf -h queue_directory 2>/dev/null || echo /var/spool/postfix)
+    queue=$(postconf -c $CONFDIR -h queue_directory 2>/dev/null || echo /var/spool/postfix2525)
     if [ -f ${queue}/pid/master.pid ]; then
   pid=$(sed 's/ //g' ${queue}/pid/master.pid)
   # what directory does the executable live in.  stupid prelink systems.
@@ -66,7 +68,7 @@
       fi
 
       # see if anything is running chrooted.
-      NEED_CHROOT=$(awk '/^[0-9a-z]/ && ($5 ~ "[-yY]") { print "y"; exit}' /etc/postfix/master.cf)
+      NEED_CHROOT=$(awk '/^[0-9a-z]/ && ($5 ~ "[-yY]") { print "y"; exit}' /etc/postfix2525/master.cf)
 
       if [ -n "$NEED_CHROOT" ] && [ -n "$SYNC_CHROOT" ]; then
     # Make sure that the chroot environment is set up correctly.
@@ -111,7 +113,7 @@
     umask $oldumask
       fi
 
-      if start-stop-daemon --start --exec ${DAEMON} -- quiet-quick-start; then
+      if start-stop-daemon --start --exec ${DAEMON} -- ${DAEMON_OPTIONS} quiet-quick-start; then
     log_end_msg 0
       else
     log_end_msg 1
@@ -123,7 +125,7 @@
   RUNNING=$(running)
   log_daemon_msg "Stopping Postfix Mail Transport Agent" postfix
   if [ -n "$RUNNING" ]; then
-      if ${DAEMON} quiet-stop; then
+      if ${DAEMON} ${DAEMON_OPTIONS} quiet-stop; then
     log_end_msg 0
       else
     log_end_msg 1

If everything went well up to now you should be able to start your new postfix instance and check that it is actually running.

# /etc/init.d/postfix2525 start
# netstat -antp | grep 2525
tcp        0      0 127.0.0.1:2525          0.0.0.0:*               LISTEN      6138/master

2. Configure main postfix to accept emails from the intermediate
Edit /etc/postfix/master.cf and add this line at the bottom:

127.0.0.1:12525 inet n - - - - smtpd  -o smtp_fallback_relay= -o smtpd_client_restrictions=  -o smtpd_helo_restrictions=  -o smtpd_recipient_restrictions=permit_mynetworks,reject  -o smtpd_data_restrictions=  -o receive_override_options=no_unknown_recipient_checks

This defines a special port for the main postfix instance that has (or maybe it hasn’t actually) some special restrictions.
Actually you will have to change this line later on upon installing postfix-policyd, but this should be good enough for now, in order for you to do some testing.
Restart postfix

# /etc/init.d/postfix restart
# netstat -antp | grep 2525
tcp        0      0 127.0.0.1:12525         0.0.0.0:*               LISTEN      26799/master    
tcp        0      0 127.0.0.1:2525          0.0.0.0:*               LISTEN      6138/master   

The intermediate postfix listens on 127.0.0.1:2525 and the main one has another special listening port on 127.0.0.1:12525.

3. Test your intermediate postfix instance
You can do this in a gazillion different ways. One of my favorite ways to test SMTP connectivity is through telnet (—> shows data entry):

# telnet localhost 2525
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
220 server.mydomain.gr ESMTP Postfix
---> EHLO koko.gr
250-server.mydomain.gr
250-PIPELINING
250-SIZE 10240000
250-VRFY
250-ETRN
250-ENHANCEDSTATUSCODES
250-8BITMIME
250 DSN
---> MAIL FROM: lala@koko.gr
250 2.1.0 Ok
---> RCPT TO: koko@destination.gr
250 2.1.5 Ok
---> DATA
354 End data with <CR><LF>.<CR><LF>
---> THIS IS A TEST
---> .
250 2.0.0 Ok: queued as C41E21C84FF
---> quit

If you were keeping an eye on syslog messages you should have seen some connection messages both from postfix2525 and from postfix. If everything went well your email _should_ have arrived at it’s destination. If this is true then your primary postfix instance now works as a relay for your intermediate queue.

Don’t read the next parts of this post if you haven’t previously managed this step!

4. Install and configure postfix-policyd

# aptitude install postfix-policyd

To run policyd you need to create a database and import policyd SQL schema to it. Your distro has probably already taken care of the previous step, if it hasn’t…do it manually and think about changing distro!
Then edit the config file usually located at /etc/postfix-policyd.conf. The options I chose to play with were the following:
SENDERTHROTTLE=1
SENDER_THROTTLE_SASL=1
SENDER_THROTTLE_HOST=0

Since all emails will be relayed through localhost there’s no point in throttling per host, what is needed is throttling per envelope sender.
You should manually review your desired limits though. I won’t post mine here because everyone has different needs and there’s no sane config for everyone.

Start postfix-policyd
# /etc/init.d/postfix-policyd start

If you get weird startup errors like:
postfix-policyd: fatal: didn't find priority 'LOG_IFOO', exiting
Edit /etc/postfix-policyd.conf, find the following line:
SYSLOG_FACILITY="LOG_MAIL | LOG_INFO"
and change it to (mind the removed spaces):
SYSLOG_FACILITY="LOG_MAIL|LOG_INFO"

5. Configure main postfix instance to use postifix-policyd
Edit /etc/postfix/main.cf and add this:
webclient_restrictions = check_policy_service inet:127.0.0.1:10031

Then edit /etc/postfix/master.cf again and change the line you had previously added to the bottom of the file with this:

127.0.0.1:12525 inet n - - - - smtpd  -o smtp_fallback_relay= -o smtpd_client_restrictions=  -o smtpd_helo_restrictions=  -o smtpd_recipient_restrictions=permit_mynetworks,reject  -o smtpd_data_restrictions=  -o receive_override_options=no_unknown_recipient_checks -o smtpd_sender_restrictions=${webclient_restrictions}

The difference is
-o smtpd_sender_restrictions=${webclient_restrictions}
which practically instructs postfix to use postfix-policyd for emails that arrive on port 12525, which is the port that the intermediate postfix instance uses to relay all emails.

6. Test your intermediate postfix instance again
If everything went well, the main postfix instance should now be able to enforce sender policies. Try sending a new email through the intermediate postfix again, yes using telnet, and you should pickup some new log lines at your syslog:

Dec 19 21:56:40 myserver postfix-policyd: connection from: 127.0.0.1 port: 45635 slots: 0 of 4096 used
Dec 19 21:56:40 myserver postfix-policyd: rcpt=5, greylist=new, host=127.0.0.1 (unknown), from=lala@koko.gr, to=koko@lalala.gr, size=348
Dec 19 21:56:40 myserver postfix/smtpd[9168]: NOQUEUE: reject: RCPT from unknown[127.0.0.1]: 450 4.7.1 : Sender address rejected: Policy Rejection- Please try later.; from= to= proto=ESMTP helo=
Dec 19 21:56:40 myserver postfix/smtp[8970]: C41E21C84FF: to=, relay=127.0.0.1[127.0.0.1]:12525, delay=20, delays=20/0/0.01/0, dsn=4.7.1, status=deferred (host 127.0.0.1[127.0.0.1] said: 450 4.7.1 : Sender address rejected: Policy Rejection- Please try later. (in reply to RCPT TO command))

The above means that greylisting through policyd works.

7. make PHP use your new intermediate postfix instance
PHP on linux by default uses the sendmail binary to send emails via the mail() function. That would use the main postfix instance though, so one needs to edit /etc/php/apache2/php.ini and change the following line:
sendmail_path = sendmail -C /etc/postfix2525 -t -i

The -C directive instructs sendmail to use the alternate config dir, so that emails will be sent to the new intermediate postfix instance and then to the main one, passing through policyd of course.

To check the queue size of the intermediate postfix:
# postqueue -p -c /etc/postfix2525/

If any PHP applications that are hosted have explicit SMTP server/port directives, then be sure to notify your clients/developers that they _MUST_ use localhost:2525 to send their emails to and not the default localhost:25. This is one of the shortcomings of the above method, if someone manually sets up his application to use the default localhost:25 his emails will get right through. But being a good sysadmin, you should monitor such behavior and punish those users accordingly!

That’s about it…with the above configuration and some tweaking to the thresholds you have very good chances of avoiding getting blacklisted because someone decided to send a few thousand spams emails. And most importantly, your normal mail service will continue to work flawlessly, no matter how big the queue of the intermediate mail server is.

Enjoy!

Reference for policyd: http://policyd.sourceforge.net/readme.html

Handling right clicks on a macbook running Linux – The 2011 Awesome Edition

2 years ago I had written a post about handling right clicks on a macbook running linux. Along with changing my window manager of choice, I think I’ve found a better/more elegant solution to that problem.

On my computer’s workspaces one will normally find one or two browser windows open, some instant messaging applications (skype,pidgin), an mp3 player (audacious2) and terminals. Lots of them. I need them to ssh to the servers I monitor/administer and for coding (with vim of course!). I even use one for my email client (mutt). So I need my terminals to be as efficient as possible. After many trials over the years I’ve decided on using urxvt as my terminal of choice.

About a month ago I gave awesome a try and since then it’s been my window manager of choice instead of fluxbox. The reason behind this is mostly fluxbox’s inability to tile terminal (call me urxvt) windows efficiently while changing resolutions. I mostly use my laptop with an external 23” monitor but I wanted to be able to tile my terminals independently of using only my laptop’s screen or both laptop’s and the external one. In fluxbox you can make a window appear on specific area of the screen, so I could open 3-4 terminals on a specific workspace/monitor. Resizing though one of them to fit some monitoring program more efficiently didn’t resize the others ‘automagically’ as well. So, I had to manually resize all open windows of that workspace. Yes, this is horrible from a usability point of view, luckily I didn’t have to do it that frequently. So, I gave awesome a try for its tiling features. I really miss though fluxbox’s tabbing features that I constantly used along with it’s amazing keybindings flexibility (Rant: isn’t it stupid that you have to write your keybindings in lua for awesome and in haskell for xmonad ?) but the tiling capabilities of awesome are currently more important to me.

So while my previous solution for right clicking without a mouse worked pretty well for fluxbox, in my new awesome world I’ve replaced it with xautomation tools. First of all, one needs to install xautomation tools

aptitude install xautomation

Then find clientbuttons configuration part in the default ~/.config/awesome/rc.lua and add this line to it:

awful.button({ modkey }, 2, function () awful.util.spawn("xte 'mouseclick 3'") end)

restart awesome and try modkey + 3 finger tap on your touchpad. You should be seeing a right click “menu”.
If you don’t know what 3 finger tap is or how to configure it, read the 2009 article.

That’s it, no more xbindkeys + xvkbd for awesome.

my past 2 articles for LinuxInside

Following my first article on the Greek Linux Magazine called LinuxInside about IPv6, I uploaded my past 2 articles for it. Both articles are in Greek of course.

The first one is about debugging network connectivity issues using the command line on Linux. It was published on the 2nd issue of LinuxInside.
Εντοπίζοντας ένα πρόβλημα δικτύωσης

The other one is an introduction to Zsh. It was published on the 3rd issue of LinuxInside.
Κατακτήστε το Z shell

If you haven’t read the magazine already, feel free to download those pdfs and read them.
All my presentations/articles can be found at: Articles/Presentations

0×375 – 0×07 – Security Considerations for a brave new (IPv6) World

I finally had the chance to present something at the Thessaloniki Tech Talk Sessions also known as 0×375. The people over there have done a great job, and I truly mean that, bringing tech people together. Almost once a month 2 speakers can present a tech topic they like at an open auditorium inside the Aristotle University of Thessaloniki. On those events people from Thessaloniki, but also from nearby cities, gather and have a great time, not only during the presentations but afterwards as well. I won’t spoil the events that take place during the tech talks, because you should definitely go if you are curious, but I can tell you that it’s not uncommon for as many as 15 to 20 people to go for beers after the talks!

So, the past Friday (25/11/2011), me and @apoikos traveled from Athens to Thessaloniki to present at 0×375. My presentation was about some security concepts on IPv6 networks, how old attacks of the IPv4 world transform to new ones on the IPv6 world and about some new ones that will appear on local networks sooner or later. I also had prepared some small live demos, but as always it’s very hard to succeed in a live demo if you don’t quite control the environment. At least some of the stuff I wanted to show were successful, and I’m happy with those. (Thanks to Nuclear for booting his OS X guinea pig)

Some apologies…When giving a presentation on IPv6, in an event that has no other introductory IPv6 presentations, I always face the same problem, most people are not very well aware of how different this protocol is from IPv4. When I ask the audience how well do they know IPv6, most people are embarrassed to say they have never actually used it, so the audience stays very, VERY silent. This means that I have to put around 15-20 slides to make a “quick introduction to IPv6″, and this unfortunately takes usually over 30′ of presentation time. Some techy/advanced people might be bored with this, but there’s no other way to overcome this “issue”. If you go straight to the point and start discussing about ND ICMPv6 messages most people won’t be able to keep up…so I’m sorry if I made some of the audience get bored by my first slides. I promise that my next talk on 0×375, cause there will surely be a next one, will be less boring for you :)

Thank you all for coming there, I hope you enjoyed it as much as I did!

You can find the slides and my live demo notes here:
0×375 – 0×07 – kargig – Security Considerations for a brave new (IPv6) World (pdf)
0×375 – 0×07 – kargig – Security Considerations for a brave new (IPv6) World – live demo notes (txt)

P.S. I’ve started collecting some interesting (for me) presentations regarding IPv6 topics at void.gr/kargig/ipv6/. Check them out if you like.

GrRBL goes django

I’ve had this thought for some time now, I needed a nice interface for GrRBL so that it would make it easier for others, read more, people to contribute. Many people have been, politely, complaining about lack of features, policy and so on.

Right now most people use either the submission form or they bounce their emails to grrbl [at] void [dot] gr. Then their emails get manually processed, filtered and if everything goes well the “useful” parts of their email end up in the DNS RBL or the email address blacklist. This process is not automated at all, entries are manually added to a database, and is therefore quite time consuming. What’s worse is that people who are listed don’t have an ‘easy’ way to opt-out, apart from emailing us. The algorithm of adding someone to these lists is also not well-defined. The main rule that is followed is that an IP or email address is added to these lists when at least 3 people have submitted them on different days.

Hopefully this is about to change soon (I don’t know how soon, but soon!). During the past month I’ve been trying to code an interface in django, even though I had no prior experience in it. It’s mostly a self educating process and I like it very much. This django application will be generic enough to cover submissions and listings for IPs, emails and possibly URLs.

  • Short term goals:
  • Anonymous users will only get to see details about an IP they search for. People though will be able to register and add their own entries to a database. These registered users will be able to see the complete listings. Each user will belong to a group and every group will have a different weight which will depend on his ‘expertise’ (I know this is broad, but read on). For example, the group of the individual users will certainly have less weight than the group of the postmasters of Greek ISPs (yeap there are some who regularly contribute). Using their weights users will be able to vote on each entry that’s inside the database. Upon a certain score these entries will be flagged as eligible to be on the blacklist. Listed people will be able to opt-out but this process will be moderated by the superusers, that means that spammers like the infamous sofokleous10 will never get a chance to opt-out even for a single second.
    Most of this functionality is already coded and is working quite well.

  • Mid term goals:
  • Various export formats will be supported (BIND/RBLDNSD, Spamassassin/Postifix/Exim/sendmail/etc). Selective/custom export of entries will be provided. Users will be able to select if they want to export/use a strict blacklist, that is hosts that are scored very high, a moderate one and a very broad/risky one. Levels have yet to be defined. An API will be published so that entries can be re-used in other applications (json format ?)

  • Long term goals:
  • A method/interface that someone would copy/paste their email and it would automagically parse it, provide the user with the discovered malicious entries (IP, emails, URLs) and propose him to add them to the database. Maybe automate this even further so that they are added on a separate moderated queue without user interaction, that would be suitable for submitting entries via email plugins for clients such as mutt/thunderbird/etc.

  • The code:
  • The django application code resides in github for now: https://github.com/kargig/grrbl_django. Everyone is welcome to submit ideas (as issues) and code! Feel free to download, test and provide feedback.

  • Greek Adblock Plus Filter
  • Since the code is very flexible I am thinking whether Greek Adblock Plus Filter can also be benefited by this voting system. It probably can, so expect some changes to that list as well. One interface to rule them all.

    Many thanks go to @apoikos who has been helping me a lot with the tons of questions I still have on django stuff.

    Notes on HP raid controllers

    Lately I had to deal with some HP raid controllers and I’ve gathered some notes on them. I’ll post them here so I won’t forget about them.

    First of all, don’t even think on using them without a battery pack. Seriously DON’T. The performance degradation is humongous. Without a battery pack the controllers were giving me 1/20th of the results with a battery pack. If you want to quickly test them, try iozone using the following options: iozone -t4 -I

    Installing hpacucli is a also a must if you want to monitor or configure the controllers from within your OS. Be sure to add the repositories from HWraid to your system and then issue: aptitude install hpacucli (you are using Debian, arent’ you?). That reminds me that I am using those repositories on so many systems I manage that I must send a donation to the people at hwraid to thank them.

    Below are some commands using hpacucli that I used.
    # Show everything about your raid controllers
    # hpacucli controller all show config detail

    Cache Board Present: True
    Cache Status: OK
    Accelerator Ratio: 25% Read / 75% Write
    Drive Write Cache: Enabled
    Total Cache Size: 512 MB
    Battery Pack Count: 1
    Battery Status: OK
    SATA NCQ Supported: True

    What you must take notice here is the Accelerator Ratio, Drive Write Cache and Battery Pack Count.
    if you have a battery pack installed but your Drive Write Cache is still shown as “Disabled”, you can enable it using the command:
    # hpacucli controller slot=X modify dwc=enable
    You’ll know what to put instead of “slot=X” from the output of the previous command (show config detail).

    To modify Accelerator Ratio (read/write):
    # hpacucli controller slot=X modify cacheratio=25/75

    To enable Array Acceleration for one of your logical drives use:
    # hpacucli controller slot=X logicaldrive Y modify aa=enable

    If you happen to face the following error while opening hpacucli, don’t worry. You don’t need to reboot your machine as I’ve seen in various blogs.

    Error: Another instance of ACU is already running (possibly a service). Please
    terminate the ACU application before running the ACU CLI. Press ENTER to
    exit.

    What you need to do is delete the shared IPC that hpacucli left when it got killed for some reason.
    To see all your ipcs:

    # ipcs
    ------ Shared Memory Segments --------
    key        shmid      owner      perms      bytes      nattch     status      
    
    ------ Semaphore Arrays --------
    key        semid      owner      perms      nsems     
    0xffffffff 32768      root       0          1         
    
    ------ Message Queues --------
    key        msqid      owner      perms      used-bytes   messages    
    

    Then use ipcrm to remove the array with the semid you want:
    # ipcrm -s 32768

    and try to start hpacucli again.

    References:
    1. http://www.datadisk.co.uk/html_docs/redhat/hpacucli.htm
    2. http://people.freebsd.org/~jcagle/hpacucli-readme

    Block Greek ads with Internet Explorer 9+

    Greek Adblock Plus filter list has more than 3500 regular unique (as per IP) subscribers daily. The majority of them uses some Mozilla-based browser (Firefox/Iceweasel). An increasing number of users has started using Chrome along with the Chrome Adblock Extension and Opera. Thanks to Panagiotis Dimopoulos, Greek Adblock Plus filter has now been converted to a Tracking Protection List for Internet Explorer 9+.

    To load the protection list visit the url: Greek Adblock Tracking Protection List for Internet Explorer 9+

    For more details about using Greek Adblock Plus filter on various browsers please visit: Greek Adblock Plus Filter.

    Don’t forget to send in comments and URLs to block. If you are using Firefox’s addon, please use the “Report Issue on this page…” feature of it by right clicking on the status icon of adblock.

    Why vacation auto-reply messages can sometimes be bad

    Say that a user has an email account at the company he works for. Before going on vacation he activates his cool “vacation auto-reply” feature that adds

    Out of Office – I will be back from holidays at the end of July.

    on the top and then quotes the email he was sent.

    During his vacation, he receives a call and he is told he has to urgently sent an email about some financial updates. He rushes to an internet cafe and sends the email. He makes a mistake though and mistypes one of the email addresses of the recipients. Instead of sending the email to “user@domain.com” he sends it at “usar@domain.com”.

    His company’s SMTP server though receives the following error message from the remote SMTP server while trying to deliver the email:

    <usar@domain.com>: host mx.domain.com[1.2.3.4] said: 550 5.1.1
       <usar@domain.com>... User unknown (in reply to RCPT TO command)

    This means that his SMTP server will then send an email to him informing him about the error and quoting parts if not all of the email he had previously sent. The email will likely appear to be from “postmaster@company.com” or “do-not-reply@company.com” or something similar.
    It will look like this:

    This is the mail system at host mail.company.com.
    
    I'm sorry to have to inform you that your message could not
    be delivered to one or more recipients. It's attached below.
    
    For further assistance, please send mail to postmaster.
    
    If you do so, please include this problem report. You can
    delete your own text from the attached returned message.
    
                      The mail system
    
    <usar@domain.com>: host mx.domain.com[1.2.3.4] said: 550 5.1.1
       <usar@domain.com>... User unknown (in reply to RCPT TO command)
    Reporting-MTA: dns; mail.company.com
    X-Postfix-Queue-ID: AE4812AE328
    X-Postfix-Sender: rfc822; employee1@company.com
    Arrival-Date: Thu,  5 May 2011 20:05:27 +0200 (CEST)
    
    Final-Recipient: rfc822; usar@domain.com
    Original-Recipient: rfc822;usar@domain.com
    Action: failed
    Status: 5.1.1
    Remote-MTA: dns; mx.domain.com
    Diagnostic-Code: smtp; 550 5.1.1 <usar@domain.com>... User unknown
    
    From: Loyal Employee <employee1@company.com>
    Date: July 5, 2011 9:05:29 PM GMT+03:00
    To: User User <usar@domain.com>
    Subject: Re: Financial updates
    
    Financial data goes here
    

    But the user has still his vacation auto-reply turned on, so when the automatic postmaster’s email reaches his mailbox, the system will automatically reply back to the “postmaster@company.com” quoting the previous email and adding his auto-reply message:

    Out of Office – I will be back from holidays at the end of July.

    So the postmaster@company.com currently has all the financial details that he shouldn’t!

    Apart from the fact that the user was sending financial data to somebody else in a clear text email instead of an encrypted one, the second biggest mistake that the user has made was that he has enabled vacation auto-replies that quote the email he was previously sent. That’s very very wrong. If you don’t want sensitive stuff ending at the postmaster’s inbox avoid quoting previous emails in your auto-replies by all means.

    Based on a true story :)

    please use indexes in your database tables

    I don’t know how many times I’ve heard a sysadmin swearing at a web/application developer that has “forgotten” to add proper index to their database tables. Please dear developers, add those indexes, it’s not that hard!

    Today I had to do it on a table for a xoops module. A single query could keep mysql very very busy…how busy ? the query could take more than 2 minutes, unless it was already cached! I’ll post the way I solved it as an example. It is actually usually quite easy to solve these kind of issues.

    1) How to spot the problem
    Your application is slow and your server’s “top” shows mysql running constantly at full load.

    2) Find the problematic query. Login to mysql and run a ‘show processlist;’

    
    mysql> show processlist;
    +------+--------------+-----------+--------------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
    | Id   | User         | Host      | db                 | Command | Time | State          | Info                                                                                                 |
    +------+--------------+-----------+--------------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
    | 1792 | admin        | localhost | aaa                | Sleep   |    0 |                | NULL                                                                                                 | 
    | 4790 | user         | localhost | bbb                | Query   |    0 | Sorting result | SELECT * FROM foobar WHERE (com_rootid = '22797' AND com_id >= '22797') ORDER BY com_id              | 
    | 4791 | user         | localhost | bbb                | Query   |    0 | Sorting result | SELECT * FROM foobar WHERE (com_rootid = '22797' AND com_id >= '22797') ORDER BY com_id              | 
    | 4817 | admin        | localhost | NULL               | Query   |    0 | NULL           | show processlist                                                                                     | 
    +------+--------------+-----------+--------------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
    4 rows in set (0.00 sec)
    

    3) Take a closer look at the structure of the table

    
    mysql> describe foobar;
    +--------------+-----------------------+------+-----+---------+----------------+
    | Field        | Type                  | Null | Key | Default | Extra          |
    +--------------+-----------------------+------+-----+---------+----------------+
    | com_id       | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment | 
    | com_pid      | mediumint(8) unsigned | NO   | MUL | 0       |                | 
    | com_rootid   | mediumint(8) unsigned | NO   |     | 0       |                | 
    | com_modid    | smallint(5) unsigned  | NO   |     | 0       |                | 
    | com_itemid   | mediumint(8) unsigned | NO   | MUL | 0       |                | 
    | com_icon     | varchar(25)           | NO   |     |         |                | 
    | com_created  | int(10) unsigned      | NO   |     | 0       |                | 
    | com_modified | int(10) unsigned      | NO   |     | 0       |                | 
    | com_uid      | mediumint(8) unsigned | NO   | MUL | 0       |                | 
    | com_ip       | varchar(15)           | NO   |     |         |                | 
    | com_title    | varchar(255)          | NO   | MUL |         |                | 
    | com_text     | text                  | NO   |     | NULL    |                | 
    | com_sig      | tinyint(1) unsigned   | NO   |     | 0       |                | 
    | com_status   | tinyint(1) unsigned   | NO   |     | 0       |                | 
    | com_exparams | varchar(255)          | NO   |     |         |                | 
    | dohtml       | tinyint(1) unsigned   | NO   |     | 0       |                | 
    | dosmiley     | tinyint(1) unsigned   | NO   |     | 0       |                | 
    | doxcode      | tinyint(1) unsigned   | NO   |     | 0       |                | 
    | doimage      | tinyint(1) unsigned   | NO   |     | 0       |                | 
    | dobr         | tinyint(1) unsigned   | NO   |     | 0       |                | 
    +--------------+-----------------------+------+-----+---------+----------------+
    20 rows in set (0.01 sec)
    

    4) run EXPLAIN on the problematic query

    
    mysql> explain SELECT * FROM foobar WHERE (com_rootid = '17536' AND com_id >= '17536') ORDER BY com_id;
    +----+-------------+--------------------+------+-----------------------+------+---------+------+-------+-----------------------------+
    | id | select_type | table              | type | possible_keys         | key  | key_len | ref  | rows  | Extra                       |
    +----+-------------+--------------------+------+-----------------------+------+---------+------+-------+-----------------------------+
    |  1 | SIMPLE      | foobar             | ALL  | PRIMARY               | NULL | NULL    | NULL | 18271 | Using where; Using filesort | 
    +----+-------------+--------------------+------+-----------------------+------+---------+------+-------+-----------------------------+
    1 row in set (0.02 sec)
    

    If “type” is “ALL”, like the case above, then you have a problem. This is the worst “type” you could have. The query above scans the whole table and has to go through 18271 rows and then has to do a filesort on these rows.

    5) Look at indexes, if there are any….

    
    mysql> show index from foobar;
    +--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    | Table              | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
    +--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    | foobar             |          0 | PRIMARY    |            1 | com_id      | A         |       18272 |     NULL | NULL   |      | BTREE      |         | 
    | foobar             |          1 | com_pid    |            1 | com_pid     | A         |           1 |     NULL | NULL   |      | BTREE      |         | 
    | foobar             |          1 | com_itemid |            1 | com_itemid  | A         |          18 |     NULL | NULL   |      | BTREE      |         | 
    | foobar             |          1 | com_uid    |            1 | com_uid     | A         |           6 |     NULL | NULL   |      | BTREE      |         | 
    | foobar             |          1 | com_title  |            1 | com_title   | A         |       18272 |       40 | NULL   |      | BTREE      |         | 
    +--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    5 rows in set (0.00 sec)
    

    6)Create necessary new index

    
    ALTER TABLE  `DB`.`foobar` ADD INDEX `com_id_rootid` (  `com_id` ,  `com_rootid` )
    

    7) Explain the query again

    
    mysql> explain SELECT * FROM foobar WHERE (com_rootid = '17536' AND com_id >= '17536') ORDER BY com_id;
    +----+-------------+--------------------+------+------------------------+---------------+---------+-------+------+-----------------------------+
    | id | select_type | table              | type | possible_keys          | key           | key_len | ref   | rows | Extra                       |
    +----+-------------+--------------------+------+------------------------+---------------+---------+-------+------+-----------------------------+
    |  1 | SIMPLE      | foobar             | ref  | PRIMARY,com_id_rootid  | com_id_rootid | 3       | const |    1 | Using where; Using filesort | 
    +----+-------------+--------------------+------+------------------------+---------------+---------+-------+------+-----------------------------+
    1 row in set (0.00 sec)
    

    now the query is of type “ref” and it only fetches 1 row!
    If the query could be optimized even more by the developer and he/she could replace ‘>=’ with ‘=’ then it would make us even happier

    
    mysql> explain SELECT * FROM foobar WHERE (com_rootid = '17536' AND com_id = '17536') ORDER BY com_id;
    +----+-------------+--------------------+-------+-----------------------+---------+---------+-------+------+-------+
    | id | select_type | table              | type  | possible_keys         | key     | key_len | ref   | rows | Extra |
    +----+-------------+--------------------+-------+-----------------------+---------+---------+-------+------+-------+
    |  1 | SIMPLE      | foobar             | const | PRIMARY,com_id_rootid | PRIMARY | 3       | const |    1 |       | 
    +----+-------------+--------------------+-------+-----------------------+---------+---------+-------+------+-------+
    1 row in set (0.00 sec)
    

    the type would now be ‘const’, which is the best type we could have.

    The problem got fixed with just one command! Now the queries take milliseconds to finish and the web application is as fast as ever. The server load dropped from 2.5 to 0.4 with just this tiny addition to the indexes.

    CPU load drops dramatically after adding the proper index to the table

    CPU Load

    So dear developers, please run EXPLAIN to your queries before you submit your applications and if you find queries with type=ALL try to add indexes! It saves all of us a loooooot of trouble!

    For anyone who wants to read a nice blog post about EXPLAIN and what each part of the output means, I recommend that he/she reads this: MySQL Explain – Reference