I would like some help… I feel like I’m almost there, just need that final push…
I would like to run Openfire on its default DB ‘chat’ while running authorization thru an existing userbase. I’m getting close, but cannot authenticate, and a lot seems counter-intuitive to me.
My external DB uses unique email addresses as username to authenticate. In my Mysql.log, that query never gets run, altho the Openfire log shows a failed authentication attempt using the email address. When run with a non-email address as username, the query shows up in the Mysql.log. That seems weird.
Here is my openfire.xml:
<?xml version="1.0" encoding="UTF-8"?> <!--
This file stores bootstrap properties needed by Openfire.
Property names must be in the format: "prop.name.is.blah=value"
That will be stored as:
<prop>
<name>
<is>
<blah>value</blah> </is>
</name>
</prop> Most properties are stored in the Openfire database. A
property viewer and editor is included in the admin console.
-->
<!-- root element, all properties must be under this element -->
<jive> <adminConsole> <!-- Disable either port by setting the value to -1 --> <port>9090</port> <securePort>9091</securePort> </adminConsole> <locale>en</locale> <!-- Network settings. By default, Openfire will bind to all network interfaces.
Alternatively, you can specify a specific network interfaces that the server
will listen on. For example, 127.0.0.1. This setting is generally only useful
on multi-homed servers. --> <!--
<network>
<interface></interface>
</network>
--> <connectionProvider> <className>org.jivesoftware.database.DefaultConnectionProvider</className> </connectionProvider> <database> <defaultProvider> <driver>com.mysql.jdbc.Driver</driver> <serverURL>jdbc:mysql://myDomain.com:3306/userDB?useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8</serverURL> <username>openfire</username> <password>openfire</password> <testSQL>select 1</testSQL> <testBeforeUse>true</testBeforeUse> <testAfterUse>true</testAfterUse> <minConnections>5</minConnections> <maxConnections>25</maxConnections> <connectionTimeout>1.0</connectionTimeout> </defaultProvider> </database> <setup>true</setup> <log> <debug> <enabled>true</enabled> </debug> </log> </jive>
Here are my relevant ofProperty records (some info is replaced for security):
jdbcAuthProvider.passwordSQL SELECT password from users where user_name=?
jdbcAuthProvider.passwordType md5
jdbcProvider.driver com.mysql.jdbc.Driver
jdbcProvider.connectionString jdbc:mysql://myDomain.com:3306/userDB?user=openfire&password=openfire
jdbcUserProvider.loadUserSQL SELECT * FROM users where email=?
jdbcUserProvider.userCountSQL SELECT COUNT(*) FROM users
jdbcUserProvider.allUsersSQL SELECT user_name FROM users
jdbcUserProvider.searchSQL SELECT user_name FROM users WHERE
jdbcUserProvider.usernameField user_name
jdbcUserProvider.nameField user_name
jdbcUserProvider.emailField email
jdbcUserProvider.useConnectionProvider true
Leave your openfire.xml config file configured to access the openfire database (you have it pointing to userDB - I don’t think that’s what you intended).
What you want to do is configure system properties to authenticate against another database. You need to set the following properties:
REPLACE INTO `ofProperty` (`name`, `propValue`) VALUES
('jdbcProvider.driver', 'com.mysql.jdbc.Driver'),
('jdbcProvider.connectionString', 'jdbc:mysql://server/userDB?user=username&password=secret'),
('jdbcAuthProvider.passwordSQL', 'SELECT PASSWORD FROM user_table WHERE username=?'),
('jdbcAuthProvider.passwordType', 'md5'),
('provider.auth.className', 'org.jivesoftware.openfire.auth.JDBCAuthProvider');
This tells openfire to use an external auth provider (provider.auth.className), which resides on “server”, and the database name is “userDB”. Then it tells openfire to look into the table “user_table” and get the “password” field where “username” matches the username openfire is giving it, and match the password against an md5 hash of the user’s input for the password.
A few things to note:
The password type is set to md5. Make sure that the password hash that you store in your user table matches the one that openfire generates.
Openfire only uses the username part of the JID to authenticate (and everything else; ). So if the “username” field in your db is joyous@myDomain.com, it won’t work because openfire is only passing “joyous” to the DB query. I don’t think that joyous@myDomain.com@myDomain.com is a valid JID, so entering the full JID/email as the username will probably not work either.
If you need to do custom authorizing, you can extend the JDBCAuthProvider class to support whatever custom ‘passwordType’ you need. It’s pretty straightforward code.
Thank you for helping me! It works. I had to remove a bunch of entries in ofProperty that referred to both jdbcAuthProvider and jdbcUserProvider before running the sql.
However, I would like jdbcUserProvider to work as well as jdbcGroupProvider. I would really like to have a single userbase, if possible. Is that possible?
I used SQL format here because i’m just taking code from one of my bootstrap scripts. You can also change the values from the web UI directly.
However, I would like jdbcUserProvider to work as well as jdbcGroupProvider. I would really like to have a single userbase, if possible. Is that possible?
I’ve never used jdbcGroupProvider, but for user listing/search set the following properties:
('jdbcUserProvider.loadUserSQL', 'SELECT first_name,email FROM auth_user WHERE username=?'),
('jdbcUserProvider.userCountSQL', 'SELECT COUNT(*) FROM auth_user'),
('jdbcUserProvider.allUsersSQL', 'SELECT username FROM auth_user'),
('jdbcUserProvider.searchSQL', 'SELECT username FROM auth_user WHERE'),
('jdbcUserProvider.usernameField', 'username'),
('jdbcUserProvider.nameField', 'first_name'),
('jdbcUserProvider.emailField', 'email'),
('provider.user.className', 'org.jivesoftware.openfire.user.JDBCUserProvider'),
Just change the DB table and column names to match your schema.
Yes, I definitely tried that page’s instructions, in many different ways, backwards and forwards. Just my luck!
It seems like there is some disagreement between whether to 1. add property key/values to the config, or 2. only to the DB, or 3. to both, depending on the Openfire version.
Yes, I definitely tried that page’s instructions, in many different ways, backwards and forwards. Just my luck!
Yeah, that page is not very clear on what settings accomplish what. There was a lot of trial an error.
Now that I look at it, it’s out of date as well because it tells you to edit openfire.xml. Don’t do that. Starting with version 3.6.1 (I think, could be wrong), they put all of the properties into the database. So that openfire.xml only serves to tell OF how to connect to the DB (i.e. where to read the rest of the configuration paramenters). So just put the configuration paremeters in the DB.
I just run the SQL that I sent you and it works well (but I started out changing params in the web UI). Make sure to restart OF after you make the SQL changes.
How can I set Openfire to load vcards from a specific database? I mean what are those property settings.
How can I manipulate user’s roaster? Suppose that I want to add user B to user A’s roaster. How can I do that? Is it possible to set Openfire to load roasters from an external DB as well? So that I can write to that DB and openfire just loads them? Or is there any kind of API or something that I can manipulate the roasters with that?
How can I set Openfire to load vcards from a specific database? I mean what are those property settings.
Sorry, I have no idea. I never had a need to work with vcards.
How can I manipulate user’s roaster? Suppose that I want to add user B to user A’s roaster. How can I do that? Is it possible to set Openfire to load roasters from an external DB as well? So that I can write to that DB and openfire just loads them? Or is there any kind of API or something that I can manipulate the roasters with that?
This is something that I had to deal with. Unfortunaly, there is no (documented) method for OF to read the rosters from an external DB like it does with user information. Short of writing a custom plugin, my workaround for this is to write directly to openfire’s DB and disable the roster cache so that those changes get reflected immediately. Note that you will not get XMPP roster subscription events in this case (but you’ll start getting presence upates, etc.). To disable the roster cache, set the following property:
cache.username2roster.size: 0
Here’s is a link to good documentation on the OF roster table (look at the botom):
The other alternative that I’ve seen mentioned is to use roster groups, which may or may not work. I stayed away from this approach becuse it seemed to be specific to OpenFire, and I wanted to stay XMPP Server agnostic (except when I really coudn’t).
). So if the “username” field in your db is