Getting a native connection from the ORM

The SQLAlchemy ORM provides a powerful abstraction from the database allowing operations to be performed on objects and queries to be constructed based on object attributes rather than dealing with attribute-to-field correspondence.  But there are still some operations for which you need to talk directly to the underlying database. 
In 'normal' mode SQLAlchemy maintains a connection pool and releases connections from the pool to the application as needed, tracks them, and tries to keep everything tidy.  When the need arises for a 'native' DBAPI connection [for this example I'm using PostgreSQL] it is possible to explicitly check a connection out from the pool - after which it is yours to deal with, to track the isolation, close, etc..
Assuming the database connection has already been created and bound to the session factory with something like:

from sqlalchemy import create_engine, Session
...
engine = create_engine( orm_dsn, **{ 'echo': orm_logging } )
Session.configure( bind=engine )

 - then sessions can be taken from the connection pool simply by calling "db = Session( )".  When the "db.close( )" is performed that session goes back into the pool.
If a connection is to be used outside of all the mechanics that SQLAlchemy provides it can be checked out, as mentioned before, using a rather verbose call:

conn = Session( ).connection( ).connection.checkout( ).connection

Now "conn" is a 'native' DBAPI connection.  You can perform low level operations such as setting the isolation level and creating cursors:

conn.set_isolation_level( psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT )
curs = conn.cursor( )
curs.execute( .... )

This is not a frequent need, but for very database backend specific it is the simplest approach [otherwise you can extend SQLAlchemy...]. One use case is using PostreSQL's asyncronous notify channels to capture database events; for this purpose an application needs to select on the DBAPI connection, there's no need for an ORM in the mix when you are just capturing events.

Setting a course for UTC

Timezones and daylight savings times are confusing; it is much more complicated that offset-from-UTC.  There are times that occur more that once a year [yep, it hurts] as well as times that are between two valid times but never happen.  It probably requires a Tardis to understand why anyone would want it to work this way.  But, sadly, it does work this way. 
If you stick to the rules, you can safely manage times... so long as those times are all localized.   Naive times, times that are not localized, are the enemy.
Unfortunately there is a lot of code out there, even in important and widely used modules, that uses nieve datetimes.  If you try to use a virtuously localized datetime object with those modules you will likely encounter the dreaded "Cannot compare naive and localized values".
One hack is to make sure the time is localized to the system's timezone, then make it naive, call the module's function, and then re-localize the result (again). Tedious and very prone to error.  The one real problem with this hack is that on most systems the Python process has not @*^$&*@* clue what time zone it is in.  Don't believe me? Try it:

>>> import time
>>> time.tzname
('EST', 'EDT')

Eh, that's a tuple.  And while "EST" is a time zone "EDT" is not a timezone.  Yes, I can determine that I am in daylight savings time locally using time.daylight; but I can't localize a datetime to a daylight timezone because daylight is an attribute of a timezone, not a timezone itself.  That is true regardless of what time.tzname says.  And the "EST" doesn't have daylight savings time, "US/Eastern" does.  "EST" is "US/Eastern" when not it daylight savings time. Gnarly.
But I want to use datetime obejcts reliably and safely with modules that require naive datetime objects....  The answer is to make the timezone known!  I cannot reliably get it from the system but I can make it what I want, and what I want is UTC!  Then my naive datetime objects do not have to be concerned with daylight savings time.  I can just localize them to UTC and subsequently convert them to whatever timezone the user needs to see.  This is accomplished using a combination of the os and time modules.  Early on in my Python application I move myself to UTC.  Here is an example that demonstrates the ugliness of naive times in an unknown timezone, and the beauty of the process being in UTC.

from datetime import datetime
import pytz, time, os

print( 'NOW: {0}'.format( datetime.now( ) ) )
print( 'UTCNOW: {0}'.format(datetime.utcnow( ) ) )
# What timezone is local?  Problem is, most of the time we just do not know.
print( 'LOCALIZEDNOW: {0}'.format( pytz.timezone( 'UTC' ).localize( datetime.now( ) ) ) )
print( 'LOCALIZEDUTC: {0}'.format( pytz.timezone( 'UTC' ).localize( datetime.utcnow( ) ) ) )

#Change to UTC
os.environ[ 'TZ' ] = 'UTC'
time.tzset( )

print( 'NOW: {0}'.format( datetime.now( ) ) )
print( 'UTCNOW: {0}'.format( datetime.utcnow( ) ) )
print( 'LOCALIZEDNOW: {0}'.format( pytz.timezone( 'UTC' ).localize( datetime.now( ) ) ) )
print( 'LOCALIZEDUTC: {0}'.format( pytz.timezone( 'UTC' ).localize( datetime.utcnow( ) ) ) )

And the output:

NOW: 2012-10-26 07:03:31.285486
UTCNOW: 2012-10-26 11:03:31.285570
LOCALIZEDNOW: 2012-10-26 07:03:31.285632+00:00
LOCALIZEDUTC: 2012-10-26 11:03:31.285705+00:00
NOW: 2012-10-26 11:03:31.285787
UTCNOW: 2012-10-26 11:03:31.285812
LOCALIZEDNOW: 2012-10-26 11:03:31.285848+00:00
LOCALIZEDUTC: 2012-10-26 11:03:31.285875+00:00

Now the danger of somehow getting a naive datetime into the mix is completely avoided - I can always safely localize a naive time to UTC.

Idjit's Guide To Installing RabbitMQ On openSUSE 12.2

The RabbitMQ team provides a generic SUSE RPM which works on openSUSE 11.x, openSUSE 12.1, and I presume on the pay-to-play versions of SuSE Enterprise Server. About the only real dependency for RabbitMQ is the erlang platform which is packaged in the erlang language repo. So the only real trick is getting the RabbitMQ package itself [from this page].  Then install and start is as simple as:

zypper ar http://download.opensuse.org/repositories/devel:/languages:/erlang/openSUSE_12.2 erlang
zypper in erlang
wget http://www.rabbitmq.com/releases/rabbitmq-server/v2.8.6/rabbitmq-server-2.8.6-1.suse.noarch.rpm
rpm -Uvh rabbitmq-server-2.8.6-1.suse.noarch.rpm

Now before you start the rabbitmq-server you need to modify the /etc/init.d/rabbitmq-server file changing "LOCK_FILE=/var/lock/subsys/$NAME" to "LOCK_FILE=/var/run/rabbitmq/$NAME".  The directory "/var/lock/subsys/$NAME" doesn't exist on openSUSE, so this change puts the lock file over under /var/run/rabbitmq along with the PID file.  Otherwise you can create the /var/lock/subsys/$NAME directory with the appropriate permissions.

Every time you modify a service script in /etc/init.d you need to then run systemctl --system daemon-reload so that systemd knows to anticipate the changed file.

If you want to use Rabbit's management interface you now need to enable the appropriate plugins:

rabbitmq-plugins enable rabbitmq_management
rabbitmq-plugins enable rabbitmq_management_visualiser

By default RabbitMQ will listen on all your hosts interfaces for erlang kernel, AMQP, and HTTP (management interface) connections.  Especially in the case of a developement host you may want to restrict the availability of one or all of these services to the local machine.

In order to keep the erlang kernel and Rabbit's AMQ listeners restricted to the local host you'll need to add two exported environment variables to the service script - just put them in following the definition of PID_FILE.

export RABBITMQ_NODENAME=rabbit@localhost
export ERL_EPMD_ADDRESS=127.0.0.1

For the management inteface and other components you'll need to modify [and possibly create] the /etc/rabbitmq/rabbitmq.config configuration file.  RabbitMQ violates the only-have-one-way-to-configure rule of system administration; this is in part due to its reliance on the Erlang runtime - controlling the behavior of the run-time is a [poorly documented] black art.  Both the environment variables and the configuration file are required to restrict all the components to the local interface.  The following configuration file restricts HTTP [management interface] and AMQP services to the localhost and informs the RabbitMQ application that it should find the Erlang kernel at the address 127.0.0.1.

[
  {mnesia, [{dump_log_write_threshold, 1000}]},
  {kernel,[{inet_dist_use_interface,{127,0,0,1}}]},
  {rabbit, [{tcp_listeners, [{"127.0.0.1", 5672}]}]},
  {rabbitmq_management,  [ {http_log_dir,   "/tmp/rabbit-mgmt"} ] },
  {rabbitmq_management_agent, [ {force_fine_statistics, true} ] },
  {rabbitmq_mochiweb, [ {listeners, [{mgmt, [{port, 55672},
                                             {ip, "127.0.0.1"}]}]},
                        {default_listener, [{port, 60000} ] } ] }
 ].

Always modify the configuration file when the RabbitMQ service is shutdown.  A botched configuration file can render the broker unable to shutdown properly leaving you to have to manually kill the processes old-school.

With the RABBITMQ_NODENAME defined in the services file you will either need to add that same variable to the administrator's and application's environment or specify the node name when attempting to connect to or manage the RabbitMQ broker service [your application probably already refers to a configured broker, but you'll certainly have to deal with this when using the rabbitmqclt command].

Now the service should start:

service rabbitmq-server start

The broker service should now be running and you can see the components' open TCP connections using the netstat command.  The management interface should also be available on TCP/55672 [via your browser of choice] unless you specified an alternative port in the rabbitmq.config file.

linux-nysu:/etc/init.d # netstat --listen --tcp --numeric --program
Active Internet connections (only servers)
Proto Local Address    Foreign Address State  PID/Program name  
tcp   127.0.0.1:5672   0.0.0.0:*       LISTEN 23180/beam.smp     
tcp   127.0.0.1:60712  0.0.0.0:*       LISTEN 23180/beam.smp     
tcp   127.0.0.1:4369   0.0.0.0:*       LISTEN 22681/epmd         
tcp   127.0.0.1:55672  0.0.0.0:*       LISTEN 23180/beam.smp     

Now you probably want to do some configuration and provisioning using the rabbitmqctl command; but your RabbitMQ instance is up and running.

Using GNOME Terminal Custom Commands

There are numerous terminal session managers and profile managers, etc... for use by people [typically network and system administrators] who have to SSH or telnet to lots of hosts.   But most of these aren't packages or very well maintained - fortunately there is an almost stealth solution built into the familiar gnome-terminal application.
Multiple profiles can be created in gnome-terminal and profiles can be assigned to "Run a custom command instread of my shell";  this command can be anything.  So that profile can automatically telnet or SSH to another host, potentiall even launch an application on that host (such as firing up GNU screen).

The defined profiles are conveniently available in "Files" / "Open Terminal" and "File" / "Open Tab" menu.  Simply create a profile for each host or device that you typically jump on to. If SSH is in use the automatic tie-in to the GNOME passphrase and keyring will kick in.
Generally you don't want a terminal emulator to grab your keystrokes - you want them to go to the application or session.  Under gnome-terminal's "Edit" / "Keyboard Shortcuts" you can enable F10 to activate the ring-menu which will allow you to conveniently navigate to profiles using just the keyboard.

Sound Converter

A common issue is to have an audio file in one format at to need it in another for compatibility with some specific application or device.  And how to covert the file and know the quality of the result, etc...?  Well... there is a simple application called ... wait for it .... "soundconverter".  It is packaged for just about every distribution and available on openSUSE through the normal repositories.  How obvious can it get?  Apparently not so obvious I couldn't have overlooked it for a long long time.

The soundconverter application.

With soundconverter you can convert between FLAC, MP3, OGG, and WAV.  Add the files you want to covert, use preferences to select the output format, and away you go.  Nice job.

Integrating Postfix And CLAMAV

The clamav-miler is packaged by most distributions in their "clamav" package can be used in conjunction with Postfix to protect your network from malware embedded in SMTP traffic. Integration of CLAMAV and Postfix involves four steps:

1. Configuration and enabling of the clamd service.
2. Updating the CLAMAV malware database and enabling the freshclam service
3. Configuration and enabling of the clamav-milter service. Current versions of the clamav-milter require connectivity to the clamd daemon through either a local UNIX socker or a TCP/IP socket.
4. Configuration of Postfix to utilize the available clamav-milter service.

Step#1 : Enabling the clamd service

LocalSocket /var/lib/clamav/clamd-socket
LogFacility LOG_MAIL
LogSyslog yes
PidFile /var/lib/clamav/clamd.pid
TCPAddr 127.0.0.1
TCPSocket 3310
User vscan

Text 1: Typical settings overridden from defaults in /etc/clamd.conf

The clamd daemon typically reads its configuration from the /etc/clamd.conf file. Most importantly this file specifies, via the TCPSocket and TCPAddr directives, on what IP port and address the service listens for connections. These directives should be set to values appropriate for the host and which will be reachable by the clamav-milter. If the clamav-milter and the clamd daemon will be running on the same host the clamd service can be configured to listen to the localhost address [127.0.0.1] to avoid any potential network firewall and traffic filtering issues.
The clamd.conf file also provides many other tunable values but almost all of these should be appropriate at the distributions defaults.
Once configured the clamd service must be started and enabled for automatic start following the system's boot-up sequence; on RPM based systems this is typically achieved using the service and chkconfig commands.

Step #2 : Enabling the freshclam service

The freshclam service is an instance of the freshclam command line tool started with the “-d” option which runs the command in daemon mode. Whether started from the command-line or running in daemon mode freshclam will read its configuration from the /etc/freshclam.conf file. When running the freshclam daemon will periodically check the CLAMAV project mirrors for new malware signatures and update the local database used by the clamd scanning service. The freshclam daemon should run as the same user context as the clamd service; the typical way to ensure this is to synchronize the values of DatabaseOwner in /etc/freshclam.conf and User in /etc/clamd.conf. The frequency which freshclam will check for new patterns is controlled by the Checks directive – the default is 12 [times a day], this value should be sufficient in most cases. When database update succeeds the freshclam service will notify the clamd service that newer patterns are now available [for this to work the NotifyClamd directive must indicate the correct path to the current clamd configuration file].

DatabaseMirror database.clamav.net
DatabaseOwner vscan
HTTPProxyPort 3128
HTTPProxyServer proxy.example.com
LogFacility LOG_MAIL
LogSyslog yes
NotifyClamd /etc/clamd.conf
OnErrorExecute /usr/local/bin/malware_update_fail.sh
OnUpdateExecute /usr/local/bin/malware_update_ok.sh
PidFile /var/lib/clamav/freshclam.pid
UpdateLogFile /var/log/freshclam.log

Text 2: Example /etc/freshclam.conf file (comments removed)

The most important considerations in configuration of freshclam is if your network configuration requires use of an HTTP proxy server in order to access the CLAMAV mirrors for updates and if you need to configure some form of notification concerning success or failure of the pattern updates – a security focused service like a malware milter doesn't help anyone if it is silently failing in the background.
The HTTPProxyPort and HTTPProxyServer directives allow an HTTP proxy to be specified; freshclam will use this proxy for all mirror requests whether running as a command-line utility or in daemon mode. Should your proxy require a username/password for authentication these can be provided using the additional HTTPProxyUsername and HTTPProxyPassword directives. However it is simpler and more reliable to simply approve the “database.clamav.net” domain and sub-domains on your HTTP proxy service; all mirror requests will be made to those domains.
For notification of successful or failed updates the OnUpdateExecute and OnErrorExecute directives are used respectively. Whatever command is specified here will execute in the security context of the DatabaseOwner. A useful approach is to enable the log file via the UpdateLogFile directive and have the tail-end of that file mailed to a responsible party such as a help-desk or system-administrator for periodic verification that the service is operational.

#!/bin/sh

tail -25  /var/log/freshclam.log \
 | mail -s "[NOTICE] Malware Database Update Successful" \
    -r milter@example.com helpdesk@example.com

Text 3: A simple example script that might be used for OnUpdateExecute

The proper operation of freshclam can be tested by simply executing the freshclam utility on the command-line; it should check the mirrors and download any new patterns without an error message. Once configured and tested the freshclam service must be started and enabled for automatic start following the system's boot-up sequence.

Step #3 : Enabling the clamav-milter service

ClamdSocket tcp:127.0.0.1
LogFacility LOG_MAIL
LogSyslog yes
MilterSocket inet:32767@192.168.1.66
OnInfected Reject
PidFile /var/lib/clamav/clamav-milter.pid
ReportHostname mail.example.com
User vscan
VirusAction /usr/local/bin/virus_notify.sh

Text 4: Example clamav-milter.conf file (with comments removed)

Once the clamd scanning service is running and the freshclam service is maintaining the malware signatures the clamav-milter must be configured and started in order to connect the scanning service into Postfix's SMTP processing. The milter service is typically loads its configuration from the /etc/clamav-milter.conf file.
The service must be informed via the ClamdSocket directive where to find the clamd scanning service and via MilterSocket where to listen for connections from Postfix. The MitlerSocket directive is “inet:port@ip-address”. VirusAction and OnInfected directives can be used to control the behavior of the service when malware is identified; an OnInfected value of Quarantine will cause Postfix to hold the infected message in it's hold queue while a value of Reject will bounce the message with an SMTP error. Especially when used in Reject mode defining an appropriate VirusAction to notify the intended recipient of the message that a message has been discarded is important. The script named by VirusAction is executed in the security context of the scanning service and is provided seven parameters:

1. Virus name-space
2. Message queue id
3. The sender's e-mail addres
4. The e-mail address of the intended recipient.
5. The subject of the message-id
6. The message's Message-ID
7. The date of the message.

Once configured the clamav-milter service must be started and set to automatically restart upon completion of system boot-up.

#!/bin/bash

# Parameters:
#   virus name, queue id, sender, destination, subject, message id, message date

(
 echo "";
 echo "   A message containing malware has been discarded.";
 echo "";
 echo "   Malware:     $1";
 echo "   Sender:      $3";
 echo "   Destination: $4";
 echo "   Subject:     $5";
 echo "   Message-ID:  $6";
 echo "   Date:        $7";
 echo "   Queue-ID:    $2";
 echo "";
) | \
 mail -s '[ALERT] Infected Messages Discarded' \
  -r milter@example.com -c helpdesk@example.com $4

Text 5: A sample script for use as the VirusAction. This script notifies the intended recipient and help-desk that a message was identified as malware and discarded.

Connecting the Postfix service to clamav-milter

In order to integrate the scanning into Postfix the milter is configured in the main.cf file as an smtpd_milter. The default action of the milter should be set to “accept” so if for any reason the milter is unresponsive messages will still be delivered. As when connecting the other components it is important to verify that the Postfix service can reach the specified service [traffic is permitted by firewall's etc...].

smtpd_milters = inet:milter.example.com:32767
milter_default_action = accept

Text 6: Configuration directives from Postfix's main.cf

Upon modification of the main.cf file the Postfix service should be restarted.
Once configured the malware filtering service should be tested; this can be accomplished by acquiring a copy of the EICAR diagnostic virus and verifying that messages with this content attached are rejected and that the end-user's are notified of the rejection [according the clamav-milter's defined VirusAction].

clamd[11973]: instream(127.0.0.1@60469): Eicar-Test-Signature FOUND

Text 7: Example clamd log message for identified malware.

When malware is detected a message will be logged by clamd via syslog regarding the event; this will typically be logged under the “mail” service. Depending on the distribution messages logged as mail will be written to either /var/log/mail or /var/log/maillog [at least with the default syslog configuration].

Enabling the RabbitMQ Management Plugin

Prior to 2.7.x version of RabbitMQ it was necessary to manually install the plug-ins that provided the management interface [as well as their dependencies]. Now in the 2.7.x series the management interface plug-in and related dependencies are included - but not enabled by default.  The management plug-in must be toggled into the enabled state using the new rabbitmq-plugins command.  Enabling a plug-in will automatically enable any other plug-ins that the specified plug-in depends on Whenever you enable or disable a plug-in you must restart the sever.
If you have a brand new 2.7.x instance installed, turn on the plug-in with:

service rabbitmq-server stop
rabbitmq-plugins enable rabbitmq_management
service rabbitmq-server restart

When you performed the rabbitmq-plugins command you should have seen the following output:

The following plugins have been enabled:
  mochiweb
  webmachine
  rabbitmq_mochiweb
  amqp_client
  rabbitmq_management_agent
  rabbitmq_management

You management interface at TCP/55672 should be available.  The initial login and password are "guest" and "guest".  You want to change those.

Idjit's Guide To Installing RabbitMQ on openSUSE 12.1

The RabbitMQ team provides a generic SUSE RPM which works on openSUSE 11.x, openSUSE 12.1, and I presume on the pay-to-play versions of SuSE Enterprise Server. About the only real dependency for RabbitMQ is the erlang platform which is packages in the erlang language repo. So the only real trick is getting the RabbitMQ package itself [from this page].  Then install and start is as simple as:

zypper ar http://download.opensuse.org/repositories/devel:/languages:/erlang/openSUSE_12.1 erlang
zypper in erlang
wget http://www.rabbitmq.com/releases/rabbitmq-server/v2.8.1/rabbitmq-server-2.8.1-1.suse.noarch.rpm
rpm -Uvh rabbitmq-server-2.8.1-1.suse.noarch.rpm
service rabbitmq-server start

Now you probably want to do some configuration and provisioning using the rabbitmqctl command; but your RabbitMQ instance is up and running.

Update 2012-04-10: Updated these instructions to install RabbitMQ 2.8.1.  The later 2.7.x series have start-up script issues as those scripts use the "runuser" command which is not present on openSUSE.  Running the latest RabbitMQ is generally a good idea in any case,  recent versions have corrected several memory leaks and manage resources more efficiently.

All those SQLite databases...

Many current application use the SQLite database for tracking information; this includes F-Spot, Banshee, Hamster, Evolution, and others.  Even the WebKit component uses SQLite [you might be surprised to discover ~/.local/share/webkit/databases].  It is wonderfully efficient that there is one common local data storage technique all these applications can use,  especially since it is one that is managable using a universally known dialect [SQL]. But there is a dark-side to SQLite.  Much like old Dbase databases it needs to be vacuumed.  And how reliably are all those applications providing their little databases with the required affection?  Also, do you trust those lazy developers to have dealt with the condition of a corrupted database?   If an application hangs, or is slow, or doesn't open... maybe that little database is corrupted?

Aside: As a system administrator for almost two decades I do not trust developers. They still put error messages in applications like "File not found!".  Argh!

On the other hand SQLite provides a handy means of performing an integrity check on databases - the "PRAGMA integrity_check" command.  I've watched a few of these little databases and discovered that (a) they aren't often all that little, and (b) manually performing a VACUUM may dramatically reduce their on-disk size.  Both these facts indicate that developers are lazy and should not be trusted.

Note: in at least one of these cases the application has subsequently been improved. Developers do respond rather quickly when offered a blend of compliments spiced with bug reports.  No, I'm not going to name offending applications as that is too easily used as fodder by nattering nabobs.  And even the laziest Open Source developer is working harder than their proprietary brothers.

In light of this situation my solution is a hack - a Python script [download] that crawls around looking for SQLite databases.  First the script attempts to open the database in exclusive mode, then it performs an integrity check, and if that succeeds it performs a vacuum operation.  Currently it looks for databases in "~/.local/share" [where it will find databases managed by application appropriately following the XDG specification], "~/.cache", "~/.pki", "~/.rcc", and "~/.config".
Download the script and run it. Worst thing that happens is that it accomplishes nothing.  On the other hand it might recover some disk space, improve application performance, or reveal a busted database.

Implementing Queue Expiration w/RabbitMQ

The latest versions of RabbitMQ support a feature where idle queues can be automatically deleted from the server.  For queues used in an RPC or workflow model this can save a lot of grief - as the consumers for these queues typically vanish leaving the queue behind. Over time these unused queues accumulate and consume resources on the server(s). If you are using pyamqplib setting the expiration on a queue is as simple as:

import amqplib.client_0_8 as amq
connection = amq.Connection(host="localhost:5672", userid=*, password=*, virtual_host="/", insist=False)
channel = connection.channel()
queue = channel.queue_declare(queue="testQueue", durable=True, exclusive=False, auto_delete=False, arguments={'x-expires': 9000})
channel.exchange_declare(exchange='testExchange', type="fanout", durable=False, auto_delete=False)
channel.queue_bind(queue="testQueue", exchange='exchange')

Now if that queue goes unused for 9 seconds it will be dropped by the server [the value is in milliseconds]. So long as the queue has consumers it will persist, but once the last consumer has disconnected and no further operations have occurred - poof, you get your resources back.

Finding Address Coordinates using Python, SOAP, & the Bing Maps API

Bing maps provides a SOAP API that can be easily accessed via the Python suds module.  Using the API it is trivial to retrieve the coordinates of a postal address.  The only requirement is to acquire a Bing API application key; this process is free, quick, and simple.

import sys, urllib2, suds

if __name__ == '__main__':  
    url = 'http://dev.virtualearth.net/webservices/v1/geocodeservice/geocodeservice.svc?wsdl'
    
    client = suds.client.Client(url)
    client.set_options(port='BasicHttpBinding_IGeocodeService')
    request = client.factory.create('GeocodeRequest')

    credentials = client.factory.create('ns0:Credentials')
    credentials.ApplicationId = 'YOUR-APPLICATION-KEY'
    request.Credentials = credentials

    #Address
    address = client.factory.create('ns0:Address')
    address.AddressLine = "535 Shirley St. NE"
    address.AdminDistrict = "Michigan"
    address.Locality = "Grand Rapids"      
    address.CountryRegion = "United States"
    request.Address = address

    try:        
        response = client.service.Geocode(request)    
    except suds.client.WebFault, e:        
        print "ERROR!"        
        print(e)
        sys.exit(1)

    locations = response['Results']['GeocodeResult'][0]['Locations']['GeocodeLocation']
    for location in locations:        
        print(location)

If you need to make the request via an HTTP proxy server expand the line client = suds.client.Client(url) to:

    proxy = urllib2.ProxyHandler({'http': 'http://YOUR-PROXY-SERVER:3128'})\
    transport = suds.transport.http.HttpTransport()
    transport.urlopener = urllib2.build_opener(proxy)
    client = suds.client.Client(url, transport=transport)

The results will be Bing API GeocodeLocation objects that have an Longitude and Latitude properties.  Note that you may receive multiple coordinates for an address as there are multiple mechanism for locating an address; the method corresponding to the coordinates is a string in the CalculationMethod property of the GeocodeLocation objects.

Suppressing SNMP Connection Messages

You have, of course, done the responsible sys-admin thing and setup an NMS (be it ZenOSS, OpenNMS, Nagios, whatever...).  Then there is the concommitant action of configuring SNMP services on all the relevant hosts.  All is good.  But running SNMP on several distributions churns out the log messages;  when you go to the logs to research a problem you have to filter out and sort through thousands upon thousands of pointless messages like:

Aug  1 16:08:38 flask-yellow snmpd[1976]: Connection from UDP: [192.168.1.38]:52021
Aug  1 16:08:38 flask-yellow last message repeated 24 times

Argh.  Detail logging is good, but pointless noise is not.  The solution isn't very well documented but you can bring this to a stop. 

Step 1.) Make sure you have net-snmp 5.3.2.2 or later.  This should not be a problem as even RHEL5/CentOS5 provide this version via update.

    $ rpm -q net-snmp
    net-snmp-5.3.2.2-9.el5_5.1

Step 2.) Edit /etc/sysconfig/snmpd.options or your system's equivalent making sure you do not pass the "-a" option to the SNMP daemon.  The "-a" option enables the logging of the source IP addresses of all incoming requests.  If you want to know about these kind of events iptables and ulog are more reliable and efficient methods for capturing that information.

    # OPTIONS="-Lsd -Lf /dev/null -p /var/run/snmpd.pid -a"
    OPTIONS="-Lsd -Lf /dev/null -p /var/run/snmpd.pid"

