Project

General

Profile

custom auth SQL

Pete Fuller
Added over 5 years ago

We have a web app that stores users in a postgres database. The database contains a users table with username and password rows. I'm attempting to access these rows from the custom auth connector in Tigase and am having a hard time finding the correct plain SQL syntax to call these. The goal would be to authenticate any of these users by passing the bare JID and matching the plain password. If anyone has that syntax, or can point me at some additional documentation that explains how to make the java class calls work, it would be greatly appreciated.

Thanks

Pete


Replies (6)

Added by Wojciech Kapcia TigaseTeam over 5 years ago

As per Tigase Custom Auth connector

Below query performs user authentication on the database level.

The Tigase server does not need to know authentication algorithm or password

encoding type, it simply passes user id (BareJID) and password in form

which was received from the client, to the stored procedure. If the

authentication was successful the procedure returns user bare JID or null otherwise.

The Tigase checks whether the JID returned from the query matches

JID passed as a parameter. If they match, the authentication is successful.

basic-conf/auth-repo-params/user-login-query={ call TigUserLoginPlainPw(?, ?) }

eg.

basic-conf/auth-repo-params/user-login-query={ select user_id from tig_users where (user_id = ?) AND (user_pw = ?) }

Added by Pete Fuller over 5 years ago

EDIT: --I commented out the user-db-uri and user-db portions of the init.properties file and was successful.

I rewrote my query, but watching my db server logs, it seems the existing user store is not being queried, only the tigase database. Below is my init.properties file, passwords redacted. Is there an order to where the two databases must be called?

Thanks

Pete

--user-db = pgsql
--admins = admin@jabber.geotrax.net
--user-db-uri = jdbc:postgresql://data22.atl.3si/tigasedb?user=tigase_user&password=xxxxx
config-type = --gen-config-def
--virt-hosts = jabber.geotrax.net
--debug = server
--auth-db = tigase-custom
--auth-db-uri = jdbc:postgresql://data22.atl.3si/tracks?user=tigase_user&password=xxxxx&autoCreateUser=true

# This query is used to check connection to the database, whether it is still alive or not
basic-conf/auth-repo-params/conn-valid-query=select 1

# This is database initialization query, normally we do not use it, especially in
# clustered environment
basic-conf/auth-repo-params/init-db-query=update tig_users set online_status = 0

# Below query performs user authentication on the database level.
# The Tigase server does not need to know authentication algorithm or password
# encoding type, it simply passes user id (BareJID) and password in form
# which was received from the client, to the stored procedure. If the
# authentication was successful the procedure returns user bare JID or null otherwise.
# The Tigase checks whether the JID returned from the query matches
# JID passed as a parameter. If they match, the authentication is successful.
#basic-conf/auth-repo-params/user-login-query={ call TigUserLoginPlainPw(?, ?) }
basic-conf/auth-repo-params/user-login-query= {select username from users where  (username = ?) and ( password = ?) }


# Below query returns number of user accounts in the database, this is mainly used
# for the server metrics and monitoring components.
basic-conf/auth-repo-params/users-count-query={ call TigAllUsersCount() }

# Below query is used to add a new user account to the database
basic-conf/auth-repo-params/add-user-query={ call TigAddUserPlainPw(?, ?) }

# Below query is used to remove existing account with all user's data from the database
basic-conf/auth-repo-params/del-user-query={ call TigRemoveUser(?) }

# This query is used for the user authentication if "user-login-query" is not defined,
# that is if there is no database level user authentication algorithm available. In such
# a case the Tigase server loads user's password from the database and compares it
# with data received from the client.
basic-conf/auth-repo-params/get-password-query=select user_pw from tig_users where user_id = ?

# Below query is used for user password update in case user decides to change his password
basic-conf/auth-repo-params/update-password-query=update tig_users set user_pw = ? where user_id = ?

# Below query is called on user logout event. Usually we use a stored procedure which
# records user logout time and marks user as offline in the database
basic-conf/auth-repo-params/user-logout-query=update tig_users, set online_status = online_status - 1 where user_id = ?

# This is configuration setting to specify what non-sasl authentication mechanisms
# expose to the client
#basic-conf/auth-repo-params/non-sasl-mechs=password,digest

# This is configuration setting to specify what sasl authentication mechanisms expose to the client
basic-conf/auth-repo-params/sasl-mechs=PLAIN,DIGEST-MD5
Avatar?id=6023&size=32x32

Added by Artur Hefczyc TigaseTeam over 5 years ago

One mistake you have in your configuration is that the part of the DB URI:

&autoCreateUser=true

should be attached to --user-db-uri not to the --auth-db-uri. But this is not a big issue anyway.

Please stop your server, remove all logs and restart it. Then look in the logs/tigase.log.0 file for an entry like this:

ConfiguratorAbstract.addAuthRepo()  INFO:    [DEFAULT] Initialized tigase-auth as user auth repository pool: 10, url: jdbc:postgresql://data22.atl.3si/tracks?user=tigase_user&password=xxxxx

Make sure the URI points to the correct DB.

Added by Slava Bendersky over 5 years ago

Small question, this query user-logout-query give me exception in mysql.

basic-conf/auth-repo-params/user-logout-query = update tig_users, set online_status = online_status - 1 where user_id = ?
2013-12-07 18:27:29.434 [session-close Queue Worker 3]  SessionManager.closeSession()  WARNING: Exception closing session... 
tigase.db.TigaseDBException: Problem accessing repository.
    at tigase.db.jdbc.TigaseCustomAuth.logout(TigaseCustomAuth.java:626)
    at tigase.db.AuthRepositoryMDImpl.logout(AuthRepositoryMDImpl.java:223)
    at tigase.server.xmppsession.SessionManager.closeSession(SessionManager.java:1271)
    at tigase.cluster.SessionManagerClustered.closeSession(SessionManagerClustered.java:654)
    at tigase.server.xmppsession.SessionManager.closeConnection(SessionManager.java:1149)
    at tigase.server.xmppsession.SessionManager$SessionCloseProc.process(SessionManager.java:2653)
    at tigase.server.xmppsession.SessionManager$ProcessorWorkerThread.process(SessionManager.java:2571)
    at tigase.util.WorkerThread.run(WorkerThread.java:132)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set online_status = online_status - 1 where user_id = 'user@mydomain.com'' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
    at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1362)
    at tigase.db.jdbc.TigaseCustomAuth.logout(TigaseCustomAuth.java:622)
    at tigase.db.AuthRepositoryMDImpl.logout(AuthRepositoryMDImpl.java:223)
    at tigase.server.xmppsession.SessionManager.closeSession(SessionManager.java:1271)
    at tigase.cluster.SessionManagerClustered.closeSession(SessionManagerClustered.java:654)
    at tigase.server.xmppsession.SessionManager.closeConnection(SessionManager.java:1149)
    at tigase.server.xmppsession.SessionManager$SessionCloseProc.process(SessionManager.java:2653)
    at tigase.server.xmppsession.SessionManager$ProcessorWorkerThread.process(SessionManager.java:2571)
    at tigase.util.WorkerThread.run(WorkerThread.java:132)

Added by Wojciech Kapcia TigaseTeam over 5 years ago

As the exceptions says You have an error in your SQL syntax, please remove colon from your query, following should work:

basic-conf/auth-repo-params/user-logout-query = update tig_users set online_status = online_status - 1 where user_id = ?

Added by Slava Bendersky over 5 years ago

Thanks it worked.

    (1-6/6)