Custom database groups integration bug

Openfire Openfire Support
1

Introduction

I am trying to integrate my custom users/groups database with Openfire, as per the instructions here…

The authentication and user integrations were very easy to get working. Now, I’m running into a problem trying to get groups working as well. The groups are being partially exported, but they are not working correctly in the Openfire admin GUI.

To better illustrate the issue, I have come up with a reproducible that anyone can easily test using a file based HSQLDB instance as the custom database. Here are the repo steps:

Repo steps

  1. Download the custom group-db database and extract to a temp dir. On my Windows install, I use C:\TEMP. This should result in the files being placed in C:\TEMP\group-db.

  2. Download the openfire.xml file with the queries for the custom database providers. Place this in your Openfire home -> conf directory. Make sure to backup your existing openfire.xml file.

  3. If you placed the group-db database files in a directory other than C:\TEMP, edit the openfire.xml and replace the file path in the HSQLDB with your own:

a. jdbc:hsqldb:file:c:/temp/group-db/test;user=sa

b. The test is the first part of the test.properties file name.

c. Example, if you placed it in Windows default Openfire dir, it would be jdbc:hsqldb:file:c:/progra~1/openfire/group-db/test;user=sa

  1. Start Openfire

  2. Login to the admin GUI as *user1/password*.

  3. Go to Users/Groups. There should be two users there, user1 and user2. Their user

properties pages lists their groups as “None”.

  1. Go to Users/Groups -> Group Summary. There should be a group there called group1 with user1 and user2 as members.

Here are some screenshots from my install of what you should see:

Group summary view

Group detail view

User detail view

Summary

The basic problem is that while the information is partially listed in the admin GUI, it is not consistent. Ie, the groups list these users, but the users do not list the groups they are in. When logged in via the Spark client, users cannot see other users in their group, even if the group is made shared.

If I create this data manually in the admin GUI without using the custom database integration, the data is consistent between the user properties pages and the group pages, and the Spark client sees the other users in the shared group.

There are no exceptions in the error.log.

2

Forgot to mention I’'m running Openfire 3.3.1 with the Enterprise plugin on Windows 2003 server.

3

One the surface, my issues appears identical to this one:

http://www.igniterealtime.org/forum/thread.jspa?messageID=144914&#144914

4

Yes, that’'s a great bug!

5

Hi,

I did create JM-1080 to track this issue.

LG

6

I think I have tracked down the issues to bugs in JDBCGroupProvider.

  1. getGroupNames(JID user) is using a JID to exectute the userGroupsSQL query versus the username. I believe it should be username, as that is what the JDBCUserProvider returns.

  2. getMembers(String groupName, boolean adminsOnly) is creating a JID using new JID() versus server.createJID(user, null). The former results in a non-JID being returned to the client (Spark).

Here is the diff for my proposed changes:

Index: JDBCGroupProvider.java

===================================================================

— JDBCGroupProvider.java (revision 8470)

+++ JDBCGroupProvider.java (working copy)

@@ -13,6 +13,7 @@

import org.jivesoftware.database.DbConnectionManager;

import org.jivesoftware.util.JiveGlobals;

import org.jivesoftware.util.Log;

+import org.jivesoftware.openfire.XMPPServer;

import org.xmpp.packet.JID;

import java.sql.DriverManager;

@@ -73,6 +74,8 @@

private String loadMembersSQL;

private String loadAdminsSQL;

  • private XMPPServer server = XMPPServer.getInstance();

/**

  • Constructor of the JDBCGroupProvider class.

*/

@@ -168,7 +171,7 @@

while (rs.next()) {

String user = rs.getString(1);

if (user != null) {

  •                JID userJID = new JID(user);

  •                JID userJID = server.createJID(user, null);

members.add(userJID);

}

}

@@ -282,7 +285,7 @@

try {

con = DriverManager.getConnection(connectionString);

pstmt = con.prepareStatement(userGroupsSQL);

  •        pstmt.setString(1, user.toString());

  •        pstmt.setString(1, server.isLocal(user) ? user.getNode() : user.toString());

rs = pstmt.executeQuery();

while (rs.next()) {

groupNames.add(rs.getString(1));

7

Tried to do pre-formatting. n/t

Message was edited by: cseibert

8

Ok, I managed to get this to work with a little change to my SQL SELECT statements…

I made a table in my CUSTOM DB called “jiveGroup” which was an exact copy of the jiveGroup table in Openfire DB. The usernames are stored in a table called “default_en_userdb”. All I did was manually add the "@mydomain.com" to the query results using CONCAT. This made the groups for great in Spark and the Admin Console. The only thing that isn’'t working right now is the contact list group sharing, but at least the custom DB groups, users and auth are working.

Jamie