Step 3.) Edit the /etc/snmpd/snmpd.conf verifying you have the dontLogTCPWrappersConnects directive set to 1 (true).  Add this directive to the configuration file if it is not specified.
Step 4.) Restart the SNMP service.

Now when you go to look into the log files it is again possible to hear the breeze, the singing of the birds, and the distant growling of that guy from Kazakstan who is trying to crack your SSH daemon.

Automated Backup of IOS Router Configuration

Who hasn't had the experience of remoting to a router and making a configuration change... and not saving that change. Inevitably that is the weekend that facility will experience a power outage long enough to deplete their UPS. And then you get that dreaded text message from NetOps that a facility is down. Argh! Fortunately Cisco IOS 12.x and later supports a cron like service known as "kron". One of the handiest uses for kron is to configure automatic backup of the router's configuration to a TFTP server.

kron occurrence backup at 0:00 Thu recurring
 policy-list backup
!
kron policy-list backup
 cli write
 cli show running-config | redirect tftp://192.168.1.38/brtgate.config
!

This creates a batch of commands named "backup" [where in typical ISO fashion everything is referred to as a "policy"] that will be executed every Thursday morning. This batch commits the running configuration to flash memory ["cli write"] and copies the running configuration to the specified TFTP server ["cli show running-config | redirect tftp://192.168.1.38/brtgate.config"]. The rather odd looking use of "redirect" is because the IOS "copy" command is interactive and interactive commands cannot be run via "kron".

