tigase-custom: Authenticating against external database with custom SQL queries

3 posts / 0 new
Last post
Anonymous
tigase-custom: Authenticating against external database with custom SQL queries

Hi,

I am trying out Tigase Server to replace our current Ejabberd instance because it seems Tigase offers much more flexibility in authentication against SQL databases.

The situation is as follows: we have a PostgreSQL database for our mail infrastructure, containing various tables, among others a "virtual_users" table that holds e-mail addresses and encrypted/hashed passwords. This mailserver database is the main authentication source for many of the services we run.

Now I want to let the Tigase Server authenticate users against that database. So we have one user DB that is managed by Tigase and one auth DB that is external to Tigase and should be touched only as little as possible.

init.properties thus looks like this:

--user-db=pgsql
--user-db-uri=jdbc:postgresql://tigase-db-server/tigase?user=tigase& \
ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory& \
password=XXXXXXX
--auth-db=tigase-custom
--auth-db-uri=jdbc:postgresql://mail-db-server/mailserver?user=validator& \
ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory& \
password=XXXXXXX&autoCreateUser=true

Note that the user DB is also a PostgreSQL database but distinct from the authentication DB.

For this scenario the "tigase-custom" authenticator seems to be the best choice. I just need to create the correct SQL queries or stored procedures but I am uncertain how the user DB and the auth DB interact and what piece of information is to be queried from where.

I have written a stored procedure for the mailserver database that returns the e-mail address (= JID) of a user if he has submitted the correct password. Passwords are never stored in clear text, only hashed. This SP looks like this (not very pretty but works):


create or replace function
check_user_login(username text, pass text)
returns text as $$
declare
result varchar(255);
begin
select email into result
from virtual_users
where email = username
and password = crypt(pass,
(select password
from virtual_users
where email = username
and password <> '')
);
return result;
end;
$$ language plpgsql;

The details are not really relevant but I use a sub-SELECT here to allow for different formats of the password hashes (DES, MD5, SHA1, salted, unsalted, etc). We took over a legacy database that stored the passwords only CRYPTed but new users and passwords should use a more secure hash algorithm.

So this SP is used as "basic-conf/auth-repo-params/user-login-query" and seems to work.

But what else does Tigase expect me to provide? Which queries are supposed to work against which database?

"init_db_query" obviously talks to the user DB, as does "user-logout-query" I suppose. But "add-user" and "del-user"? And "update-password-query"? Does Tigase store any passwords in its user DB? If so, why? And if not, is "update-password-query" supposed to update the passwords in the main authentication DB?

What about the SASL mechanisms? As far as I can tell SASL is of no interest to me since the authentication is purely SQL-based and all the hashing and encrypting takes place in the auth DB itself.

Please enlighten me :) The tigase-custom-auth-connector guide is useful but unclear on a few crucial details, such as those described above.

What I want to do, in the end, is use Tigase and its user DB to store everything relevant to the user but use my external mailserver database for authentication. There is also no way I can afford to store clear-text passwords anywhere. Can this be done?

Thanks in advance!

PS: Many, if not all, links to the Trac instance on projects.tigase.org return 404, i.e. http://projects.tigase.org/server/trac/browser/trunk/database/postgresql-schema-4.sql

Application: 

That's a lot of questions. Let me just point out one thing before I forget. You have missed one element to the configuration: 'autoCreateUser=true'. Look at the end of this article for explanation and all the details.

--user-db is the database where all the user data is stored, like roster, private data, privacy lists, vcards, etc... This database has to be correctly prepared, that is it needs Tigase schema to be loaded into it.

For your authentication database you should be only required to provide queries you want to use. Unfortunately it is implemented in such a way that it expects all queries to be provided. My suggestion is to put some dummy queries like 'select 1' in place where you don't want to provide the functionality.

All the queries listed in the Tigase Custom Auth connector document refer to authentication database only.

I don't think the init_db_query is called at all right now. There is kind of a problem with this in clustered environment so it has been disabled.

If you use external authentication database then the Tigase does not store user passwords in user DB.

In your case, if you have encoded password in DB you should use only SASL-PLAIN or NON-SASL-PASSWORD mechanisms only.

What you are trying to achieve is pretty common type of deployment and it can certainly be done the way you want it to be. I think you are already quite close to it.

Where are (on what pages) the broken links? I have moved project tracking to a different location. Apparently not all places have been updated yet.

daff

Thank you for the quick reply! It was indeed a lot of questions but I think you managed to answer them comprehensively :) Thanks!

I actually have the autoCreateUser=true parameter set as you can see above but it was set in the auth-db-uri, not the user-db-uri :-/ That seems to have been my biggest problem.

So when setting up authentication against my external database it seems to be sufficient to provide the user-login-query. Does it depend on any other queries?

A follow-up: I would also like to provide the update-password-query, for which I have written another SP that seems to work fine. Does this one need any additional queries set? I also set non-sasl-mechs to "password" and sasl-mechs to "PLAIN". Should that be enough?

I ask because when I try to change the password from Psi all I get is

There was an error when trying to set the password.
Reason: Feature not implemented.The feature requested is not implemented by the recipient or server and therefore cannot be processed.Feature not supported yet.

Is this by design because user DB and auth DB are different?

Last question, because it somewhat fits: do you recommend MySQL or PostgreSQL for the user DB? Meaning: which one do you, as developer, care more about? It is likely that there are more bugs to be found in PostgreSQL-related code if your primary concern is MySQL and vice versa :) I have found a bug or two regarding PostgreSQL, which I will report shortly.

Regarding the dead links:

http://www.tigase.org/content/scripting-introduction-hello-world
http://www.tigase.org/content/tigase-custom-auth-connector
http://www.tigase.org/content/tigase-auth-connector (almost all links there are dead)
http://www.tigase.org/content/tigase-server-binary-updates

It seems to be all links to the Trac source browser.