iterate_query only works with
userdb { driver = sql args = /path/to/auth-sql.conf.ext }
Aki
On 02/12/2023 00:02 EET John Stoffel john@stoffel.org wrote:
"John" == John Stoffel john@stoffel.org writes:
Do I think I'm on the right track here, since I removed the following from /etc/dovecot/conf.d/auth-sql.conf.ext
#userdb { # driver = static # args = uid=mail gid=mail home=/var/mail/%d/%n #}So now my error is as follows:
# doveadm user -u '*' Error: auth-master: userdb list: User listing returned failure Fatal: user listing failedBecause now when I restart dovecot, I see the following in the log:
Dec 01 16:55:14 master: Info: Dovecot v2.3.21 (47349e2482) starting up for imap, lmtp, sieve (core dumps disabled) Dec 01 16:55:14 auth: Warning: sql: Ignoring changed iterate_query in /etc/dovecot/dovecot-sql.conf.ext, because userdb sql not used. (If this is intentional, set userdb_warning_disable=yes) Dec 01 16:55:14 auth: Error: auth-master client: Trying to iterate users, but userdbs don't support it (created 0 msecs ago, handshake 0 msecs ago)So I commented out my 'iterate_query = ...' (see below) from /etc/postfix/dovecot-sql.conf.ext and now I get the error on startup which says:
Dec 01 16:57:42 master: Info: Dovecot v2.3.21 (47349e2482) starting up for imap, lmtp, sieve (core dumps disabled) Dec 01 16:57:42 auth: Error: auth-master client: Trying to iterate users, but userdbs don't support it (created 0 msecs ago, handshake 0 msecs ago) Dec 01 16:57:42 replicator: Error: auth-master: userdb list: User listing returned failure Dec 01 16:57:42 replicator: Error: listing users failed, can't replicate existing data
Which tells me I need the iteracte_users setting, but I've got a bogus query in there. So I think I should be using something like this:
iterate_query = SELECT email AS user from virtual_users;
where 'virtual_users' is the one and only table in my sqlite db file. And I'm just returning the 'email' column as 'user', since that's what it seems to expect.
Hmmm...
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 }
dovecot mailing list -- dovecot@dovecot.org To unsubscribe send an email to dovecot-leave@dovecot.org
dovecot mailing list -- dovecot@dovecot.org To unsubscribe send an email to dovecot-leave@dovecot.org