Remember that the file on the TFTP server has to exist, even if zero sized, and be world writable; otherwise the redirect will fail with a permission denied error.

block_dump logging

There are lots of tools for studying the systems use of CPU and memory, but I/O is generally harder to track down.  A useful trick is available via the block dump.  Setting the value to "1" turns on block access logging to the kernel ring-buffer [aka dmesg] and a value of "0" turns it back on.  This means it can be turned on by a simple:

echo "1" > /proc/sys/vm/block_dump

This logs the accesses to the block storage as:

[ 2032.934178] postmaster(11528): READ block 5058592 on dm-3 (16 sectors)
[ 2032.934200] postmaster(11528): READ block 5058624 on dm-3 (32 sectors)
[ 2032.934240] postmaster(11528): READ block 3172800 on dm-3 (16 sectors)
[ 2032.945328] banshee-1(11267): dirtied inode 1051864 (banshee.db-journal) on dm-0
[ 2032.945336] banshee-1(11267): dirtied inode 1051864 (banshee.db-journal) on dm-0
[ 2033.042671] python(11518): READ block 9017928 on dm-2 (32 sectors)
[ 2033.055771] python(11518): dirtied inode 267260 (expatbuilder.pyc) on dm-2
[ 2033.055808] python(11518): READ block 9017960 on dm-2 (40 sectors)
[ 2033.412972] nautilus(11078): dirtied inode 410492 (dav:host=127.0.0.1,port=8080,ssl=false) on dm-0
[ 2033.413001] nautilus(11078): READ block 50855560 on dm-0 (40 sectors)
[ 2033.431011] nautilus(11078): dirtied inode 410596 (dav:host=127.0.0.1,port=8080,ssl=false-ab9de673.log) on dm-0
[ 2033.431044] nautilus(11078): READ block 50855736 on dm-0 (64 sectors)
[ 2034.221831] jbd2/dm-2-8(386): WRITE block 21261800 on dm-2 (8 sectors)
[ 2034.221887] jbd2/dm-2-8(386): WRITE block 21261808 on dm-2 (8 sectors)

 Handy.

