Postfixadmin Installer for Wheezy

Debian Wheezy ships with Dovecot 2.x which has a different config layout to the 1.x verion in Lenny and Squeeze. In response, I've created a wheezy branch of postfixadmin-installer (there's an issue for it, too) which configures Dovecot 2.x and it's actually been a really easy switch.

In much the same way as the current version generally does away with the heavily commented documentation masquerading as a config file, this one simply moves /etc/dovecot out of the way and writes two files into it - dovecot.conf and dovecot-sql.conf (which are the same as for 1.x). This causes a pretty hilarious reduction in filesize, too:

root@pfa:~# find /etc/dovecot/ -type f -exec cat {} \; | wc -l
48
root@pfa:~# find /etc/dovecot_2013-01-29/ -type f -exec cat {} \; | wc -l
1772
root@pfa:~#

Anyway, with some incredibly limited testing, and assuming you have already installed dovecot, this seems to work. If you want to test it (please!), enable Wheezy backports in Squeeze and then:

apt-get install libwww-perl mysql-server postfix
apt-get -t squeeze-backports install dovecot-common dovecot-imapd dovecot-pop3d
wget --no-check-certificate https://raw.github.com/BigRedS/postfixadmin-installer/wheezy/postfixadmin-installer
perl ./postfixadmin-installer

And, finally, here's that working config I'm using, in case that's what you're after:
/etc/dovecot/dovecot.conf

protocols = imap pop3
log_timestamp = "%Y-%m-%d %H:%M:%S "
mail_location = maildir:/var/vmail/%d/%n
mail_privileged_group = vmail
# This should match that of the owner of the /var/lib/vmail hierarchy, and
# be the same as the one postfix uses.
first_valid_uid = 999
# Allow people to use plaintext auth even when TLS/SSL is available (you
# might not want this but it is handy when testing):
disable_plaintext_auth = no
# Uncomment this to get nice and verbose messages about authentication
# problems:
# auth_debug=yes

ssl = no

protocol imap {
}

protocol pop3 {
  pop3_uidl_format = %08Xu%08Xv
}

# 'plain' here doesn't override the disble_plaintext_auth_default of 'yes'.
# you should add any other auth mechanisms you want
#auth_mechanisms = plain
userdb {
  driver = sql
  args = /etc/dovecot/dovecot-sql.conf
}
passdb {
  driver = sql
  args = /etc/dovecot/dovecot-sql.conf
}

service auth {
  unix_listener /var/spool/postfix/private/auth {
    mode = 0660
    # yes, 'postfix' (or the user that owns the above socket file), not vmail
    user = postfix
    group = postfix
  }
}

/etc/dovecot/dovecot-sql.conf

connect = host=localhost dbname=vmail user=vmail password=1lgI2ehK6aEqytjkeDFT4Z7Pq
driver = mysql
default_pass_scheme = MD5-CRYPT
password_query = SELECT username AS user,password FROM mailbox WHERE username = '%u' AND active='1'
user_query = SELECT maildir, 999 AS uid, 122 AS gid FROM mailbox WHERE username = '%u' AND active='1'

Tidying up postfixadmin installer

I've *finally* merged about a billion changes into master in postfixadmin installer, chief amongst them is that most of the boring output now goes to a logfile, the vacation plugin might work after install and it the setup password is randomised. This is all procrastination in order to avoid working out how to configure Dovecot on Wheezy.

It's still a big pile of poor hacks rather than a 'proper' script, but if you just don't look at the source you'll be fine!

Sitecreator

I've just spent a few days using up spare holiday, which means I've been making things for work that work doesn't want but I do. This time it's sitecreator, a tool for configuring websites and all their dependencies (Unix users, databases, ssh keys, DNS records etc.) on servers.

Since there's so many possible things for the site to rely upon, and I'm not *that* fond of reinventing the wheel, all it really does is generate passwords and call scripts. There's a configuration file that tells it how many passwords to generate, how to work out what the username should be and perhaps to generate a couple of other things (like database names) if needed. Another bit of the config then explains which scripts to call and with which arguments (including these recently-generated passwords and usernames), and at the end it tells you what it thinks it did. I've written a few scripts for it already (mirroring what I want to do with it).

For example, here's a relatively simple config file with some explanation of what's going on, and some output with that configuration:

