[SOLVED] how to manually create roundcubemail user

tshikose · 12-11-2018, 09:11 AM

Hi,

I have a Linux box running successfully an email solution relying on:
CentOS 7
postfix-2.10.1-6.el7.x86_64
dovecot-2.2.10-8.el7.x86_64
mariadb-server-5.5.60-1.el7_5.x86_64
roundcubemail-1.1.12-2.el7.noarch

It works well with roundcubemail default of
$config['auto_create_user'] = true;

But I actually want to control the way users are created for roundcubemail in the database presetting some fields.
So I have first disabled the auto creation with
$config['auto_create_user'] = false;

Then I manually run the below SQL to create a user before its first login on roundcubemail.

Code:

mysql -u root -p -e " INSERT INTO users (username, mail_host, language) VALUES ('jsmith', '127.0.0.1', 'fr_FR'); " webmail
mysql -u root -p -e " SELECT user_id FROM users WHERE username = 'jsmith'; " webmail
mysql -u root -p -e " INSERT INTO identities (user_id, standard, name, email) VALUES ( New_User_ID, 1, 'John Smith', 'jsmith@example.com'); " webmail

After that, unfortunately the user is enable to login on roundcubemail webmail interface.
The /var/log/roundcubemail/errors.log thraws errors such below

Code:

[11-Dec-2018 14:23:45 +0100]: <ne6qfvr2> PHP Error: Access denied for new user jsmith. 'auto_create_user' is disabled in /usr/share/roundcubemail/program/include/rcmail.php on line 624 (POST /webmail/?_task=login?_task=login&_action=login)

Obviously the used password is correct, as the username is able to log in through SSH.

At this stage I do suppose that my 3 SQL statements above are not enough to replicate what the auto_create_user does when set to true.
Any ideas what I am missing?
Or how are we supposed to manually create user in roundcubemail?

scasey · 12-12-2018, 06:28 PM

Roundcube appears to be a web-based IMAP client. Yes?

Typically, mail clients log in with the credentials of the OS/MTA email user...so any user of (for example) postfix mail should be able to log into the webmail interface.

I wouldn't expect (and don't see, in a quick scan of the documentation) any email client to be able to create a user. Have you reviewed the documentation at roundcube.net

PS The error is pretty clear. Looks like you need to enable auto_create_user

I'd do that, then compare the entries it creates with the entries you're creating...
(not sure what you're trying to accomplish, tho. Why not just use the software as it's designed to be used?)

tshikose · 12-14-2018, 04:20 AM

Hi Sean,

Thanks for taking time to reply to me.

I thought that I had explained clearly the purpose I am pursuing: I want to control the way user are created in the database by preseting some of the fields.

My understanding of the documentation is that auto_create_user does what its name intents: auto create users.
So it should be possible to "manually" create them as well.
I am exactly after how to do that "manually".

Any help?

tshikose · 12-10-2021, 04:13 AM

Dear All,

I was still having the same problem, and I browsed the Net to find a viable solution, and I came accros my old unsolved post.
So this time I ensured that better did my homework.

I will share here the solution I applied that fulfilled my goal.
It might interest some else.

I first want to say that I worked with the recent versions of those involved programs, and I did not attempted to see if a similar solution could have been devised for the old version I used in the past.

My actual environment is as below
Rocky Linux 8.5
postfix 3.5
dovecot 2.3
MariaDB 10.5
roundcubemail 1.4.12
roundcube password plugin configured to interact directly with the database table to keeps the sha512-crypt encrypted user passwords.

It appeared that the relevant settings to have in roundcubemail are two below
$config['auto_create_user'] = false;
$config['user_aliases'] = true;
$config['password_force_new_user'] = true;

With the above, obviously new users will not be auto created, and they will be able to log in with identities present in the identities table. But still, those new users will have to exist in the virtual emails table with valid password.
The last one will force new users to change their passwords at next login and prevent them to perform any other action before.

So with the below bash commands I am able to easily automate the provisioning of users and allow a valid user to log in on roundcubemail.

Code:

DVL_Virtual_Local_Part='foobar'
DVL_Virtual_Domain_Name='example.com'
DVL_Password_MariaDB_roundcube='roundcube_password'

DVL_Virtual_User_ID="$( echo "INSERT INTO users (username, mail_host, created, preferences) VALUES ('${DVL_Virtual_Local_Part,,}@${DVL_Virtual_Domain_Name,,}', 'localhost', NOW(), 'a:1:{s:15:\"newuserpassword\";b:1;}') RETURNING user_id" | mariadb -ss --user=roundcube --password="${DVL_Password_MariaDB_roundcube}" roundcube | tee /dev/tty )"

DVL_Virtual_Identity_ID="$( echo "INSERT INTO identities (user_id, email, name, organization, standard, changed) VALUES (${DVL_Virtual_User_ID}, '${DVL_Virtual_Local_Part,,}@${DVL_Virtual_Domain_Name,,}', '${DVL_Virtual_Local_Part^}', '${DVL_Virtual_Domain_Name^}', 1, NOW()) RETURNING identity_id" | mariadb -ss --user=roundcube --password="${DVL_Password_MariaDB_roundcube}" roundcube | tee /dev/tty )"

In order to not post a long reply to this old thread, I have occulted some aspects and configurations that one might stumble across when trying to reuse what I present here for example.
Just let me know and I will be glad to help if I can.

Finally, I will mark this thread as SOLVED.