Hi all, I've been pounding my head against the sand for a while here trying to figure out why I can't get:
doveadm user '*' working properly. I've got a Debian 11 VPS runnig dovecot version 2.3.21-1+debian10 and it works great. But not I'm trying to add in simple replication to a home dovecot instance over a wireguard tunnel so I can do backups and have a little better resiliency. Maybe.
In any case, my sqlite schema looks like this:
sqlite> .schema virtual_users
CREATE TABLE `virtual_users` (
`id` integer NOT NULL PRIMARY KEY AUTOINCREMENT
, `domain_id` integer NOT NULL
, `password` varchar(106) NOT NULL
, `email` varchar(100) NOT NULL
, UNIQUE (`email`)
, CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`) REFERENCES `virtual_domains` (`id`) E
);
CREATE INDEX "idx_virtual_users_domain_id" ON "virtual_users" (`domain_id`);and I don't have any other tables. The 'domain_id' was/is a leftover from my thinking I needed it for extra testing of other domains and such.
I can do 'doveadm user john@stoffel.org' and it works just fine. When I do "doveadm user '*'" it fails and I get:
doveadm user '*'
Error: auth-master: userdb list: User listing returned failure
Fatal: user listing failedSo my config looks like this:
root@mail:/etc/dovecot/conf.d# cat auth-sql.conf.ext
# Authentication for SQL users. Included from 10-auth.conf.
#
#
passdb { driver = sql
# Path for SQL configuration file, see
example-config/dovecot-sql.conf.ext
args = /etc/dovecot/dovecot-sql.conf.ext}
userdb { driver = static args = uid=mail gid=mail home=/var/mail/%d/%n }
My /etc/dovecot/dovecot-sql.conf.ext has the following:
driver = sqlite connect = /etc/dovecot/private/virtual_users.sqlite3
default_pass_scheme = SHA512-CRYPT
password_query = SELECT '/var/mail/%d/%u' AS userdb_home, 'mail' AS userdb_uid, 'mail' AS userdb_gid, email as user, password FROM virtual_users WHERE email='%u';
iterate_query = SELECT email AS user from virtual_users;
And my general doveadm config output is this, slightly edited down to remove stuff I don't think I need to show is at the end. Any hints on what I've done wrong here? Do I need a more complete sqlite3 schema? I wish I could get more debugging info on what query it's trying to run and the error(s) it's getting.
Thanks, John
# 2.3.21 (47349e2482): /etc/dovecot/dovecot.conf # Pigeonhole version 0.5.21 (f6cd4b8e) # OS: Linux 5.10.0-26-amd64 x86_64 Debian 11.8 ext4 # Hostname: localhost # NOTE: Send doveconf -n output instead when asking for help. auth_anonymous_username = anonymous auth_cache_negative_ttl = 1 hours auth_cache_size = 0 auth_cache_ttl = 1 hours auth_cache_verify_password_with_worker = no auth_debug = no auth_debug_passwords = no auth_failure_delay = 2 secs auth_gssapi_hostname = auth_krb5_keytab = auth_master_user_separator = auth_mechanisms = plain login auth_policy_check_after_auth = yes auth_policy_check_before_auth = yes auth_policy_hash_mech = sha256 auth_policy_hash_nonce = auth_policy_hash_truncate = 12 auth_policy_log_only = no auth_policy_reject_on_fail = no auth_policy_report_after_auth = yes auth_policy_request_attributes = login=%{requested_username} pwhash=%{hashed_password} remote=%{rip} device_id=%{client_id} protocol=%s session_id=%{session} auth_policy_server_api_header = auth_policy_server_timeout_msecs = 2000 auth_policy_server_url = auth_proxy_self = auth_realms = auth_socket_path = auth-userdb auth_ssl_require_client_cert = no auth_ssl_username_from_cert = no auth_stats = no auth_use_winbind = no auth_username_chars = abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890.-_@ auth_username_format = %Lu auth_username_translation = auth_verbose = no auth_verbose_passwords = no auth_winbind_helper_path = /usr/bin/ntlm_auth auth_worker_max_count = 30 base_dir = /run/dovecot config_cache_size = 1 M debug_log_path = default_client_limit = 1000 default_idle_kill = 1 mins default_internal_group = dovecot default_internal_user = dovecot default_login_user = dovenull default_process_limit = 100 default_vsz_limit = 256 M deliver_log_format = msgid=%m: %$ dict_db_config = disable_plaintext_auth = yes dotlock_use_excl = yes doveadm_allowed_commands = doveadm_api_key = doveadm_http_rawlog_dir = doveadm_password = doveadm_port = 0 doveadm_socket_path = doveadm-server doveadm_ssl = no doveadm_username = doveadm doveadm_worker_count = 0 first_valid_gid = 1 first_valid_uid = 0 import_environment = TZ CORE_OUTOFMEM CORE_ERROR LISTEN_PID LISTEN_FDS NOTIFY_SOCKET info_log_path = libexec_dir = /usr/lib/dovecot listen = * log_core_filter = log_debug = log_path = /var/log/dovecot.log log_timestamp = "%b %d %H:%M:%S " mail_access_groups = mail_always_cache_fields = mail_attachment_detection_options = mail_attachment_dir = mail_attachment_fs = sis posix mail_attachment_hash = %{sha1} mail_attachment_min_size = 128 k mail_attribute_dict = mail_cache_fields = flags mail_chroot = mail_debug = no mail_fsync = optimized mail_full_filesystem_access = no mail_gid = mail_home = mail_location = maildir:/var/mail/%d/%n/Maildir mail_log_prefix = "%s(%u)<%{pid}><%{session}>: " mail_max_keyword_length = 50 mail_max_lock_timeout = 0 mail_max_userip_connections = 10 mail_never_cache_fields = imap.envelope mail_nfs_index = no mail_nfs_storage = no mail_plugin_dir = /usr/lib/dovecot/modules mail_plugins = " notify replication" mail_prefetch_count = 0 mail_privileged_group = mail mail_save_crlf = no mail_server_admin = mail_server_comment = mail_shared_explicit_inbox = no mail_sort_max_read_count = 0 mail_temp_dir = /tmp mail_temp_scan_interval = 1 weeks mail_uid = mail_vsize_bg_after_count = 0 passdb { args = /etc/dovecot/dovecot-sql.conf.ext auth_verbose = default default_fields = deny = no driver = sql master = no mechanisms = name = override_fields = pass = no result_failure = continue result_internalfail = continue result_success = return-ok skip = never username_filter = } protocols = imap lmtp sieve recipient_delimiter = +_ service auth-worker { chroot = client_limit = 1 drop_priv_before_exec = no executable = auth -w extra_groups = group = idle_kill = 0 privileged_group = process_limit = 0 process_min_avail = 0 protocol = service_count = 0 type = worker unix_listener auth-worker { group = mode = 0600 user = $default_internal_user } user = mail vsz_limit = 18446744073709551615 B } service auth { chroot = client_limit = 0 drop_priv_before_exec = no executable = auth extra_groups = group = idle_kill = 0 privileged_group = process_limit = 1 process_min_avail = 0 protocol = service_count = 0 type = unix_listener /var/spool/postfix/private/auth { group = postfix mode = 0666 user = postfix } unix_listener auth-client { group = mode = 0600 user = $default_internal_user } unix_listener auth-login { group = mode = 0600 user = $default_internal_user } unix_listener auth-master { group = mode = 0600 user = } unix_listener auth-userdb { group = mode = 0666 user = mail } unix_listener login/login { group = mode = 0666 user = } unix_listener token-login/tokenlogin { group = mode = 0666 user = } user = dovecot vsz_limit = 18446744073709551615 B } service doveadm { chroot = client_limit = 1 drop_priv_before_exec = no executable = doveadm-server extra_groups = $default_internal_group group = idle_kill = 0 privileged_group = process_limit = 0 process_min_avail = 0 protocol = service_count = 1 type = unix_listener doveadm-server { group = mode = 0600 user = } user = vsz_limit = 18446744073709551615 B } service log { chroot = client_limit = 0 drop_priv_before_exec = no executable = log extra_groups = group = idle_kill = 4294967295 secs privileged_group = process_limit = 1 process_min_avail = 0 protocol = service_count = 0 type = log unix_listener log-errors { group = mode = 0600 user = } user = vsz_limit = 18446744073709551615 B } state_dir = /var/lib/dovecot stats_http_rawlog_dir = syslog_facility = mail userdb { args = uid=mail gid=mail home=/var/mail/%d/%n auth_verbose = default default_fields = driver = static name = override_fields = result_failure = continue result_internalfail = continue result_success = return-ok skip = never }