Configuring Spacewalk's jabberd to use a PostgreSQL backend

4 March 2017 · How-to · 8 min read

This post details how to switch the database backed used by jabberd on a Spacewalk server to use a PostgreSQL Database so that osa-dispatcher becomes stable.

By default, jabberd when installed for use by Spacewalk uses a Berkeley DB to store session data. This is notoriously flaky which results in osad clients dropping their connection and often requires an administrator to regularly stop the jabberd service, remove the corrupt database and restart it.

The solution is to reconfigure jabberd to use a better database to store its session information.

The following process will switch jabberd to use PostgreSQL instead. It assumes that you already have PostgreSQL up and running as the database backend for Spacewalk itself either locally on the same server as jabberd or remotely on another server.

Create the PostgreSQL user and database

Run the following commands to create a new PostgreSQL user and a new PostgreSQL database with the required jabberd schema in place and assign ownership of the database to the newly created user:

1
2
# sudo su - postgres
$ createuser -P -U postgres jabberd2

You will be prompted twice for a password for the new user.

Once you have provided a password, continue to creating the database:

1
$ createdb -U postgres -O jabberd2 jabberd2

This make take a few moments to complete. You can verify the database was created correctly by running psql -l and checking for the following output:

1
2
3
Name       |   Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+-----------+----------+-------------+-------------+-----------------------
jabberd2   | jabberd2  | UTF8     | en_AU.UTF-8 | en_AU.UTF-8 |

Edit the PostgreSQL configuration to allow connections

By default, PostgreSQL does not allow connections to new databases. You need to modify the pg_hba.conf file to allow connections for the newly created user to access the newly created database.

Open /var/lib/pgsql/data/pg_hba.conf and add the following line:

1
local  jabberd2  jabberd2                   md5

This will allow you to connect to the jabberd2 database as the jabberd2 user from the local machine using the password you provided when you created the user.

If you have a standalone PostgreSQL and need to provide remote connectivity for your jabberd services, you need to add an additional line.

1
host  jabberd2  jabberd2  192.168.1.100/32  md5

This will permit the jabberd2 user to connect to the jabberd2 database from 192.168.1.100. Change the IP address to match your jabberd server.

If you’re using IPv6, you may need to provide another line for the IPv6 address for your jabberd server:

1
host  jabberd2  jabberd2  2001:db8:a0b:12f0::1/128  md5

Once you’ve updated pg_hba.conf, restart PostgreSQL to allow access:

Using service on OL6:

1
# service postgresql restart

Using systemctl on OL7:

1
# systemctl restart postgresql

Import the base schema into the PostgreSQL database

This can either be run locally if jabberd and PostgreSQL are on the same server or remotely from the Spacewalk/jabberd server. You need to run this command on the server that has the db-setup.pgsql file.

Local PostgreSQL instance

1
2
# sudo su - postgres
$ psql -U jabberd2 jabberd2 < /usr/share/jabberd/db-setup.pgsql

Remote PostgreSQL instance

1
$ psql -h postgresql.fqdn -U jabberd2 jabberd2 < /usr/share/jabberd/db-setup.pgsql

Replace postgresql.fqdn with the fully-qualified domain name of your PostgreSQL server.

You will be prompted for the jabberd2 user’s password. Once you provide the password, you should see the following output as the schema is created:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
CREATE SEQUENCE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "authreg_pkey" for table "authreg"
CREATE TABLE
CREATE INDEX
CREATE INDEX
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "active_pkey" for table "active"
CREATE TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "logout_pkey" for table "logout"
CREATE TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "roster-items_pkey" for table "roster-items"
CREATE TABLE
CREATE INDEX
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "roster-groups_pkey" for table "roster-groups"
CREATE TABLE
CREATE INDEX
CREATE INDEX
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "vcard_pkey" for table "vcard"
CREATE TABLE
CREATE TABLE
CREATE INDEX
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "private_pkey" for table "private"
CREATE TABLE
CREATE INDEX
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "motd-message_pkey" for table "motd-message"
CREATE TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "motd-times_pkey" for table "motd-times"
CREATE TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "privacy-default_pkey" for table "privacy-default"
CREATE TABLE
CREATE TABLE
CREATE INDEX
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "vacation-settings_pkey" for table "vacation-settings"
CREATE TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "status_pkey" for table "status"
CREATE TABLE

Stop jabberd and osa-dispatcher

Use service on OL6:

1
2
# service osa-dispatcher stop
# service jabberd stop

Or systemctl on OL7:

1
2
# systemctl stop osa-dispatcher
# systemctl stop jabberd

Edit the jabberd configuration

Local PostgreSQL instance

This configuration is used when PostgreSQL and jabberd are on the same server.

Edit /etc/jabberd/sm.xml with the following changes using the password you provided when creating the jabberd2 user for PostgreSQL:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
--- sm.xml.orig	2017-03-04 07:55:19.162581048 +1100
+++ sm.xml	2017-03-04 07:55:58.615794384 +1100
@@ -91,7 +91,7 @@
     <path>/usr/lib64/jabberd</path>

     <!-- By default, we use the SQLite driver for all storage -->
-    <driver>db</driver>
+    <driver>pgsql</driver>

     <!-- Its also possible to explicitly list alternate drivers for
          specific data types. -->
@@ -188,7 +188,7 @@
       <!-- PostgreSQL connection info.
            For the rest of the options see
            http://www.postgresql.org/docs/8.0/interactive/libpq.html -->