Manually Adding an ACL To An Object

In OpenGroupware the ACLs applied to an object are stored in the "object_acl" table.  If, for example, I want to add the list, view, read, write, and administer privileges for the team 11,530 for object 1,6829,810 the correct SQL to execute is:

INSERT INTO object_acl
  (object_acl_id, sort_key, action, object_id, auth_id, permissions)
VALUES (nextval('key_generator'), 0, 'allowed', 16829810, 11530, 'lvrwa')

The important points are:

1. Use the "key_generator" sequence to assign the "object_acl_id" value.  This is the object id of the ACL itself;  all object ids are assigned from the key_generator sequence.
2. The value of "sort_key" is always 0.  This value isn't actually used for anything.
3. The value of "action" must be either "allowed" or "denied".  In most cases "allowed" is what you want in order to grant access.
4. "object_id" is the object id of the object to which the ACL is applied in contrast to "auth_id" is the context to which the privileges, specified in "permissions", are either granted [if "action" is "allowed"] or revoked [if "action" is "denied"].  The value of "auth_id" should be the object id of an account or a team.
5. The permissions string is always lower case.  Permission flags are documented in WMOGAG.

The ACLs in "object_acl" are the primary access control mechanism for all entities excepting Projects and Appointments.

Bootstrapping "opengoupware.us"

