Roundcube passwords

[therentabrain]

Hi!
Some of the users I am supporting with AMS are using Roundcube to access their mail via IMAP, some of the time.
The Password plugin for Roundcube speaks a lot of protocols to change passwords. Is there a way to allow it to change a user's password?

The list of protocols (ignore the numbering):

Code: Select all

2.1.1.  Database (sql)
2.1.2.  Cyrus/SASL (sasl)
2.1.3.  Poppassd/Courierpassd (poppassd)
2.1.4.  LDAP (ldap)
2.1.5.  DirectAdmin Control Panel (directadmin)
2.1.6.  cPanel
2.1.7.  XIMSS/Communigate (ximms)
2.1.8.  Virtualmin (virtualmin)
2.1.9.  hMailServer (hmail)
2.1.10. PAM (pam)
2.1.11. Chpasswd (chpasswd)
2.1.12. LDAP - no PEAR (ldap_simple)
2.1.13. XMail (xmail)
2.1.14. Pw (pw_usermod)
2.1.15. domainFACTORY (domainfactory)
2.1.16. DBMail (dbmail)
2.1.17. Expect (expect)
2.1.18. Samba (smb)
2.1.19. Vpopmail daemon (vpopmaild)
2.1.20. Plesk (Plesk RPC-API)
2.1.21. Kpasswd
2.1.22. Modoboa
2.1.23. LDAP - Password Modify Extended Operation (ldap_exop)
2.1.24. TinyCP
2.1.25. Mail-in-a-Box (miab)
2.1.26. HTTP-API (httpapi)
2.1.27. dovecot_passwdfile
2.1.28. Mailcow

Currently I believe that AMS is using INI files, not ODBC, and LDAP is not turned on. But these things can be changed if it helps solve the problem!

The only way I can think of is to convert the files to ODBC storage in SQL and use the SQL driver to talk to it. Would that work? Would AMS be fine with the password being changed while it's running? Is there a better way?

Edit: This worked fine when I tried it on a test server, though it seemed to require that I restart the server for it to refresh and notice. Please lmk if there's a solution there...

Thanks!

[Code Crafters]

You can edit the INI files but it probably is easier to convert your users to be stored using ODBC and update that database. You can use plain passwords then every 30 minutes by default (auto reload time) AMS will look for changes in INI or ODBC and re-import them as well as hashing the passwords using it's own algorithm.

[therentabrain]

Sounds great; I have started trying this and had an issue. Using v5.0.2, though we'll be upgrading soon. I got it hooked to MySQL. The CREATE TABLE statement used nullptr instead of NULL, which I figured out via logging; replacing nullptr with NULL worked fine.

But, now I can't change a user's password from the GUI, and users can't change their own passwords via the standard webmail. I also tried adding an alias to someone and got the same error. Anonymized example:

Code: Select all

UPDATE `amsusers` SET `pass`=_latin1'mypass' WHERE `domainname`=_latin1'mydomain.com' AND `username`=_latin1'user' AND `pass`=_latin1'>>rue<<123456' AND `ingroup`=_latin1'office' AND `dir`=_latin1'config\\accounts\\domain.com\\user\\' AND `enabled`= LIMIT 1

Note that enabled has no value after the =, before the LIMIT. The error shows up as an error near LIMIT. Is there something I can do, or is this stuff baked in the code and needs your help?

Thanks again. Looking forward to trying the new version this coming week!

[Code Crafters]

I've updated the code to use NULL instead of nullptr. Good catch; this is a coding change where C++ uses nullptr in place of NULL but we accidentally changed the ODBC table code too. This will be in the next release but if you need it, let me know and I'll put a small release out sooner.

Please see https://www.codecrafters.com/AbilityMai ... torialODBC for more details about using ODBC although I think you've already got past this anyway.

1. Did you manage to create the table and use the Test Database button in General -> ODBC?
2. Did users from your database show up in the UI?
3. When changing password or adding an alias what error do you get?

[therentabrain]

I've updated the code to use NULL instead of nullptr. Good catch; this is a coding change where C++ uses nullptr in place of NULL but we accidentally changed the ODBC table code too. This will be in the next release but if you need it, let me know and I'll put a small release out sooner.

That would be great. I think the missing value near LIMIT is a bigger problem (currently a show stopper) so a small fix would be needed there for sure.

1. Did you manage to create the table and use the Test Database button in General -> ODBC?
2. Did users from your database show up in the UI?
3. When changing password or adding an alias what error do you get?

Yes, I figured out the nullptr problem by turning on logging in MySQL and reading the log. Once I got the db set up, the Test button worked. Yes the users are in and when I make changes in the db, it changes in the UI after the refresh.

The error:

Code: Select all

Unable to update the record in the ODBC source. (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 'LIMIT 1' at line 1 )

As I pasted above, there is a value missing before the word LIMIT.

Thanks!

[Code Crafters]

I've logged a bug for us to update fix this. The SQL syntax works with SQL server but obviously not MySQL. We'll change it to be something that works for both.

[therentabrain]

Thank you!
And can I say, I love how responsive yall are with issues that have come up.

FWIW, I don't think any SQL software would like this:

Code: Select all

AND `enabled`= LIMIT 1

It's missing a value there. It should say enabled equals something (TRUE, FALSE, 0, 1, something before LIMIT).

*shrug* Good luck, I look forward to a fix! Thank you.

Edited to add: I'd love it if the admin could edit the SQL command strings as needed for various versions of backends, though I guess that might be a can o'worms.

[Code Crafters]

Yes, it definitely is. It will be a missing value that's substituted in. Leave it with us and we'll fix it.

[therentabrain]

Hi! Just checking, any idea when a patch or update / fix might be ready?

Thanks!

[Code Crafters]

We looked into this and it's not as simple as it first appeared. We actually use a built in C++ library called afxdb to do the ODBC commands. We override this but it generates all the SQL not us. The fact that your enabled field didn't have a value is very strange. Please check your database table to make sure that a database column called 'enabled' exists and is of type BIT. You may want to try deleting the table (or at least renaming it) and recreating the table before moving your INI files to ODBC via the General settings -> ODBC tab to see if this recreates the table any differently and hopefully resolving the issue. If not, you may want to try a different SQL provider such as SQL server or others.

[therentabrain]

For sure, there is indeed that field, and it is type BIT. But even if that field were not present or were defined wrong, the SQL that AMS is sending is broken. I don't think there is a SQL server in the world that would be able to run it.

* Stop the presses *
I did some research and solved it. The trick is to change the BIT field into a TINYINT.

I realized reading the logs that every time AMS makes the change, it looks at the fields - I assume in order to ascertain the structure and field types. So the UPDATE query is constructed based on the field types it gets back. I thought perhaps by changing the field type, it would be able to phrase the UPDATE properly.

MySQL stores 'b' values in bit fields, not a simple 0 or 1 that you might expect. Changing it to TINYINT meant it could now accept a 0 or 1 no problem. AMS now seems to store in MySQL just fine.

It still required changing "nullptr" to NULL so that's still worth fixing sometime. Or you could make the SQL create table stuff copy/pastable so the user can adapt it as needed.

Anyway, wish I'd thought of this sooner.

[Code Crafters]

Glad you managed to fix this. As you say, the library we use for ODBC dynamically generates the SQL based on the schema and possibly a newer version of that library would have handled BIT fields correctly but at least we know this now.

The nullptr issue has been fixed in the code and will be included in the next release. This was caused by a find / replace in the c++ code to use nullptr instead of NULL for a newer version of the language but we missed that this updated the SQL generated NULL literals as well.