avi@amazing:~$ sitecreator example.com
MySQL:
        username: example
        password: gN?@c6$Y7}Y{yg
        database: example

SSH:
        username: example
        password: r;x6kEgO!

MySQL dev:
        username: example_dev
        password: vA!)9WIMo&by}'
        database: example

avi@amazing:~$

And there's at least another example config file in etc/config/. Anyway, hopefully this'll be useful to somebody else who isn't quite into automation enough to have already done this (or to have started using puppet or similar), but does have enough users or systems to configure that some automation would be good.

Oh, it's not very tested yet, and I've still not come up with a sane thing to do with the output from the scripts :)

Tagging images by path in Shotwell

I've finally decided to use an image manager, and since it comes with Ubuntu this week I've gone with Shotwell. I've got a directory hierarchy containing most of my images which is sort-of sorted already, and I'm probably going to keep adding to it, if for no other reason than force of habit.

I know that one of the wonderful features of these photo managers is that you can tag photos, and obviously a photo can be in more than one tag rather more easily than it can be in several directories. That said, all photo managers seem to have decided that an easy, fast way to tag photos isn't what's needed.

Additionally, shotwell's got this weird thing for hiding the fact that there's a filesystem from you, and I can't find any way to tag files by directory. So I've poked around the database and written a script to do it, which is below and here and pasted below in case I change my mind about file hierarchies later.

The oddest bit is the way the filenames are linked to the tags. The TagTable table has a field `photo_id_list` which contains a list of photo IDs in a format that I've not found anywhere else in the (admittedly not very extensive) db.

They're created by taking the id of the image (its value in the `id` field of the PhotoTable table), converting it to a hex value, padding it out to 16 characters with leading zeroes, and then concatenating it onto the string 'thumb':

  1. my $hexPhotoId = sprintf("%x", $photoId);
  2. my $thumbString = "thumb".sprintf('%016s', $hexPhotoId);

Anyway, the script's a bit simple because bash is quite good at handling loads of files; usage is like this to tag the contents of ~/Pictures/2011-france/ with the tag 'morzine':

avi@brilliant:~$ find ~/Pictures/2011-france/ -type f -exec ./shotwell-tag {} morzine \;
Creating tag morzine
tagged /home/avi/Pictures/2011-france/R0012810.JPG with morzine
tagged /home/avi/Pictures/2011-france/R0012850.JPG with morzine
tagged /home/avi/Pictures/2011-france/R0012911.JPG with morzine
tagged /home/avi/Pictures/2011-france/R0012931.JPG with morzine
tagged /home/avi/Pictures/2011-france/R0012921.JPG with morzine
tagged /home/avi/Pictures/2011-france/R0012794.JPG with morzine
tagged /home/avi/Pictures/2011-france/R0012883.JPG with morzine
tagged /home/avi/Pictures/2011-france/R0012881.JPG with morzine

I've no idea if it breaks anything - I wrote it about an hour ago, have tagged ~500 photos with it since, and Shotwell doesn't seem to be annoyed. YMMV. Here's the script:

  1.  
  2. #! /usr/bin/perl
  3.  
  4. # shotwell-tag
  5. #
  6. # Tags files specified by filename in shotwell. Handy for
  7. # getting round shotwell's attempts at hiding the filesystem.
  8. #
  9. # Avi 2011
  10.  
  11. use strict;
  12. use DBI;
  13.  
  14. my $file = shift;
  15. my $tag = shift;
  16.  
  17. if ($tag !~ /.+/){
  18. print "Usage:\n\n\tshotwell-tag [file] [tag]\n\n";
  19. print "Tags [file] with [tag] in shotwell's db\n";
  20. exit 1;
  21. }
  22.  
  23. my $dbfile = $ENV{'HOME'}."/.shotwell/data/photo.db";
  24. my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","");
  25.  
  26. # Each tag has a string of photo 'ids'. These are generated
  27. # by taking the ID of the photo from PhotoTable, representing
  28. # it in hex, padding that out to 16 characters with leading
  29. # zeroes and then appending it to the string 'thumb'
  30. my $sth = $dbh->prepare("select id from PhotoTable where filename='$file'");
  31. $sth->execute();
  32. my $row = $sth->fetch;
  33. my $photoId = $row->[0];
  34. unless($photoId =~ /\d+/){print "$file is not in shotwell library\n"; exit 0;}
  35. my $hexPhotoId = sprintf("%x", $photoId);
  36. my $thumbString = "thumb".sprintf('%016s', $hexPhotoId);
  37.  
  38. $sth = $dbh->prepare("select id from TagTable where name='$tag'");
  39. $sth->execute();
  40. $row = $sth->fetch;
  41. my $tagId = $row->[0];
  42. unless($tagId =~ /\d+/){
  43. print "Creating tag $tag\n";
  44. my $sth = $dbh->prepare("insert into TagTable (name) values('$tag')");
  45. $sth->execute;
  46. }
  47.  
  48. $sth = $dbh->prepare("Select photo_id_list from TagTable where name='$tag'");
  49. $sth->execute();
  50. $row = $sth->fetch;
  51. my $photoList = $row->[0];
  52. if($photoList !~ /,$/ && $photoList =~ /._/){
  53. $photoList.=',';
  54. }
  55. if($photoList =~ /$thumbString/){
  56. print "$file is already tagged with $tag\n";
  57. exit 0;
  58. }else{
  59. $photoList.=$thumbString.',';
  60. $sth = $dbh->prepare("update TagTable set photo_id_list = '$photoList' where name='$tag'");
  61. $sth->execute;
  62. print "tagged $file with $tag\n";
  63. exit 0;
  64. }
  65.  