Thanks to the addition of the "pages" feature in Blogspot I've finally created a site at opengroupware.us, The OpenGroupware [Legacy] project website (I won't even bother to link to it) has been worthless for some time, and the information about the constellation of projects beyond legacy is very scattered. opengroupware.us is an attempt to at least create an index of that information as well as resurrect some of the good content from the abyss that is the docs plone.  If you want to submit content to opengroupware.us or want to help edit / maintain content just let me know any I'll add your Blogspot account to the site permissions.

Coils: Features merged into "default"

This morning the following features were merged into OpenGroupware Coils "default" branch, and will be available in the next release.

•  The scheduler service no longer depends on the ticktock heartbeat.  It checks the run queue in its work method.
• Format(s) now support logging of rejected records to a buffer.  This feature is available via a new parameter on the readAction: "rejectionsLabel".  This is documented in the readAction's wiki page.
•  The above motivated me to abstract how ActionCommand goes about creating [output] messages.  It is now simple for an action to create additional messages [beyond it's 'default' output message] by just calling the store_in_message(label, wfile, mimetype='application/octet-stream') method.
• New CLI tools: "coils-list-schedule" and "coils-unschedule-process" that let the administrator view and modify the workflow scheduler from the command line.  Yes, we still need a "coils-schedule-process". 
• getopt isn't the greatest command line option parser,  would be great if someone would look into swapping that out with something better.
• The above motivated me to add an initialize_tool(name, argv, arguments=['',[]]) function to coils.core.utility.  This provides a simple way for a tool [vs. a service] to bootstrap itself onto the OpenGroupware Coils service bus so that it can receive callbacks, etc... Return value is an AdministrativeContext (with a registered Broker for IPC) and dictionary of unconsumed command line parameters. initialize_tool automatically consumes the --store=, --add-bundle=, and --ban-bundle parameters in order to initialize the Coils environment.

In the last week or so there has been significant work on tidying things up, and the deployment instructions on the wiki have been updated as well.  Getting a working OpenGroupware Coils instance should now be very straight-forward.   Big work ahead is the move to WSGI which is underway in the NetComm branch (which needs to be remerged with default, btw).  And with the impending release of openSUSE 11.3, and its inclusion of Evolution 1.30.x,  the GroupDAV address book support can be completely tested and polished.  Evolution 1.30.x contains several enhancements to make it's WebDAV address book support fully compatible with GroupDAV.

Keeping an OIE Route After Completion

Once an OpenGroupware Integration Engine route has successfully completed it is automatically garbage collected.  This prevents the system from filling up with messages and version information.  But if an external application, like ogo-curses-putter, wants to retrieve the output of the route... it's gone!  To facilitate this use-case the garbage collection can be suppressed per-route by setting the {http://www.opengroupware.us/oie}preserveAfterCompletion property of the route entity with a value of "YES".  If this property value exists the processes derived from that route will not be garbage collected.  The simplest way to set the property is to retrieve the route via "route::get" and use your context's property manager:

from coils.core import *
ctx = AdministrativeContext()
r = ctx.run_command('route::get', name='MTAStockOrder_TEST')
ctx.property_manager.set_property(r, 'http://www.opengroupware.us/oie', 'preserveAfterCompletion', 'YES')
ctx.commit() 

The above will set the value of {http://www.opengroupware.us/oie}preserveAfterCompletion to "YES" for the route named "MTAStockOrder_TEST". The property manager will update the value of the property if such a property already exists, or it will create a new property with the specified value.

Performing PROPFIND from Python

Surprising, when I went looking for examples of performing PROPFIND requests from Python I found very little information. For testing OpenGroupware Coil's WebDAV presentation I needed a simple way to perform the PROPFIND required to list the entities in a collection and then request each entity. With a bit of scratching through the thin documentation I was able to come up with the following:

1. import httplib, urllib2, base64, sys
2. from lxml import etree
4. PROPFIND = u'''<?xml version="1.0" encoding="utf-8"?>
5. <propfind xmlns="DAV:">
6. <prop>
7. <getetag/>
8. </prop>
9. </propfind>'''
11. SERVER_HOST = '127.0.0.1'
12. SERVER_PORT = 8080
13. SERVER_PATH = '/dav/Contacts'
14. CLIENT_AGENT = 'Whitemice rules/so very true'
16. auth_string = 'Basic {0}'.format(base64.encodestring('adam:fred123')[:-1])
18. urllib2.install_opener(urllib2.build_opener(urllib2.HTTPHandler()))
20. connection = httplib.HTTPConnection(SERVER_HOST, SERVER_PORT)
21. connection.putrequest('PROPFIND', SERVER_PATH)
22. connection.putheader('Authorization', auth_string)
23. connection.putheader('User-Agent', CLIENT_AGENT)
24. connection.putheader('Depth', 1)
25. connection.putheader('Content-Length', str(len(PROPFIND)))
26. connection.endheaders()
27. connection.send(PROPFIND)
28. response = connection.getresponse()
29. if response.status == 207:
30.   data = response.read()
31.   connection.close()
32.   response = None
33.   namespace_prefix_map = { 'D' : 'DAV:' }
34.   document = etree.fromstring(data)
35.   for path in document.xpath('/D:multistatus/D:response/D:href/text()',
36.       namespaces=namespace_prefix_map):
37.     if (path != SERVER_PATH):
38.       connection = httplib.HTTPConnection('127.0.0.1', 8080)
39.       connection.putrequest('GET', path)
40.       connection.putheader('Authorization', auth_string)
41.       connection.putheader('User-Agent', CLIENT_AGENT)
42.       connection.endheaders()
43.       response = connection.getresponse()
44.       if response.status != 200:
45.         print 'Error retrieving {0}'.format(path)
46.         sys.exit(1)

This will make the PROPFIND for all the items in the collection, requesting the getetag property, and then from the response perform an HTTP GET for every item. Even better would be if it requested isCollection and knew better than to perform GETs on collections.  I hope this simple example will be of use to someone.

Disabling GNOME Automount

When you connect a mass storage device to a computer running the GNOME desktop environment it automatically mounts the device in /media and places a short-cut to the device on your desktop.  This is an excellent default behavior;  but if you are working with various devices sometimes it can get in the way.  To correctly disable this feature simply execute:

gconftool-2 --type bool --set /apps/nautilus/preferences/media_automount false

The devices will still appear in computer:/// (in nautilus) where you can right click on them to select the mount action if and when you want them to be mounted.  If and when you want to re-enable to auto-mounting feature execute:

gconftool-2 --type bool --set /apps/nautilus/preferences/media_automount true