-      <conninfo>dbname=jabberd2 user=jabberd2 password=secret</conninfo>
+      <conninfo>dbname=jabberd2 user=jabberd2 password=Password</conninfo>

       <!-- Alternatively you may set connection settings separately.
            These are used only in absence of 'conninfo' -->

Edit /etc/jabberd/c2s.xml and make the following changes:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
--- c2s.xml.orig	2017-03-04 07:55:25.939274161 +1100
+++ c2s.xml	2017-03-04 07:57:52.642759216 +1100
@@ -386,7 +386,7 @@
     <path>/usr/lib64/jabberd</path>

     <!-- Backend module to use -->
-    <module>db</module>
+    <module>pgsql</module>

     <!-- Available authentication mechanisms -->
     <mechanisms>
@@ -510,7 +510,7 @@
       <!-- PostgreSQL connection info.
            For the rest of the options see
            http://www.postgresql.org/docs/8.0/interactive/libpq.html -->
-      <conninfo>dbname=jabberd2 user=jabberd2 password=secret</conninfo>
+      <conninfo>dbname=jabberd2 user=jabberd2 password=Password</conninfo>

       <!-- Alternatively you may set connection settings separately.
            These are used only in absence of 'conninfo' -->

If your Spacewalk Server has SElinux enabled in ENFORCING mode, you will need to allow the jabberd processes to connect to PostgreSQL via the socket.

To do so, run the following command:

1
# setsebool -P daemons_enable_cluster_mode 1

Remote PostgreSQL instance

This configuration is used when PostgreSQL is on another server.

Edit /etc/jabberd/sm.xml with the following changes using the password you provided when creating the jabberd2 user for PostgreSQL. Remember to change postgresql.fqdn to the real fully-qualified domain name of your PostgreSQL server.

If you want to remove the overhead of hostname resolution, you can replace the host parameter with hostaddr and provide the IP address instead.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
--- sm.xml.orig	2017-03-04 07:55:19.162581048 +1100
+++ sm.xml	2017-03-06 06:33:36.239782112 +1100
@@ -91,7 +91,7 @@
     <path>/usr/lib64/jabberd</path>

     <!-- By default, we use the SQLite driver for all storage -->
-    <driver>db</driver>
+    <driver>pgsql</driver>

     <!-- Its also possible to explicitly list alternate drivers for
          specific data types. -->
@@ -188,7 +188,7 @@
       <!-- PostgreSQL connection info.
            For the rest of the options see
            http://www.postgresql.org/docs/8.0/interactive/libpq.html -->
-      <conninfo>dbname=jabberd2 user=jabberd2 password=secret</conninfo>
+      <conninfo>host=postgresql.fqdn dbname=jabberd2 user=jabberd2 password=Password</conninfo>

       <!-- Alternatively you may set connection settings separately.
            These are used only in absence of 'conninfo' -->

Edit /etc/jabberd/c2s.xml and make the following changes:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
--- c2s.xml.orig	2017-03-04 07:55:25.939274161 +1100
+++ c2s.xml	2017-03-06 06:34:09.870277574 +1100
@@ -386,7 +386,7 @@
     <path>/usr/lib64/jabberd</path>

     <!-- Backend module to use -->
-    <module>db</module>
+    <module>pgsql</module>

     <!-- Available authentication mechanisms -->
     <mechanisms>
@@ -510,7 +510,7 @@
       <!-- PostgreSQL connection info.
            For the rest of the options see
            http://www.postgresql.org/docs/8.0/interactive/libpq.html -->
-      <conninfo>dbname=jabberd2 user=jabberd2 password=secret</conninfo>
+      <conninfo>host=postgresql.fqdn dbname=jabberd2 user=jabberd2 password=Password</conninfo>

       <!-- Alternatively you may set connection settings separately.
            These are used only in absence of 'conninfo' -->

Start jabberd and osa-dispatcher again

Using service on OL6:

1
2
# service jabberd start
# service osa-dispatcher start

Or using systemctl on OL7:

1
2
# systemctl start jabberd
# systemctl start osa-dispatcher

Ensure that PostgreSQL is being used

Check /var/log/messages to make sure that jabberd uses the pgsql storage module.

1
2
3
4
5
6
# cat /var/log/messages | grep pgsql
Mar  4 07:36:36 sirius c2s: Sat Mar  4 07:36:36 2017 [info] loading 'pgsql' authreg module
Mar  4 07:36:36 sirius jabberd/sm[16349]: loading 'pgsql' storage module
Mar  4 07:36:36 sirius jabberd/sm[16349]: initialised storage driver 'pgsql'
Mar  4 07:36:36 sirius c2s: Sat Mar  4 07:36:36 2017 [notice] initialized auth module 'pgsql'
Mar  4 07:36:36 sirius jabberd/c2s[16352]: [spacewalk.domain.com] configured; realm=, authreg=pgsql, registration enabled, using PEM:/etc/pki/spacewalk/jabberd/server.pem

Reset and restart osad

If you have already registered client servers and installed osad on those servers, it will most likely be necessary to remove the osad-auth.conf file and restart osad before they will work after the database has been switched.

Using service on OL6:

1
2
3
# service osad stop
# rm -f /etc/sysconfig/rhn/osad-auth.conf
# service osad start

Using systemctl on OL7:

1
2
3
# systemctl stop osad
# rm -f /etc/sysconfig/rhn/osad-auth.conf
# systemctl start osad

This will trigger the osad client on each server to re-authenticate to jabberd.