MySQL backend for authentication

[amardeep-s]

I am looking at setting up MySQL backend using synapse+mxisd+mysql. Going through documentation can't seem to find any reference for MySQL settings. It only says type: sqlite or postgresql

Though in application.example it says ###############

SQL Backend

###############

If you would like to integrate with a MySQL/MariaDB/PostgreQL/SQLite DB,

see https://github.com/kamax-io/mxisd/blob/master/docs/backends/sql.md

Do we have any example how to achieve that if supported?

[maxidorius]

I didn't add MySQL support as it's not something Synapse supports so it makes little sense but that's something I can do very easily.

Due to the complexity of getting authentication working correctly with a SQL database (how to deal with the password? is it a hash? it is crypted? etc.), the SQL Identity store only does Directory and Identity, which might not make much sense on its own. You would typically have that SQL database used by another application. In that case, you would use the REST Identity store, or maybe a new Identity store.

Could you go in more details in what you're trying to achieve, including exact technologies, so I can get a better sense of your needs and offer the best solution?

[viktara]

I'm working on the same issue along with @amardeep-s. We have an existing application that stores it's user credentials in MySQL - so we're looking to authenticate users on synapse with those same credentials. The passwords are hashed and encrypted.

We could sync our mysql user information into LDAP, but when we noticed your sql support it could potentially save us some time.

[maxidorius]

@viktara I'll be happy to provide a MySQL integration but writing the authentication would be up to you via an external Java class (in its own jar). I am not willing (yet) to support some hashing/encryption mechanims directly in mxisd. Would you be happy with that?

Or I could also write an Identity provider which run a regular command like in a shell and get result out of that, if that could be of use to you via some kind of script or an existing CLI command?

[maxidorius]

@amardeep-s @viktara any update on this on your end?

[Rohithzr]

@amardeep-s @viktara even if there is mysql support, there is no plug and play solution for authenticating the users with IS instead of HS. If there is can you share that?

[maxidorius]

@amardeep-s @viktara I'll close this for now. Please re-open with answers to my previous questions if you are still interested in this.

[maxidorius]

@Iratu your question spans three different topics, each to be solved on its own:

• Single Sign-on
• MySQL integration
• Support for most common and/or arbitrary encryption/hashing mechanism when validating credentials

Let's handle those one by one in order of difficulty:

MySQL integration

Nothing difficult here. Dealing with a SQL backend is already dealt with so it's only a matter of adding the right library into mxisd. At this point, it would only handle the Directory and Identity features.

Credentials validation

This is slightly more tricky because credentials should never be stored in plaintext. This means the application that validates the credentials needs to know how those are stored and handle it properly. For well-known hashing (MD5, SHA-1, SHA-256, etc.) this is not a problem. But once you add a salt, or if you use encryption in the process, this is very much a non-trivial process as you can't just use configuration.

This is typically where the REST identity store is a better fit since you can integrate with the credentials integration of the existing app (like your website per example) and re-use existing code and workflow.

Single Sign-on

This is the technical term for the process that allows your user to only login once and re-use that login session across applications. While this process seems "magic", it is actually a multi-steps handshake involving the client and the server(s). mxisd is compatible with such setup and contains the Google Firebase Identity store which is a Single Sign-on implementation. This kind of setup involves changes to the client (Riot, usually).

---

So to finally answer your question:

Would this be possible with using this system and adding basic MySQL support?

Yes, but only if you use basic hashing. Salt support might be possible, but I would need to look into it. My advise would be to look into the REST identity store instead.