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 <j...@stoffel.org> wrote:
>
>
> >>>>> "John" == John Stoffel <j...@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 failed
>
> Because 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 j...@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 failed
>
>
> > So 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.
> > #
> > # <doc/wiki/AuthDatabase.SQL.txt>
>
> > 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-le...@dovecot.org
> _______________________________________________
> dovecot mailing list -- dovecot@dovecot.org
> To unsubscribe send an email to dovecot-le...@dovecot.org
_______________________________________________
dovecot mailing list -- dovecot@dovecot.org
To unsubscribe send an email to dovecot-le...@dovecot.org