Splitting massive MySQL dumps

As I posted yesterday, I have a massive MySQL dump to import. I tried BigDump, but one of the tables kept producing errors and so BigDump would exit. I don't need the whole db imported, so I wrote this to split it by table. It produces a new sql file for every table it finds, numbered sequentially so if you process them in alphabetical order it's the equivalent of the whole dump. USE statements get their own files in the same sequence.

  1. #! /usr/bin/perl
  2.  
  3. use strict;
  4. use warnings;
  5. use 5.010;
  6.  
  7. my $dump_file = $ARGV[0];
  8. &usage() if !$dump_file;
  9.  
  10. say "using ".$dump_file;
  11.  
  12. my ($line, $table,@query, $file_number,$file_name);
  13. my $line_number = 1;
  14. my $find_count = 0;
  15.  
  16. open(DUMP_IN, "< $dump_file");
  17. while(<DUMP_IN>){
  18. my $line = $_;
  19. if (/^USE\s.(\w+)./){
  20. say "changing db: ".$1;
  21. $file_name = &make_file_name("USE_$1", "$find_count");
  22. &write_USE($file_name, $line);
  23. $find_count++;
  24. }elsif (/^-- Table structure for table .(.+)./){
  25. ## If the current line is the beginning of a table definition
  26. ## and @query is defined, then @query must be full of the previous
  27. ## table, so we want to process it now:
  28. if (@query){
  29. $file_name = &make_file_name("$table", "$find_count");
  30. open(OUTPUT, ">$file_name");
  31. foreach(@query){
  32. print OUTPUT $_;
  33. }
  34. close OUTPUT;
  35. undef @query;
  36. }
  37. $table = $1;
  38. $find_count++;
  39. }
  40. next unless $table;
  41. push @query, $line;
  42.  
  43. $line_number++;
  44. }
  45. close DUMP_IN;
  46. say $line_number;
  47.  
  48. ## Subroutines!
  49. sub write_USE() {
  50. my($filename, $line) = @_[0,1];
  51. open (OUTPUT, ">$filename");
  52. print OUTPUT $line;
  53. close OUTPUT;
  54. }
  55.  
  56. sub make_file_name() {
  57. my ($type, $number) = @_[0,1];
  58. $number = sprintf("%05d", $number);
  59. $file_name=$number."_".$type.".sql";
  60. return $file_name;
  61. }
  62.  
  63. sub usage() {
  64. say "Error: missing arguments.";
  65. say "Usage:";
  66. say "$0 [MYSQL_DUMP]";
  67. exit 1;
  68. }
  69.  

A small downside is that this replaces my 2.5Gb file with about 1800 smaller ones. A scripted importer is to follow.

Generating Fluxbox menus for VNC (Vinagre) connections

