Objective:
Configure a jabber server which authenticates against an existing userbase and "just works" when a user tries to log to the jabber server in using their credentials.

Projects utilized:
JabberD 2.0.3
PostgreSQL 7.4.7

Outcome:
Success, while I could have reached the objective using my existing jabberd 1.4.3 setup, I opted instead to upgrade to jabberd2. My reasoning for this was mostly whim, 1) I couldn't find a stand-alone copy of xdb_sql because of some issues with a server being hacked, and the author not re-verifying the code. I could have tried stripping it from the 1.4.4 distribution, but I wasn't sure if it was complete and I couldn't find much in the way of documentation about it. 2) I have no prior experience with jabberd2 and wanted it.

jabberd2 includes database backend support out of the box, there is the advantage that all data is now stored in the database where they were in xdb_file before. This was an unexpect, but welcome surprise to me; I had planned at exploring options for relational database storage of the roster and other data after I finished this project.

Steps:
First of all, I built the jabberd2 port on freebsd using the WITH_POSTGRESQL option. Next I followed the instructions here to get the server setup and authenticating against a brand new database. Once I was able to observe the data the server created, I was able to determine that only the authreg table needed to be in my current app's database. So I went to town looking for how to make my custom queries like xdb_sql would allow in jabber1.4, but there was no documentation of the sort for jabberd2. Finally after some googling I found this thread which explained that the sm module is basically set in stone, but the c2s module is as flexible as xdb_sql. After some tinkering, I found that this functionality was indeed possible, though undocumented. In the end, I changed c2s to use my existing database with these settings:

<pgsql>
    <!-- Database server host and port -->
    <host>localhost</host>
    <port>5432</port>

    <!-- Database name -->
    <dbname>mint2</dbname>

    <!-- Database username and password -->
    <user>xxxxxx</user>
    <pass>xxxxxx</pass>

    <table>users</table>

    <sql>
      <!-- use 32 as a flag for disabled jabber account -->
      <select>SELECT "password" FROM "users" WHERE "username" = '%s' AND "realm" = '%s' AND (status & 32) = 0</select>
      <delete>UPDATE "users" SET status = status | 32  WHERE "username" = '%s' AND "realm" = '%s'</delete>
    </sql>
  </pgsql>

I was also required to enable <auto-create/> in the sm module for the active table. An alternative to this would be to create an entry in my registration script OR to setup another handler for the active data. The latter would be the best option IMO, but for now auto-create will more than suffice.