Custom Database Integration with Kohana

[David_van_der_Tuijn]

Hey,

I am using the Kohana PHP framework for my website and i would to add authentication from users in an external database.

I read the Custom Database Integration Guide : http://www.igniterealtime.org/builds/openfire/docs/latest/documentation/db-integ ration-guide.html

My openfire database is called ‘openfire’ and my website database is called ‘kohana’.

When i login to the Openfire Admin Console the users from the kohana database are NOT shown in the User Summary or Group Summary.

I think i’ve made a mistake in my config file, when i remove defaultProvider it seems Openfire is installing the new tables in the database ‘kohana’ wich i do not preffer.

/opt/openfire/conf/openfire.conf:

<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>myipaddress</interface>
     </network>
     <connectionProvider>
          <className>org.jivesoftware.database.DefaultConnectionProvider</className>
     </connectionProvider>
     <database>
          <defaultProvider>
               <driver>com.mysql.jdbc.Driver</driver>
               <serverURL>jdbc:mysql://localhost:3306/openfire</serverURL>
               <username>openfire</username>
               <password>mypassword</password>
               <testSQL>select 1</testSQL>
               <testBeforeUse>true</testBeforeUse>
               <testAfterUse>true</testAfterUse>
               <minConnections>5</minConnections>
               <maxConnections>25</maxConnections>
               <connectionTimeout>1.0</connectionTimeout>
          </defaultProvider>
     </database>
     <jdbcProvider>
          <driver>com.mysql.jdbc.Driver</driver>
          <connectionString>jdbc:mysql://localhost/kohana?user=kohana&amp;password=mypassword</connectionString>
     </jdbcProvider>
     <provider>
          <auth>
               <className>org.jivesoftware.openfire.auth.JDBCAuthProvider</className>
          </auth>
          <user>
               <className>org.jivesoftware.openfire.user.JDBCUserProvider</className>
          </user>
          <group>
               <className>org.jivesoftware.openfire.group.JDBCGroupProvider</className>
          </group>
     </provider>
     <jdbcAuthProvider>
          <passwordSQL>SELECT password FROM users WHERE username=?</passwordSQL>
          <passwordType>sha256</passwordType>
     </jdbcAuthProvider>
     <jdbcUserProvider>
          <loadUserSQL>SELECT firstname,email FROM users WHERE status = 5 AND username=?</loadUserSQL>
          <userCountSQL>SELECT COUNT(*) FROM users WHERE status = 5</userCountSQL>
          <allUsersSQL>SELECT username FROM users</allUsersSQL>
          <searchSQL>SELECT username FROM users WHERE status = 5 AND</searchSQL>
          <usernameField>username</usernameField>
          <nameField>firstname</nameField>
          <emailField>email</emailField>
     </jdbcUserProvider>
     <jdbcGroupProvider>
          <groupCountSQL>SELECT count(*) FROM roles</groupCountSQL>
          <allGroupsSQL>SELECT name FROM roles</allGroupsSQL>
          <userGroupsSQL>SELECT description FROM roles WHERE id IN (SELECT role_id AS id FROM roles_users INNER JOIN users ON (roles_users.user_id = users.id) WHERE status = 5 AND username=?)</userGroupsSQL>
          <descriptionSQL>SELECT description FROM roles WHERE name=?</descriptionSQL>
          <loadMembersSQL>SELECT username FROM users WHERE status = 5 and id IN (SELECT user_id AS id FROM roles_users INNER JOIN roles ON (roles_users.role_id = roles.id) WHERE roles.name=?)</loadMembersSQL>
          <loadAdminsSQL>SELECT username FROM users WHERE status = 5 and id IN (SELECT user_id AS id FROM roles_users INNER JOIN roles ON (roles_users.role_id = roles.id) WHERE roles.name=?)</loadAdminsSQL>
     </jdbcGroupProvider>
     <setup>true</setup>
</jive>

More information about the users table : https://github.com/kohana/orm/blob/3.2%2Fmaster/auth-schema-mysql.sql

I changed the System Properties:

provider.auth.className

org.jivesoftware.openfire.auth.DefaultConnectionProvider => org.jivesoftware.openfire.auth.JDBCAuthProvider

provider.user.className

org.jivesoftware.openfire.auth.DefaultConnectionProvider => org.jivesoftware.openfire.auth.JDBCUserProvider

Now i can no longer login to the Admin Console

Help appreciated!

CRAP!, the passwords are stored with sha256 using salt that would explain why the login doesnt work.

I could make changes to the Openfire JDBCAuthProvider->authenticate() function, But its been years ago programming JAVA.

Not sure what todo, it will take me a few days i think.

Maybe i’ll just insert users directly into the Openfire Datase with SQL query’s from my website.

Regards,

David van der Tuijn

Message was edited by: David van der Tuijn