How to manually create a user in the database?

[mjnewton]

With reference to the auto_create_user setting, if this is set to false then one must ensure that an entry in Roundcube's local database is created for a user prior to them being able to log in.

Can anyone advise how I would do this? I know little about manual database manipulation (I am using an SQLite database) and even if I could learn enough from Googling to determine the right commands I am assuming I will also need some knowledge of the specific table(s) and their layout(?) in order to ensure I am populating the right fields (and enough of them to meet the minimum set required).

In case the context and rationale matters, whilst I could set this option to true and let users set themselves up (perhaps aided by the new_user_dialog plugin) I would prefer to do this for them to ensure everything is correct otherwise there'll be missing surnames, no first-letter capitalisation and other things that'll wind me up!

[Dv2Corn]

You only need one row in the users table for the login to be allowed, plus (recommended) one row in identities so the user has a From address. That is what Roundcube itself writes when auto_create_user is true.

What Roundcube checks at login

With auto_create_user = false, the login looks the user up by two columns, username and mail_host, and refuses the login if no row matches:

roundcubemail/program/include/rcmail.php

Line 773 in e46005c

if ($user = rcube_user::query($username, $host)) {

roundcubemail/program/include/rcmail.php

Lines 820 to 826 in e46005c

	}
	} else {
	self::raise_error([
	'code' => 621,
	'message' => "Access denied for new user {$username}. 'auto_create_user' is disabled",
	], true, false);
	}

So these two values have to be exactly what Roundcube computes:

• mail_host is the host name from your imap_host setting without the scheme and port. For imap_host = 'ssl://mail.example.com:993' it is mail.example.com.
  

  roundcubemail/program/include/rcmail.php

  Line 719 in e46005c

  [$host, $scheme, $port] = rcube_utils::parse_host_uri($host, 143, 993);

• username is the IMAP login name as typed, lowercased (default login_lc = 2), with username_domain appended if you have that option set. So if people log in as Jane@Example.com, store jane@example.com.
  

  roundcubemail/program/include/rcmail.php

  Line 692 in e46005c

  $login_lc = $this->config->get('login_lc', 2);

The other columns can be left at their defaults. Roundcube itself stores a:0:{} (an empty serialized array) in preferences:

roundcubemail/program/lib/Roundcube/rcube_user.php

Lines 651 to 659 in e46005c

	$insert = $dbh->query(
	'INSERT INTO ' . $dbh->table_name('users', true)
	. ' (`created`, `last_login`, `username`, `mail_host`, `language`, `preferences`)'
	. ' VALUES (' . $dbh->now() . ', ' . $dbh->now() . ', ?, ?, ?, ?)',
	$data['user'],
	$data['host'],
	$data['language'],
	serialize($data['preferences'])
	);

Table layout:

roundcubemail/SQL/sqlite.initial.sql

Lines 7 to 19 in e46005c

	CREATE TABLE users (
	user_id integer NOT NULL PRIMARY KEY,
	username varchar(128) NOT NULL default '',
	mail_host varchar(128) NOT NULL default '',
	created datetime NOT NULL default '0000-00-00 00:00:00',
	last_login datetime DEFAULT NULL,
	failed_login datetime DEFAULT NULL,
	failed_login_counter integer DEFAULT NULL,
	language varchar(16),
	preferences text DEFAULT NULL
	);
	CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host);

and

roundcubemail/SQL/sqlite.initial.sql

Lines 85 to 99 in e46005c

	CREATE TABLE identities (
	identity_id integer NOT NULL PRIMARY KEY,
	user_id integer NOT NULL
	REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
	changed datetime NOT NULL default '0000-00-00 00:00:00',
	del tinyint NOT NULL default '0',
	standard tinyint NOT NULL default '0',
	name varchar(128) NOT NULL default '',
	organization varchar(128) default '',
	email varchar(128) NOT NULL default '',
	"reply-to" varchar(128) NOT NULL default '',
	bcc varchar(128) NOT NULL default '',
	signature text NOT NULL default '',
	html_signature tinyint NOT NULL default '0'
	);

SQL for SQLite

Make a copy of the database file first (the path is in db_dsnw in config/config.inc.php), then open it with sqlite3 /path/to/roundcube.db and run:

INSERT INTO users (username, mail_host, created, language, preferences)
VALUES ('jane@example.com', 'mail.example.com', datetime('now'), 'en_US', 'a:0:{}');

INSERT INTO identities (user_id, changed, standard, name, email)
SELECT user_id, datetime('now'), 1, 'Jane Doe', 'jane@example.com'
FROM users
WHERE username = 'jane@example.com' AND mail_host = 'mail.example.com';

user_id is filled in automatically. language can also be left out (NULL); then the user gets the default language. standard = 1 marks the identity as the default one. If you skip the identity, the user can log in, but has no sender address and sending fails with "Missing email address in selected identity" until they add one under Settings > Identities.

To check what you created:

SELECT user_id, username, mail_host FROM users;
SELECT identity_id, user_id, standard, name, email FROM identities;

Alternative for the identity part

On 1.7 and later there is a script for identities, so only the users row needs SQL:

bin/identity.sh add -u jane@example.com -e jane@example.com -n 'Jane Doe' -S 1
bin/identity.sh list -u jane@example.com

(-S takes 1/0; -S true is rejected.) It finds the user by username and the host from imap_host, the same way the login does. If you have more than one entry in imap_host, add --host=mail.example.com (the long form; -h means something else in this script).

I tested the above against a fresh database created from SQL/sqlite.initial.sql on current master with auto_create_user = false: a user inserted this way can log in (also when typing the address in mixed case), a user without a row gets "Access denied for new user ... 'auto_create_user' is disabled" in the log, and the identity shows up as the default one.

[mjnewton]

Oh wow, thank you for such a quick and comprehensive response. Your explanation of the background and logic is also really appreciated as it helps me understand the why and not just the how.

I will give it a try later!

[mjnewton]

It worked perfectly - thank you again!