One of the lovely things about Fluxbox is the text-driven menu. One of the nice things about Vinagre (Gnome's VNC client) is the xml-based bookmarks file. Here's a handy script to create a Fluxbox submenu out of your Vinagre bookmarks:

  1.  
  2. #! /usr/bin/perl
  3.  
  4. use strict;
  5. use warnings;
  6. use XML::Simple;
  7. my $HOME = $ENV{ HOME };
  8.  
  9. my $bookmarks_file = "$HOME/.local/share/vinagre/vinagre-bookmarks.xml";
  10. my $menu_file = "$HOME/.fluxbox/vnc_menu";
  11.  
  12. my $xml = new XML::Simple (KeyAttr=>[]);
  13. my $data = $xml->XMLin("$bookmarks_file");
  14.  
  15. open(MENU, ">$menu_file") || die "Error opening \$menu_file: $menu_file $0";
  16.  
  17. print MENU "[begin]\n";
  18.  
  19. foreach my $b(@{$data->{"item"}}){
  20. print MENU "[exec] ($b->{name}) {vinagre $b->{host}:$b->{port}}\n";
  21. }
  22. print MENU "[end]\n";
  23. close MENU;
  24.  

Dell Warranty Info

I hate navigating the Dell website. It's inconsistent and messy and noisy, and all I generally want is a single date (when the warranty expires or expired on a given box). So I wrote this. It scrapes the Dell website, and returns the warranty info for the service tag it's been passed.
I've CGI'd it here.

  1. #! /usr/bin/perl
  2.  
  3. use strict;
  4. use warnings;
  5.  
  6. die "$0\n\tGet warranty info from dell.\nUsage\n$0 [SERVICE TAG]\n" if !$ARGV[0];
  7.  
  8. my $service_tag = $ARGV[0];
  9.  
  10. use LWP::Simple;
  11. use HTML::TableExtract; # Is in the CPAN, and exists in the debian repositories as libhtml-tableextract-perl
  12.  
  13. ## Make a URL:
  14. my $url_base = "http://support.euro.dell.com/support/topics/topic.aspx/emea/shared/support/my_systems_info/en/details";
  15. my $url_params = "?c=uk&cs=ukbsdt1&l=en&s=gen";
  16. my $url = $url_base.$url_params."&servicetag=".$service_tag;
  17. my $content = get($url);
  18.  
  19. # Tell HTML::TableExtract to pick out the table(s) whose class is 'contract_table':
  20. my $table = HTML::TableExtract->new( attribs => { class => "contract_table" } );
  21. $table->parse($content);
  22.  
  23. ## Gimme infos!
  24. foreach my $ts ($table->tables) {
  25. foreach my $row ($ts->rows) {
  26. print "", join("\t", @$row), "\n";
  27. }
  28. }

Getopt in Perl

Oddly, it's taken me until this afternoon to have real need for using getopts in Perl. After a not-overly-brief look around, I've settled on Getopt::Long for the purpose. It's marginally more complicated than the alternative (Getopt::Std), but more flexible and better at error checking.

To use it, you pass a hash of valid options to GetOptions, where the keys are options and the values are the references to variables in which to put their arguments.
The name of the option dictates what value(s) it can hold: the final character indicates type (i - integer, f - float, s - string), and the penultimate whether it is optional or not (= - required, : - optional). Flags are indicated by not following this pattern - they're just given a name with no symbols.

Getopt::Long allows for the shortest unambiguous switch to be used, doesn't distinguish between -o and --o, and allows for the negation of flags (if -flag sets a flag to 1, -noflag will set it to 0). It also doesn't touch @ARGV when it's done getting its flags out of it.

Here's a brief script hopefully helping explain the above:

  1. #!/usr/bin/perl
  2. use strict;
  3. use warnings;
  4. use Getopt::Long;
  5.  
  6. # This is only neccesary when using strict. Which is always.
  7. my ($flag, $compulsory_string, $optional_string, $compulsory_integer, $optional_integer, $compulsory_
  8.  
  9. GetOptions(
  10. "o"=>\$flag,
  11. "cfloat=f"=>\$compulsory_float,
  12. "cint=i"=>\$compulsory_integer,
  13. "cstring=s"=>\$compulsory_string,
  14. "ofloat:f"=>\$optional_float,
  15. "oint:i"=>\$optional_integer,
  16. "ostring:s"=>\$optional_string,
  17. );
  18.  
  19. print "flag set\n" if $flag;
  20. print $compulsory_float."\n" if $compulsory_float;
  21. print $compulsory_integer."\n" if $compulsory_integer;
  22. print $compulsory_string."\n" if $compulsory_string;
  23. print $optional_float."\n" if $optional_float;
  24. print $optional_integer."\n" if $optional_integer;
  25. print $optional_string."\n" if $optional_string;

Munin plugins are really easy to write

Munin plugins basically need to output variable names and values, and a little bit of config. They're tremendously easy to write.

My plugin is mostly useless - it graphs the value returned by /dev/urandom, and the random constants from debian and dilbert. Current graph is here and the code is as follows:

  1. #! /bin/bash
  2.  
  3. case $1 in
  4. config)
  5. cat < < EOF
  6. graph_category amusement
  7. graph_title Random numbers
  8. graph_vlabel value
  9. debian.label debian
  10. dilbert.label dilbert
  11. urandom.label /dev/urandom
  12. graph_scale no
  13. EOF
  14. exit 0
  15. esac
  16.  
  17. urandom=$(cat /dev/urandom | tr -dc '0-9' | head -c2)
  18.  
  19. echo "urandom.value " $urandom
  20. echo "debian.value 4"
  21. echo "dilbert.value 9"
  22.  

Munin's plugins live in /etc/munin/plugins/, most of which are symlinks to scripts in /usr/share/plugins/. On restart, munin-node rechecks the plugins directory and loads any new plugins.
For a plugin called foo, munin-node will run foo configure first to get the configuration of the graph (which is passed to munin-graph), and then foo. For information as to graph configuration, see here.
It takes about 15 mins of collection for it to start making a graph, and you'll get more data every 5mins thereafter.

The script itself is mostly self-explanatory, except for:

- The values and the labels are linked by what occurs before the dot. If you define foo.label in the config output, that is what will be used to label the number that comes after foo.value in the 'normal' output. The munin tutorial sort-of hints at this, but only uses one variable.

- Munin doesn't care what order the variables come out in, it uses the labels to determine who's who. Similarly, it doesn't seem particularly fussed as to which flavour of horizontal whitespace is used.

Simple complex password generator

These are really easy to write, but it's always handy to have your own. This one lives here in its cgi form.

  1. #! /usr/bin/perl
  2.  
  3. use strict;
  4. use warnings;
  5.  
  6. ## Let the browser know what we're sending it
  7. print "content-type: text/html\n\n";
  8.  
  9. ## Spew some HTML since we're not going to get away with plain text formatting
  10. print "<html>\n\t<head>";
  11. print "\t\t<title>Passwords!</title>";
  12. print "\t</head>";
  13. print "<body>\n\t<h1>Avi's Magical Password Generator</h1>";
  14.  
  15. ## Define two sets of data. The first is the lengths of password we want
  16. ## to produce, the second is the allowed characters. Each space-separated
  17. my $lengths="1 4 8 10 20 30 50 80 100";
  18. my $characters="A B C D E F G H I J K L M N O P Q R S T U V W X Y Z a b c d e f g h i j k l m n o p q r s t u v w x y z 1 2 3 4 5 6 7 8 9 0 ! $ % ^ & _ + = : ; @ # ~ , . ? ";
  19.  
  20. ## Split the above by space into arrays
  21. my @chars=split(/ /, $characters);
  22. my @lens=split(/ /, $lengths);
  23.  
  24. ## Work out how many characters we are allowed, so that when we
  25. ## come to pick one at random we can use this number as the maximum.
  26. my $chars_count = @chars;
  27.  
  28. ## Spew out some html for pretty formatting:
  29. print "<table>\n";
  30. print "\t<tr><td>length</td><td></td></tr>";
  31.  
  32.  
  33. ## The loop!
  34. foreach (@lens){
  35. my $length=$_;
  36. my $count;
  37. ## The first cell of the row, containing the length:
  38. print "\n\t<tr><td>".$_."</td><td>";
  39. for ($count = 1; $count < = $length; $count++){
  40. ## Working backwards, $chars_count is the above-defined
  41. ## number of characters we have to play with,
  42. ## rand($chars_count) picks a random number between 0 and
  43. ## $chars_count, and int(rand($chars_count)) makes sure
  44. ## it's an integer. This in the square brackets after $chars
  45. ## means we're picking a random element out of the array
  46. ## @chars, which is effectively picking a random character
  47. ## out of the list of allowed ones.
  48. print $chars[int(rand($chars_count))];
  49. }
  50. print "</td></td></tr>\t\t\n";
  51. }
  52. print "</table>\n\n";
  53.  
  54.  
  55. print "<a href=./password.txt>sauce</a> <a href=..>home</a>";
  56. print "</body>";</html>